Management of MySQL Users

management of MySQL users

First, view the current Connection account information

1.1, view the current database to connect the account information

Use command: Show Processlist

MySQL [(None)]> show processlist;

+--------+-------------+---------------------+--------+---------+------+----------+------------------+

| Id | User | Host | db | Command | Time | State | Info |

+--------+-------------+---------------------+--------+---------+------+----------+------------------+

| 232091 | Zabbix | 172.17.207.88:558 | Zabbix |   Sleep |          20 | | NULL |

1.2. See what account is currently in use

View using the command Select User () command

MySQL [(None)]> Select User ();

+--------------------+

| User () |

+--------------------+

| [Email protected] |

+--------------------+

1 row in Set (0.00 sec)

MySQL [(None)]>

Second, create the user

2.1, the creation of new users

Create a user and create a password using the creat user command

In the following: Create user ' Zhang ' @ ' localhost ' identified by ' Zhang ';

• Create a Zhang user can use any address to access and set the password for Zhang

MySQL [(None)]> create user ' Zhang ' @ '% ' identified by ' Zhang ';

Query OK, 0 rows affected (0.01 sec)

MySQL [(None)]>

• To see if the creation was successful after Setup is complete

MySQL [(None)]> select User,host from Mysql.user;

+-------------+----------------+

| user | Host |

+-------------+----------------+

| Jumpserver | %              |

| Root | %              |

| WordPress | %              |

| Zabbix | 39.106.3.162 |

| %           | localhost |

| Zhang | localhost |

+-------------+----------------+

9 rows in Set (0.01 sec)

MySQL [(None)]>

• Sign in with the newly created user Zhang and view the database

[Email protected] ~]# mysql-uzhang-h120.26.32.14-p

Enter Password:

Welcome to the MariaDB Monitor. Commands End With; or \g.

Your MySQL Connection ID is 1204

Server version:5.6.35 Source Distribution

Copyright (c), Oracle, MariaDB Corporation Ab and others.

Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.

• View Database

MySQL [(None)]> show databases;

+--------------------+

| Database |

+--------------------+

| Information_schema |

| Test |

+--------------------+

2 rows in Set (0.02 sec)

MySQL [(None)]>

Third, delete the database account

To delete a user by using the drop user command

MySQL [(None)]> drop user ' Zhang ' @ ' localhost ';

Query OK, 0 rows Affected (0.00 sec)

MySQL [(None)]>

Iv. Renaming users

4.1. Rename user by modifying with rename user command

MySQL [(None)]> rename user ' Zhang ' @ '%c ' to ' Zhang ' @ '% ';

Query OK, 0 rows affected (0.02 sec)

MySQL [(None)]> select User,host from Mysql.user;

+-------------+-------------------+

| user | Host |

+-------------+-------------------+

| Root | %                 |

| User_name | %                 |

| xuchangming | %                 |

| Zhang | %                 |

| Root | 127.0.0.1 |

| Root | :: 1 |

| | instance-jvfp1b6r |

| Root | instance-jvfp1b6r |

| Root | localhost |

| xuchangming | localhost |

+-------------+-------------------+

Rows in Set (0.01 sec)

MySQL [(None)]>

Five, authorized account number

5.1. Use the grant command to authorize an account

The command format is:

Grant permissions privileges on library. Table to ' account number ' @ ' IP ' [identified by ' login password '];

Library Table Permission Description:

On * *: Administrator privileges, any database can be manipulated

On db_name.*: Specifies that only one library has permission to operate on a library

On Db_name.tables_name: Specifies that a table in one library has permission to operate

On db_name.routine_name: Specifies a stored procedure or storage function for a library

5.2. Use the command Show grants command to view permissions

SQL [(None)]> show grants;

+-------------------------------------------------------------------------------------------------------------- ------------------+

|                                                                                                              Grants for [email protected]% |

+-------------------------------------------------------------------------------------------------------------- ------------------+

