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.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 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.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= 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、重启MariaDB数据库

$ sudo service mysql start * Starting MariaDB database server mysqld[ OK ] $ sudo service mysql status * /usr/bin/mysqladminVer 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 version10.3.31-MariaDB-0ubuntu0.20.04.1 Protocol version10 ConnectionLocalhost via UNIX socket UNIX socket/var/run/mysqld/mysqld.sock Uptime:2 secThreads: 6Questions: 61Slow queries: 0Opens: 33Flush tables: 1Open tables: 26Queries per second avg: 30.500

1.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 -p123

2、MariaDB权限管理
【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)

推荐阅读

• 

  天地伟业销售待遇怎么样 天地伟业公司待遇怎么样
• 

  吃杏子为什么会拉肚子
• 

  华为mQ3平板出货量大涨 OPPO平板明年出 期待
• 

  三菱中央空调eo代码处理方法 广东三菱日机空调故障代码 ,来全面的了解下
• 

  EPSONL3110Series开机自动登录设置方法详解
• 

  卵生动物有哪些
• 

  女生考公务员,选择哪些职位比较合适？
• 

  陆虎越野车是哪国的 陆虎越野车价格表
• 

  郭靖是哪个朝代的人 郭靖是哪个朝代的人物
• 

  梦想帝王手游礼包有哪些 游戏福利礼包汇总
• 

  免费的c语言编程软件下载，c语言软件免费下载
• 

  PS合成逼真的子弹击碎玻璃特效
• 

  行程卡怎么隐藏自己的行程
• 

  备案制就是无编制吗
• 

  喜马拉雅网
• 

  qq游戏大厅怎么添加游戏 QQ游戏大厅添加游戏的简单几步讲述
• 

  出生缺陷|防范出生缺陷，生育健康宝宝
• 

  五g卡4g手机能用吗
• 

  word水印添加方法
• 

  易拉罐能放冰箱吗？影响食品安全和电器维修的相关问题解析

• 急于表达——往往欲速则不达
• Docker应用:容器间通信与Mariadb数据库主从复制
• 第三节|第三节 快乐和幸福（12）
• 20170612时间和注意力开销记录
• 2.6|2.6 Photoshop操作步骤的撤消和重做 [Ps教程]
• 对称加密和非对称加密的区别
• 眼光要放高远
• 樱花雨
• 前任
• 2020-04-07vue中Axios的封装和API接口的管理