Detailed explanation of MySQL user rights management examples

This article mainly introduces the relevant information of MySQL user Permission management in detail. Interested friends can refer to

User permission management mainly has the following functions:
1. You can restrict which libraries and tables users can access
2. You can restrict which tables users can perform SELECT, CREATE, DELETE, DELETE, ALTER and other operations
3. You can restrict user login's IP or domain name
4. You can limit whether the user's own permissions can be authorized to other users

1. User authorization

The code is as follows:

mysql> grant all privileges on *.* to 'yangxin'@'%' identified by 'yangxin123456' with grant option;

•all privileges: means granting all privileges to the user. Specific permissions can also be specified, such as: SELECT, CREATE, DROP, etc.
•on: indicates which databases and tables these permissions are effective for, format: database name. table name, writing "*" here indicates all databases and all tables. If I want to specify permissions to be applied to the user table of the test library, I can write: test.user
•to:To which user the permissions are granted. Format: "Username"@"Login IP or domain name". % means there is no restriction and you can log in from any host. For example: "yangxin"@"192.168.0.%" means that the user yangxin can only log in in the 192.168.0 IP segment
•identified by: Specify the user's login password
•With grant option: means allowing users to authorize their permissions to other users

You can use GRANT to add permissions to users , permissions will be automatically superimposed and will not overwrite previously granted permissions. For example, if you first add a SELECT permission to the user, and then add an INSERT permission to the user, then the user will have both SELECT and INSERT permissions.

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

2. Refresh permissions

After making permission changes to the user, be sure to remember to reload the permissions and change the permissions Information is written from memory to the database.

mysql> flush privileges;

3. View user permissions

The code is as follows:

mysql> grant select,create,drop,update,alter on *.* to 'yangxin'@'localhost' identified by 'yangxin0917' with grant option;
mysql> show grants for 'yangxin'@'localhost';

4. Recover permissions

Delete the create permission of the user yangxin, and the user will not be able to create databases and tables.

mysql> revoke create on *.* from 'yangxin@localhost';
mysql> flush privileges;

5. Delete user

mysql> select host,user from user;
+---------------+---------+
| host   | user |
+---------------+---------+
| %    | root |
| %    | test3 |
| %    | yx  |
| 192.168.0.% | root |
| 192.168.0.% | test2 |
| 192.168.0.109 | test |
| ::1   | yangxin |
| localhost  | yangxin |
+---------------+---------+
8 rows in set (0.00 sec)
mysql> drop user 'yangxin'@'localhost';

6. Rename user

##shell> rename user 'test3 '@'%' to 'test1'@'%';

7. Change password

1> Update mysql. user table

mysql> use mysql;
# mysql5.7之前
mysql> update user set password=password('123456') where user='root';
# mysql5.7之后
mysql> update user set authentication_string=password('123456') where user='root';
mysql> flush privileges;

2> Use the set password command

Syntax: set password for 'Username'@'Login address'=password('password')

mysql> set password for 'root'@'localhost'=password('123456');

3> mysqladmin

Syntax: mysqladmin -u username -p old password password new password

mysql> mysqladmin -uroot -p123456 password 1234abcd

Note: mysqladmin is located in the bin directory of the mysqlinstallation directory

8. Forgot password

##1> Add login skip permission check configurationModify my.cnf, Add skip-grant-tables configuration to the mysqld configuration node

[mysqld]

skip-grant-tables

##2> Restart mysql service

shell> service mysqld restart

3> Change password

At this time You do not need a user password when logging in with the mysql command on the terminal, and then change the password according to the first method of changing the password.

4> Restore login permissions and skip checking configuration

Delete the skip-grant-tables configuration of the mysqld node in my.cnf, and then restart the service That’s it.

The above is the detailed content of Detailed explanation of MySQL user rights management examples. For more information, please follow other related articles on the PHP Chinese website!