搞定linux上MySQL編程（五）：MySQL許可權管理，mysql許可權管理

搞定linux上MySQL編程（五）：MySQL許可權管理，mysql許可權管理
【著作權聲明：尊重原創，轉載請保留出處：blog.csdn.net/shallnet，文章僅供學習交流，請勿用於商業用途】
        mysql中提供了比較完整的安全/許可權管理系統，下面簡單介紹許可權的原理和使用。        mysql資料庫安裝完成啟動之後，能看當前資料庫列表可以看到一個mysql的資料庫：

mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || mysql              || test               |+--------------------+

該資料庫是MySQL用來儲存所有授權資訊，該資料庫由若干張資料表組成，具體資料表如下：

mysql> use mysqlDatabase changedmysql> show tables;+---------------------------+| Tables_in_mysql           |+---------------------------+| columns_priv              || db                        || event                     || func                      || general_log               || help_category             || help_keyword              || help_relation             || help_topic                || host                      || ndb_binlog_index          || plugin                    || proc                      || procs_priv                || servers                   || slow_log                  || tables_priv               || time_zone                 || time_zone_leap_second     || time_zone_name            || time_zone_transition      || time_zone_transition_type || user                      |+---------------------------+23 rows in set (0.00 sec)

在這些資料表中，涉及全縣控制的有5張表，分別是columns_priv（為某一張表中單個列進行存取權限設定） 、db（對給定資料庫的所有表設定存取權限）、host（對訪問ip地址進行許可權設定）、tables_priv（對使用者的預存程序進行許可權設定）、user（用於管理MySQL的使用者）等。在MySQL中採用兩個階段來進行許可權的存取與控制，這兩個階段為：1.當使用者發起串連時，MySQL伺服器先檢查user表，檢查方式是通過使用者名稱、密碼、主機的組合判斷使用者是否為授權使用者，不是則直接拒絕。2.若串連成功，對於提交的請求，MySQL將通過順序檢查db、host、tables_priv、columns_priv判斷是否存在存取權限。
        在MySQL中盡量不要使用超級使用者登入，因為這樣很容易帶來安全隱患，比較正確的方式是設定一個超級使用者，同時設定幾個普通使用者，這樣可以分層分級來實現資料的安全管理。        新增加使用者可以使用cerate user命令、使用grand授權、或向使用者表user中直接添加使用者記錄等方式來完成。

mysql> create user allen identified by 'allen' ; Query OK, 0 rows affected (0.39 sec)  <span style="font-family: 微軟雅黑;">      </span>

        查看當前所有使用者權限表：

mysql> select user,host,super_priv from user;+------+-----------------------+------------+| user | host                  | super_priv |+------+-----------------------+------------+| root | localhost             | Y          || root | localhost.localdomain | Y          || root | 127.0.0.1             | Y          ||      | localhost             | N          ||      | localhost.localdomain | N          || allen | %                     | N          |+------+-----------------------+------------+6 rows in set (0.00 sec)mysql> 

        刪除使用者可以使用drop陳述式完成，也可以使用revoke方式、或者使用delete語句刪除user表中對於user記錄來完成同樣工作。

mysql> drop user allen;Query OK, 0 rows affected (0.00 sec)mysql> select user,host,super_priv from user;+------+-----------------------+------------+| user | host                  | super_priv |+------+-----------------------+------------+| root | localhost             | Y          || root | localhost.localdomain | Y          || root | 127.0.0.1             | Y          ||      | localhost             | N          ||      | localhost.localdomain | N          |+------+-----------------------+------------+5 rows in set (0.00 sec)

使用grant語句為使用者授予許可權，其格式為：grant priv_set on dbname to username;其中priv_set為許可權集合，dbname是指資料庫物件，username為使用者。
現在來看看前面建立的使用者：

[root@localhost ~]# mysql -u allen -p -h 172.27.35.8Enter password: Welcome to the MySQL monitor.  Commands end with ; or \g.......mysql>mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || db_users           || mysql              || test               |+--------------------+4 rows in set (0.00 sec)mysql> usse db_users;ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'usse db_users' at line 1

