MySQL列許可權和程式許可權管理

標籤：comm   type   match   set   正式   name   sage   inf   format   

列許可權管理

            MySQL是由列層級許可權存在的。這也體現了MySQL進階特性。實現了限制使用者對錶上特定列的存取權限。            一般都是實現對錶層級不具備存取權限，但是對某些列有存取權限。當然也存在其他情形。

1# 列許可權相關的字典表：

([email protected])[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列上，表上也沒有。

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

            對name列授權update：

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

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

([email protected])[sample2]> update smp set name=‘bbb‘;Query OK, 1 row affected (0.00 sec)Rows matched: 1  Changed: 1  Warnings: 0

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

([email protected])[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([email protected])[mysql]> show grants for test1;+------------------------------------------------------------------------------------------------------+| Grants for [email protected]%                                                                                   |+------------------------------------------------------------------------------------------------------+| 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;([email protected])[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([email protected])[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([email protected])[mysql]> select * from mysql.tables_priv where db=‘sample2‘;+------+---------+-------+------------+----------------+---------------------+------------+-------------+| Host | Db      | User  | Table_name | Grantor        | Timestamp           | Table_priv | Column_priv |+------+---------+-------+------------+----------------+---------------------+------------+-------------+| %    | sample2 | test1 | smp        | [email protected] | 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  #授予許可權            相關的字典表：

([email protected])[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列許可權和程式許可權管理