Mysql下建立使用者授權許可權例子

建立使用者，授權資料庫：

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許可權，USAGE表示串連許可權
+-------------------------------------------------------------------------------------------------------------+
| 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對資料庫gbk擁有所有許可權
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資料庫所有許可權，同上
mysql> show grants for 'test'@'localhost';
+-------------------------------------------------------------------------------------------------------------+
| Grants for test@localhost                                                                                   |
+-------------------------------------------------------------------------------------------------------------+
| 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'@'授權可串連主機' identified by 'admin123';    #這是Server端
[root@lamp ~]# /usr/local/mysql/bin/mysql -uroot -p'admin123' -h hk.t4x.org    #這是client端

補充：ALL PRIVILEGES許可權包括：

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許可權包括：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