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.
The scope of a system privilege is larger than that of an object privilege.
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.
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: A user with the RESOURCE role can create database objects only in the schema of the user.
CONNECT role: A user with the CONNECT role can only log on 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. However, direct privileges are required to perform the following operations:
Access objects in a view when the view is created.
Execute statements in named PL/SQL blocks that require definer rights for execution.
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 the privileges to modify system settings and access all tables.
Database privileges: the privileges to manage all objects in a database, such as the privileges to create, access, and delete tables in the database.
Object privileges: the privileges to manage a specific object, such as the privileges to access 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, the OBServer 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, the access is allowed.
You can specify the values in the following formats:
An regular IP address such as 192.168.1.1. The 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.1.0/24 or 192.168.1.0/255.255.255.0. In this case, mask matching is used. The access is allowed only when the Client_IP & netmask equals the specified value.
Row-level access control
OceanBase Database is compatible with Oracle Label Security. You can implement row-level access control to ensure the security of data reads and writes.
Label Security is a forced 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.
Notice
Label Security is supported only in Oracle mode.