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

This article mainly tells you how to set the permissions of the mysql.db database layer. You can look up the relevant professional terms on the Internet or find some related books to supplement them. We will not dabble here, so let's go straight to the topic. I hope this article on how to set the permissions of the mysql.db database layer can bring you some practical help.

"Database layer permissions record location

The permissions at the table level are recorded in the mysql.tables_ private table.

(root@localhost) [mysql] > (root@localhost) [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 at the database level are recorded in the mysql.db table

(root@localhost) [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 ('Nickel dagger Y') | NO | | N | | Update_priv | enum (' Noble dagger Y') | NO | | N | | Delete_priv | enum ('N') | |'Y') | NO | | N | | Create_priv | enum ('Nickel dagger Y') | NO | | N | | Drop_priv | enum (' Noble dagger Y') | NO | | N | | Grant_priv | enum ('N') | |'Y') | NO | | N | | References_priv | enum ('Nickel dagger Y') | NO | | N | | Index_priv | enum (' Noble dagger Y') | NO | | N | | Alter_priv | enum ('N') | |'Y') | NO | | N | | Create_tmp_table_priv | enum ('Nickel dagger Y') | NO | | N | | Lock_tables_priv | enum (' Noble dagger Y') | NO | | N | | Create_view_priv | enum ('N') | |'Y') | NO | | N | | Show_view_priv | enum ('Nickel dagger Y') | NO | | N | | Create_routine_priv | enum (' Noble dagger Y') | NO | | N | | Alter_routine_priv | enum ('N') | |'Y') | NO | | N | | Execute_priv | enum ('Nickel dagger Y') | NO | | N | | Event_priv | enum (' Noble dagger Y') | NO | | N | | Trigger_priv | enum ('N') | 'Y') | NO | | N | | +-+-+ 22 rows in set (0.00 sec) (root@localhost) [mysql] > select * from db\ G* * * 1. Row * * Host:% Db: sample User: test1 Select_priv: y Insert_priv: n Update_priv: n Delete_ Priv: NCreate_ priv: Y Drop_priv: N Grant_priv: N References_priv: N Index_priv: N Alter_priv: NCreate_tmp_table_priv: N Lock_tables_priv: NCreate_ view_priv: N Show_view_priv: NCreate_ 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:

(root@localhost) [mysql] > show grants for test1 +-+ | Grants for test1@% | +- -+ | 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 2:

GRANT SELECT, CREATE ON sample.* TO 'test1'@'%'

Try to add more permissions:

(root@localhost) [mysql] > grant all privileges on sample.* to test1 Query OK 0 rows affected (0.00 sec) (root@localhost) [mysql] > (root@localhost) [mysql] > (root@localhost) [mysql] > select * from db\ gateway * 1. Row * * Host:% Db: sample User: test1 Select_priv: Y Insert_priv: Y Update_priv: Y Delete_priv: YCreate_ 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

(root@localhost) [mysql] > grant all privileges on sample.* to test1 with grant option Query OK 0 rows affected (0.00 sec) (root@localhost) [mysql] > select * from db\ gateway * 1. Row * * Host:% Db: sample User: test1 Select_priv: Y Insert_priv: Y Update_priv: Y Delete_priv: YCreate_ priv: Y Drop_priv: Y Grant_priv: Y References_priv: Y Index_priv: Y Alter_priv: YCreate_tmp_table_priv: Y Lock_tables_priv: YCreate_ 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 permission. Incorrect writing. Revoke cannot take with grant option to reclaim grant option.

(root@localhost) [mysql] > revoke all privileges on sample.* from test1 with grant option;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 'grant option' at line 1 (root@localhost) [mysql] >

It is still not correct to write like this:

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

Just write separately:

(root@localhost) [mysql] > revoke all privileges on sample.* from test1;Query OK, 0 rows affected (0.00 sec) (root@localhost) [mysql] > revoke grant option on sample.* from test1;Query OK, 0 rows affected (0.00 sec) (root@localhost) [mysql] >

Grant option is granted using the with clause and needs to be recycled separately when reclaiming.

"databases accessible to ordinary users:

Under test1, view the databases that you can access:

