MariaDB账户和权限管理

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 USERGRANT语句,另一种是直接操作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,并授予用户对所有表的SELECTUPDATE权限,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/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、免密码登陆数据库,并执行修改数据库的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服务器带来安全隐患,数据库管理员要对所有用户的权限进行合理规划管理。

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)

你可能感兴趣的