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

The following along with the author to understand how to better manage MySQL permissions, I believe you will benefit a lot after reading, the text in the essence is not much, hope that how to better manage MySQL permissions this short content is what you want.

"View permissions

For example, we want to see what permissions the root user of MySQL has:

(root@localhost) [(none)] > select user (); +-+ | user () | +-+ | root@localhost | +-+ 1 row in set (0.00 sec) (root@localhost) [(none)] > show grants +- -+ | Grants for root@localhost | +-- -+ | GRANT ALL PRIVILEGES ON *. * TO 'root'@'localhost' IDENTIFIED BY PASSWORD' * A0F874BC7F54EE086FCE60A37CE7887D8B31086B' WITH GRANT OPTION | | GRANT PROXY ON'@''TO 'root'@'localhost' WITH GRANT OPTION | +-- -+ 2 rows in set (0.00 sec)

A show grants solved the problem and even gave us the grant statement. That is to say, we can recreate another superuser like root with a little modification.

Or use:

Show grants for 'xxx'@'xxxxx'

The first of these is:

GRANT ALL PRIVILEGES ON *. * TO 'root'@'localhost' IDENTIFIED BY PASSWORD' * A0F874BC7F54EE086FCE60A37CE7887D8B31086B' WITH GRANT OPTION

It's not just grant. The ALL PRIVILEGES on the page gives' root'@'localhost', and even authentication passwords and WITH information. Actually, this statement can be used to create this user. This is also an alternative way to create users.

View other people's permissions:

(root@localhost) [(none)] > show grants for test1-> +-+ | Grants for test1@% | +- -+ | GRANT USAGE ON *. * TO 'test1'@'%' IDENTIFIED BY PASSWORD' * CFA887C680E792C2DCF622D56FB809E3F8BE63CC' | + -+ 1 row in set (0.00 sec)

Here we see that the user 'test1'@'%' has only one permission, which is also the default create user statement.

"Grant permissions:

Users must need to use the database. So if the user only has the useless permission of usage, the user does not need to exist.

Syntax:

(root@localhost) [(none)] > help grantName: 'GRANT'Description:Syntax:GRANT priv_type [(column_list)] [, priv_type [(column_list)]]. ON [object_type] priv_level TO user_specification [, user_specification]... [REQUIRE {NONE | tsl_option [[AND] tsl_option]...}] [WITH {GRANT OPTION | resource_option}.] GRANT PROXY ON user_specification # this proxy statement also exists separately in TO user_specification [, user_specification]. [WITH GRANT OPTION] object_type: {# object type TABLE | FUNCTION | PROCEDURE} priv_level: {# permission level classification * | *. * | db_name.* | Db_name.tbl_name | tbl_name | db_name.routine_name} user_specification: # user user [auth_option] auth_option: {# authentication information IDENTIFIED BY 'auth_string' | IDENTIFIED BY PASSWORD' hash_string' | IDENTIFIED WITH auth_plugin | IDENTIFIED WITH auth_plugin AS 'hash_string'} tsl_option: {# SSL type SSL | X509 | CIPHER' cipher' | ISSUER 'issuer' | SUBJECT' subject'} resource_option: {# Resource usage definition | MAX_QUERIES_PER_HOUR count | MAX_UPDATES_PER_HOUR count | MAX_CONNECTIONS_PER_HOUR count | MAX_USER_CONNECTIONS count}

For a list of user permissions, see the official document: https://dev.mysql.com/doc/refman/5.6/en/grant.html

The user test1 currently does not have any permissions. Suppose we need to give him access to the mysql.user table

(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] > select count (*) from user;+-+ | count (*) | +-+ | 4 | +-+ 1 row in set (0.00 sec) (test1@localhost) [mysql] >

Create a new database, create a new table, access and control the table:

