MySQL Authorization sequencing test

Recently encountered a small problem in actual work, check the authorization, obviously has given a table to authorize the create, but still error no create permission.

Here are the detailed steps and the final solution.

You can see that there is a certain matching rule on MySQL authorization.

1. Root user, CREATE database

mysql> CREATE DATABASE GW_CGM;

Query OK, 1 row affected (0.01 sec)

mysql> CREATE DATABASE Gw_ga;

Query OK, 1 row Affected (0.00 sec)

mysql> CREATE DATABASE gw_log01;

Query OK, 1 row Affected (0.00 sec)

mysql> CREATE DATABASE gw_log02;

Query OK, 1 row Affected (0.00 sec)

2. Create APP_DDD user and authorize

Authorize all tables of gw_% first

Set the Create and drop permissions for GW_CGM again

Mysql> Show grants for [email protected] ' 10.% ';

+-------------------------------------------------------------------------------------------------------------+

| Grants for [email protected]% |

+-------------------------------------------------------------------------------------------------------------+

| GRANT USAGE on * * to ' app_ddd ' @ ' 10.% ' |

| GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE on ' gw_% '. * to ' app_ddd ' @ ' 10.% ' |

| GRANT CREATE, DROP on ' gw_cgm '. * to ' app_ddd ' @ ' 10.% ' |

+-------------------------------------------------------------------------------------------------------------+

3 Rows in Set (0.00 sec)

3. Log in to MySQL using APP_DDD user

Mysql-uapp_ddd

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| Information_schema |

| GW_CGM |

| GW_LOG01 |

| gw_log02 |

| Gw_ga |

+--------------------+

4, create a table, error, no permissions

Mysql> Use GW_CGM

Database changed

mysql> CREATE TABLE ' T5 ' (' id ' int (1) NOT null default ' 1 ', ' name ' varchar (5) Default NULL) Engine=innodb default Charse T=latin1;

ERROR 1142 (42000): CREATE command denied to user ' app_ddd ' @ ' 10.12.200.102 ' for table ' T5 '

5, switch root user under

(1) Recycling APP_DDD users

Mysql> REVOKE SELECT, INSERT, UPDATE, DELETE, EXECUTE on ' gw_% '. * from ' app_ddd ' @ ' 10.% ';

Query OK, 0 rows Affected (0.00 sec)

Mysql> REVOKE CREATE, DROP on ' gw_cgm '. * from ' app_ddd ' @ ' 10.% ';

Query OK, 0 rows Affected (0.00 sec)

(2) Re-authorize APP_DDD users, pay attention to the order of authorization here.

First give GW_CGM Create, drop permissions

and give gw_% authorization.

Mysql> GRANT CREATE, DROP on ' gw_cgm '. * to ' app_ddd ' @ ' 10.% ';

Query OK, 0 rows Affected (0.00 sec)

Mysql> GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE on ' gw_% '. * to ' app_ddd ' @ ' 10.% ';

Query OK, 0 rows Affected (0.00 sec)

Mysql> Show grants for [email protected] ' 10.% ';

+-------------------------------------------------------------------------------------------------------------+

| Grants for [email protected]% |

+-------------------------------------------------------------------------------------------------------------+

| GRANT USAGE on * * to ' app_ddd ' @ ' 10.% ' |

| GRANT CREATE, DROP on ' gw_cgm '. * to ' app_ddd ' @ ' 10.% ' |

| GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE on ' gw_% '. * to ' app_ddd ' @ ' 10.% ' |

+-------------------------------------------------------------------------------------------------------------+

3 Rows in Set (0.00 sec)

6. Re-login with APP_DDD

Mysql> Use GW_CGM

Database changed

mysql> CREATE TABLE ' T5 ' (' id ' int (1) NOT null default ' 1 ', ' name ' varchar (5) Default NULL) Engine=innodb default Charse T=latin1;

Query OK, 0 rows affected (0.13 sec)

Through the test can be seen: First give gw_% basic permissions, after the new create permissions, MySQL and to authorize the create.

This article is from the "rookie site" blog, please be sure to keep this source http://yangjingangel.blog.51cto.com/8351501/1745977

MySQL Authorization sequencing test