关于mysql:一文弄懂-OceanBase-用户管理和访问控制

本文次要以 MySQL 和 OceanBase 比照的形式，来介绍 OceanBase（MySQL 模式）平安体系中对于用户治理和访问控制的相干内容，包含用户治理、用户操作权限管制、网络安全访问控制、行级权限管制、角色治理。

作者：金长龙

爱可生测试工程师，负责 DMP 产品的测试工作。

本文起源：原创投稿

* 爱可生开源社区出品，原创内容未经受权不得随便应用，转载请分割小编并注明起源。

本文次要以 MySQL 和 OceanBase 比照的形式，来介绍 OceanBase（MySQL 模式）平安体系中对于用户治理和访问控制的相干内容，包含用户治理、用户操作权限管制、网络安全访问控制、行级权限管制、角色治理。

用户治理

1.1 基本概念

租户

OceanBase 数据库租户是一个逻辑概念，是资源分配的单位。OceanBase 数据库租户间的数据是齐全隔离的，每个租户都相当于传统数据库的一个数据库实例。

OceanBase 数据库租户分为：零碎租户 和一般租户。

• OceanBase 数据库预约义了用于治理的零碎租户（sys 租户），其兼容模式为 MySQL
• 一般租户又分为 Oracle 模式租户和 MySQL 模式租户

用户

OceanBase 数据库用户分为：零碎租户用户 和一般租户用户。

• 零碎租户的内置系统管理员为用户 root
• MySQL 租户的内置租户管理员为用户 root
• Oracle 租户的内置租户管理员为用户 sys
• 创立用户时，如果以后会话的租户为零碎租户，则新建的用户为零碎租户用户，反之为一般租户用户

1.2 用户名称语法

用户名称呈现在 SQL 语句中（如：CREATE USER, GRANT, SET PASSWORD）须要遵循一些规定，测试这些规定在 OceanBase 和 MySQL 中的体现是否统一。

OceanBase

# 用户名称语法为 'user_name'@'host_name'
obclient [oceanbase]> create user 'test01'@'%' identified by '123456';
Query OK, 0 rows affected (0.017 sec)
 
# @'host_name' 局部是可选的
obclient [oceanbase]> create user test02;
Query OK, 0 rows affected (0.017 sec)
 
# 如果用户名和主机名作为不带引号的标识符是非法的，则无需将其引号括起来。如果 user_name 字符串蕴含特殊字符（如空格或 -），或者 host_name 字符串蕴含特殊字符或通配符（如 . 或 %），则必须应用引号
obclient [oceanbase]> create user test02@%;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use near '%' at line 1
obclient [oceanbase]> create user test02@sun;
Query OK, 0 rows affected (0.027 sec)
  
# 主机值能够是主机名或 IP 地址（IPv4 或 IPv6）obclient [oceanbase]> create user 'test02'@'127.0.0.1';
Query OK, 0 rows affected (0.021 sec)
 
# 主机名或 IP 地址值中容许应用 % 和 _ 通配符
obclient [oceanbase]> create user 'test02'@'%.mysql.com';
Query OK, 0 rows affected (0.016 sec)
 
# 对于指定为 IPv4 地址的主机值，能够提供一个网络掩码来批示要用于网络号的地址位数
obclient [oceanbase]> CREATE USER 'test02'@'198.51.100.0/255.255.255.0';
Query OK, 0 rows affected (0.017 sec)
 
# 指定为 IPv4 地址的主机值能够应用 CIDR 表示法写入
obclient [oceanbase]> CREATE USER 'test02'@'198.51.100.0/24';
Query OK, 0 rows affected (0.028 sec)

MySQL

# 用户名称语法为 'user_name'@'host_name'
mysql [localhost:8031] {msandbox} ((none)) > create user 'test01'@'%' identified with mysql_native_password by '123456';
Query OK, 0 rows affected (0.03 sec)
 
# @'host_name' 局部是可选的
mysql [localhost:8031] {root} ((none)) > create user test02;
Query OK, 0 rows affected (0.03 sec)
 
# 如果用户名和主机名作为不带引号的标识符是非法的，则无需将其引号括起来。如果 user_name 字符串蕴含特殊字符（如空格或 -），或者 host_name 字符串蕴含特殊字符或通配符（如 . 或 %），则必须应用引号
mysql [localhost:8031] {root} ((none)) > create user test02@%;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '%' at line 1
mysql [localhost:8031] {root} ((none)) > create user test02@sun;
Query OK, 0 rows affected (0.03 sec)
 
# 主机值能够是主机名或 IP 地址（IPv4 或 IPv6）mysql [localhost:8031] {root} ((none)) > create user 'test02'@'127.0.0.1';
Query OK, 0 rows affected (0.01 sec)
 
# 主机名或 IP 地址值中容许应用 % 和 _ 通配符
mysql [localhost:8031] {root} ((none)) > create user 'test02'@'%.mysql.com';
Query OK, 0 rows affected (0.03 sec)
 
