MariaDB user and permission management

1. MariaDB account management

MariaDB provides many statements to manage user accounts. These statements can be used to manage including logging in and logging out of the MariaDB server, creating users, deleting users, password management and authority management, etc. The security of MariaDB database needs to be guaranteed through account management.

1.1. Query online users

Local query: When we log in to the database locally, we can use local query. The query SQL statement is as follows:

MariaDB [(none)]> SHOW processlist;
+----+-------------+-----------------+-------+---------+------+--------------------------+------------------+----------+
| Id | User        | Host            | db    | Command | Time | State                    | Info             | Progress |
+----+-------------+-----------------+-------+---------+------+--------------------------+------------------+----------+
|  3 | system user |                 | NULL  | Daemon  | NULL | InnoDB purge worker      | NULL             |    0.000 |
|  2 | system user |                 | NULL  | Daemon  | NULL | InnoDB purge coordinator | NULL             |    0.000 |
|  1 | system user |                 | NULL  | Daemon  | NULL | InnoDB purge worker      | NULL             |    0.000 |
|  4 | system user |                 | NULL  | Daemon  | NULL | InnoDB purge worker      | NULL             |    0.000 |
|  5 | system user |                 | NULL  | Daemon  | NULL | InnoDB shutdown handler  | NULL             |    0.000 |
| 41 | admin       | localhost:55902 | mysql | Sleep   |  174 |                          | NULL             |    0.000 |
| 42 | admin       | localhost:55904 | mysql | Sleep   |  174 |                          | NULL             |    0.000 |
| 43 | root        | localhost       | NULL  | Query   |    0 | Init                     | show processlist |    0.000 |
+----+-------------+-----------------+-------+---------+------+--------------------------+------------------+----------+
8 rows in set (0.000 sec)

remote query: If you query a remote database on a remote terminal, we can use the following SQL statement if the database has remote authorization enabled:

$ mysqladmin -u admin -p processlist
Enter password:
+----+-------------+-----------------+-------+---------+------+--------------------------+------------------+----------+
| Id | User        | Host            | db    | Command | Time | State                    | Info             | Progress |
+----+-------------+-----------------+-------+---------+------+--------------------------+------------------+----------+
| 3  | system user |                 |       | Daemon  |      | InnoDB purge worker      |                  | 0.000    |
| 2  | system user |                 |       | Daemon  |      | InnoDB purge coordinator |                  | 0.000    |
| 1  | system user |                 |       | Daemon  |      | InnoDB purge worker      |                  | 0.000    |
| 4  | system user |                 |       | Daemon  |      | InnoDB purge worker      |                  | 0.000    |
| 5  | system user |                 |       | Daemon  |      | InnoDB shutdown handler  |                  | 0.000    |
| 41 | admin       | localhost:55902 | mysql | Sleep   | 220  |                          |                  | 0.000    |
| 42 | admin       | localhost:55904 | mysql | Sleep   | 220  |                          |                  | 0.000    |
| 44 | admin       | localhost       |       | Query   | 0    | Init                     | show processlist | 0.000    |
+----+-------------+-----------------+-------+---------+------+--------------------------+------------------+----------+

check all users: We build SELECT statement to query the specified field (host, user, password), query mysql.user , the SQL statement is as follows:

$ sudo mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 45
Server version: 10.3.31-MariaDB-0ubuntu0.20.04.1 Ubuntu 20.04

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

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

MariaDB [(none)]> SELECT host, user, password FROM mysql.user;
+-----------+-------+-------------------------------------------+
| host      | user  | password                                  |
+-----------+-------+-------------------------------------------+
| localhost | root  |                                           |
| localhost | admin | *9D59E8AF06195817B4585B0045A6601BBE64259F |
| %         | admin | *9D59E8AF06195817B4585B0045A6601BBE64259F |
+-----------+-------+-------------------------------------------+
3 rows in set (0.000 sec)

de- uses the distinct command to make the query result non-repetitive and automatically filter the duplicate records.

$ sudo mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 45
Server version: 10.3.31-MariaDB-0ubuntu0.20.04.1 Ubuntu 20.04

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

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

