一文弄懂 OceanBase 用户管理和访问控制

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

作者：金长龙
爱可生测试工程师，负责 DMP 产品的测试工作。
本文来源：原创投稿
* 爱可生开源社区出品，原创内容未经授权不得随意使用，转载请联系小编并注明来源。

用户管理

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` `mysql.db`
`information_schema`	`information_schema.COLUMN_PRIVILEGES` `information_schema.SCHEMA_PRIVILEGES` `information_schema.TABLE_PRIVILEGES` `information_schema.USER_PRIVILEGES`
`oceanbase`	`oceanbase.DBA_OB_DATABASE_PRIVILEGE` `oceanbase.CDB_OB_DATABASE_PRIVILEGE`

MySQL

相关库	相关表
`mysql`	`user` `global_grants` `db` `tables_priv` `columns_priv` `procs_priv` `proxies_priv` `default_roles` `role_edges` `password_history`
`information_schema`	`information_schema.COLUMN_PRIVILEGES` `information_schema.SCHEMA_PRIVILEGES` `information_schema.TABLE_PRIVILEGES` `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 审核工具。

SQLE 获取

类型	地址
版本库	https://github.com/actiontech/sqle
文档	https://actiontech.github.io/sqle-docs/
发布信息	https://github.com/actiontech/sqle/releases
数据审核插件开发文档	https://actiontech.github.io/sqle-docs-cn/3.modules/3.7_audit...

一文弄懂 OceanBase 用户管理和访问控制

用户管理

1.1 基本概念

租户

用户

1.2 用户名称语法

OceanBase

MySQL

1.3 用户密码设置

OceanBase

MySQL

1.4 用户锁定

OceanBase

MySQL

用户操作权限控制

2.1 权限管理

OceanBase

MySQL

权限管理对比

2.2 授权语句

2.3 授权表

OceanBase

MySQL

2.4 部分撤销权限限制

OceanBase

MySQL

网络安全访问控制

OceanBase

MySQL

行级权限控制

OceanBase

MySQL

角色管理

OceanBase

MySQL

小结

关于 SQLE

SQLE 获取

爱可生开源社区

引用和评论

Oracle 迁移到 OB 过程中有哪些不兼容的 DDL 语法？

在Java程序中监听mysql的binlog

Mysql数据库sql优化案例

MySQL 回收表碎片实践教程

高效工程师的七个习惯

Mysql梳理1

mysql梳理2