RH358配置MariaDB SQL数据库--管理MariaDB用户和访问权限

RH358配置MariaDB SQL数据库–管理MariaDB用户和访问权限

本章节介绍如何在MariaDB中管理用户和访问权限。在数据库操作中，也是基础使用，必须学会。

RH358专栏地址：https://blog.csdn.net/qq_41765918/category_11532281.html

1. 在MariaDB中创建用户帐号

默认情况下，MariaDB将其用户及其密码与本地系统的用户和密码分开。这意味着MariaDB数据库用户与服务器的Linux用户不同，即使用户帐户具有相同的名称，默认情况下，密码将分别跟踪。

为了控制用户对数据库服务器的访问级别，必须在MariaDB中设置数据库用户，并授予他们对服务器及其数据执行操作的权限。

可以使用MariaDB pam身份验证插件将系统用户帐户和密码集成为MariaDB数据库用户，本课程不涉及该配置。在大多数情况下，最好在数据库服务器上分别管理对数据库服务的访问和对shell提示符的访问

创建新用户需要具备以下其中一个权限级别:

• MariaDB root用户。

• 是一个被授予全局CREATE user权限的用户。

• 是一个被授予mysql数据库的INSERT权限的用户。

CREATE USER语句在mysql数据库的user表中创建一条新记录。这个用户没有特权。

用户名指定为user_name@host_name。这使得可以创建具有相同名称、但根据源主机(即用户所连接的主机)具有不同特权的多个用户帐户。

MariaDB [(none)]> CREATE USER mobius@localhost IDENTIFIED BY ‘redhat’ ;

• 该帐户可以用来连接的用户名/主机名。

• 该帐号的密码。

**注意：**如果没有提供主机名，用户可从任何主机获得访问权限。

目前，mobius帐户只能从本地主机连接，密码为redhat。密码在用户表中加密:

MariaDB [mysql]> SELECT host,user,password FROM user WHERE user = 'mobius';
+-----------+--------+-------------------------------------------+
| host       | user   |   password |
+-----------+--------+-------------------------------------------+
| localhost | mobius | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |
+-----------+--------+-------------------------------------------+
1 row in set (0.00 sec)

账户定义示例

账户	描述
mobius	用户mobius可以从任何主机连接。
mobius@’%'	用户mobius可以从任何主机连接。
mobius@'localhost’	用户mobius只能从Localhost连接。
mobius@'192.168.1.5’	用户mobius只能通过IP地址192.168.1.5进行连接。
mobius@'192.168.1.%'	用户mobius可以从属于该网络192.168.1.0/24的任何地址进行连接。
mobius@'2001:db8:18:b51:c32:a21’	用户mobius只能通过IP地址2001:db8:18:b51:C32: a21进行连接。

2. 控制用户权限

默认情况下，新帐户被授予最低权限。在不授予额外特权的情况下，mobius用户可以访问最小的帐户信息，但大多数其他操作都被拒绝。

# 在下面的例子中，允许访问:
[user@host ~]$ mysql -u mobius -p
Enter password: redhat
MariaDB [(none)]> SELECT USER();
+------------------+
| user() |
+------------------+
| mobius@localhost |
+------------------+
1 row in set (0.000 sec)

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.000 sec)

# 在下一个例子中，用户验证了身份，但是访问被拒绝了:
[user@host ~]$ mysql -u mobius -p
Enter password: redhat
...output omitted...
MariaDB [(none)]> USE mysql;
ERROR 1044 (42000): Access denied for user 'mobius'@'localhost' to database 'mysql'
MariaDB [(none)]> CREATE DATABASE inventory;
ERROR 1044 (42000): Access denied for user 'mobius'@'localhost' to database 'inventory'

权限是用户在MariaDB中拥有的权限。它们决定了用户可以做什么，以及用户可以在MariaDB中看到什么。特权是按其详细范围组织的。

• 全局权限(例如CREATE USER)用于管理MariaDB数据库服务器本身。

• 数据库特权(如CREATE Database)用于在MariaDB服务器上创建数据库和使用数据库。

• 表特权(比如CRUD命令)用于在特定数据库中创建表和操作数据。

• 列特权用于授予类似于表的命令使用，但是在特定的列上(通常很少)。

• 其他更细粒度的特权将在本节末尾引用的MariaDB文档中详细讨论。

授予用户权限

GRANT语句可用于向帐户授予权限。要授予权限，连接的用户必须具有GRANT OPTION，并且必须具有正在授予的GRANT OPTION权限。

例如，mobius用户不能授予数据库表上的SELECT权限，除非他们已经拥有SELECT权限和grant OPTION表权限。

# 在这个示例中，MariaDB根用户将CRUD权限授予inventory数据库中类别表上的mobius用户。
[user@host ~]$ mysql -u root -p
Enter password: redhat
...output omitted...
MariaDB [(none)]> USE inventory;
...output omitted...
Database changed

MariaDB [(inventory)]> GRANT SELECT, UPDATE, DELETE, INSERT
-> ON inventory.category
-> TO mobius@localhost ;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(inventory)]> exit
Bye

# 然后你可以确认mobius用户的权限如下:
[user@host ~]$ mysql -u mobius -p
Enter password: redhat
MariaDB [(none)]> USE inventory;
MariaDB [(inventory)]> SELECT * FROM category;
+----+------------+
| id | name |
+----+------------+
| 1 | Networking |
| 2 | Servers |
| 3 | Ssd |
+----+------------+
3 rows in set (0.00 sec)

GRANT操作的例子