MariaDB [(none)]> SELECT distinct host, user, password FROM mysql.user;
+-----------+-------+-------------------------------------------+
| host      | user  | password                                  |
+-----------+-------+-------------------------------------------+
| localhost | root  |                                           |
| localhost | admin | *9D59E8AF06195817B4585B0045A6601BBE64259F |
| %         | admin | *9D59E8AF06195817B4585B0045A6601BBE64259F |
+-----------+-------+-------------------------------------------+
3 rows in set (0.000 sec)

1.2, create a new ordinary user

To create a new user, you must have the appropriate permissions to perform the creation operation. In the MariaDB database, there are two ways to create a new user: one is to use the CREATE USER or GRANT statement, and the other is to directly manipulate the MariaDB authorization table. The best way is to use the GRANT statement, because it is more accurate. If you use the CREATE USER statement After the user is created, the user has no permissions and needs to be added manually, and the GRANT statement can be used directly in one step.

1.2.1, use the CREATE USER statement to create a new user:

1. CREATE USER , the name is jeffrey , the password is mypass , and specifies to open the% remote authority .

MariaDB [(none)]> CREATE USER 'jeffrey'@'%' IDENTIFIED BY 'mypass';
Query OK, 0 rows affected (0.000 sec)

2. Then create a new user jeffreys , the password is mypass , and specify the use of localhost local authority.

MariaDB [(none)]> SELECT PASSWORD('mypass');
+-------------------------------------------+
| PASSWORD('mypass')                        |
+-------------------------------------------+
| *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 |
+-------------------------------------------+
1 row in set (0.000 sec)

MariaDB [(none)]> CREATE USER 'jeffreys'@'localhost' IDENTIFIED BY PASSWORD '*6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4';
Query OK, 0 rows affected (0.000 sec)

1.2.2, use GRANT statement to create user

Use the GRANT statement for a new user myuser , the password is 123123 , and grant the user SELECT and UPDATE permissions for all tables, the SQL statement is as follows:

MariaDB [(none)]> GRANT SELECT, UPDATE ON *.* TO 'myuser'@'localhost' IDENTIFIED BY '123123';
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> SELECT host, user, select_priv, update_priv FROM mysql.user WHERE user = 'myuser';
+-----------+--------+-------------+-------------+
| host      | user   | select_priv | update_priv |
+-----------+--------+-------------+-------------+
| localhost | myuser | Y           | Y           |
+-----------+--------+-------------+-------------+
1 row in set (0.001 sec)

1.3, delete ordinary users

1.3.1, use the DROP USER statement to delete users:

MariaDB [(none)]> SELECT DISTINCT user, host FROM mysql.user;
+----------+-----------+
| user     | host      |
+----------+-----------+
| admin    | %         |
| jeffrey  | %         |
| admin    | localhost |
| jeffreys | localhost |
| lyshark  | localhost |
| myuser   | localhost |
| root     | localhost |
+----------+-----------+
7 rows in set (0.000 sec)

MariaDB [(none)]> DROP USER 'lyshark'@'localhost';
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> SELECT DISTINCT user, host FROM mysql.user;
+----------+-----------+
| user     | host      |
+----------+-----------+
| admin    | %         |
| jeffrey  | %         |
| admin    | localhost |
| jeffreys | localhost |
| myuser   | localhost |
| root     | localhost |
+----------+-----------+
6 rows in set (0.000 sec)

1.3.2, use DELETE statement to delete users:

MariaDB [(none)]> DELETE FROM mysql.user WHERE host = 'localhost' AND user = 'myuser';
Query OK, 1 row affected (0.000 sec)

1.4, modify user password

modify its own password: modify the user name and password of its own user.

MariaDB [(none)]> SET PASSWORD=PASSWORD("123123");
Query OK, 0 rows affected (0.00 sec)

modify the specified user's password: modify lyshark User License is localhost password is 123123

MariaDB [(none)]> SET PASSWORD for "lyshark"@"localhost"=PASSWORD("123123");
Query OK, 0 rows affected (0.00 sec)