(root@localhost) [mysql] > create database sample;Query OK, 1 row affected (0.00 sec) (root@localhost) [mysql] > use sample;Database changed (root@localhost) [sample] > show tables;Empty set (0.00 sec) (root@localhost) [sample] > create table smp (id int,name char (20)); Query OK, 0 rows affected (0.07 sec) (root@localhost) [sample] > grant all privileges on sample.smp to test1 Query OK, 0 rows affected (0.00 sec) (root@localhost) [sample] > select User,Table_name,Table_priv from mysql.tables_priv +-+ | User | Table _ name | Table_priv | + -+ | test1 | user | Select | | test1 | smp | Select Insert,Update,Delete,Create,Drop,References,Index,Alter,Create View,Show view Trigger | +-+ 2 rows in Set (0.00 sec) (root@localhost) [sample] >

More on how to delete and delete tables

(test1@localhost) [sample] > insert into smp values; Query OK, 1 row affected (0.00 sec) (test1@localhost) [sample] > select * from smp;+-+-+ | id | name | +-+-+ | 1 | abc | +-+-+ 1 row in set (0.00 sec) [sample] > delete from smp Query OK, 1 row affected (0.00 sec) (test1@localhost) [sample] > select * from smp;Empty set (0.00 sec) (test1@localhost) [sample] > drep table smp;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 'drep table smp' at line 1 (test1@localhost) [sample] > drop table smp;Query OK, 0 rows affected (0.00 sec)

Drop table is DDL. At this time, table has been deleted. Check the permissions:

(root@localhost) [sample] > select User,Table_name,Table_priv from mysql.tables_priv +-+ | User | Table _ name | Table_priv | + -+ | test1 | user | Select | | test1 | smp | Select Insert,Update,Delete,Create,Drop,References,Index,Alter,Create View,Show view Trigger | +-+ 2 rows in set (0.00 sec)

Permissions still exist. Indicates that deleting a table does not delete the object permissions that the user has.

Try to build it back:

(test1@localhost) [sample] > create table smp (id int,name char (20)); Query OK, 0 rows affected (0.00 sec) (test1@localhost) [sample] > create table smp1 (id int,name char (20)); ERROR 1142 (42000): CREATE command denied to user 'test1'@'localhost' for table' smp1' (test1@localhost) [sample] >

Successfully built back. So is it possible to create another table in this database? no way.

To sample. Permission is granted at the level.

(root@localhost) [sample] > grant create on sample.* to test1;Query OK, 0 rows affected (0.00 sec) (root@localhost) [sample] > select User,Table_name,Table_priv from mysql.tables_priv +-+ | User | Table _ name | Table_priv | + -+ | test1 | user | Select | | test1 | smp | Select Insert,Update,Delete,Create,Drop,References,Index,Alter,Create View,Show view Trigger | +-+ 2 rows in Set (0.00 sec) (root@localhost) [sample] > show grants for test1 +-+ | Grants for test1@% | +- -+ | GRANT USAGE ON *. * TO 'test1'@'%' IDENTIFIED BY PASSWORD' * CFA887C680E792C2DCF622D56FB809E3F8BE63CC' | | GRANT 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) (root@localhost) [sample] > flush privileges Query OK, 0 rows affected (0.00 sec)

At this time, test1 users have CREATE ON sample. . Try to create a table object in the database.

(test1@localhost) [sample] > create table smp1 (id int,name char (20)); ERROR 1142 (42000): CREATE command denied to user 'test1'@'localhost' for table' smp1'

Failure! It's strange that you already have the authority, but you still fail. Try to log in again

(test1@localhost) [sample] > exitBye [mysql@mysql01 ~] $mysql-utest1-S / data/mysqldata/3306/mysql.sock-pEnter password:Welcome to the MySQL monitor. Commands end with; or\ g.Your MySQL connection id is 25Server version: 5.6.31-log Source distributionCopyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement. (test1@localhost) [(none)] > (test1@localhost) [(none)] > (test1@localhost) [(none)] > create table smp1 (id int,name char (20)); ERROR 1046 (3D000): No database selected (test1@localhost) [(none)] > use sample;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) [sample] > create table smp1 (id int,name char (20)) Query OK, 0 rows affected (0.01 sec)

