Comparison of User Management and Access Control between OceanBase (MySQL Mode) and MySQL

1 User Management

1.1 Basic Concepts

Tenant

In OceanBase a tenant is a logical resource allocation unit; data between tenants is completely isolated, similar to a separate database instance.

Tenants are divided into system tenant and normal tenant .

User

Users are classified as system‑tenant users and normal‑tenant users . The built‑in administrators are:

System tenant: root

MySQL tenant: root

Oracle tenant: sys

If the current session belongs to the system tenant, a newly created user becomes a system‑tenant user; otherwise it is a normal‑tenant user.

1.2 User Name Syntax

The user name appears in SQL statements such as CREATE USER , GRANT , and SET PASSWORD . The syntax rules are the same in OceanBase and MySQL.

OceanBase

# User name syntax: 'user_name'@'host_name'
obclient [oceanbase]> create user 'test01'@'%' identified by '123456';
Query OK, 0 rows affected (0.017 sec)

# '@host_name' part is optional
obclient [oceanbase]> create user test02;
Query OK, 0 rows affected (0.017 sec)

# Host part can be a hostname or IP, wildcards % and _ are allowed, CIDR notation is supported
obclient [oceanbase]> create user 'test02'@'%.mysql.com';
Query OK, 0 rows affected (0.016 sec)
obclient [oceanbase]> CREATE USER 'test02'@'198.51.100.0/24';
Query OK, 0 rows affected (0.028 sec)

MySQL

# User name syntax: 'user_name'@'host_name'
mysql [localhost:8031] > create user 'test01'@'%' identified with mysql_native_password by '123456';
Query OK, 0 rows affected (0.03 sec)

# '@host_name' part is optional
mysql [localhost:8031] > create user test02;
Query OK, 0 rows affected (0.03 sec)

# Host part supports hostname, IP, wildcards, CIDR (since 8.0.23)
mysql [localhost:8031] > create user 'test02'@'%.mysql.com';
Query OK, 0 rows affected (0.03 sec)
mysql [localhost:8031] > CREATE USER 'test02'@'198.51.100.0/24';
Query OK, 0 rows affected (0.04 sec)

Test result: behaviour is consistent .

1.3 Password Setting

Common statements are CREATE USER , ALTER USER , and SET PASSWORD . Syntax support differs slightly.

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.

obclient [oceanbase]> SET PASSWORD FOR 'jeffrey'@'%' = PASSWORD('password');
Query OK, 0 rows affected (0.015 sec)

MySQL

mysql [localhost:8031] > CREATE USER 'jeffrey'@'%' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.53 sec)

mysql [localhost:8031] > ALTER USER 'jeffrey'@'%' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.00 sec)

mysql [localhost:8031] > SET PASSWORD FOR 'jeffrey'@'%' = 'password';
Query OK, 0 rows affected (0.02 sec)

mysql [localhost:8031] > SET PASSWORD FOR 'jeffrey'@'%' = PASSWORD('password');
ERROR 1064 (42000): You have an error in your SQL syntax ...

Test result:

The SET PASSWORD syntax differs between the two databases.

MySQL’s ALTER USER ... IDENTIFIED BY supports the USER() function, which OceanBase does not.

1.4 User Locking

Both databases support locking via ALTER USER ... ACCOUNT LOCK/UNLOCK . However, OceanBase does not support the CREATE USER ... ACCOUNT LOCK syntax.

OceanBase

obclient > alter user 'jeffrey'@'%' account unlock;
Query OK, 0 rows affected (0.004 sec)

obclient > alter user 'jeffrey'@'%' account lock;
Query OK, 0 rows affected (0.019 sec)

obclient > create user 'jin'@'%' account lock;
ERROR 1064 (42000): syntax error near 'account lock'

Lock status can be checked in the __all_user table’s is_locked column.

MySQL

mysql > alter user 'jeffrey'@'%' account unlock;
Query OK, 0 rows affected (0.03 sec)

mysql > alter user 'jeffrey'@'%' account lock;
Query OK, 0 rows affected (0.03 sec)

mysql > create user 'jin'@'%' account lock;
Query OK, 0 rows affected (0.01 sec)

Lock status is stored in mysql.user.account_locked .

2 User Operation Permission Control

2.1 Permission Levels

Both OceanBase (MySQL mode) and MySQL define three permission levels:

Administrative permissions – affect the whole tenant/server.

Database permissions – affect all objects within a specific database.

Object permissions – affect a specific object such as a table, view, or index.

2.2 Grant Statements

Supported statements are GRANT , REVOKE , WITH GRANT OPTION , and SHOW GRANTS . Syntax is consistent between the two databases.

2.3 Grant Tables

OceanBase

Related Database

Related Tables

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

Related Database

Related Tables

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

Result: the implementation of grant tables differs significantly.

2.4 Partial Revokes

OceanBase does not support partial revocation of global privileges. MySQL can enable it by setting the partial_revokes variable.

3 Network Security Access Control

OceanBase provides a tenant white‑list strategy via the ob_tcp_invited_nodes variable, supporting IP, subnet, wildcard, and mixed formats. MySQL has no equivalent built‑in feature.

4 Row‑Level Permission Control

Neither OceanBase (MySQL mode) nor MySQL natively support row‑level security; Oracle tenant mode can achieve it via Label Security, and MySQL can simulate it with views or triggers.

5 Role Management

MySQL supports role management, while OceanBase (MySQL mode) does not; role‑like functionality is available only in Oracle tenant mode.

Migration note: When moving from MySQL to OceanBase, expand MySQL roles into their constituent privileges because OceanBase lacks native role support.

6 Summary

In user management, both databases share identical user‑name syntax, have minor differences in password‑setting syntax, and differ in user‑locking capabilities. Permission management statements are consistent, but grant tables and dynamic privileges differ. OceanBase offers a tenant white‑list for network access, while MySQL does not. Role management is absent in OceanBase (MySQL mode) but present in MySQL.

References

[1] OceanBase MySQL‑mode privilege classification: https://www.oceanbase.com/docs/common-oceanbase-database-cn-10000000001702232

[2] MySQL Privileges Provided: https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html

[3] Row‑level security in MySQL: https://www.sqlmaestro.com/resources/all/row_level_security_mysql/