Detailed MySQL Third-DCL statement

DCL (Data Control Language) statement: A statement that controls the level of permission and access to different data segments directly. These statements define the database, table, field, user's access rights, and security level. The main statement keywords include GRANT, revoke, and so on.

DCL statements are primarily used by DBAs to manage object permissions in the system, and are seldom used by general developers. Here is an example to illustrate briefly.

Create a database user Z1 with Select/insert permissions to all tables in the Sakila database:

123456789101112

Mysql> Grant Select,Insert on sakila.* to ' z1 ' @' localhost ' identified by ' 123 '; QueryOK, 0 rows affected (0.00 sec) Mysql> exit Bye[[email protected]~]$ mysql -uz1 -p123 WelcomeTo the MySQL Monitor. Commands End with ; or \g. Your Mysql connection ID is 21671 to server version: 5.1.9-beta -log Type ' help; ' or ' \h ' for help . Type ' \c ' to clear the buffer. Mysql> Use sakila Database changed Mysql> INSERT INTO emp values (' bzshen ',' 2005-04-01 ', '3 '); QueryOK, 1 row affected (0.04 sec)

Due to changes in permissions, you need to change the permissions of the Z1, retract the INSERT, only the data can be SELECT operation:

12345678

[[Email protected]~]$ mysql -uroot WelcomeTo the MySQL Monitor. Commands End with ; or \g. Your Mysql connection ID is 21757 to server version: 5.1.9-beta -log Type ' help; ' or ' \h ' for help . Type ' \c ' to clear the buffer. Mysql> revoke insert on sakila.* from ' z1 ' @' localhost '; QueryOK, 0 rows affected (0.00 sec) Mysql> exit Bye

The user Z1 after logging back in to execute the preceding statement:

12345678910

[mysql@db3 ~]$ mysql -uz1 -p123 welcome to the mysql monitor. commands end with ; or \g your mysql connection id is 21763 to server version: 5.1.9< Span class= "Crayon-o" >-beta-log type ' help; ' or ' \h ' for help type ' \c ' to clear the buffer mysql> insert into emp values(' Bzshen ',' 2005-04-01 ',3000 ,' 3 '); ERROR 1046 (3d000): No database selected MySQL> Use sakila Database changed mysql> insert into emp values(' Bzshen ',' 2005-04-01 ', ,' 3 '); ERROR 1142 (42000): INSERT command denied to user ' z1 '@' localhost ' for table ' emp '

The grant and revoke in the above example grant and reclaim some of the user Z1 's permissions to our purposes.

Detailed MySQL Third-DCL statement