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 modes.
MySQL mode
Identity authentication
In MySQL 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.
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 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.
| Variable | Feature | Description |
|---|---|---|
| validate_password_check_user_name | Specifies whether the user password can be the same as the username. | When the value is set to on, the user password cannot be the same as the username. |
| validate_password_length | Specifies the minimum length of the user password. | - |
| validate_password_mixed_case_count | Specifies the minimum number of uppercase and lowercase letters that a user password must contain. | - |
| validate_password_number_count | Specifies the minimum number of digits that a user password must contain. | - |
| validate_password_policy | Specifies the password checking strategy. Valid values: Low and Medium. |
|
| validate_password_special_char_count | Specifies the minimum number of special characters that a user password must contain. | - |
Login failure processing
In the Oracle 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 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 ismin( 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 mode
Identity authentication
In Oracle 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.
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 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 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 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.
