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 ';
MySQL [(None)]> create user ' Zhang ' @ '% ' identified by ' Zhang ';
Query OK, 0 rows affected (0.01 sec)
MySQL [(None)]>
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)]>
[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.
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