Authentication

The identity authentication feature validates the user identity when a user logs on to a database to access data, verifies whether the user can be associated with a database user, and implements security control on the activities of the user in the database based on the privileges of the associated database user. OceanBase Database supports identity authentication in both MySQL 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.6.1 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 hashed 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

In the Oracle-compatible mode of OceanBase Database, for users who have multiple failed login attempts, the system will lock the users to prevent malicious password attacks, thereby improving database security.

In MySQL-compatible mode, you can use the connection_control_failed_connections_threshold variable to specify the threshold of 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, users in a tenant must have unique usernames. However, users in different tenants have the same usernames. Therefore, a user is globally identified by the combination of username and tenant name in the username@tenant name format.

Authentication plugins

Starting from V4.6.1, 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 parameter of the profile to verify whether the password complexity meets the requirements. To verify the password complexity, you must create a PL function that meets the following requirements:

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 will be called, and the new password will be judged whether it meets complexity requirements based on the execution result of the function.

Login failure processing

In the Oracle-compatible mode of OceanBase Database, for users who have multiple failed login attempts, the system will lock the users to prevent malicious password attacks, thereby improving database security.

In Oracle-compatible mode, you can use User Profile to lock users. User Profile allows you to lock a user after consecutive failed login attempts by specifying the following two parameters:

• failed_login_attempts: the number of consecutive failed login attempts.

• password_lock_time: the lock time, in days.