About mysql.db and Database layer permissions

# database Layer Permission record location
Table-level permissions are recorded in the Mysql.tables_priv table.

([email protected]) [mysql]> ([email protected]) [mysql]> desc tables_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 |                             | || Grantor |                                                                                                                          Char (77) | NO |                   MUL |                             | || Timestamp |                                                                                                                         Timestamp |     NO | | Current_timestamp | On Update Current_timestamp | | Table_priv | Set ('Select ', ' Insert ', ' Update ', ' Delete ', ' create ', ' Drop ', ' Grant ', ' References ', ' Index ', ' Alter ', ' Create View ', ' Show view ' ', ' Trigger ') |     NO |                   |                             | || Column_priv |                                                                                      Set (' Select ', ' Insert ', ' Update ', ' References ') |     NO |                   |                             | |+-------------+----------------------------------------------------------------------------------------------- ------------------------------------+------+-----+-------------------+-----------------------------+8 rows in Set (0.00 sec)

However, permissions on the database level are recorded in the Mysql.db table

([email protected]) [mysql]> desc db;+-----------------------+---------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-----------------------+---------------+------+-----+---------+-------+| Host | char (60) | NO |         PRI |       | || Db | CHAR (64) | NO |         PRI |       | || User | char (16) | NO |         PRI |       | || Select_priv | Enum (' N ', ' Y ') |     NO | |       N | || Insert_priv | Enum (' N ', ' Y ') |     NO | |       N | || Update_priv | Enum (' N ', ' Y ') |     NO | |       N | || Delete_priv | Enum (' N ', ' Y ') |     NO | |       N | || Create_priv | Enum (' N ', ' Y ') |     NO | |       N | || Drop_priv | Enum (' N ', ' Y ') |     NO | |       N | || Grant_priv | Enum (' N ', ' Y ') |     NO | |       N | ||   References_priv    | Enum (' N ', ' Y ') |     NO | |       N | || Index_priv | Enum (' N ', ' Y ') |     NO | |       N | || Alter_priv | Enum (' N ', ' Y ') |     NO | |       N | || Create_tmp_table_priv | Enum (' N ', ' Y ') |     NO | |       N | || Lock_tables_priv | Enum (' N ', ' Y ') |     NO | |       N | || Create_view_priv | Enum (' N ', ' Y ') |     NO | |       N | || Show_view_priv | Enum (' N ', ' Y ') |     NO | |       N | || Create_routine_priv | Enum (' N ', ' Y ') |     NO | |       N | || Alter_routine_priv | Enum (' N ', ' Y ') |     NO | |       N | || Execute_priv | Enum (' N ', ' Y ') |     NO | |       N | || Event_priv | Enum (' N ', ' Y ') |     NO | |       N | || Trigger_priv | Enum (' N ', ' Y ') |     NO | |       N | |+-----------------------+---------------+------+-----+---------+-------+22 rows in Set (0.00 sec) ([email  Protected]) [mysql]> SELECT * from db\g*****1.          Row *************************** Host:% db:sample user:test1            Select_priv:y insert_priv:n update_priv:n delete_priv:n create_priv:y Drop_priv:n grant_priv:n references_priv:n index_priv:n alter_priv:ncreate_tmp_tab Le_priv:n lock_tables_priv:n create_view_priv:n show_view_priv:n create_routine_priv:n Alter_routine _priv:n execute_priv:n event_priv:n trigger_priv:n1 row in Set (0.00 sec)

The corresponding GRANT statement is:

([email protected])[mysql]> show grants for test1;+------------------------------------------------------------------------------------------------------+| Grants for [email protected]%                                                                                   |+------------------------------------------------------------------------------------------------------+| GRANT USAGE ON *.* TO ‘test1‘@‘%‘ IDENTIFIED BY PASSWORD ‘*CFA887C680E792C2DCF622D56FB809E3F8BE63CC‘ || GRANT SELECT, CREATE ON `sample`.* TO ‘test1‘@‘%‘                                                    || GRANT ALL PRIVILEGES ON `sample`.`smp` TO ‘test1‘@‘%‘                                                || GRANT SELECT ON `mysql`.`user` TO ‘test1‘@‘%‘                                                        |+------------------------------------------------------------------------------------------------------+4 rows in set (0.00 sec)

Article II:
GRANT SELECT, CREATE on sample . * to ' test1 ' @ '% '

Try adding permissions again:

([email protected])[mysql]> grant all privileges on sample.* to test1;Query OK, 0 rows affected (0.00 sec)([email protected])[mysql]> ([email protected])[mysql]> ([email protected])[mysql]> select * from db\G*************************** 1. row ***************************                 Host: %                   Db: sample                 User: test1          Select_priv: Y          Insert_priv: Y          Update_priv: Y          Delete_priv: Y          Create_priv: Y            Drop_priv: Y           Grant_priv: N      References_priv: Y           Index_priv: Y           Alter_priv: YCreate_tmp_table_priv: Y     Lock_tables_priv: Y     Create_view_priv: Y       Show_view_priv: Y  Create_routine_priv: Y   Alter_routine_priv: Y         Execute_priv: Y           Event_priv: Y         Trigger_priv: Y1 row in set (0.00 sec)

Grant all privileges permissions. Note that grant option is not included in all privileges. You can use the WITH clause

([email protected])[mysql]> grant all privileges on sample.* to test1 with grant option;Query OK, 0 rows affected (0.00 sec)([email protected])[mysql]> select * from db\G*************************** 1. row ***************************                 Host: %                   Db: sample                 User: test1          Select_priv: Y          Insert_priv: Y          Update_priv: Y          Delete_priv: Y          Create_priv: Y            Drop_priv: Y           Grant_priv: Y      References_priv: Y           Index_priv: Y           Alter_priv: YCreate_tmp_table_priv: Y     Lock_tables_priv: Y     Create_view_priv: Y       Show_view_priv: Y  Create_routine_priv: Y   Alter_routine_priv: Y         Execute_priv: Y           Event_priv: Y         Trigger_priv: Y1 row in set (0.00 sec)

Reclaim all privileges permissions, error notation, REVOKE does not take with GRANT option to reclaim grant option

It is still wrong to write:

revoke all privileges, grant option  on sample.* from test1;ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘on sample.* from test1‘ at line 1

You can write it separately:

Grant OPTION uses the WITH clause when it is granted, and it needs to be reclaimed separately when it is reclaimed.

A database accessible to general users:
Under Test1 users, view the databases that can be accessed:

([email protected]) [(None)]> show databases, +--------------------+| Database |+--------------------+| Information_schema | | MySQL | | Sample |+--------------------+3 rows in Set (0.00 sec) ([email protected]) [(none)]> ([Email protec Ted]) [(none)]> ([email protected]) [(none)]> ([email protected]) [(none)]> use MySQL; Reading table information for completion of table and column namesyou can turn off this feature to get a quicker startup W Ith-adatabase changed ([email protected]) [mysql]> show tables;+-----------------+| Tables_in_mysql |+-----------------+| User |+-----------------+1 row in Set (0.00 sec) ([email protected]) [mysql]> show grants;+-------------- -------------------------------------------------------+| Grants for [email protected]% |+------------------------------------ ---------------------------------+| GRANT USAGE ON * * to ' test1 ' @ '% ' identified by PASSWORD <secret> | | GRANT all privileges in ' sample '. * to ' test1 ' @ '% ' with GRANT OPTION | | GRANT all privileges in ' sample '. ' SMP ' to ' test1 ' @ '% ' | | GRANT SELECT on ' MySQL '. ' User ' to ' test1 ' @ '% ' |+---------------------------------------------------- -----------------+4 rows in Set (0.00 sec)-----------------------------------------------------------------+| Grants for [email protected]% |+-------------------------------------- -----------------------------+| GRANT USAGE on *. test1 ' @ '% ' identified by PASSWORD <secret> | | GRANT all privileges in ' sample '. ' SMP ' to ' test1 ' @ '% ' | | GRANT SELECT on ' MySQL '. ' User ' to ' test1 ' @ '% ' |+------------------------------------------------------  -------------+3 rows in Set (0.00 sec) ([email protected]) [mysql]>

The sample database is the granted all privileges

About mysql.db and Database layer permissions