一文弄懂 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 个级别：
- 管理权限：可以影响整个租户的权限，例如：修改系统设置、访问所有的表等权限。
- 数据库权限：可以影响某个特定数据库下所有对象的权限，例如：在对应数据库下创建删除表，访问表等权限。
- 对象权限：可以影响某个特定对象的权限，例如：访问一个特定的表、视图或索引的权限。
当前 OceanBase（MySQL 模式）的所有权限列表，可查询 OB 官方文档 MySQL 模式下的权限分类。
MySQL
MySQL 权限同样分为 3 个级别：
-
管理权限：管理权限使用户能够管理 MySQL 服务器的操作。这些特权是全局的，因为它们不特定于特定数据库。
-
数据库权限：数据库权限适用于数据库及其中的所有对象。可以为特定数据库授予这些权限，也可以全局授予这些权限，以便将它们应用于所有数据库。
-
对象权限：可以为数据库中的特定对象、数据库中给定类型的所有对象（例如，数据库中的所有表）或对所有数据库中给定类型的所有对象全局授予数据库对象（如表、索引、视图和存储例程）的权限。
MySQL 还区分静态权限和动态权限，具体的权限列表可查询 MySQL 官方文档Privileges Provided by MySQL。
权限管理对比
- 用户权限级别都分为 3 个级别，且表达的含义一致。
- 细分的权限上大同小异，OceanBase 目前还有些尚未支持。 从 OB 的官方文档看，目前授权表里预留了一些字段但尚未支持。
- OceanBase 特有的几个权限：
ALTER TENANT,ALTER SYSTEM,CREATE RESOURCE POOL,CREATE RESOURCE UNIT。 - 关于 MySQL 的动态权限，OceanBase 暂不支持。
2.2 授权语句
- 授权 GRANT
- 撤销授权 REVOKE
- 权限转授 WITH GRANT OPTION
- 查看用户权限 SHOW GRANTS
测试结果：OceanBase（MySQL 模式）和 MySQL 在授权语句、语法上都一致。
2.3 授权表
OceanBase
| 相关库 | 相关表 |
|---|---|
mysql |
mysql.user <br/> mysql.db |
information_schema |
information_schema.COLUMN_PRIVILEGES <br/> information_schema.SCHEMA_PRIVILEGES <br/> information_schema.TABLE_PRIVILEGES <br/> information_schema.USER_PRIVILEGES |
oceanbase |
oceanbase.DBA_OB_DATABASE_PRIVILEGE <br/> oceanbase.CDB_OB_DATABASE_PRIVILEGE |
MySQL
| 相关库 | 相关表 |
|---|---|
mysql |
user <br/> global_grants <br/> db <br/> tables_priv <br/> columns_priv <br/> procs_priv <br/> proxies_priv <br/> default_roles <br/> role_edges <br/> password_history |
information_schema |
information_schema.COLUMN_PRIVILEGES <br/> information_schema.SCHEMA_PRIVILEGES <br/> information_schema.TABLE_PRIVILEGES <br/> information_schema.USER_PRIVILEGES |
测试结果：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 审核工具。