# 对于指定为 IPv4 地址的主机值，能够提供一个网络掩码来批示要用于网络号的地址位数
mysql [localhost:8031] {root} ((none)) > CREATE USER 'test02'@'198.51.100.0/255.255.255.0';
Query OK, 0 rows affected (0.02 sec)
 
# 从 MySQL 8.0.23 开始，指定为 IPv4 地址的主机值能够应用 CIDR 表示法写入
mysql [localhost:8031] {root} ((none)) > CREATE USER 'test02'@'198.51.100.0/24';
Query OK, 0 rows affected (0.04 sec)

测试后果：体现统一。

1.3 用户明码设置

常见的明码调配语句有：CREATE USER, ALTER USER, SET PASSWORD，测试在 OceanBase 和 MySQL 中语法的反对状况。

OceanBase

obclient [oceanbase]> CREATE USER 'jeffrey'@'%' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.018 sec)
 
obclient [oceanbase]> ALTER USER 'jeffrey'@'%' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.017 sec)
 
obclient [oceanbase]> SET PASSWORD FOR 'jeffrey'@'%' = 'password';
ERROR 1827 (42000): The password hash doesn't have the expected format. Check if the correct password algorithm is being used with the PASSWORD() function.
 
obclient [oceanbase]> SET PASSWORD FOR 'jeffrey'@'%' = PASSWORD('password');
Query OK, 0 rows affected (0.015 sec)
 
obclient [(none)]> ALTER USER USER() IDENTIFIED BY 'password';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use near '() IDENTIFIED BY'password'' at line 1

MySQL

mysql [localhost:8031] {msandbox} ((none)) > CREATE USER 'jeffrey'@'%' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.53 sec)
 
mysql [localhost:8031] {msandbox} ((none)) > ALTER USER 'jeffrey'@'%' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.00 sec)
 
mysql [localhost:8031] {msandbox} ((none)) > SET PASSWORD FOR 'jeffrey'@'%' = 'password';
Query OK, 0 rows affected (0.02 sec)
 
mysql [localhost:8031] {msandbox} ((none)) > SET PASSWORD FOR 'jeffrey'@'%' = PASSWORD('password');
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PASSWORD('password')' at line 1
 
mysql [localhost:8031] {jeffrey} ((none)) > ALTER USER USER() IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.03 sec)

测试后果：

• 两种数据库的 set password 语法略有不同。
• MySQL 的 alter user 语句反对带 user() 函数，而在 OceanBase 中暂不反对该写法。

通过命令行工具批改明码：

• OceanBase：暂无。
• MySQL：能够通过 mysqladmin 工具批改。

明码特有问题：

• OceanBase：暂无。
• MySQL：如果应用的是 MySQL 复制，目前正本用作 CHANGE REPLICATION SOURCE TO 语句（来自 MySQL 8.0.23）或 CHANGE MASTER TO 到语句（MySQL 8.0.23 之前）的明码实际上限度为 32 个字符的长度；如果明码较长，则任何多余的字符都将被截断。这不是因为 MySQL 服务器通常施加的任何限度，而是 MySQL 复制特有的问题。

1.4 用户锁定

测试 OceanBase 和 MySQL 的 ALTER USER, CREATE USER 语句，是否反对用户锁定。

OceanBase

obclient [oceanbase]> alter user 'jeffrey'@'%' account unlock;
Query OK, 0 rows affected (0.004 sec)
 
obclient [oceanbase]> alter user 'jeffrey'@'%' account lock;
Query OK, 0 rows affected (0.019 sec)
 
obclient [oceanbase]> create user 'jin'@'%' account lock;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use near 'account lock' at line 1

OceanBase 能够通过 __all_user 表中 is_locked 字段来确认用户的锁定状态。

MySQL

mysql [localhost:8031] {msandbox} ((none)) > alter user 'jeffrey'@'%' account unlock;
Query OK, 0 rows affected (0.03 sec)
 
mysql [localhost:8031] {msandbox} ((none)) > alter user 'jeffrey'@'%' account lock;
Query OK, 0 rows affected (0.03 sec)
 
mysql [localhost:8031] {msandbox} ((none)) > create user 'jin'@'%' account lock;
Query OK, 0 rows affected (0.01 sec)

MySQL 能够通过 mysql.user 表中的 account_locked 字段来确认用户的锁定状态。

测试后果：

• OceanBase：ALTER USER 反对用户锁定，CREATE USER 不反对用户锁定。
• MySQL：ALTER USER 和 CREATE USER 都反对用户锁定。

用户操作权限管制

2.1 权限治理

OceanBase

OceanBase（MySQL 模式）的权限分为 3 个级别：