(test1@localhost) [(none)] > show databases-> +-+ | Database | +-+ | information_schema | | mysql | | sample | +-+ 3 rows in set (0.00 sec) (test1@localhost) [(none)] > (test1@localhost) [(none) ] > (test1@localhost) [(none)] > (test1@localhost) [(none)] > use mysql Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with-ADatabase changed (test1@localhost) [mysql] > show tables +-+ | Tables_in_mysql | +-+ | user | # since only select permission is granted on the user table, show tables can only see one table +-+ 1 row in set (0.00 sec) (test1@localhost) [mysql] > show grants +-- + | Grants for test1@% | +- -- + | GRANT USAGE ON *. * TO 'test1'@'%' IDENTIFIED BY PASSWORD | | GRANT ALL PRIVILEGES ON `sample`.* TO' test1'@'%' WITH GRANT OPTION | | GRANT ALL PRIVILEGES ON `sample`.`smp`TO 'test1'@'%' | | GRANT SELECT ON `mysql`.`user`TO 'test1'@'%' | +-+ 4 rows in set (0.00 sec)-- -+ | Grants for test1@% | +- -+ | GRANT USAGE ON *. * TO 'test1'@'%' IDENTIFIED BY PASSWORD | | GRANT ALL PRIVILEGES ON `sample`.`smp`TO' test1'@'%' | | GRANT SELECT ON `mysql`.`user`TO 'test1'@'%' | +-- -- + 3 rows in set (0.00 sec) (test1@localhost) [mysql] >

The sample database is the granted all privileges, so all the table objects in the library are available for access by test1 users. As for mysql, there is only one select permission on the user table, so it is also classified as one of the databases that can be accessed, but you can actually see only one table with show tables.

Database layer permissions from scratch, after granting permissions to a user, the changes in the db table:

A # create a new database sample2

(root@localhost) [(none)] > create database sample2;Query OK, 1 row affected (0.00 sec)

At this time, to view the database permissions owned by user test1, there is only sample database. The MYSQL database does not display.

(root@localhost) [mysql] > select * from db\ gateway * 1. Row * * Host:% Db: sample User: test1 Select_priv: y Insert_priv: Y Update_priv: Y Delete_priv: YCreate_ priv: Y Drop_priv: Y Grant_priv: Y References_priv: Y Index_priv: Y Alter_priv: YCreate_tmp_table_priv: Y Lock_tables_priv: YCreate_ view_priv: Y Show_view_priv: YCreate_ routine_priv Y Alter_routine_priv: y Execute_priv: y Event_priv: y Trigger_priv: Y1 row in set (0.00 sec) (root@localhost) [mysql] > # at this time, test1 users also failed to try to access the sample2 database: (test1@localhost) [sample] > use sample2ERROR 1044 (42000): Access denied for user 'test1'@'%' to database' sample2' (test1@localhost) [sample] >

Run the authorization statement to give select permission to the test1 recipient sample2. At this time, there are two rows of data in the mysql.db database, and one more row of records about the sample2:

(root@localhost) [mysql] > select * from db\ gateway * 1. Row * * Host:% Db: sample2 User: test1 Select_priv: y Insert_priv: N Update_priv: N Delete_priv: NCreate_ priv: N Drop_priv: N Grant_priv: N References_priv: N Index_priv: N Alter_priv: NCreate_tmp_table_priv: N Lock_tables_priv: NCreate_ view_priv: N Show_view_priv: NCreate_ routine_priv : n Alter_routine_priv: n Execute_priv: n Event_priv: n Trigger_priv: nasty * 2. Row * * Host:% Db: sample User: test1 Select_priv: Y Insert_priv: Y Update_priv: Y Delete_priv: YCreate_ 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: Y2 rows in set (0.00 sec)

From the records available, you can see that the test1 user has select permissions on the sample2 database. That is, the access permission, which means that all the objects in it have. But there is no watch in it.

(test1@localhost) [(none)] > use sample2;Database changed (test1@localhost) [sample2] > show tables;Empty set (0.00 sec) # and test1 users can use select to output smp table data. (test1@localhost) [sample2] > select * from smp;+-+-+ | id | name | +-+-+ | 1 | aaa | +-+-+ 1 row in set (0.00 sec) # however, test1 users do not show that they have table permissions on the sample2. The so-called inheritance of permissions. (test1@localhost) [information_schema] > select * from TABLE_PRIVILEGES where TABLE_SCHEMA='sample2';Empty set (0.00 sec)

# it is strange that the table smp is not authorized to select, but the recipient is select on sample2.*. Attempt to reclaim select permissions on the table separately:

(root@localhost) [sample2] > revoke select on sample2.smp from test1;ERROR 1147 (42000): There is no such grant defined for user 'test1' on host'%'on table 'smp'# ended in failure. The displayed revoke does not reclaim implicitly inherited permissions. (test1@localhost) [sample2] > show tables;+-+ | Tables_in_sample2 | +-+ | smp | +-+ 1 row in set (0.00 sec)

Mysql.db database layer permissions how to set up to tell you here, for other related issues you want to know can continue to pay attention to our industry information. Our section will capture some industry news and professional knowledge to share with you every day.

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.