Mysql下建立用户授权权限例子

用户授权在mysql中使用grant命令就可以了,我相信各位都会有了解过了,下面小编为各位介绍一个Mysql下建立用户授权权限例子,希望本文章对各位有帮助.

建立用户,授权数据库:

1. mysql>createuser'byrd'@'localhost'identifiedby'admin123';#建立主机为localhost，密码为admin123的用户byrd
2. QueryOK,0rowsaffected(0.05sec)
3. mysql>showgrantsfor'byrd'@'localhost';#查看byrd权限，USAGE表示连接权限
4. +-------------------------------------------------------------------------------------------------------------+
5. |Grantsforbyrd@localhost|
6. +-------------------------------------------------------------------------------------------------------------+
7. |GRANTUSAGEON*.*TO'byrd'@'localhost'IDENTIFIEDBYPASSWORD'*01A6717B58FF5C7EAFFF6CB7C96F7428EA65FE4C'|
8. +-------------------------------------------------------------------------------------------------------------+
9. 1rowinset(0.00sec)
10. mysql>selectuser,hostfrommysql.user;
11. +------+-----------+
12. |user|host|
13. +------+-----------+
14. |root|127.0.0.1|
15. |root|::1|
16. |root|lamp|
17. |byrd|localhost|
18. |root|localhost|
19. +------+-----------+
20. 5rowsinset(0.00sec)
21. mysql>grantallongbk.*to'byrd'@'localhost';#用户byrd、主机localhost对数据库gbk拥有所有权限
22. QueryOK,0rowsaffected(0.01sec)
23. mysql>showmysqlsfor'byrd'@'localhost';
24. +-------------------------------------------------------------------------------------------------------------+
25. |Grantsforbyrd@localhost|
26. +-------------------------------------------------------------------------------------------------------------+
27. |GRANTUSAGEON*.*TO'byrd'@'localhost'IDENTIFIEDBYPASSWORD'*01A6717B58FF5C7EAFFF6CB7C96F7428EA65FE4C'|
28. |GRANTALLPRIVILEGESON`gbk`.*TO'byrd'@'localhost'|
29. +-------------------------------------------------------------------------------------------------------------+
30. 2rowsinset(0.00sec)
31. mysql>flushprivileges;
32. mysql>grantallongbk.*totest@'localhost'identifiedby'admin123';#建立用户test，用户gbk数据库所有权限，同上
33. mysql>showgrantsfor'test'@'localhost';
34. +-------------------------------------------------------------------------------------------------------------+
35. |Grantsfortest@localhost|--phpfensi.com
36. +-------------------------------------------------------------------------------------------------------------+
37. |GRANTUSAGEON*.*TO'test'@'localhost'IDENTIFIEDBYPASSWORD'*01A6717B58FF5C7EAFFF6CB7C96F7428EA65FE4C'|
38. |GRANTALLPRIVILEGESON`gbk`.*TO'test'@'localhost'|
39. +-------------------------------------------------------------------------------------------------------------+
40. 2rowsinset(0.00sec)
41. QueryOK,0rowsaffected(0.00sec)

远程连接同上:

1. mysql>grantallongbk.*to'user'@'授权可连接主机'identifiedby'admin123';#这是Server端
2. [root@lamp~]#/usr/local/mysql/bin/mysql-uroot-p'admin123'-hhk.t4x.org#这是client端

补充:ALL PRIVILEGES权限包括:

1. mysql>showgrantsfor'byrd'@'localhost';
2. +-------------------------------------------------------------------------------------------------------------+
3. |Grantsforbyrd@localhost|
4. +-------------------------------------------------------------------------------------------------------------+
5. |GRANTUSAGEON*.*TO'byrd'@'localhost'IDENTIFIEDBYPASSWORD'*01A6717B58FF5C7EAFFF6CB7C96F7428EA65FE4C'|
6. |GRANTALLPRIVILEGESON`gbk`.*TO'byrd'@'localhost'|
7. +-------------------------------------------------------------------------------------------------------------+
8. mysql>revokeinserton`gbk`.*from'byrd'@'localhost';
9. QueryOK,0rowsaffected(0.00sec)
10. mysql>showgrantsforbyrd@'localhost';
11. +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
12. |Grantsforbyrd@localhost|
13. +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
14. |GRANTUSAGEON*.*TO'byrd'@'localhost'IDENTIFIEDBYPASSWORD'*01A6717B58FF5C7EAFFF6CB7C96F7428EA65FE4C'|
15. |GRANTSELECT,UPDATE,DELETE,CREATE,DROP,REFERENCES,INDEX,ALTER,CREATETEMPORARYTABLES,LOCKTABLES,EXECUTE,CREATEVIEW,SHOWVIEW,CREATEROUTINE,ALTERROUTINE,EVENT,TRIGGERON`gbk`.*TO'byrd'@'localhost'|
16. +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
17. ALLPRIVILEGES权限包括：GRANTSELECT,UPDATE,DELETE,CREATE,DROP,REFERENCES,INDEX,ALTER,CREATETEMPORARYTABLES,LOCKTABLES,EXECUTE,CREATEVIEW,SHOWVIEW,CREATEROUTINE,ALTERROUTINE,EVENT,insert