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

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

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

How does MySQL authorize a self-created user such as daitest to create a new database? Command

Take a look
In mysql, you can grant one or more permissions to a user, such as select, insert, update, and delete. The grant command is used in the following format:
Grant permission on database objects to users
1. grant normal data users the right to query, insert, update, and delete all table data in the database.
Grant select on testdb. * to common_user @ '%'
Grant insert on testdb. * to common_user @ '%'
Grant update on testdb. * to common_user @ '%'
Grant delete on testdb. * to common_user @ '%'
Alternatively, replace the following with a mysql command:
Grant select, insert, update, delete on testdb. * to common_user @ '%'

2. grant database developers to create tables, indexes, views, stored procedures, and functions... .
Grant permissions to create, modify, and delete mysql Data Table structures.
Grant create on testdb. * to developer @ '192. 192.% ';
Grant alter on testdb. * to developer @ '192. 192.% ';
Grant drop on testdb. * to developer @ '192. 192.% ';
Grant the mysql foreign key operation permission.
Grant references on testdb. * to developer @ '192. 192.% ';
Grant the permission to operate mysql temporary tables.
Grant create temporary tables on testdb. * to developer @ '2017. 192.% ';
Grant the permission to operate mysql indexes.
Grant index on testdb. * to developer @ '192. 192.% ';
Grant permissions to operate the mysql view and view the source code.
Grant create view on testdb. * to developer @ '192. 192.% ';
Grant show view on testdb. * to developer @ '192. 192.% ';
Grant permissions to operate mysql stored procedures and functions.
Grant create routine on testdb. * to developer @ '192. 192.% ';-now, can show procedure status
Grant alter routine on testdb. * to developer @ '192. 192.% ';-now, you can drop a procedure
Grant execute on testdb. * to developer @ '192. 192.% ';

3. grant common dba permission to manage a mysql database.
Grant all privileges on testdb to dba @ 'localhost'
The keyword "privileges" can be omitted.

4. grant senior dba permission to manage all databases in mysql.
Grant... the remaining full text>

Mysql user creation Problems

Add new users.
Format: grant select on database. * to username @ login host identified by "password"
1. Add a user named "test1" with the password "abc" so that he can log on to any host and have the permission to query, insert, modify, and delete all databases. First, use the root user to connect to MYSQL, and then type the following command:
Grant select, insert, update, delete on *. * to [email = test1 @ "%] test1 @" % [/email] "Identified by" abc ";
However, the added users are very dangerous. If someone knows the password of test1, then he can log on to your mysql database on any computer on the internet and do whatever he wants for your data. See solution 2.
2. Add a user named "test2" with the password "abc" so that the user can only log on to localhost and query, insert, modify, and delete the database mydb (localhost refers to the local host, that is, the host of the MYSQL database ),
In this way, the user knows the password of test2 and cannot directly access the database from the internet. He can only access the database through the web page on the MYSQL host.
Grant select, insert, update, delete on mydb. * to [email = test2 @ localhost] test2 @ localhost [/email] identified by "abc ";
If you do not want test2 to have a password, you can run another command to remove the password.
Grant select, insert, update, delete on mydb. * to [email = test2 @ localhost] test2 @ localhost [/email] identified by "";
Next I will perform database operations in MYSQL. Note: you must first log on to MYSQL. The following operations are performed at the MYSQL prompt and each command ends with a semicolon.