發現使用者allen無法使用db_users資料庫，退出登陸後使用root使用者給allen賦予所有許可權。

mysql> grant all privileges on *.* to allen@localhost; 

再次使用allen登陸後：

[root@localhost ~]# mysql -u allen -p -h 172.27.35.8 Enter password: Welcome to the MySQL monitor.  Commands end with ; or \g.......mysql> use db_users;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> 

發現可以使用資料庫了。        事實上對於新建立的使用者，如果沒有獲得授權，是無法進行資料管理工作的。在實際應用中，grant可以執行使用者建立，也可以對多使用者在多個層級上進行許可權管理，分別是，全域級、資料庫級、資料表級以及欄位級。下面分別從這4個層級對grant的使用進行介紹。1.全域許可權分配，可以為某個建立使用者指派全部操作許可權，這些許可權儲存在mysql.user表中，grant all privileges on *.* to username@ '%'該語句能授權username使用者在任意一台主機上對資料庫伺服器進行管理。雖然username擁有全部系統管理權限，但並沒有為其自身分配再授予許可權，所以不能為新建立的使用者指派任何許可權。以上面allen為例：

[root@localhost ~]# mysql -u allen -p -h 172.27.35.8Enter password: Welcome to the MySQL monitor.  Commands end with ; or \g.......mysql> create user allen_test1 identified by 'allen_test';Query OK, 0 rows affected (0.00 sec)mysql> grant select on *.* to allen_test1@localhost;ERROR 1045 (28000): Access denied for user 'allen'@'%' (using password: YES)mysql> 

如果想讓allen獲得grant的操作許可權，應該這樣寫： grant all privileges on *.* to allen@'%' with grant option;
如下：

[root@localhost ~]# mysql -u root -pEnter password: ......mysql> grant all privileges on *.* to allen@'%' with grant option;Query OK, 0 rows affected (0.00 sec)mysql> flush privileges;Query OK, 0 rows affected (0.00 sec)mysql> quit[root@localhost ~]# mysql -u allen -p -h 172.27.35.8Enter password: ......mysql> grant select on *.* to allen_test1@localhost;Query OK, 0 rows affected (0.00 sec)

發現現在allen使用者給allen_test授權成功。
2.資料庫級許可權範圍是在給定的一個資料庫中的所有目標的操作許可權，這些許可權會儲存在mysql.db和mysql.host中。其文法為：

[root@localhost ~]# mysql -u root -p Enter password: ......mysql> grant all privileges on db_users.* to allen_test2@'%'  identified by 'allen'; Query OK, 0 rows affected (0.01 sec)mysql> flush privileges;Query OK, 0 rows affected (0.00 sec)mysql> quitBye

然後使用allen_test2使用者登入：

[root@localhost ~]# mysql -u allen_test2 -p -h 192.168.65.30Enter password: Welcome to the MySQL monitor.  Commands end with ; or \g.......mysql> use db_users;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> create table tb_test(name varchar(32), sex bool);Query OK, 0 rows affected (0.06 sec)mysql> insert into tb_test values ('allen', 1);Query OK, 1 row affected (0.00 sec)mysql> create database db_test;ERROR 1044 (42000): Access denied for user 'allen_test2'@'%' to database 'db_test'mysql> 

可以看到，如果執行db_users資料庫內操作是可以的，但建立一個新的資料庫就會出錯。說明資料庫級許可權已經生效 。

3.資料表級許可權範圍是在給定的一個資料表中所有的目標的操作許可權，這些許可權會儲存在mysql.tables_priv中，通常一個資料表所擁有的許可權有select、insert、delete、update等。下面為一個使用者建立一個只有select的許可權：

[root@localhost ~]# mysql -uroot -pEnter password: Welcome to the MySQL monitor.  Commands end with ; or \g.mysql> grant select on db_users.* to allen_test3@'%' identified by 'allen';Query OK, 0 rows affected (0.00 sec)mysql> flush privileges;Query OK, 0 rows affected (0.00 sec)mysql> quitBye

