"Mysql" Common instruction--user action (create, authorize, modify, delete)

Users in MySQL have different user addresses and permissions for each user

There are three ways to create a MySQL user 1, create user 2, Grant 3, Operation Mysql.user table

1, CREATE USER ' username ' @ ' host ' identified by ' password ';

Example: CREATE USER ' aa ' @ ' localhost ' identified by ' 123456 ';

CREATE USER ' AA ' @ ' 192.168.1.101_ ' idendified by ' 123456 ';

CREATE USER ' AA ' @ '% ' identified by ' 123456 ';

CREATE USER ' BB ' @ '% ' identified by ';

CREATE USER ' cc ' @ '% ';

User has two parts format: name @ Host

[Email protected] AA user initiating a link to the machine

[email protected] User BB with client address 152.236.20.10

[Email protected]%% wildcard, indicating all

Syntax:mysql> grant permission 1, permission 2,... Permission n on the database name. Table name to User name @ user address identified by ' connection password ';

Permission 1, permission 2,... The permission n represents the

Select,insert,update,delete,create,drop,index,alter,grant,references,reload,shutdown,process,

Instance of 14 permissions, such as file:

mysql>grant Select,insert,update,delete,create,drop on vtdc.employee to [email  protected] identified by ' 123 ';

Assigns the user Joe from 10.163.225.87 the ability to perform operations such as Select,insert,update,delete,create,drop on the employee table VTDC the database, and sets the password to 123.

Mysql>grant All privileges the vtdc.* to [email protected] identified by ' 123 ';
The
assigns user Joe from 10.163.225.87 the right to all operations on the database VTDC all tables, and sets the password to 123.

Mysql>grant All privileges on * * to [email protected] identified by ' 123 ';
The
gives user Joe from 10.163.225.87 the right to all operations on all tables in all databases and sets the password to 123.

Mysql>grant All privileges on * * to [email protected] identified by ' 123 ';
The
gives the native user Joe permission to perform all operations on all tables in all databases, and sets the password to 123.

3. Insert the record directly into the Mysql.user table:

mysql> INSERT INTO User (Host,user,password) values ('% ', ' Jss_insert ', password (' JSS '));

Mysql>flush privileges; Refresh System Permissions Table

Modify user password: 1, mysqladmin 2, modify Mysql.user table 3, set password

For example: Mysqladmin-u root-p 123 password 456;

2, directly modify user password:

Syntax: Update mysql.user set Password=password (' New password ') where user= " Phplamp "and host=" localhost ";

Instance: Update user set Password=password (' 54netseek ') where user= ' root ';

Flush Privileges;

3, use the SET PASSWORD statement to modify the password: syntax:

SET PASSWORD for ' username ' @ ' host ' = PASSWORD (' NewPassword ');

If the user is currently logged in with Set PASSWORD = PASSWORD ("NewPassword");

Instance:

Set password for [Email protected]=password (');

SET PASSWORD for Name=password (' New PASSWORD ');

SET PASSWORD for ' pig ' @ '% ' = PASSWORD ("123456");

Delete users and Revoke permissions: 1, DROP user 2, revoke authorized users 3, delete records in the Mysql.user table

1. Cancel an account and its permissions

Drop user User;

drop user [email protected] '% '

drop user [email protected]

2. Cancellation of authorized users:

Syntax: REVOKE privilege on databasename.tablename from ' username ' @ ' host ';

Example: REVOKE SELECT on * * from ' pig ' @ '% ';

REVOKE SELECT on Test.user from ' pig ' @ '% ';

Revoke all on * * from [email protected];

Revoke all on user.* from ' admin ' @ '% ';

SHOW GRANTS for ' pig ' @ '% '; View authorizations

3. Delete the User:

Syntax: Delete from user where user = "user_name" and host = "HOST_NAME";

Example: Delete from user where user= ' sss ' and host= ' localhost ';

Reference post: http://blog.csdn.net/leili0806/article/details/8573636