1.5 Retrieve ROOT password

1.5.1, close MariaDB database

$ sudo service mysql stop
 * Stopping MariaDB database server mysqld                                                                       [ OK ]
$ sudo service mysql status
 * MariaDB is stopped.

1.5.2, modify the main configuration file of MariaDB, add the skip-grant-table statement in the Mysqld area, save and exit

$ sudo vim /etc/mysql/mariadb.conf.d/50-server.cnf
[mysqld]

#
# * Basic Settings
#
user                    = mysql
pid-file                = /run/mysqld/mysqld.pid
socket                  = /run/mysqld/mysqld.sock
#port                   = 3306
basedir                 = /usr
datadir                 = /var/lib/mysql
tmpdir                  = /tmp
lc-messages-dir         = /usr/share/mysql
#skip-external-locking
skip-grant-table

1.5.3, restart MariaDB database

$ sudo service mysql start
 * Starting MariaDB database server mysqld                                                                       [ OK ]
$ sudo service mysql status
 * /usr/bin/mysqladmin  Ver 9.1 Distrib 10.3.31-MariaDB, for debian-linux-gnu on x86_64
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Server version          10.3.31-MariaDB-0ubuntu0.20.04.1
Protocol version        10
Connection              Localhost via UNIX socket
UNIX socket             /var/run/mysqld/mysqld.sock
Uptime:                 2 sec

Threads: 6  Questions: 61  Slow queries: 0  Opens: 33  Flush tables: 1  Open tables: 26  Queries per second avg: 30.500

1.5.4, login to the database without password, and execute the SQL statement to modify the database

$ sudo mysql -u root -p
MariaDB [(none)]> UPDATE mysql.user SET password = password('123') WHERE user = 'root';
MariaDB [(none)]> QUIT;

1.5.5. After the modification is completed, skip-grant-table attribute of the main configuration file (refer to step 1.5.2), restart the database, and log in with the password next time

$ sudo vim /etc/mysql/mariadb.conf.d/50-server.cnf
$ sudo service mysql restart
 * Stopping MariaDB database server mysqld                                                                       [ OK ]
 * Starting MariaDB database server mysqld                                                                       [ OK ]
$ sudo mysql -u root -p123

2. MariaDB permission management

Permission management is mainly to verify the permissions of users who log in to MariaDB. All user permissions are stored in the MariaDB permissions table. Unreasonable permission planning will bring security risks to the MariaDB server. The database administrator must check the permissions of all users. Reasonable planning and management.

2.1, view permissions

queries all permissions: queries all user lists and permissions information in the database.

MariaDB [(none)]> SELECT DISTINCT CONCAT("用户:", user, " 权限:", host, " : ") AS query FROM mysql.user;
+-------------------------------------+
| query                               |
+-------------------------------------+
| 用户:admin 权限:% :                 |
| 用户:jeffrey 权限:% :               |
| 用户:admin 权限:localhost :         |
| 用户:jeffreys 权限:localhost :      |
| 用户:root 权限:localhost :          |
+-------------------------------------+
5 rows in set (0.000 sec)

query specified permissions: query all permissions information about lyshark

MariaDB [(none)]> SHOW GRANTS FOR lyshark;
+----------------------------------------------+
| Grants for lyshark@%                         |
+----------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'lyshark'@'%' |
+----------------------------------------------+
1 row in set (0.00 sec)

query specified permissions: query lyshark user remote% permissions, and local localhost permissions

MariaDB [(none)]> SHOW GRANTS FOR 'lyshark'@'%';
+----------------------------------------------+
| Grants for lyshark@%                         |
+----------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'lyshark'@'%' |
+----------------------------------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> SHOW GRANTS FOR 'lyshark'@'localhost';
+----------------------------------------------------------------------------------------------------------------+
| Grants for lyshark@localhost                                                                                   |
+----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'lyshark'@'localhost' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' |
+----------------------------------------------------------------------------------------------------------------+
1 row in set (0.37 sec)

2.2, grant permissions

