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
shell> service mysqld restart
3> Change passwordAt 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!