Shulou(Shulou.com)06/01 Report--

This article mainly introduces the MySQL column rights management handout, which involves things, learned from the theoretical knowledge, there are many books, literature for your reference, from the perspective of practical significance, accumulated years of practical experience can be shared with you.

Column rights management

MySQL exists with column-level permissions. This also reflects the advanced features of MySQL. It is implemented to restrict user access to specific columns on the table. Generally, the implementation does not have access to the table level, but does have access to certain columns. Of course, there are other situations.

List the dictionary tables related to permissions:

(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) total 7 columns It's easy to understand. A specific column-level permission needs to define a total of five dimensions, host+db+user+table+column. The types of permissions that can be granted are divided into 4 categories, select, insert, update, and refernces. Among them, the first three items have been put into use, and references has not come into effect in 5.6yet.

The authorization method

The authorization method of column permissions is slightly different from that of other dimensions, because instead of using the form of on db.table.column as expected, the column name is appended to the authorization category:

Test update and verify that there is no update permission on the name column, nor on the table.

(test1@localhost) [sample2] > update smp set name='bbb';ERROR 1142 (42000): UPDATE command denied to user 'test1'@'localhost' for table' smp' (test1@localhost) [sample2] > authorize update to the name column: (root@localhost) [mysql] > grant update (name) on sample2.smp to test1;Query OK, 0 rows affected (0.00 sec) try the update name column again, and the update is successful. (test1@localhost) [sample2] > update smp set name='bbb';Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 validate update id column, foreseeable failure: (test1@localhost) [sample2] > update smp set id=2;ERROR 1143 (42000): UPDATE command denied to user 'test1'@'localhost' for column' id' in table 'smp'

Query for permission:

Four ways, one is show grants, the other is to run sql query dictionary table, DBA can query mysql.columns_priv, ordinary users can query information_schema.COLUMN_PRIVILEGES. There are slight differences between the two, but the main columns are the same, and the fourth way is to query mysql.tables_priv.

# 1. Direct 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 Query 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-0000: 00:00 | Update | +-- -+ 1 row in set (0.00 sec) # 3 Query 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 Query 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-0000: 00:00 | | Update | + -+-+ 1 row in set (0.00 sec) Program Rights Management MySQL Program (process/routine) one global permission: CREATE ROUTINE There are three object-level permissions in the user,db table, which are mainly divided into two object types: procedure and function. For programs, their types of permissions are 1Magi execute # execute ROUTINE # modify permissions 3Magi Grant # Dictionary tables related to permissions granted: (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)

Read the above MySQL column rights management handout introduction, hope to bring some help to everyone in the practical application. Due to the limited space in this article, there will inevitably be deficiencies and areas that need to be supplemented. You can continue to pay attention to the industry information section and will update your industry news and knowledge regularly. If you need more professional answers, you can contact us on the official website for 24-hour pre-sales and after-sales to help you answer questions at any time.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.