Detailed graphic explanation of MySQL user rights management

UserPermission managementmainly 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. Yes Restrict the IP or domain name of the user's login

4. You can limit whether the user's own permissions can be authorized to other users

1. User authorization

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

•all privileges: means granting all permissions to the user. You can also specify specific permissions, such as: SELECT, CREATE, DROP, etc. •on:
indicates which databases and tables these permissions apply to. The format is: database name. table name. "*" here indicates all databases and all tables. If I want to specify the permissions to be applied to the user table of the test library, I can do this. Write: test.user
•to:
Grant permission to which user. Format: "Username"@"Login IP or domain name" % means there is no restriction, and it can be used on any host. Log in. 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 that users are allowed to grant their permissions to other users
You can use GRANT to add permissions to users, and the permissions will be automatically superimposed and will not overwrite previous ones. 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 MySQL. Official website description: http://dev.mysql.com/doc/refman/5.7/en/privileges-provided.html




2. Refresh permissions

After changing the permissions of the user, be sure to reload the permissions and write the permission information from the memory to the database.
mysql> flush privileges;


3. View user permissions

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 yangxin With the create permission of this user, this user will not be able to create databases and tables.

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


5. Delete users

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';

shell> rename user 'test3'@'%' to 'test1'@'%';
7. Change the password


1> Update the 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;

Syntax:set password for 'username'@'login address'=password('password')
mysql> set password for ' root'@'localhost'=password('123456');
3> mysqladmin


##Syntax:
mysqladmin -uusername-p Old password password New password mysql> mysqladmin -uroot -p123456 password 1234abcd

Note: mysqladmin is located in the bin directory of the mysql installation directory


8. Forgot password

1> Add login skip permission check configuration
Modify my.cnf and add skip-grant to the mysqld configuration node -tables configuration

[mysqld]skip-grant-tables


2> Restart the mysql service


shell> service mysqld restart

3> Change password

At this time, the user password is not required when logging in with the mysql command on the terminal, and then change the password according to the first method of changing the password. That’s it.



4> Restore login permissions and skip checking configuration

Configure the skip-grant-tables of the mysqld node in my.cnf Delete it and then restart the service.

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