Rights Management for MySQL

MySQL permissions granted to the Recycle

The biggest difficulty in life is laziness.

began to stir up the agitation. Permissions on MySQL are granted to the recycle

User Rights Management mainly has the following functions:
1. You can restrict which libraries a user accesses, which tables
2. You can restrict which tables the user performs for select, CREATE, delete, delete, alter, etc.
3. The IP or domain name that can restrict user login
4. You can restrict whether the user's own permissions can be authorized to other users

1. View users in the current system

Mysql> Select Host,user from mysql.user;+-----------+------------------+| Host | User |+-----------+------------------+| %         | Root | | %         | Test | | localhost | Debian-sys-maint | | localhost | mysql.session | | localhost | Mysql.sys | | localhost | Root |+-----------+------------------+6 rows in Set (0.00 sec)

2. Granting permissions

Mysql> grant all on *.* to [email protected] '% '  identified by   ' CMZ '; query ok, 0 rows affected, 1 warning  (0.00 sec) mysql> select  host,user from mysql.user;+-----------+------------------+| host       | user             |+------- ----+------------------+| %         | cmz               | |  %         | root              | |  %         | test              | |  localhost | debian-sys-maint | |  localhost | mysql.session    | |  localhost | mysql.sys        | |  localhost | root              |+-----------+------------------+7 rows in set  (0.00 sec) mysql> flush  privileges;  #  Refresh query ok, 0 rows affected  (0.00 sec)

• All privileges: Indicates that all permissions are granted to the user. You can also specify specific permissions, such as: SELECT, CREATE, drop, and so on.

• on: Indicates which databases and tables are in effect for these permissions, in the format: Database name. Table name, where the "*" is written to represent all databases, all tables. If I want to specify that permissions should be applied to the user table of the test library, you can write this: Test.user

• to: Which user to grant permissions to. Format: "User name" @ "Login IP or domain name". % means no limit and can be logged in on any host. For example: ' Leco ' @ ' 192.168.0.% ', means Leco This user can only log in 192.168.0 IP segment

• identifiedby: Specify the user's login password

• With Grant OPTION: Allows users to authorize their own permissions to other users

You can use grant to add permissions to the user, the permissions will automatically overlay, not overwrite the permissions previously granted, such as when you first add a SELECT permission to the user, and then add an INSERT permission to the user, then the user has both select and insert permissions.

For a list of user details, please refer to the MySQL website description: http://dev.mysql.com/doc/refman/5.7/en/privileges-provided.html

3. View the permissions granted

Mysql> Show grants for cmz;+------------------------------------------+| Grants for [email protected]% |+------------------------------------------+| GRANT all privileges on * * to ' cmz ' @ '% ' |+------------------------------------------+1 row in Set (0.00 sec)

4. Reclaim Permissions

mysql> revoke delete,create on *.* from  cmz; query ok, 0 rows affected  (0.00 sec) mysql> show grants for  cmz\G;*************************** 1. row ***************************Grants for  [email protected]%: grant select, insert, update, 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  ' cmz ' @ '% ' 1 row in set  (0.00 sec) error: no query  specified 

You can see that the permission to delete and create is gone.

Rights Management for MySQL