共计 12664 个字符，预计需要花费 32 分钟才能阅读完成。
1、MariaDB 账户治理
MariaDB 提供了许多语句来治理用户账号，这些语句能够用来治理包含登陆和退出 MariaDB 服务器，创立用户，删除用户，明码治理和权限治理等，MariaDB 数据库的安全性，须要通过账户治理来保障。
1.1、查问在线用户
本地查问：当咱们本地登录到数据库时，能够应用本地查问，查问 SQL 语句如下：
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)近程查问：如果在近程终端机上查问近程数据库，前提是数据库开启了近程受权咱们能够应用以下 SQL 语句：
$ 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 |
+----+-------------+-----------------+-------+---------+------+--------------------------+------------------+----------+查全副用户：咱们通过构建 SELECT 语句查问指定字段 (host, user, password)，查问mysql.user 这个数据表，SQL 语句如下：
$ 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)去重查问：通过应用 distinct 命令使查问后果不反复，主动过滤反复的记录。
$ 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、新建普通用户
创立新用户，必须有相应的权限来执行创立操作。在 MariaDB 数据库中，有两种形式创立新用户：一种是应用 CREATE USER 或GRANT语句，另一种是间接操作 MariaDB 受权表，最好的办法是应用 GRANT 语句，因为这样更准确，如果应用 CREATE USER 语句创立用户后用户无权限，须要手动增加权限，而间接应用 GRANT 语句能够一步到位。
1.2.1、应用 CREATE USER 语句创立新用户:
1、应用 CREATE USER 创立一个用户，名称为 jeffrey，明码是mypass， 指定开启 %近程 权限。
MariaDB [(none)]> CREATE USER 'jeffrey'@'%' IDENTIFIED BY 'mypass';
Query OK, 0 rows affected (0.000 sec)2、接着创立一个新用户 jeffreys，明码是mypass，并指定应用localhost 本地权限。
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、应用 GRANT 语句创立用户
应用 GRANT 语句一个新用户 myuser，明码是123123，并授予用户对所有表的SELECT 和UPDATE权限，SQL 语句如下：
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、删除普通用户
1.3.1、应用 DROP USER 语句删除用户：
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、应用 DELETE 语句删除用户：
MariaDB [(none)]> DELETE FROM mysql.user WHERE host = 'localhost' AND user = 'myuser';
Query OK, 1 row affected (0.000 sec)1.4、批改用户明码
批改本身明码：批改本人用户的用户名和明码。
MariaDB [(none)]> SET PASSWORD=PASSWORD("123123");
Query OK, 0 rows affected (0.00 sec)批改指定用户的明码：批改 lyshark 用户受权形式为 localhost 的明码为123123
MariaDB [(none)]> SET PASSWORD for "lyshark"@"localhost"=PASSWORD("123123");
Query OK, 0 rows affected (0.00 sec)1.5、ROOT 密码找回
1.5.1、敞开 MariaDB 数据库
$ sudo service mysql stop
* Stopping MariaDB database server mysqld [OK]
$ sudo service mysql status
* MariaDB is stopped.1.5.2、批改 MariaDB 的主配置文件, 在 Mysqld 区域增加 skip-grant-table 语句, 保留退出
$ 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-table1.5.3、重启 MariaDB 数据库
$ 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.5001.5.4、免明码登陆数据库，并执行批改数据库的 SQL 语句
$ sudo mysql -u root -p
MariaDB [(none)]> UPDATE mysql.user SET password = password('123') WHERE user = 'root';
MariaDB [(none)]> QUIT;1.5.5、批改实现后将主配置文件的 skip-grant-table 属性去掉（参考步骤 1.5.2），重启数据库即可，下次应用明码登陆即可
$ 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 -p1232、MariaDB 权限治理
权限治理次要是对登录到 MariaDB 的用户进行权限验证所有用户的权限都存储在 MariaDB 的权限表中，不合理的权限布局会给 MariaDB 服务器带来安全隐患，数据库管理员要对所有用户的权限进行正当布局治理。
2.1、查看权限
查问所有权限：查询数据库中所有用户列表和权限信息.
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)查问指定权限：查问对于 lyshark 的所有权限信息
MariaDB [(none)]> SHOW GRANTS FOR lyshark;
+----------------------------------------------+
| Grants for lyshark@% |
+----------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'lyshark'@'%' |
+----------------------------------------------+
1 row in set (0.00 sec)查问指定权限：查问 lyshark 用户的 近程 % 权限，和本地localhost 权限
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、授予权限
创立用户并受权：创立 wang 用户并给予 % 远程登陆的权限，并对所有数据库全副受权
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)创立用户并受权：创立用户名 wang1，并容许localhost 本机拜访，对 mysql 库中的所有表具备 SELECT 查问权限，明码为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)创立用户并受权：创立用户名 wang3，且只能在192.168.1.59 上登陆，并对 mysql 数据库有 SELECT 权限，明码为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)创立用户并受权：创立一个普通用户 wang4，且仅有 mysql 库的 SELECT 权限，明码为123
MariaDB [(none)]> GRANT USAGE, SELECT ON mysql.* TO 'wang4'@'localhost' IDENTIFIED BY '123';
Query OK, 0 rows affected (0.000 sec)只受权用户权限：受权用户 wang4，对所有数据库的全副权限，明码123
MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO 'wang4'@'localhost' IDENTIFIED BY '123';
Query OK, 0 rows affected (0.000 sec)只受权用户权限：受权一个已存在账号容许远程登陆最大权限
MariaDB [(none)]> GRANT ALL ON *.* TO 'root'@'%';
Query OK, 0 rows affected (0.07 sec)2.3、发出权限
发出用户受权：撤销 lyshark 用户，对所有数据库的近程 % 用户权限
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)发出用户受权：撤销 lyshark 用户的远程登陆权限
MariaDB [(none)]> REVOKE CREATE ON *.* FROM 'lyshark'@'%';
Query OK, 0 rows affected (0.000 sec)2.4、刷新权限
刷新权限：批改权限实现当前，记得执行权限的刷新操作，否则权限会不失效。
MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)正文完