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

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

• 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 solves the problem due to the centralization of privileges to a limited number of authorizers. When you grant a privilege to a user by using a WITH ADMIN OPTION or WITH GRANT OPTION clause in the GRANT statement, you allow the user to grant the same privilege to other users. The revocation of object privileges extends to dependent users. For example, when you revoke an object privilege from User A who has delegated the object privilege to User B, the object privilege is also revoked from User B. The revocation of system privileges does not extend to dependent users.

Role

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 only have permission to connect to the database.

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 permission to access objects in a view.

• Permissions 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

In MySQL mode, roles are not supported and the privilege management model is simple. The logic of privilege check is the same as that in Oracle mode.

Privilege types

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

• Administrator privileges: the privileges to manage a tenant, such as modifying system settings and accessing all tables in the tenant.

• 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:

• An 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 such as 192.168.x.x/24 or 192. 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.