用戶授權在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