The table object was established successfully after re-login. Indicates that the permissions of ordinary users need to be refreshed when logging in.

An alternative method to see what all the permissions of MYSQL have:

Mysql > grant ALL PRIVILEGES ON *. * to test@'localhost' IDENTIFIED BY 'oldboy123' with grant option;Query OK, 0 rows affected (0.00 sec) mysql > flush privileges;Query OK, 0 rows affected (0.00 sec) mysql > select user,host from mysql.user +-+-- + | user | host | +-+-+ | mysql_user |% | root | | 127.0.0.1 | | rep | 192.168.1.1% | | alrin | 192.168.1.0swap 24 | | alrin | 192.168.1.0 to 255.255.255.0 | | root | localhost | | test | localhost | +-| +-- + 7 rows in set (0.00 sec) mysql > show grants for test@localhost-> +- -+ | Grants for test@localhost | +-- -+ | GRANT ALL PRIVILEGES ON *. * TO 'test'@'localhost' IDENTIFIED BY PASSWORD' * FE28814B4A8B3309DAC6ED7D3237ADED6DA1E515' WITH GRANT OPTION | +- -+ 1 row in set (0.00 sec) mysql > revoke insert on *. * from 'test'@'localhost' Query OK, 0 rows affected (0.00 sec) mysql > show grants for 'test'@'localhost' +- - - -+ | Grants for test@localhost | | +- - - -+ | GRANT SELECT UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER CREATE TABLESPACE ON *. * TO 'test'@'localhost' IDENTIFIED BY PASSWORD' * FE28814B4A8B3309DAC6ED7D3237ADED6DA1E515' WITH GRANT OPTION | +- - - -+ 1 row in set (0.00 sec) mysql > exitBye [root@mysql01 3307] # mysql-utest-poldboy123-S / data/3307/mysql.sock-e "show grants for 'test'@'localhost'" | grep GRANT | tail-1 | tr' '\ n' > mysql_all_privileges.txtWarning: Using a password on the command line interface can be insecure. [root@mysql01 3307] # mysql-utest-poldboy123-S / data/3307/mysql.sock-e "show grants for' test'@'localhost'" | grep GRANT | tail-1 | tr' ''\ n' > mysql_all_ privileges. Txt [root @ mysql01 3307] # cat mysql_all_privileges.txt GRANT SELECT UPDATE DELETE CREATE DROP RELOAD SHUTDOWN PROCESS FILE REFERENCES INDEX ALTER SHOW DATABASES SUPER CREATE TEMPORARY TABLES LOCK TABLES EXECUTE REPLICATION SLAVE REPLICATION CLIENT CREATE VIEW SHOW VIEW CREATE ROUTINE ALTER ROUTINE CREATE USER EVENT TRIGGER CREATE TABLESPACE ON *. * TO 'test'@'localhost' IDENTIFIED BY PASSWORD' * FE28814B4A8B3309DAC6ED7D3237ADED6DA1E515' WITH GRANT OPTION [root@mysql01 3307] # vi mysql_all_privileges.txt GRANT SELECT UPDATE DELETE INSERT CREATE DROP RELOAD SHUTDOWN PROCESS FILE REFERENCES INDEX ALTER SHOW DATABASES SUPER CREATE TEMPORARY TABLES LOCK TABLES EXECUTE REPLICATION SLAVE REPLICATION CLIENT CREATE VIEW SHOW VIEW CREATE ROUTINE ALTER ROUTINE CREATE USER EVENT TRIGGER CREATE TABLESPACE ON *. * TO 'test'@'localhost' WITH GRANT OPTION~ "mysql_all_privileges.txt" 28L 370C written

[root@mysql01 3307] #

After reading this article on how to better manage MySQL permissions, many readers will want to know more about it. If you need more industry information, you can follow our industry information section.

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.