Summary of MySQL grant user permissions

grant all privileges on lap_main_sxdqtri_prd.* to 'cjxx'@'localhost' identified by '123456';
UPDATE mysql.user SET PASSWORD=PASSWORD('techops') where USER='root';

mysql grant Summary of user rights

User rights management has the following functions ： 
1. Which libraries can be restricted to users 、 Which watch  
2. You can limit which tables users execute on SELECT、CREATE、DELETE、DELETE、ALTER Wait for the operation  
3. Users can be restricted to log in IP Or domain name  
4. You can limit whether the user's own permissions can be authorized to other users

One , User authorization
mysql> grant all privileges on *.* 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 etc. .
on： Indicates which databases and tables these permissions apply to , Format ： Database name . Table name , Write here “*” Represents all databases , All tables . If I want to specify that permissions should be applied to test Library user In the table , It can be written like this ：test.user
to： Which user will be granted permission to . Format ：” user name ”@” Sign in IP Or domain name ”.% There is no limit , You can log in on any host . such as ：”yangxin”@”192.168.0.%”, Express yangxin This user can only be in 192.168.0IP Duan login
identified by： Specify the user's login password
with grant option： Indicates that users are allowed to authorize their own permissions to other users

Two , Refresh the permissions
After making a permission change to the user , Be sure to reload the permissions , Write permission information from memory to database .
mysql> flush privileges;

3、 ... and , 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';

Four , Recycling permissions
Delete yangxin This user's create jurisdiction , The user will not be able to create databases and tables .
mysql> revoke create on *.* from '[email protected]';
mysql> flush privileges;

5、 ... and , Delete user
mysql> select host,user from user;
mysql> drop user 'yangxin'@'localhost';

6、 ... and , User rename
shell> rename user 'test3'@'%' to 'test1'@'%';

7、 ... and , Change Password
1. to update mysql.user surface
mysql> use mysql;
# mysql5.7 Before
mysql> update mysql.user set password=password('123456') where user='root';
# mysql5.7 after
mysql> update mysql.user set authentication_string=password('123456') where user='root';
mysql> flush privileges;
2. use set password command
grammar ：set password for ‘ user name ’@’ The login address ’=password(‘ password ’)
mysql> set password for 'root'@'localhost'=password('123456');
3. mysqladmin
grammar ：mysqladmin -u user name -p The old code password New password
mysql> mysqladmin -uroot -p123456 password 1234abcd
Be careful ：mysqladmin be located mysql Installation directory bin Under the table of contents

8、 ... and , Forget the password
1. Add login skip permission check configuration
modify /etc/my.cnf, stay mysqld Configure nodes to add skip-grant-tables To configure
[mysqld]
skip-grant-tables
2. Restart mysql service
shell> service mysqld restart
3. Change Password
At this time, it is used at the terminal mysql No user password is required for command login , Then change the password in the first way .
Be careful ：mysql Library user surface ,5.7 The following version password field is password,5.7 The password field of the above version is authentication_string
UPDATE mysql.user SET PASSWORD=PASSWORD('techops') where USER='root';

4, Restore login permissions skip checking configuration
take /etc/my.cnf in mysqld Node skip-grant-tables Configure delete , Then restart the service .