Grant	描述
GRANT SELECT ON database.table TO username@hostname	将特定数据库中特定表的SELECT权限授予特定用户。
GRANT SELECT ON database.* TO username@hostname	将特定数据库中所有表的SELECT权限授予特定用户。
GRANT SELECT ON *.* TO username@hostname	将所有数据库中所有表的SELECT权限授予特定用户。
GRANT CREATE, ALTER, DROP ON database.* to username@hostname	将特定数据库中的CREATE、ALTER、DROP TABLES权限授予特定用户。
GRANT ALL PRIVILEGES ON *.* to username@hostname	将所有数据库的所有可用权限授予特定用户，有效地创建了一个超级用户，类似于root。

撤销用户权限

REVOKE语句从帐户中删除特权。连接的用户必须具有GRANT OPTION权限，并且具有正在被撤销的权限以撤销权限。

MariaDB [(none)]> REVOKE SELECT, UPDATE, DELETE, INSERT

-> ON inventory.category FROM mobius@localhost ;

Query OK, 0 rows affected (0.00 sec)

**重要：**在修改授权表之后运行FLUSH PRIVILEGES命令是一个好习惯。虽然MariaDB会注意到一些语句并自动加载，但是大多数撤销特权的语句都需要用FLUSH PRIVILEGES重新加载特权表才能生效。

MariaDB [(none)]> FLUSH PRIVILEGES;

显示用户权限

可以验证哪些特权被授予给了用户。SHOW GRANTS FOR username; 提供了该用户的权限列表:

MariaDB [(none)]> SHOW GRANTS FOR root@localhost;
+---------------------------------------------------------------------+
| Grants for root@localhost |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

3. 删除用户帐号

当您不再需要一个特定的用户帐户时，可以使用DROP USER从数据库中删除它。username应该使用与CREATE USER相同的’user’@'host’格式。

MariaDB [(none)]> DROP USER mobius;

Query OK, 0 rows affected (0.002 sec)

**重要：**如果当前连接的帐户被删除，该帐户将直到连接关闭后才会被删除。如果该用户有一个活动的连接，当帐户被删除时，它不会自动关闭。

4. 数据库访问的故障诊断

下表总结了用户在身份验证和访问方面可能遇到的一些问题以及可能的原因。

一些常见的数据库访问问题

问题	解决方案
用户已被授予从任何主机连接的访问权限，但只能在数据库服务器上使用shell中的mysql命令进行连接。	如果在/etc/my.cnf.d/mariadb-server.cnf中设置了skip-networking，请删除该指令并重新启动服务。
用户可以连接localhost上的任何应用程序，但不能远程连接。	确保/etc/my.cnf.d/mariadb-server.cnf中的bind-address配置是正确的， 确保可以访问数据库。确保user表中包含用户试图连接的主机的用户条目。
用户可以连接，但不能看到除information_schema之外的任何数据库。	确保用户已被授予访问其数据库的特权。当用户刚刚创建时，这是一个常见的问题，因为默认情况下创建的用户帐户具有最小的权限。
该用户可以连接，但不能创建任何数据库。	考虑为用户授予全局CREATE特权(这也授予DROP特权)。
用户可以连接，但不能读写任何数据。	为用户打算使用的数据库授予CRUD特权。

5. 课本练习

[student@workstation ~]$ lab database-users start

创建用户与授权

[student@servera ~]$ mysql -u root -p
Enter password: redhat

MariaDB [(none)]> CREATE USER john@localhost identified by 'john_password';
Query OK, 0 rows affected (0.000 sec)
MariaDB [(none)]> CREATE USER steve@'%' identified by 'steve_password';
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> GRANT INSERT, UPDATE, DELETE,SELECT on inventory.* to john@localhost;
Query OK, 0 rows affected (0.000 sc)
MariaDB [(none)]> GRANT SELECT on inventory.* to steve@'%';
Query OK, 0 rows affected (0.000 sec)

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

验证

[student@servera ~]$ mysql -u john -p
Enter password: john_password
MariaDB [(none)]>
MariaDB [(none)]> USE inventory;
Database changed
MariaDB [(inventory)]> SELECT * FROM category;
+----+------------+
| id | name |
+----+------------+
| 1 | Networking |
| 2 | Servers |
| 3 | Ssd |
+----+------------+
3 rows in set (0.00 sec)

MariaDB [(inventory)]> INSERT INTO category(name) VALUES('Memory');
Query OK, 1 row affected (0.00 sec)
MariaDB [(inventory)]> UPDATE category SET name='Solid State Drive' WHERE id = 3;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

MariaDB [(inventory)]> SELECT * FROM category;
+----+-------------------+
| id | name |
+----+-------------------+
| 1 | Networking |
| 2 | Servers |
| 3 | Solid State Drive |
| 5 | Memory |
+----+-------------------+
4 rows in set (0.000 sec)

MariaDB [(inventory)]> DELETE FROM category WHERE name LIKE 'Memory';
Query OK, 1 row affected (0.01 sec)
MariaDB [(inventory)]> exit
Bye

[student@serverb ~]$ mysql -u steve -h servera -p
Enter password: steve_password
MariaDB [(none)]> USE inventory;
Database changed
MariaDB [(inventory)]> SELECT * FROM category;
+----+-------------------+
| id | name |
+----+-------------------+
| 1 | Networking |
| 2 | Servers |
| 3 | Solid State Drive |
+----+-------------------+
3 rows in set (0.00 sec)

MariaDB [(inventory)]> INSERT INTO category(name) VALUES('Memory');
ERROR 1142 (42000): INSERT command denied to user 'steve'@'serverb.example.com'for table 'category'
MariaDB [(inventory)]> exit
Bye

完成实验

[student@workstation ~]$ lab database-users finish

总结

• 介绍如何在MariaDB中创建账户。
• 介绍如何管理MariaDB账户。
• 介绍数据库访问的故障诊断。
• 若喜欢金鱼哥的文章，顺手点个赞。也可点个关注，因为后续会不断上干货。