MySQL列權限管理講義

本文主要給大家介紹MySQL列權限管理講義，其所涉及的東西，從理論知識來獲悉，有很多書籍、文獻可供大家參考，從現實意義角度出發，億速云累計多年的實踐經驗可分享給大家。

 列權限管理

            MySQL是由列級別權限存在的。這也體現了MySQL高級特性。實現了限制用戶對表上特定列的訪問權限。
            一般都是實現對表級別不具備訪問權限，但是對某些列有訪問權限。當然也存在其他情形。

1# 列權限相關的字典表：

(root@localhost)[mysql]> desc columns_priv;
+-------------+----------------------------------------------+------+-----+-------------------+-----------------------------+
| Field       | Type                                         | Null | Key | Default           | Extra                       |
+-------------+----------------------------------------------+------+-----+-------------------+-----------------------------+
| Host        | char(60)                                     | NO   | PRI |                   |                             |
| Db          | char(64)                                     | NO   | PRI |                   |                             |
| User        | char(16)                                     | NO   | PRI |                   |                             |
| Table_name  | char(64)                                     | NO   | PRI |                   |                             |
| Column_name | char(64)                                     | NO   | PRI |                   |                             |
| Timestamp   | timestamp                                    | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| Column_priv | set('Select','Insert','Update','References') | NO   |     |                   |                             |
+-------------+----------------------------------------------+------+-----+-------------------+-----------------------------+
7 rows in set (0.00 sec)

            總共7列，很好理解。一條特定的列級別權限共需要定義5個維度，host+db+user+table+column。可授予的
            權限種類分為4中，select, insert, update, refernces。其中前3項已經投入使用，references在5.6中還未正式
            生效。

2# 授權方法
列權限的授權方法和其他維度的授權方法有些許的差異，因為并非按照想像中會用on db.table.column這樣的形式，而是將列名附帶在授權種類之后：
測試update，確認沒有update權限在name列上，表上也沒有。

(test1@localhost)[sample2]> update smp set name='bbb';
ERROR 1142 (42000): UPDATE command denied to user 'test1'@'localhost' for table 'smp'
(test1@localhost)[sample2]> 

            對name列授權update：

(root@localhost)[mysql]> grant update (name) on sample2.smp to test1;
Query OK, 0 rows affected (0.00 sec)

            再次嘗試update  name列，更新成功。

(test1@localhost)[sample2]> update smp set name='bbb';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

            驗證update  id列，可以預見的失敗：

(test1@localhost)[sample2]> update smp set id=2;
ERROR 1143 (42000): UPDATE command denied to user 'test1'@'localhost' for column 'id' in table 'smp'

3# 權限的查詢：
4個方式，一個是show grants，另一個是跑sql查詢字典表,DBA可以查詢mysql.columns_priv, 普通用戶可以查詢information_schema.COLUMN_PRIVILEGES。兩者有細微的差別，但主要列一樣,第四種方式是查詢mysql.tables_priv。

#1，直接show grants
(root@localhost)[mysql]> show grants for test1;
+------------------------------------------------------------------------------------------------------+
| Grants for test1@%                                                                                   |
+------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test1'@'%' IDENTIFIED BY PASSWORD '*CFA887C680E792C2DCF622D56FB809E3F8BE63CC' |
| GRANT SELECT ON `sample2`.* TO 'test1'@'%'                                                           |
| GRANT ALL PRIVILEGES ON `sample`.* TO 'test1'@'%' WITH GRANT OPTION                                  |
| GRANT SELECT ON `mysql`.`user` TO 'test1'@'%'                                                        |
| GRANT UPDATE (name) ON `sample2`.`smp` TO 'test1'@'%'                                                |
| GRANT ALL PRIVILEGES ON `sample`.`smp` TO 'test1'@'%'                                                |
+------------------------------------------------------------------------------------------------------+
6 rows in set (0.00 sec)

#2，查詢mysql.columns_priv;
(root@localhost)[mysql]> select * from mysql.columns_priv;
+------+---------+-------+------------+-------------+---------------------+-------------+
| Host | Db      | User  | Table_name | Column_name | Timestamp           | Column_priv |
+------+---------+-------+------------+-------------+---------------------+-------------+
| %    | sample2 | test1 | smp        | name        | 0000-00-00 00:00:00 | Update      |
+------+---------+-------+------------+-------------+---------------------+-------------+
1 row in set (0.00 sec)

#3，查詢information_schema.COLUMN_PRIVILEGES
(root@localhost)[mysql]> select * from information_schema.COLUMN_PRIVILEGES;
+-------------+---------------+--------------+------------+-------------+----------------+--------------+
| GRANTEE     | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | PRIVILEGE_TYPE | IS_GRANTABLE |
+-------------+---------------+--------------+------------+-------------+----------------+--------------+
| 'test1'@'%' | def           | sample2      | smp        | name        | UPDATE         | NO           |
+-------------+---------------+--------------+------------+-------------+----------------+--------------+
1 row in set (0.00 sec)

#4，查詢mysql.tables_priv
(root@localhost)[mysql]> select * from mysql.tables_priv where db='sample2';
+------+---------+-------+------------+----------------+---------------------+------------+-------------+
| Host | Db      | User  | Table_name | Grantor        | Timestamp           | Table_priv | Column_priv |
+------+---------+-------+------------+----------------+---------------------+------------+-------------+
| %    | sample2 | test1 | smp        | root@localhost | 0000-00-00 00:00:00 |            | Update      |
+------+---------+-------+------------+----------------+---------------------+------------+-------------+
1 row in set (0.00 sec)

程序權限管理

            MySQL的程序（process/routine）
            一個全局權限：CREATE ROUTINE，在user,db表中體現
            三個對象級權限，主要分為procedure和function兩個對象類型。對于程序而言他們的權限種類有
            1，EXECUTE #執行權限
            2，ALTER ROUTINE #修改權限
            3，GRANT  #授予權限

            相關的字典表：

(root@localhost)[mysql]> desc procs_priv;
+--------------+----------------------------------------+------+-----+-------------------+-----------------------------+
| Field        | Type                                   | Null | Key | Default           | Extra                       |
+--------------+----------------------------------------+------+-----+-------------------+-----------------------------+
| Host         | char(60)                               | NO   | PRI |                   |                             |
| Db           | char(64)                               | NO   | PRI |                   |                             |
| User         | char(16)                               | NO   | PRI |                   |                             |
| Routine_name | char(64)                               | NO   | PRI |                   |                             |
| Routine_type | enum('FUNCTION','PROCEDURE')           | NO   | PRI | NULL              |                             |
| Grantor      | char(77)                               | NO   | MUL |                   |                             |
| Proc_priv    | set('Execute','Alter Routine','Grant') | NO   |     |                   |                             |
| Timestamp    | timestamp                              | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+--------------+----------------------------------------+------+-----+-------------------+-----------------------------+
8 rows in set (0.00 sec)
看了以上MySQL列權限管理講義介紹，希望能給大家在實際運用中帶來一定的幫助。本文由于篇幅有限，難免會有不足和需要補充的地方，大家可以繼續關注億速云行業資訊板塊，會定期給大家更新行業新聞和知識，如有需要更加專業的解答，可在官網聯系我們的24小時售前售后，隨時幫您解答問題的。