[Mysql] Common commands-user operations (create, authorize, modify, delete)

[Mysql] Common commands-user operations (create, authorize, modify, delete)

Each user in Mysql corresponds to different user addresses and permissions.

There are three ways to create a Mysql user: 1. create user 2. grant 3. Operate the mysql. user table.

1. create user 'username' @ 'host' identified by 'Password ';

Example: create user 'A' aa '@ 'localhost' identified by '123 ';

Create user 'A' @ '192. 168.1.101 _ 'idendified BY '123 ';

Create user 'A' @ '%' identified by '201312 ';

Create user 'bb '@' % 'identified '';

Create user 'cc' @ '% ';

The user has two component formats: Name @ host

Aa @ localhost: The aa user who initiates a link on the local machine

User bb with bb@152.236.20.10 client address 152.236.000010

Cc @ % wildcard, indicating all

2. Use the grant Statement (authorization method)

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

Permission 1, permission 2,... Permission n stands

Select, insert, update, delete, create, drop, index, alter, grant, references, reload, shutdown, process, file, and other 14 Permissions

Instance:

Mysql> grant select, insert, update, delete, create, drop on vtdc. employee to joe@10.163.225.87 identified by '20140901 ';

Assign the user joe from 10.163.225.87 the permission to perform select, insert, update, delete, create, drop, and other operations on the database's vtdc employee table, and set the password to 123.

Mysql> grant all privileges on vtdc. * to joe@10.163.225.87 identified by '20140901 ';

Assign the user joe from 10.163.225.87 the permission to perform all operations on all tables in the database vtdc and set the password to 123.

Mysql> grant all privileges on *. * to joe@10.163.225.87 identified by '20140901 ';

Assign the user joe from 10.163.225.87 the permission to perform all operations on all tables in all databases and set the password to 123.

Mysql> grant all privileges on *. * to joe @ localhost identified by '20140901 ';

Grant the local user joe the permission to perform all operations on all tables in all databases and set the password to 123.

3. insert records directly to the mysql. user table:

Mysql> insert into user (host, user, password) values ('%', 'jss _ insert', password ('jss '));

Mysql> flush privileges; // refresh the system permission list

Change user password: 1. mysqladmin 2. Modify mysql. user table 3. set password

1. Use mysqladmin Syntax: mysqladmin-u username-p old password New password

For example, mysqladmin-u root-p 123 password 456;

2. directly modify the user password of the user table:

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 change the PASSWORD: Syntax:

Set password for 'username' @ 'host' = PASSWORD ('newpassword ');

If the current login user uses set password = PASSWORD ("newpassword ");

Instance:

Set password for root @ localhost = password ('');

Set password for name = PASSWORD ('new password ');

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

Delete users and revoke permissions: 1. drop user 2. Cancel authorized users 3. delete records in the mysql. user table

1. Cancel an account and its permissions

Drop USER user;

Drop user username @ '%'

Drop user username @ localhost

2. cancelling 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 sss @ localhost;

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

Show grants for 'pig' @ '%'; // view authorization

3. delete a user:

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

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

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