creates a user and authorizes: creates a wang user and grants % remote login permissions, and authorizes all databases

MariaDB [(none)]> GRANT ALL ON *.* TO 'wang'@'%' IDENTIFIED BY '123';
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> SHOW GRANTS FOR 'wang'@'%';
+--------------------------------------------------------------------------------------------------------------+
| Grants for wang@%                                                                                            |
+--------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO `wang`@`%` IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' |
+--------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

creates a user and authorizes: creates a user name wang1 , and allows localhost SELECT query permissions for all tables in the mysql library, and the password is 123

MariaDB [(none)]> GRANT SELECT ON mysql.* TO 'wang1'@'localhost' IDENTIFIED BY '123';
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> SHOW GRANTS FOR 'wang1'@'localhost';
+--------------------------------------------------------------------------------------------------------------+
| Grants for wang1@localhost                                                                                   |
+--------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `wang1`@`localhost` IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' |
| GRANT SELECT ON `mysql`.* TO `wang1`@`localhost`                                                             |
+--------------------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)

creates a user and authorizes: creates a user name wang3 , and can only 192.168.1.59 SELECT permissions to the mysql database, and the password is 123

MariaDB [(none)]> GRANT SELECT ON mysql.* TO 'wang3'@'192.168.1.59' IDENTIFIED BY '123';
 FOR 'wang3'Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> SHOW GRANTS FOR 'wang3'@'192.168.1.59';
+-----------------------------------------------------------------------------------------------------------------+
| Grants for wang3@192.168.1.59                                                                                   |
+-----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `wang3`@`192.168.1.59` IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' |
| GRANT SELECT ON `mysql`.* TO `wang3`@`192.168.1.59`                                                             |
+-----------------------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)

creates a user and authorizes: creates an ordinary user wang4, and only SELECT mysql library, and the password is 123

MariaDB [(none)]> GRANT USAGE, SELECT ON mysql.* TO 'wang4'@'localhost' IDENTIFIED BY '123';
Query OK, 0 rows affected (0.000 sec)

only authorized user rights: authorized user wang4, full rights to all databases, password 123

MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO 'wang4'@'localhost' IDENTIFIED BY '123';
Query OK, 0 rows affected (0.000 sec)

only authorizes user rights: authorizes an existing account to allow remote login maximum rights

MariaDB [(none)]> GRANT ALL ON *.* TO 'root'@'%';
Query OK, 0 rows affected (0.07 sec)

2.3, withdraw permissions

withdraw user authorization: revoke lyshark user, remote % user rights to all databases

MariaDB [(none)]> SHOW GRANTS FOR lyshark;
+-----------------------------------------------------------------------------------------------------------------+
| Grants for lyshark@%                                                                                            |
+-----------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, UPDATE ON *.* TO `lyshark`@`%` IDENTIFIED BY PASSWORD '*E56A114692FE0DE073F9A1DD68A00EEB9703F3F1' |
+-----------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

MariaDB [(none)]> REVOKE ALL ON *.* FROM 'lyshark'@'%';
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> SHOW GRANTS FOR lyshark;
+--------------------------------------------------------------------------------------------------------+
| Grants for lyshark@%                                                                                   |
+--------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `lyshark`@`%` IDENTIFIED BY PASSWORD '*E56A114692FE0DE073F9A1DD68A00EEB9703F3F1' |
+--------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

withdraw user authorization: revoke the remote login permission of user lyshark

MariaDB [(none)]> REVOKE CREATE ON *.* FROM 'lyshark'@'%';
Query OK, 0 rows affected (0.000 sec)

2.4, refresh permissions

refresh permissions: After modify permissions, remember to refresh permissions, otherwise the permissions will not take effect.

MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)

3. Quickly create databases and users

DROP USER IF EXISTS 'glc'@'%';

DROP DATABASE IF EXISTS glcdb;

CREATE DATABASE glcdb;

GRANT ALL ON glcdb.* TO 'glc'@'%' IDENTIFIED BY 'glc@123';

FLUSH PRIVILEGES;

QUIT;