Shulou(Shulou.com)06/01 Report--

Management of mysql users

1. View the current connection account information

1.1. View the account information of the current database to connect

Use the command: show processlist

MySQL [(none)] > show processlist

+-+ +

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

+-+ +

| | 232091 | zabbix | 172.17.207.88 NULL | zabbix | Sleep | 20 | | NULL |

1.2. Check what account is currently used to log in

Use the command select user () command to view

MySQL [none)] > select user ()

+-+

| | user () |

+-+

| | root@172.17.37.88 |

+-+

1 row in set (0.00 sec)

MySQL [(none)] >

Second, create users

2.1. Creation of new users

Use the creat user command to create a user and a password

Liezi: create user 'zhang'@'localhost' identified by' zhang'

Create zhang users can use any address to access and set the password to zhang

MySQL [(none)] > create user 'zhang'@'%' identified by' zhang'

Query OK, 0 rows affected (0.01 sec)

MySQL [(none)] >

Check whether the creation is successful after the setting is completed.

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)] >

Log in and view the database using the newly created user zhang

[root@iZ2zegql6fupnhn8aei0bgZ] # mysql-uzhang-h220.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) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

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

View the database

MySQL [(none)] > show databases

+-+

| | Database |

+-+

| | information_schema |

| | test |

+-+

2 rows in set (0.02 sec)

MySQL [(none)] >

Delete the database account

Delete a user using the drop user command

MySQL [(none)] > drop user 'zhang'@'localhost'

Query OK, 0 rows affected (0.00 sec)

MySQL [(none)] >

IV. Rename users

4.1. use the rename user command to modify and rename the user

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 |

+-+ +

10 rows in set (0.01 sec)

MySQL [(none)] >

5. Authorized account

5.1.Use grant command to authorize accounts

The format of the command is:

Grant permission privileges on library. Table to 'account' @ 'ip' [identified by' login password']

Database table permissions description:

On *. *: administrator privilege, any database can be operated

On db_name.*: specifies to operate on a library, and only has permissions for a library

On db_name.tables_name: specifies that a table in a library has operation permissions

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

5.2.Use the command show grants command to view permissions

SQL [(none)] > show grants

+-+

| | Grants for root@% |

+-+

| | GRANT ALL PRIVILEGES ON *. * TO 'root'@'%' IDENTIFIED BY PASSWORD' * 0FC3121124C80F34B383F5FCA33F0D68B6AFA1C0' WITH GRANT OPTION |

+-+

1 row in set (0.01 sec)

MySQL [(none)] >

5.3. Liezi

5.3.1. Authorize all permissions [administrator permissions] to a certain account

Create a boos user and set the login password to boss, authorize all operations on all libraries and tables, and allow all address connections

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

[root@iZ2zegql6fupnhn8aei0bgZ] # 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) 2000, 2017, 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 () |

+-+

| | boos@120.76.32.14 |

+-+

1 row in set (0.02 sec)

MySQL [(none)] >

5.3.2. Authorize all permissions to an account only for a certain database

Create the account zhang and add the password zhang, and modify all operations of the permission to the ceshi library

MySQL [(none)] > grant all privileges on ceshi.* to 'zhang'@'%' identified by' zhang'

Query OK, 0 rows affected (0.02 sec)

MySQL [(none)] >

[root@iZ2zegql6fupnhn8aei0bgZ] # 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) 2000, 2017, 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 zhang@% |

+-+

| | GRANT USAGE ON *. * TO 'zhang'@'%' IDENTIFIED BY PASSWORD |

| | GRANT ALL PRIVILEGES ON `ceshi`. * TO 'zhang'@'%' |

+-+

2 rows in set (0.01sec)

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 and operate only for one database

Create the account zhang and run all ip address connections and create the password zhang, and set the permission to select query only against the ceshi database

MySQL [(none)] > grant select on ceshi.* to 'zhang'@'%' identified by' zhang'

Query OK, 0 rows affected (0.02 sec)

MySQL [(none)] >

MySQL [(none)] > show grants

+-+

| | Grants for zhang@% |

+-+

| | GRANT USAGE ON *. * TO 'zhang'@'%' IDENTIFIED BY PASSWORD |

| | GRANT SELECT ON `ceshi`. * TO 'zhang'@'%' |

+-+

2 rows in set (0.02 sec)

MySQL [(none)] >

Use create to create a table to test whether you have permission to create it. If it is not created successfully as shown below, it means there is no permission.

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'@'%' identified by' zhang'

Query OK, 0 rows affected (0.02 sec)

View this account permissions

MySQL [(none)] > show grants for 'zhang'@'%'

+-+

| | Grants for zhang@% |

+-+

| | GRANT USAGE ON *. * TO 'zhang'@'%' IDENTIFIED BY PASSWORD' * 5D83A6402DF44A7D8EC2B8861B19F8A2F4F3EA2F' |

| | GRANT SELECT, CREATE ON `ceshi`. * TO 'zhang'@'%' |

+-+

2 rows in set (0.01sec)

MySQL [(none)] >

5.3.4. Authorize a column

MySQL [ceshi] > grant select (table_name,engine) on test.t to 'zhang'@'localhost'

VI. Revocation of authority

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

View the current permission list of zhang users

MySQL [ceshi] > show grants for 'zhang'@'%'

+-+

| | Grants for zhang@% |

+-+

| | 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 permission of the create of the zhang user so that it cannot be created using create

MySQL [ceshi] > revoke create on ceshi.* from 'zhang'@'%'

Query OK, 0 rows affected (0.02 sec)

MySQL [ceshi] > show grants for 'zhang'@'%'

+-+

| | Grants for zhang@% |

+-+

| | GRANT USAGE ON *. * TO 'zhang'@'%' IDENTIFIED BY PASSWORD' * 5D83A6402DF44A7D8EC2B8861B19F8A2F4F3EA2F' |

| | GRANT SELECT ON `ceshi`. * TO 'zhang'@'%' |

+-+

2 rows in set (0.01sec)

MySQL [ceshi] >

7. 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)

8. How to restrict the resources of an account

Resources can include:

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 hour

How many queries per hour for each account?

How many times per hour does each account update?

How many concurrent links per account per hour

8.1. No more than 2 queries per hour

MySQL [ceshi] > grant all privileges on *. * to 'boss'@'%' with MAX_QUERIES_PER_HOUR 2

Query OK, 0 rows affected (0.02 sec)

MySQL [ceshi] >

9. Retrieve the password

[root@iZ2ze2rrr9fg73mstmet9tZ] # / usr/local/mysql/bin/mysqld_safe-- skip-grant-tables &

[root@iZ2ze2rrr9fg73mstmet9tZ ~] # mysql

Clear root password

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.