1. 管理权限：能够影响整个租户的权限，例如：批改零碎设置、拜访所有的表等权限。
2. 数据库权限：能够影响某个特定数据库下所有对象的权限，例如：在对应数据库下创立删除表，拜访表等权限。
3. 对象权限：能够影响某个特定对象的权限，例如：拜访一个特定的表、视图或索引的权限。

以后 OceanBase（MySQL 模式）的所有权限列表，可查问 OB 官网文档 MySQL 模式下的权限分类。

MySQL

MySQL 权限同样分为 3 个级别：

1. 管理权限：管理权限使用户可能治理 MySQL 服务器的操作。这些特权是全局的，因为它们不特定于特定数据库。
2. 数据库权限：数据库权限实用于数据库及其中的所有对象。能够为特定数据库授予这些权限，也能够全局授予这些权限，以便将它们利用于所有数据库。
3. 对象权限：能够为数据库中的特定对象、数据库中给定类型的所有对象（例如，数据库中的所有表）或对所有数据库中给定类型的所有对象全局授予数据库对象（如表、索引、视图和存储例程）的权限。

MySQL 还辨别动态权限和动静权限，具体的权限列表可查问 MySQL 官网文档 Privileges Provided by MySQL。

权限治理比照

1. 用户权限级别都分为 3 个级别，且表白的含意统一。
2. 细分的权限上大同小异，OceanBase 目前还有些尚未反对。从 OB 的官网文档看，目前受权表里预留了一些字段但尚未反对。
3. OceanBase 特有的几个权限：ALTER TENANT, ALTER SYSTEM, CREATE RESOURCE POOL, CREATE RESOURCE UNIT。
4. 对于 MySQL 的动静权限，OceanBase 暂不反对。

2.2 受权语句

1. 受权 GRANT
2. 撤销受权 REVOKE
3. 权限转授 WITH GRANT OPTION
4. 查看用户权限 SHOW GRANTS

测试后果：OceanBase（MySQL 模式）和 MySQL 在受权语句、语法上都统一。

2.3 受权表

OceanBase

MySQL

测试后果：OceanBase（MySQL 模式）和 MySQL 在受权表的实现上差异比拟大。

2.4 局部撤销权限限度

OceanBase

不反对局部撤销全局权限。

MySQL

开启变量 partial_revokes 后，能够局部撤销全局权限。

测试后果：OceanBase 暂不反对局部撤销全局权限。

网络安全访问控制

OceanBase

OceanBase 数据库提供租户白名单策略，实现网络安全访问控制。租户白名单指的是该租户容许登录的客户端列表，零碎反对以下多种租户白名单格局：

• IP 地址的模式，例如：10.10.10.10, 10.10.10.11
• 子网 / 掩码的模式，例如：10.10.10.0/24
• 含糊匹配的模式，例如：10.10.10.% 或者 10.10.10._
• 多种格局混合的模式，例如：10.10.10.10, 10.10.10.11, 10.10.10.%, 10.10.10._, 10.10.10.0/24

能够通过批改变量 ob_tcp_invited_nodes 设置租户的白名单。

MySQL

MySQL 本身没有找到相似性能。

测试后果：OceanBase 在网络安全访问控制上反对白名单，但 MySQL 本身不反对。

行级权限管制

OceanBase

MySQL 租户模式不反对，在 Oracle 租户模式下通过 Label Security 实现。

MySQL

没有相干性能，能够通过视图 / 触发器间接实现。

测试后果：OceanBase（MySQL 模式）和 MySQL 均不反对行级别的权限管制。

角色治理

OceanBase

MySQL 租户模式不反对，在 Oracle 租户模式下反对。

MySQL

反对角色治理。

测试后果：OceanBase 不反对角色治理。

这里咱们思考一个问题：因为 MySQL 是反对角色治理的，如果从 MySQL 迁徙至 OceanBase 应该怎么解决？

从我集体的了解，角色就是一组权限的汇合，它的益处是代替单个受权的便捷形式和概念化所有调配的权限。所以如果从 MySQL 迁徙至 OceanBase，实践上对角色的权限开展就能够了。

小结

在用户治理方面，OceanBase 和 MySQL 对用户名称呈现在 SQL 语句中遵循的规定是统一的，调配明码的 SQL 语法方面略有差别，用户锁定的 SQL 语句反对略有差别。

在权限治理方面，OceanBase 和 MySQL 的受权语句和语法是统一的，两种数据库都有各自特有的受权表，OceanBase 临时不反对动静权限和局部撤销全局权限。

在角色治理和行级权限性能方面，OceanBase 在 MySQL 租户模式不反对，但在 Oracle 租户模式下能够反对。

值得一提的是，OceanBase 还提供租户白名单性能，用来管制容许登录的客户端。

对于 SQLE

爱可生开源社区的 SQLE 是一款面向数据库使用者和管理者，反对多场景审核，反对标准化上线流程，原生反对 MySQL 审核且数据库类型可扩大的 SQL 审核工具。

SQLE 获取