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

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

建立用户,授权数据库:

1. mysql> create user 'byrd'@'localhost' identified by 'admin123';#建立主机为localhost，密码为admin123的用户byrd 
2. Query OK, 0 rows affected (0.05 sec) 
3. mysql> show grants for 'byrd'@'localhost';#查看byrd权限，USAGE表示连接权限 
4. +-------------------------------------------------------------------------------------------------------------+ 
5. | Grants for byrd@localhost                                                                                   | 
6. +-------------------------------------------------------------------------------------------------------------+ 
7. | GRANT USAGE ON *.* TO 'byrd'@'localhost' IDENTIFIED BY PASSWORD '*01A6717B58FF5C7EAFFF6CB7C96F7428EA65FE4C' | 
8. +-------------------------------------------------------------------------------------------------------------+ 
9. 1 row in set (0.00 sec) 
10. mysql> select user,host from mysql.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. 5 rows in set (0.00 sec) 
21. mysql> grant all on gbk.* to 'byrd'@'localhost';    #用户byrd、主机localhost对数据库gbk拥有所有权限 
22. Query OK, 0 rows affected (0.01 sec) 
23. mysql> show mysqls for 'byrd'@'localhost'; 
24. +-------------------------------------------------------------------------------------------------------------+ 
25. | Grants for byrd@localhost                                                                                   | 
26. +-------------------------------------------------------------------------------------------------------------+ 
27. | GRANT USAGE ON *.* TO 'byrd'@'localhost' IDENTIFIED BY PASSWORD '*01A6717B58FF5C7EAFFF6CB7C96F7428EA65FE4C' | 
28. | GRANT ALL PRIVILEGES ON `gbk`.* TO 'byrd'@'localhost'                                                       | 
29. +-------------------------------------------------------------------------------------------------------------+ 
30. 2 rows in set (0.00 sec) 
31. mysql> flush privileges; 
32. mysql> grant all on gbk.* to test@'localhost' identified by 'admin123';    #建立用户test，用户gbk数据库所有权限，同上 
33. mysql> show grants for 'test'@'localhost'; 
34. +-------------------------------------------------------------------------------------------------------------+ 
35. | Grants for test@localhost                                                                                   |  --Vevb.com 
36. +-------------------------------------------------------------------------------------------------------------+ 
37. | GRANT USAGE ON *.* TO 'test'@'localhost' IDENTIFIED BY PASSWORD '*01A6717B58FF5C7EAFFF6CB7C96F7428EA65FE4C' | 
38. | GRANT ALL PRIVILEGES ON `gbk`.* TO 'test'@'localhost'                                                       | 
39. +-------------------------------------------------------------------------------------------------------------+ 
40. 2 rows in set (0.00 sec) 
41. Query OK, 0 rows affected (0.00 sec) 

远程连接同上:

1. mysql> grant all on gbk.* to 'user'@'授权可连接主机' identified by 'admin123';    #这是Server端 
2. [root@lamp ~]# /usr/local/mysql/bin/mysql -uroot -p'admin123' -h hk.t4x.org    #这是client端 

补充:ALL PRIVILEGES权限包括:

1. mysql> show grants for 'byrd'@'localhost'; 
2. +-------------------------------------------------------------------------------------------------------------+ 
3. | Grants for byrd@localhost                                                                                   | 
4. +-------------------------------------------------------------------------------------------------------------+ 
5. | GRANT USAGE ON *.* TO 'byrd'@'localhost' IDENTIFIED BY PASSWORD '*01A6717B58FF5C7EAFFF6CB7C96F7428EA65FE4C' | 
6. | GRANT ALL PRIVILEGES ON `gbk`.* TO 'byrd'@'localhost'                                                       | 
7. +-------------------------------------------------------------------------------------------------------------+ 
8. mysql> revoke insert on `gbk`.* from 'byrd'@'localhost'; 
9. Query OK, 0 rows affected (0.00 sec) 
10. mysql> show grants for byrd@'localhost'; 
11. +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 
12. | Grants for byrd@localhost                                                                                                                                                                                                   | 
13. +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 
14. | GRANT USAGE ON *.* TO 'byrd'@'localhost' IDENTIFIED BY PASSWORD '*01A6717B58FF5C7EAFFF6CB7C96F7428EA65FE4C'                                                                                                                 | 
15. | GRANT SELECT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `gbk`.* TO 'byrd'@'localhost' | 
16. +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 
17. ALL PRIVILEGES权限包括：GRANT SELECT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT,insert