| GRANT all privileges on * * to ' root ' @ '% ' identified by PASSWORD ' *0fc3121124c80f34b383f5fca33f0d68b6afa1c0 ' with GRANT OP tion |

+-------------------------------------------------------------------------------------------------------------- ------------------+

1 row in Set (0.01 sec)

MySQL [(None)]>

5.3.

5.3.1, authorizing all rights "Administrator rights" to an account

Create boos user and set login password as boss, authorize all operations on all libraries and tables and allow all addresses to connect

MySQL [(None)]> Grant all privileges on * * to ' boos ' @ '% ' identified by ' boss ';

Query OK, 0 rows affected (0.02 sec)

MySQL [(None)]>

Log in to view

[Email protected] ~]# mysql-uboos-h120.76.32.14 -P

Enter Password:

Welcome to the MariaDB Monitor. Commands End With; or \g.

Your MySQL Connection ID is 1217

Server version:5.6.35 Source Distribution

Copyright (c), Oracle, MariaDB Corporation Ab and others.

Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.

MySQL [(None)]> show databases;

+--------------------+

| Database |

+--------------------+

| Information_schema |

| Test |

| Ceshi |

| Employees |

| MySQL |

| Performance_schema |

| Test |

+--------------------+

7 rows in Set (0.01 sec)

MySQL [(None)]>

MySQL [(None)]> Select User ();

+---------------------+

| User () |

+---------------------+

| [Email protected] |

+---------------------+

1 row in Set (0.02 sec)

MySQL [(None)]>

5.3.2, authorizing all permissions to an account for only one database

Create account Zhang and add password Zhang, modify the permissions to all operations of the Ceshi library

MySQL [(None)]> grant all privileges on ceshi.* to ' Zhang ' @ ' percent ' identified by ' Zhang ';

Query OK, 0 rows affected (0.02 sec)

MySQL [(None)]>

[Email protected] ~]# mysql-uzhang-h120.76.32.14 -P

Enter Password:

Welcome to the MariaDB Monitor. Commands End With; or \g.

Your MySQL Connection ID is 1458

Server version:5.6.35 Source Distribution

Copyright (c), Oracle, MariaDB Corporation Ab and others.

Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.

MySQL [(None)]> show databases;

+--------------------+

| Database |

+--------------------+

| Information_schema |

| Ceshi |

| Test |

+--------------------+

3 Rows in Set (0.02 sec)

MySQL [(None)]> show grants;

+-------------------------------------------------------------------+

| Grants for [email protected]% |

+-------------------------------------------------------------------+

| GRANT USAGE on * * to ' Zhang ' @ '% ' identified by PASSWORD <secret> |

| GRANT all Privileges "Ceshi". * to ' Zhang ' @ '% ' |

+-------------------------------------------------------------------+

2 rows in Set (0.01 sec)

MySQL [(None)]> use test;

Database changed

MySQL [test]> show tables;

Empty Set (0.02 sec)

5.3.3, authorize a certain permission to an account, only for a database to operate

Create account Zhang and run all IP address connections and create password Zhang, set permissions to select queries only for Ceshi databases

MySQL [(None)]> Grant Select on ceshi.* to ' Zhang ' @ ' percent ' identified by ' Zhang ';

Query OK, 0 rows affected (0.02 sec)

MySQL [(None)]>

MySQL [(None)]> show grants;

+-------------------------------------------------------------------+

| Grants for [email protected]% |

+-------------------------------------------------------------------+

| GRANT USAGE on * * to ' Zhang ' @ '% ' identified by PASSWORD <secret> |

| GRANT SELECT on ' Ceshi '. * to ' Zhang ' @ '% ' |

+-------------------------------------------------------------------+

2 rows in Set (0.02 sec)

MySQL [(None)]>

Create a table for testing with creation, if you have permission to create it, the following display is not created successfully, indicating no permissions

MySQL [ceshi]> CREATE table t1;