使用使用者allen_test3使用者登入：

[root@localhost ~]# mysql -u allen_test3 -p -h 192.168.65.30Enter password: Welcome to the MySQL monitor.  Commands end with ; or \g.mysql> select * from tb_test;+-------+------+| name  | sex  |+-------+------+| allen |    1 |+-------+------+1 row in set (0.00 sec)mysql> insert into tb_test values ('Lily', 0);ERROR 1142 (42000): INSERT command denied to user 'allen_test3'@'192.168.65.30' for table 'tb_test'mysql> 

可見執行查詢操作是可以的，但是執行插入操作出錯。
4.欄位級是在欄位一級對使用者進行全線管理，設定使用者只有若干欄位的某些操作許可權，欄位的許可權資訊儲存在mysql.columns_priv表中。下例對一個新使用者賦予db_users資料庫中tb_test表的sex欄位查看和更新的許可權。

[root@localhost ~]# mysql -u root -p Enter password: Welcome to the MySQL monitor.  Commands end with ; or \g.mysql> grant select,update(sex) on db_users.tb_test to allen_test4@'%' identified by 'allen';Query OK, 0 rows affected (0.01 sec)mysql> flush privileges;Query OK, 0 rows affected (0.01 sec)mysql> quitBye

allen_test4使用者登入驗證許可權授予是否成功：

[root@localhost ~]# mysql -u allen_test4 -p -h 192.168.65.26Enter password: ......mysql> use db_users;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> select * from tb_test;+-------+------+| name  | sex  |+-------+------+| allen |    1 |+-------+------+1 row in set (0.00 sec)mysql> update tb_test set sex=0 where name='allen';Query OK, 1 row affected (0.01 sec)Rows matched: 1  Changed: 1  Warnings: 0mysql> select * from tb_test;+-------+------+| name  | sex  |+-------+------+| allen |    0 |+-------+------+1 row in set (0.00 sec)mysql> update tb_test set name='allen_new';ERROR 1143 (42000): UPDATE command denied to user 'allen_test4'@'192.168.65.26' for column 'name' in table 'tb_test'mysql>

可以看到select許可權沒有問題，也可以對sex欄位進行更新操作。但是更新name欄位報錯，因為沒有授予其許可權。
使用show grants可以查看使用者已經獲得的許可權，查看自己的操作許可權使用show grants命令可以查看自身許可權，使用show grants for username可以查看使用者username的許可權。下例為查看root使用者自身和查看allen_test4的操作許可權：

[root@localhost ~]# mysql -u root -p Enter password: Welcome to the MySQL monitor.  Commands end with ; or \g.......mysql> show grants;+----------------------------------------------------------------------------------------------------------------------------------------+| Grants for root@localhost                                                                                                              |+----------------------------------------------------------------------------------------------------------------------------------------+| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' WITH GRANT OPTION |+----------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)mysql> show grants for allen_test4;+------------------------------------------------------------------------------------------------------------+| Grants for allen_test4@%                                                                                   |+------------------------------------------------------------------------------------------------------------+| GRANT USAGE ON *.* TO 'allen_test4'@'%' IDENTIFIED BY PASSWORD '*C94FD2FCBF408CBBFAAB9C07FF4221D265AFB18F' || GRANT SELECT, UPDATE (sex) ON `db_users`.`tb_test` TO 'allen_test4'@'%'                                    |+------------------------------------------------------------------------------------------------------------+2 rows in set (0.00 sec)

和grant相反的操作時使用revoke操作，revoke作用是回收或者取消許可權。
1.撤銷全部許可權，

