MySQL user creation, and authorization

1. First look at all the users in the system:
Select Host,user from Mysql.user;

2, delete the system's redundant account syntax drop user "user" @ "host Domain" note the quotation marks, can be single or double quotation marks;
Example: Drop user ' @ ' moban2 '
#如果为空直接为空即可;
#如果drop删除不了 (typically special characters or uppercase), you can delete them in the following way:
Example: Delete from Mysql.user where user= ' root ' and host= ' 127.0.0.0.1 ';

3. When creating a user, it is best to first view the Grant command help by helping:
CREATE USER ' Jeffrey ' @ ' localhost ' identified by ' mypass ';
GRANT all on db1.* to ' Jeffrey ' @ ' localhost ';
GRANT SELECT on Db2.invoice to ' Jeffrey ' @ ' localhost ';
GRANT USAGE on *. Jeffrey ' @ ' localhost ' with max_queries_per_hour 90;

4, operation and maintenance personnel commonly used to create a method, use the grant command when creating a user, Authorization permission:
Example: Grant all privileges the db1.* to [e-mail protected] identified by "password";
# Grant all privileges in db1.* to [e-mail protected] identified by ' passwd ' authorization command corresponding permissions (all permissions) target: libraries and tables Account name and client host user password

5. To refresh the permissions after the authorization is complete:

Flush privileges;

6. View created Users:

Select Host,user from Mysql.user;

7. View permissions to create a user: show grants for [email protected];
#USAGE indicates that the user can only log on, no other permissions, the operation of the time display access denied;

Or:
View Help: Helper Create user
CREATE USER ' Jeffrey ' @ ' localhost '
identified with My_auth_plugin;
Create a user first :

Create user [email protected] identified by "password";
To view User rights:

Show grants for [email protected];
in the authorization:

Grant all on dbname.* to [email protected];
To view permissions:

Show grants for [email protected];

8, authorized LAN host remote connection database, the common use of% matching method:
Example: Grant all on * * to [e-mail protected] ' 10.10.36.% ' identified by "123456";
Refresh permissions: Flush privileges;
Login using-H to specify the host,-P specify port

Example: Mysql-u username_2-p-H 10.10.36.170

Determine the permissions that MySQL can authorize, if you do not know:
⑴ Help VIEW: Revoke (permission recall)

    revoke all privileges, GRANT OPTION From user [, user] ...
⑵ Permissions View: Show grants for [email protected];
⑶ Retract insert permission: Revoke insert  on  * * from ' user_name ' @ ' localhost ';
  #注意此处指定数据库
⑷ access after logging in to the database: show grants for [email protected];
⑸ after exiting the database: MYSQL-UROOT-P123456-E "show grants for" username "@" localhost ";" | Grep-i grant  | Tail-1|tr ' \ n ' >all.privileges
The following permissions are in the database:
SELECT query \insert insert \update update \delete Delete \create Create library and table \drop  ;    Delete libraries and tables \index     index \alter     Modify \create temporary TABLES create temporary table \ LOCK tables& nbsp;    Lock table \ execute     execute \ Create view     CREATE VIEW \ SHOW view     Show view \ Create ROUTINE create stored procedure \alter routine     Modify stored procedures \ Event          events \ TRIGGER      Trigger
or: SELECT * from Mysql.user\g;

9, for the blog, CMS and other products installed during the use of the most basic principle: In addition to select,insert,update,delete4 a right

Also requires create,drop and other dangerous rights.
Example: Grant Select,insert,update,delete,create,drop on blog.* to [email protected] ' 10.10.36.% '

Identified by "password";

10, the production database after the recovery of Rights (best evaluation):
Example: Revoke Create,drop on blog.* from [email protected] ' 10.10.36.% ';

The master-slave database permission settings are slowly being mended.

This article from "As always" blog, declined reprint!

MySQL user creation, and authorization