Mysql User Rights Management detailed _mysql

User Rights Management mainly has the following functions:
1. Can restrict user access to which libraries, which tables
2. You can restrict which tables the user performs 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

First, user authorization

Copy Code code as follows:

Mysql> grant all privileges in *.* to ' yangxin ' @ '% ' identified by ' yangxin123456 ' with GRANT option;

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 write "*" means all databases, all tables. If I want to specify that permissions be applied to the user table in the test library, you can write this: Test.user
to:Which user to grant permissions to. Format: "username" @ "Login IP or domain name". % indicates no limit and can be logged on to any host. For example: "Yangxin" @ "192.168.0.%", which means that yangxin user can only log on in 192.168.0IP segment
identified by:Specify a user's logon password
with GRANT option:Indicates that users are allowed to authorize their own permissions to other users

You can add permissions to a user by using grant, which automatically overlays and does not overwrite previously granted permissions, such as when you add a SELECT permission to a user and then add an INSERT permission to the user, then the user has both select and insert permissions.

User Details permission list please refer to MySQL official website description:http://dev.mysql.com/doc/refman/5.7/en/privileges-provided.html

Second, the right to refresh

After the user has made the permission change, must remember to reload the permission, the permission information writes the database from the memory.

mysql> flush Privileges;

Third, view user rights

Copy Code code as follows:

Mysql> Grant Select,create,drop,update,alter on *.* to ' yangxin ' @ ' localhost ' identified by ' yangxin0917 ' with Grant opt Ion
Mysql> Show grants for ' yangxin ' @ ' localhost ';

Iv. Recycling Permissions

Delete Yangxin This user's create permission, the user will not be able to create the database and table.

Mysql> revoke create on *.* from ' Yangxin@localhost ';
mysql> flush Privileges;

V. Delete users

Mysql> select Host,user from user;
+---------------+---------+
| host   | user |
+---------------+---------+
|%    | 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 ';

Vi. User Renaming

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

Seven, modify the password

1> Update Mysql.user Table

mysql> use MySQL;
# mysql5.7 before
mysql> Update user set Password=password (' 123456 ') where user= ' root ';
# after mysql5.7
mysql> update user set Authentication_string=password (' 123456 ') where user= ' root ';
mysql> flush Privileges;

2> with 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 MySQL installation directory in the bin directory

Eight, forget the password

1> Add login Skip Permission Check configuration

modifying my.cnf, adding Skip-grant-tables configuration to the MYSQLD configuration node

[Mysqld]
Skip-grant-tables

2> Restart the MySQL service

Shell> Service mysqld Restart

3> Modify Password

At this point in the terminal with the MySQL command to log in without requiring a user password, and then modify the password according to the first way the password can be modified.

4> Restore logon permissions Skip Check configuration

Remove the Skip-grant-tables configuration of the mysqld node in my.cnf, and then restart the service.

The above is the entire content of this article, I hope to help you learn, but also hope that we support the cloud habitat community.