mysql> show  grants for allen_test2;+------------------------------------------------------------------------------------------------------------+| Grants for allen_test2@%                                                                                   |+------------------------------------------------------------------------------------------------------------+| GRANT USAGE ON *.* TO 'allen_test2'@'%' IDENTIFIED BY PASSWORD '*C94FD2FCBF408CBBFAAB9C07FF4221D265AFB18F' || GRANT ALL PRIVILEGES ON `db_users`.* TO 'allen_test2'@'%'                                                  |+------------------------------------------------------------------------------------------------------------+2 rows in set (0.00 sec)mysql> revoke all privileges, grant option from allen_test2;Query OK, 0 rows affected (0.00 sec)mysql> show  grants for allen_test2;+------------------------------------------------------------------------------------------------------------+| Grants for allen_test2@%                                                                                   |+------------------------------------------------------------------------------------------------------------+| GRANT USAGE ON *.* TO 'allen_test2'@'%' IDENTIFIED BY PASSWORD '*C94FD2FCBF408CBBFAAB9C07FF4221D265AFB18F' |+------------------------------------------------------------------------------------------------------------+1 row in set (0.01 sec)mysql> 

2.撤銷表級某類許可權，例如使用者allen_test3許可權如下：

mysql> show  grants for allen_test3;+------------------------------------------------------------------------------------------------------------+| Grants for allen_test3@%                                                                                   |+------------------------------------------------------------------------------------------------------------+| GRANT USAGE ON *.* TO 'allen_test3'@'%' IDENTIFIED BY PASSWORD '*C94FD2FCBF408CBBFAAB9C07FF4221D265AFB18F' || GRANT SELECT ON `db_users`.* TO 'allen_test3'@'%'                                                          |+------------------------------------------------------------------------------------------------------------+2 rows in set (0.00 sec)mysql> 

執行撤銷後許可權如下：

mysql> revoke select on db_users.* from allen_test3;Query OK, 0 rows affected (0.00 sec)mysql> show  grants for allen_test3;+------------------------------------------------------------------------------------------------------------+| Grants for allen_test3@%                                                                                   |+------------------------------------------------------------------------------------------------------------+| GRANT USAGE ON *.* TO 'allen_test3'@'%' IDENTIFIED BY PASSWORD '*C94FD2FCBF408CBBFAAB9C07FF4221D265AFB18F' |+------------------------------------------------------------------------------------------------------------+1 row in set (0.01 sec)mysql> 

3.撤銷某欄位許可權，例如allen_test4許可權如下：

mysql> show  grants for allen_test4;+------------------------------------------------------------------------------------------------------------+| Grants for allen_test4@%                                                                                   |+------------------------------------------------------------------------------------------------------------+| GRANT USAGE ON *.* TO 'allen_test4'@'%' IDENTIFIED BY PASSWORD '*C94FD2FCBF408CBBFAAB9C07FF4221D265AFB18F' || GRANT SELECT, UPDATE (sex) ON `db_users`.`tb_test` TO 'allen_test4'@'%'                                    |+------------------------------------------------------------------------------------------------------------+2 rows in set (0.00 sec)

可見使用者allen_test4擁有select和update 欄位sex的許可權，現在撤銷update的sex欄位，如下：

mysql> revoke update(sex) on db_users.tb_test from allen_test4;Query OK, 0 rows affected (0.00 sec)mysql> show  grants for allen_test4;+------------------------------------------------------------------------------------------------------------+| Grants for allen_test4@%                                                                                   |+------------------------------------------------------------------------------------------------------------+| GRANT USAGE ON *.* TO 'allen_test4'@'%' IDENTIFIED BY PASSWORD '*C94FD2FCBF408CBBFAAB9C07FF4221D265AFB18F' || GRANT SELECT ON `db_users`.`tb_test` TO 'allen_test4'@'%'                                                  |+------------------------------------------------------------------------------------------------------------+2 rows in set (0.00 sec)mysql> 

最後附上查看所有使用者的sql語句：

mysql> use mysql;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> select user from user;+-------------+| user        |+-------------+| allen_test2 || allen_test3 || allen_test4 || root        ||             || root        ||             || root        |+-------------+

許可權管理就到此為止了。