Overview

Tenant

OceanBase Database's tenant is a logical concept that serves as a unit of resource allocation and the basis for managing database objects and resources. As a container for various database objects, tenants also serve as containers for resources such as CPU, memory, and I/O. Data is completely isolated between tenants, with each tenant being equivalent to an instance of a traditional database. Tenants are divided into system tenants and user tenants. The system tenant ("sys" tenant) is predefined for management purposes and compatible with MySQL. User tenants are further divided into Oracle mode tenants and MySQL mode tenants, commonly referred to as Oracle tenants and MySQL tenants respectively.

User

OceanBase Database users are classified into two categories: users in a system tenant and users in a user tenant. The built-in system administrator for a system tenant is the root user. The built-in tenant administrator for a MySQL tenant is the root user, and that for an Oracle tenant is the SYS user. When you create a user, if the current session is in a system tenant, the created user belongs to the system tenant. Otherwise, the user belongs to a user tenant. Users created by the tenant administrator can only log on to the current tenant, regardless of whether it is a system tenant or a user tenant. Usernames are unique within a tenant, but users in different tenants can have the same username. Therefore, a user is uniquely identified globally in the system in the username@tenant name format.

Administrators in Oracle mode

A three-tier security management system is used for database administration in OceanBase Database's Oracle mode, not in MySQL mode.

In real life, the abuse of privileges may occur if all privileges are granted to the same person. Likewise, to protect the security of a large database, it is necessary to grant the privileges of the database system to different user roles that take different responsibilities. In this way, the user roles can restrict and supervise each other, thereby ensuring the overall security of the system.

In the three-tier security management system, administrators are classified into database administrator (DBA), system security officer (SSO), and auditor.

• DBA

  Each database is managed by at least one DBA. The DBA is responsible for installing and upgrading the database management system, configuring database parameters, creating database objects, granting access privileges, importing and exporting data, as well as backing up and restoring the database. In the Oracle mode of OceanBase Database, the SYS user serves as the DBA.

• SSO

  The SSO is responsible for configuring audit settings, defining new database auditors, viewing audit records, setting the system's security levels, scopes, and groups for the system, and defining security labels for subjects and objects, thereby improving the overall system security. In the Oracle mode of OceanBase Database, the LBACSYS user serves as the SSO.

• Auditor

  The auditor is responsible for enforcing mandatory access control over the system, setting objects and operations that need to be audited, defining new database auditors, viewing and analyzing audit records, and setting system policies and labels. By setting up auditing, almost any action performed by anyone in the system can be tracked, making it convenient for post-event investigation. In the Oracle mode of OceanBase Database, the ORAAUDITOR user serves as the auditor.

The three administrator roles restrict each other but work together to manage the database.

Privilege

In MySQL mode, roles are not supported and the privilege management model is simple. The privilege check logic of MySQL mode is the same as that of 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.

    For more information about the privileges in Oracle mode, see Privilege types.

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

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

    For more information about the privilege types in MySQL mode, see Privilege types.

Privilege delegation

Privilege delegation resolves the issue of centralized authorization. By specifying WITH ADMIN OPTION or WITH GRANT OPTION during authorization, users can be granted the permission to delegate corresponding 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.

Role

In order to facilitate privilege management, the concept of role is introduced into OceanBase Database's Oracle mode. A role is a combination of system privileges and object privileges and can also include other roles. Roles can be granted to users, giving those users all the privileges included in the role. When a new tenant is created, it automatically comes with the following default roles:

• 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 can only log on to the database.
• PUBLIC role: This role applies to all users in a tenant. By default, no privilege is granted to the role.

For more information, see Manage roles.

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, there are certain operations where direct privileges are required. These operations include:

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

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

Privilege check

During the SQL parsing phase, the system parses and checks all the required privileges for a SQL statement one by one to see whether the user has the corresponding privileges. For system operations, if the privilege is insufficient, an error will be directly reported indicating insufficient privileges. For accessing objects, if the user has no privileges on the object, an error will be reported indicating that the object does not exist. If the user has other privileges on the object but not the required privilege, an error will be reported indicating insufficient privileges.