Access control

A well-developed database system requires an administrator to manage it and general users to operate on database objects in it. To access and operate on database objects, the general users must have the corresponding privileges.

Privileges in Oracle mode

Privilege types

The Oracle mode of OceanBase Database supports the following two types of privileges:

• Object privileges: the privileges to operate on specific objects, such as the ALTER, SELECT, and UPDATE privileges on a table.

• System privileges: the privileges that allow users to perform specific database operations on one or all schemas.

System privileges provide much broader permissions than object privileges.

Privilege delegation

Privilege delegation addresses the challenge of centralized authorization. By specifying with admin option or with grant option during the granting process, grantors allow users to delegate those same privileges to other users. When revoking object privileges, the corresponding privileges that the user has delegated to other users will also be revoked. For example, if user A grants privilege to user B and user B grants privilege to user C, when user A revokes privilege from user B, privilege of user C will also be revoked. Revoking system privileges does not revoke delegated privileges.

Roles

You can manage privileges by role. A role is a set of system and object privileges. A role can contain other roles. If you grant a role to a user, the user has all the privileges of the role. When a new tenant is created, it has three built-in roles by default:

• DBA role: This role has most system privileges.

• RESOURCE role: Users with the RESOURCE role can only create database objects in their own schema.

• CONNECT role: Users with the CONNECT role have the privilege to connect to databases.

• PUBLIC role: This role applies to all users in a tenant. By default, no privilege is granted to the role.

• STANDBY_REPLICATION: This role applies to network-based Physical Standby Database scenarios.

Indirect privileges

User privileges are divided into direct and indirect privileges. Direct privileges are the system or object privileges that are granted to a user. Indirect privileges are those that are granted to roles of a user. Most operations can be performed with direct or indirect privileges. Direct privileges are required to perform the following operations:

• When creating a view, the user needs privileges to access the objects in the view.

• Privileges required to execute statements in a named PL block with the definers' rights.

Privilege check

Resolver parses all the privileges required to execute an SQL statement and checks whether you have the corresponding privileges. When you attempt to perform a system operation but you do not have sufficient privileges, an error is returned indicating that you have insufficient privileges. When you attempt to access an object on which you do not have any privilege, an error is returned indicating that the object does not exist. If you have privileges other than the required ones on the object that you want to access, an error is returned indicating that you have insufficient privileges.

Privileges in MySQL mode

Comparison with Oracle mode

The MySQL mode of OceanBase Database supports roles but does not provide any built-in roles. The privilege check logic is consistent with that in Oracle mode.

Privilege types

OceanBase Database supports the following three levels of privileges in MySQL mode:

• Global privileges: the privileges to manage the entire tenant, such as modifying system settings and accessing all tables.

• Database privileges: the privileges to manage all objects in a database, such as creating or deleting tables in the database, and accessing those tables.

• Object privileges: the privileges to manage a specific object, such as accessing a specific table, view, or index.

Network security access control

OceanBase Database allows you to implement network access control based on allowlist strategies. The tenant allowlist is specified by the ob_tcp_invited_nodes variable. You can specify a list of values for the system variable and separate them with commas (,). For example, you can specify the values in the A,B,C,D format.

When a user logs on to an OBServer node, the OBServer node matches the IP address of the user against the four values. If the IP address of the user matches none of them, the access is denied. If the IP address of the user matches any one of them, access is allowed.

You can specify the values in the following formats:

• A regular IP address such as 192.168.1.1. Access is allowed only when the IP address of the client is identical to the specified value.

• An IP address that contains percent signs (%) or underscores (__), such as 192.168.1.% or 192.168.1_. In this case, fuzzy matching is used, which is similar to the LIKE operator.

• An IP address that contains netmasks, for example, 192.168.x.x/24 or 192.168.x.x/255.255.xxx.x. In this case, mask matching is used. The access is allowed only when the client IP address and netmask equals the specified value.

Row-level access control

OceanBase Database is compatible with Oracle's label security feature, which provides row-level access control to ensure the security of reading and writing data.

Label security is a forcible access control strategy. It records the label value of each row by adding a label column to the table, and compares the user label and data label to constrain the access.

OceanBase Database provides a built-in security administrator LBACSYS to manage and use Label Security. The security administrator can create customized security strategies by defining labels in the strategies and setting user labels. A security strategy can be applied to multiple tables, and multiple security strategies can be applied to one table. When a security strategy is applied to a table, a column is automatically added to the table to control the table access.