Identity authentication

The identity identification and authentication feature validates the identity of users who log on to the database to access data, verifies whether a user can be associated with a database user, and implements security control on the user's data access activities in the database based on the privileges of the associated database user. OceanBase Database supports identity authentication in both MySQL-compatible and Oracle-compatible modes.

MySQL-compatible mode

Identity authentication

In MySQL-compatible mode, a user is identified by its user_name and host. The following example shows how to create three users with the same username:

create user 'u1'@'%' identified by '***';
create user 'u1'@'1.1.1.1' identified by '***';
create user 'u1'@'2.2.2.2' identified by '***';

When a user logs on, the OBServer node controls the login based on the user_name, client_ip, and password.

Authentication plugins

In MySQL-compatible mode, OceanBase Database supports using authentication plugins (Authentication Plugin) to control password hashing and verification methods. Currently, the following two authentication plugins are supported:

• mysql_native_password: The traditional password authentication plugin that uses the SHA-1 hash algorithm.
• caching_sha2_password: A password authentication plugin based on SHA-256, providing a higher level of security. This plugin is supported starting from V4.4.2 BP2 and has the following features:
  • SHA-256 hash algorithm: Replaces SHA-1 with the stronger SHA-256 algorithm and appends a random value when generating the hash.
  • Dual authentication modes: Supports fast authentication (based on hash cache) and full authentication (based on storage-layer hash using multiple rounds of SHA-256 computation).
  • Secure transmission: Supports encrypted transmission of user passwords over SSL and RSA.
  • Password caching: The server caches the hash values of authenticated users to improve subsequent authentication performance.

Password complexity

To prevent malicious password attacks, OceanBase Database allows you to set a password complexity function as needed to verify the login identity and improve database security.

In MySQL-compatible mode, you can specify password complexity rules by configuring a series of tenant-level system variables. When you create a user or when a user changes the login password, the login password is verified based on the configured system variables. If the password verification fails, an error is returned. The following table describes the relevant system variables.

Login failure processing

OceanBase Database locks users who have multiple failed login attempts. This is mainly intended to prevent malicious password attacks, protect the database, and improve database security.

In MySQL-compatible mode, the system variable connection_control_failed_connections_threshold is used to specify the threshold for failed login attempts. This is a tenant-level variable. The value is of an integer type and falls in the range of [0,2147483647]. The default value is 0. If you set the variable to 0, this feature is disabled and the number of failed login attempts is not limited. When the number of failed login attempts exceeds the specified threshold, the account is locked. You can use the following two parameters to specify the lock period:

• connection_control_min_connection_delay: specifies the lock period for the first failed login attempt after the threshold is exceeded. The period of the second lock is min( connection_control_min_connection_delay+1000, 1000* trunc ( connection_control_min_connection_delay/1000,0)). After that, the lock period increases by an additional 1,000 milliseconds for each failed login attempt.

• connection_control_max_connection_delay: specifies the maximum lock period for a failed login attempt.

Oracle-compatible mode

Identity authentication

In Oracle-compatible mode, usernames are unique within a tenant, but users in different tenants can have the same username. Therefore, a user is uniquely identified across the system by the username@tenant_name format.

Authentication plugins

Starting from V4.4.2 BP2, OceanBase Database supports the password authentication plugin mechanism in Oracle-compatible mode. You can explicitly specify to use the caching_sha2_password (SHA-256-based) authentication plugin when creating or modifying a user, which provides a higher level of security protection.

Password complexity

To prevent malicious password attacks, OceanBase Database allows you to set a password complexity function as needed to verify the login identity and improve database security.

In Oracle-compatible mode, you can use the PASSWORD_VERIFY_FUNCTION attribute in a Profile to verify whether the password complexity meets the requirements. You must first create a PL function for password complexity verification that meets the following interface:

FUNCTION verify_function (username     IN VARCHAR2,
              password     IN VARCHAR2,
              old_password IN VARCHAR2)
RETURN BOOLEAN;

When creating a user or modifying a password, the verification function is called, and the new password is judged to meet or not meet the complexity requirements based on the function result.

Login failure processing

OceanBase Database locks users who have multiple failed login attempts. This is mainly intended to prevent malicious password attacks, protect the database, and improve database security.

Oracle-compatible mode uses User Profile to implement user locking. User Profile provides two password parameters to lock a user after consecutive failed login attempts:

• failed_login_attempts: the number of consecutive failed login attempts.

• password_lock_time: the lock time, in days.