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 processlistEnter 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 mysqlWelcome to the MariaDB monitor. Commands end with ; or \g.Your MariaDB connection id is 45Server version: 10.3.31-MariaDB-0ubuntu0.20.04.1 Ubuntu 20.04Copyright (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 mysqlWelcome to the MariaDB monitor. Commands end with ; or \g.Your MariaDB connection id is 45Server version: 10.3.31-MariaDB-0ubuntu0.20.04.1 Ubuntu 20.04Copyright (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 = mysqlpid-file = /run/mysqld/mysqld.pidsocket = /run/mysqld/mysqld.sock#port = 3306basedir = /usrdatadir = /var/lib/mysqltmpdir = /tmplc-messages-dir = /usr/share/mysql#skip-external-lockingskip-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_64Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.Server version 10.3.31-MariaDB-0ubuntu0.20.04.1Protocol version 10Connection Localhost via UNIX socketUNIX socket /var/run/mysqld/mysqld.sockUptime: 2 secThreads: 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 -pMariaDB [(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)