ERROR 1142 (42000): CREATE command denied to user ' "Zhang ' @ '120.76.32.14' for table ' t1 '

MySQL [ceshi]>

Add create creation permission to the Zhang account

MySQL [(None)]> Grant Create on ceshi.* to ' Zhang ' @ '% ' of ' identified by ' Zhang ';

Query OK, 0 rows affected (0.02 sec)

View this account permissions

MySQL [(None)]> show grants for ' Zhang ' @ '% ';

+------------------------------------------------------------------------------------------------------+

| Grants for [email protected]% |

+------------------------------------------------------------------------------------------------------+

| GRANT USAGE on * * to ' Zhang ' @ '% ' identified by PASSWORD ' *5d83a6402df44a7d8ec2b8861b19f8a2f4f3ea2f ' |

| GRANT SELECT, CREATE on ' Ceshi '. * to ' Zhang ' @ '% ' |

+------------------------------------------------------------------------------------------------------+

2 rows in Set (0.01 sec)

MySQL [(None)]>

5.3.4, authorizing a column

MySQL [ceshi]> Grant Select (table_name,engine) on test.t to ' Zhang ' @ ' localhost ';

Vi. Revocation of permissions

Format command:revoke permissions on library. Table from ' user ' @ ' host ';

View the current list of permissions for Zhang users

MySQL [ceshi]> Show grants for ' Zhang ' @ '% ';

+------------------------------------------------------------------------------------------------------+

| Grants for [email protected]% |

+------------------------------------------------------------------------------------------------------+

| GRANT USAGE on * * to ' Zhang ' @ '% ' identified by PASSWORD ' *5d83a6402df44a7d8ec2b8861b19f8a2f4f3ea2f ' |

| GRANT SELECT, CREATE on ' Ceshi '. * to ' Zhang ' @ '% ' |

+------------------------------------------------------------------------------------------------------+

2 rows in Set (0.02 sec)

MySQL [ceshi]>

Delete the Create permission for the Zhang user so that it cannot be created using the

MySQL [ceshi]> Revoke create on ceshi.* from ' Zhang ' @ '% ';

Query OK, 0 rows affected (0.02 sec)

MySQL [ceshi]> Show grants for ' Zhang ' @ '% ';

+------------------------------------------------------------------------------------------------------+

| Grants for [email protected]% |

+------------------------------------------------------------------------------------------------------+

| GRANT USAGE on * * to ' Zhang ' @ '% ' identified by PASSWORD ' *5d83a6402df44a7d8ec2b8861b19f8a2f4f3ea2f ' |

| GRANT SELECT on ' Ceshi '. * to ' Zhang ' @ '% ' |

+------------------------------------------------------------------------------------------------------+

2 rows in Set (0.01 sec)

MySQL [ceshi]>

Seven, change the password of the account

Format command:set password for ' user ' @ ' host ' = password (' New-password ');

MySQL [ceshi]> Set password for ' Zhang ' @ '% ' = password (' boss ');

Query OK, 0 rows affected (0.02 sec)

Viii. How to limit the resources of an account

Resources can be included for:

Resource_option: {

| Max_queries_per_hour Count

| Max_updates_per_hour Count

| Max_connections_per_hour Count

| Max_user_connections Count

Number of links per one hours

How many times per account per one-hour query

How many times each account is updated every one hours

Number of concurrent links per account every one hours

8.1. No more than 2 queries per one hours

MySQL [ceshi]> Grant all privileges on * * to ' bosses ' @ '% ' with max_queries_per_hour 2;

Query OK, 0 rows affected (0.02 sec)

MySQL [ceshi]>

Nine, recover password

[Email protected] ~]#/usr/local/mysql/bin/mysqld_safe--skip-grant-tables &

[[Email protected] ~] #mysql

Clear root Password

MySQL [ceshi]> Update user set password= ' where user= ' root ' and host= ' localhost '

Management of MySQL Users