Create, authorize, delete, and change passwords for MySql study notes users

For mysql User management, it is best not to use the root account when connecting to each Database. You need to allocate corresponding users to ensure the security and convenience of mysql database management.

I. User creation

Method 1: execute the command

Mysql create user 'username' @ 'host' identified by 'password ';

Parameter analysis:

Username: User name
Host: specifies the host that the user can log on to. localhost indicates local logon, % wildcard indicates remote logon, and a separate ip address can be specified.
Password: the user's logon password. If this parameter is not specified, the user can log on without entering the password.

Example:

Mysql> create user 'lxh' @ 'localhost' identified by '000000'; // only allow local login
Mysql> create user 'lxh' @ '%' identified by '000000'; // allow remote logon.
Mysql> create user 'lxh' @ '% 'identified BY ''; // allow users to log on without entering a password (this is not recommended)

Method 2: directly forget to insert user data into the user table in the mysql database

Mysql> user mysql;
Mysql> insert into user (Host, User, Password) values ("localhost", "lxh", password ("123456 "));

II. User authorization

First, you have to log on to the root account (or an account with permissions)

Full permission authorization:

Mysql> GRANT all privileges ON databasename. tablename TO 'username' @ 'host ';

Partial permission authorization

Mysql> grant select, insert on databasename. tablename TO 'username' @ 'host ';

Parameter analysis:
Databasename: name of the database to be authorized. For all databases, you can use *
Tablename: name of the table in the database to be authorized. For all tables, you can use *
Username: mysql User name
Host: host name, which can make the local machine localhost or remote % (this depends on whether the user is a local user or a remote user)

Ps: To authorize another user, run the following command:

Mysql> GRANT all privileges ON databasename. tablename TO 'username' @ 'host' with grant option;

Remember to refresh the system permission table after authorization:

Mysql> flush privileges;

3. Change the user's password

Mysql> grant all privileges on databasename. * to username @ localhost identified by 'newpwd ';

Or directly execute the statement using mysql.

Mysql> update mysql. user set password = password ('New password') where User = "lxh" and Host = "localhost ";

4. Delete a user

Mysql> Delete FROM user Where User = "lxh" and Host = "localhost ";
Mysql> flush privileges;

Ps: after each update operation, it is best to refresh the system permission table flush privileges;

5. Two methods for viewing user permissions

1. Use MySQL grants

 

Mysql> show grants for username @ localhost;
Instance:
Mysql> show grants for root @ localhost;
+ --------------------------------------------------------------------- +
| Grants for root @ localhost |
+ --------------------------------------------------------------------- +
| Grant all privileges on *. * TO 'root' @ 'localhost' with grant option |
+ --------------------------------------------------------------------- +
1 row in set (0.01 sec)

2. Query statements directly through mysql select:

Mysql> select * from mysql. user where user = 'test' and host = '2017. 0.0.1 '\ G;
* *************************** 1. row ***************************
Host: 127.0.0.1
User: test
Password: * EB3C643405D7F53BD4BF7FBA98DCF5641E228833
Select_priv: N
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Reload_priv: N
Shutdown_priv: N
Process_priv: N
File_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Show_db_priv: N
Super_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Execute_priv: N
Repl_slave_priv: N
Repl_client_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Create_user_priv: N
Event_priv: N
Trigger_priv: N
Create_tablespace_priv: N
Ssl_type:
Ssl_cipher:
X509_issuer:
X509_subject:
Max_questions: 0
Max_updates: 0
Max_connections: 0
Max_user_connections: 0
Plugin: mysql_native_password
Authentication_string:
Password_expired: N
1 row in set (0.00 sec)
As you can see, if Select_priv, Insert_priv, Update_priv... is N, the user has no permission, and the user permission is clear at a glance. In this case, you can use the command

6. 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 @ '%'

9>. 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.% ';

10>. grant common DBA permission to manage a MySQL database.

Grant all privileges on testdb to dba @ 'localhost'

The keyword "privileges" can be omitted.

11>. grant Senior DBA permission to manage all databases in MySQL.

Grant all on *. * to dba @ 'localhost'

12>. MySQL grant permissions can be applied to multiple levels.

1. grant applies to the entire MySQL server:

Grant select on *. * to dba @ localhost; -- dba can query tables in all databases in MySQL.

Grant all on *. * to dba @ localhost; -- dba can manage all databases in MySQL

2. grant applies to a single database:

Grant select on testdb. * to dba @ localhost; -- dba can query tables in testdb.

3. grant applies to a single data table:

Grant select, insert, update, delete on testdb. orders to dba @ localhost;

4. grant applies to columns in the table:

Grant select (id, se, rank) on testdb. apache_log to dba @ localhost;

5. grant applies to stored procedures and functions:

Grant execute on procedure testdb. pr_add to 'dba '@ 'localhost'

Grant execute on function testdb. fn_add to 'dba '@ 'localhost'

Note: after modifying the permissions, you must refresh the service or restart the service to use flush privileges.