MySQL入门教程之用户管理(增加,删除,权限)

在mysql用户管理中主要讲述到了关于mysql用户增加,删除,设置密码,删除及权限的设置,各位有需要了解的朋友不防进入参考.

添加用户:本机访问权限:

1. mysql>GRANTALLPRIVILEGESON*.*TO'username
3. '@'localhost'
4. ->IDENTIFIEDBY'password'WITHGRANTOPTION;

远程访问权限:

1. mysql>GRANTALLPRIVILEGESON*.*TO'username'@'%'
3. ->IDENTIFIEDBY'password'WITHGRANTOPTION;

另外还有一种方法是直接Insert INTO user,注意这种方法之后需要 FLUSH PRIVILEGES 让服务器重读授权表,删除用户.

DROP USER user;限制用户资源

1. mysql>GRANTALLONcustomer.*TO'francis'@'localhost'
2. ->IDENTIFIEDBY'frank'--phpfensi.com
3. ->WITHMAX_QUERIES_PER_HOUR20
4. ->MAX_UPDATES_PER_HOUR10
5. ->MAX_CONNECTIONS_PER_HOUR5
6. ->MAX_USER_CONNECTIONS2;

设置用户密码:

命令行:shell> mysqladmin -u user_name -h host_name password "newpwd"或执行语句:mysql> SET PASSWORD FOR 'username'@'%'= PASSWORD('password');

如果只是更改自己的密码,则直接:mysql> SET PASSWORD = PASSWORD(‘password’);

在全局级别使用GRANT USAGE语句(在*.*)来指定某个账户的密码:mysql> GRANT USAGE ON *.* TO 'username'@'%'

IDENTIFIED BY 'password';

或直接修改MySQL库表:

1. mysql>UPDATEuserSETPassword=PASSWORD('bagel')
3. ->WHEREHost='%'ANDUser='francis';
5. mysql>FLUSHPRIVILEGES;

关于加密:

1. mysql>selectPASSWORD('password');
2. +-------------------------------------------+
3. |PASSWORD('password')|
4. +-------------------------------------------+
5. |*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19|
6. +-------------------------------------------+
7. 1rowinset(0.00sec)
8. mysql>selectMD5('hello');
9. +----------------------------------+
10. |MD5('hello')|
11. +----------------------------------+
12. |5d41402abc4b2a76b9719d911017c592|
13. +----------------------------------+
14. 1rowinset(0.00sec)
15. mysql>selectSHA1('abc');
16. ->'a9993e364706816aba3e25717850c26c9cd0d89d'

SHA1()是为字符串算出一个 SHA1 160比特检查和,如RFC 3174,安全散列算法,中所述.