mysql> create user 'byrd'@'localhost' identified by 'admin123';#建立主機為localhost，密碼為admin123的用戶byrd Query OK, 0 rows affected (0.05 sec) mysql> show grants for 'byrd'@'localhost';#查看byrd權(quán)限，USAGE表示連接權(quán)限 +-------------------------------------------------------------------------------------------------------------+ | Grants for byrd@localhost | +-------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'byrd'@'localhost' IDENTIFIED BY PASSWORD '*01A6717B58FF5C7EAFFF6CB7C96F7428EA65FE4C' | +-------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> select user,host from mysql.user; +------+-----------+ | user | host | +------+-----------+ | root | 127.0.0.1 | | root | ::1 | | root | lamp | | byrd | localhost | | root | localhost | +------+-----------+ 5 rows in set (0.00 sec) mysql> grant all on gbk.* to 'byrd'@'localhost'; #用戶byrd、主機localhost對數(shù)據(jù)庫gbk擁有所有權(quán)限 Query OK, 0 rows affected (0.01 sec) mysql> show mysqls for 'byrd'@'localhost'; +-------------------------------------------------------------------------------------------------------------+ | Grants for byrd@localhost | +-------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'byrd'@'localhost' IDENTIFIED BY PASSWORD '*01A6717B58FF5C7EAFFF6CB7C96F7428EA65FE4C' | | GRANT ALL PRIVILEGES ON `gbk`.* TO 'byrd'@'localhost' | +-------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) mysql> flush privileges; mysql> grant all on gbk.* to test@'localhost' identified by 'admin123'; #建立用戶test，用戶gbk數(shù)據(jù)庫所有權(quán)限，同上 mysql> show grants for 'test'@'localhost'; +-------------------------------------------------------------------------------------------------------------+ | Grants for test@localhost | --phpfensi.com +-------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'test'@'localhost' IDENTIFIED BY PASSWORD '*01A6717B58FF5C7EAFFF6CB7C96F7428EA65FE4C' | | GRANT ALL PRIVILEGES ON `gbk`.* TO 'test'@'localhost' | +-------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) 遠程連接同上:
mysql> grant all on gbk.* to 'user'@'授權(quán)可連接主機' identified by 'admin123'; #這是Server端 [root@lamp ~]# /usr/local/mysql/bin/mysql -uroot -p'admin123' -h hk.t4x.org #這是client端 補充:ALL PRIVILEGES權(quán)限包括:
mysql> show grants for 'byrd'@'localhost'; +-------------------------------------------------------------------------------------------------------------+ | Grants for byrd@localhost | +-------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'byrd'@'localhost' IDENTIFIED BY PASSWORD '*01A6717B58FF5C7EAFFF6CB7C96F7428EA65FE4C' | | GRANT ALL PRIVILEGES ON `gbk`.* TO 'byrd'@'localhost' | +-------------------------------------------------------------------------------------------------------------+ mysql> revoke insert on `gbk`.* from 'byrd'@'localhost'; Query OK, 0 rows affected (0.00 sec) mysql> show grants for byrd@'localhost'; +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Grants for byrd@localhost | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'byrd'@'localhost' IDENTIFIED BY PASSWORD '*01A6717B58FF5C7EAFFF6CB7C96F7428EA65FE4C' | | 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' | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ ALL PRIVILEGES權(quán)限包括：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。
