MySQL column permissions and program Rights Management

Column Rights Management

            MySQL是由列级别权限存在的。这也体现了MySQL高级特性。实现了限制用户对表上特定列的访问权限。            一般都是实现对表级别不具备访问权限，但是对某些列有访问权限。当然也存在其他情形。

The dictionary table for a # column permission-Related:

([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中还未正式            生效。

Authorization method
The authorization method for column permissions differs slightly from the authorization method for other dimensions, because it is not imagined that it will be in the form of on db.table.column, but rather that the column name is included with the authorization category:
Test the update to verify that no update permissions are on the Name column, nor on the table.

            对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‘

Query for 3# permissions:
4 ways, one is show grants, the other is run SQL query dictionary table, DBA can query Mysql.columns_priv, ordinary users can query information_schema. Column_privileges. There are subtle differences, but the main column is the fourth way to query Mysql.tables_priv.

#1, direct show grants ([email protected]) [mysql]> show grants for test1;+---------------------------------------- --------------------------------------------------------------+| Grants for [email protected]% |+--- ---------------------------------------------------------------------------------------------------+| GRANT USAGE on *. test1 ' @ '% ' identified by PASSWORD ' *cfa887c680e792c2dcf622d56fb809e3f8be63cc ' | | GRANT SELECT on ' sample2 '. * to ' test1 ' @ '% ' | | GRANT all privileges in ' sample '. * to ' test1 ' @ '% ' with GRANT OPTION | | GRANT SELECT on ' MySQL '. ' User ' to ' test1 ' @ '% ' | | GRANT UPDATE (name) on ' Sample2 '. ' SMP ' to ' test1 ' @ '% ' | |                       GRANT all privileges in ' sample '. ' SMP ' to ' test1 ' @ '% '                         |+----------------------------------------------------------------------------------------------- -------+6 rows in Set (0.00 sec) #2, query 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, query 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 r ow in Set (0.00 sec) #4, Query 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) 

Program Rights Management

            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 column permissions and program Rights Management