Best practices for security certification-OceanBase

As data security governance becomes an increasingly important global concern, ensuring the security of databases, foundational software for storing and managing critical data assets, has become a key challenge that requires collaboration between database vendors and customers. OceanBase has always prioritized product security. Over the years, it has actively complied with regulatory requirements, established a comprehensive security management system, and focused on building stable, reliable, secure, and open data infrastructure. Through continuous technological innovation, OceanBase helps customers protect the confidentiality, integrity, and availability of their data, earning its reputation as a trusted provider of foundational software. However, ensuring database security is not just the responsibility of vendors—it also requires close collaboration with customers.

Security is a key factor in evaluating the performance of a database. A database management system must prevent unauthorized access to safeguard the files and data it contains.

This topic provides the best practices for achieving security certification.

MySQL tenant

Disable accounts with null passwords

Accounts with null passwords significantly lower the barrier for attackers to compromise a database. Once attackers successfully guess the account's username, they can gain access without needing a password. To prevent unauthorized access and protect your database from malicious users, it is essential to disable any accounts with null passwords.

Self-check

Check for accounts with null passwords.

obclient> SELECT user, password FROM mysql.user WHERE LENGTH(password)=0;

Security hardening

Execute the following statement as the database administrator (DBA) to configure a password for an account with a null password.
```
obclient> SET PASSWORD FOR u0 = PASSWORD('auth_string');
obclient> ALTER USER u0 IDENTIFIED by '******';
```

Prohibit account creation by using the GRANT statement

When using the GRANT statement, if the target user does not exist, the statement will attempt to create the user. This can lead to unintended consequences—if the GRANT statement is incorrectly written, it may inadvertently create a user account with a null password, posing risks to account security. To prevent this, you can include NO_AUTO_CREATE_USER in the value of the sql_mode system variable, which disables user creation through the GRANT statement.

Self-check

Check the configurations.
```
obclient> SELECT @@global.sql_mode;
obclient> SELECT @@session.sql_mode;
```
Make sure that all return results contain the value of NO_AUTO_CREATE_USER.
Security hardening

Execute the following statement by using an administrator account with the SUPER privilege.
```
obclient> SET @@global.sql_mode=CONCAT(@@global.sql_mode, ',NO_AUTO_CREATE_USER');
```

Enable password complexity check

In a MySQL tenant, you can use tenant-level system variables to configure a password complexity strategy to prevent weak passwords and increase the difficulties in password cracking.

Variable	Description
validate_password_check_user_name	Specifies whether the account password can be identical to the username. `on`: The account password cannot be identical to the username. `off`: The account password can be identical to the username.
validate_password_length	The minimum length of the account password.
validate_password_mixed_case_count	The minimum number of uppercase and lowercase letters that an account password must contain.
validate_password_number_count	The minimum number of digits that an account password must contain.
validate_password_special_char_count	The minimum number of special characters that an account password must contain.
validate_password_policy	The password check strategy. Valid values: `low`: specifies to check only the password length. `medium`: specifies to check the password length, the number of uppercase letters, the number of lowercase letters, the number of digits, the number of special characters, and whether the password is identical to the username.

Self-check

View the password complexity configurations of the account used for logging in to the current tenant.

obclient> SHOW VARIABLES LIKE "validate_password%";

The return result is as follows:

+--------------------------------------+-------+
| Variable_name                        | Value |
+--------------------------------------+-------+
| validate_password_check_user_name    | on    |
| validate_password_length             | 0     |
| validate_password_mixed_case_count   | 0     |
| validate_password_number_count       | 0     |
| validate_password_policy             | low   |
| validate_password_special_char_count | 0     |
+--------------------------------------+-------+
6 rows in set

Security hardening

The recommended configurations are as follows:
- validate_password_length: the minimum length of the account password. We recommend that the password contain at least 14 characters.
- validate_password_check_user_name: specifies whether the account password can be identical to the username. We recommend that you set the variable to ON.
- validate_password_policy: the password check strategy. We recommend that you set the variable to medium.
- validate_password_mixed_case_count: the minimum number of uppercase and lowercase letters that an account password must contain. We recommend that you set the variable to a value greater than or equal to 1.
- validate_password_number_count: the minimum number of digits that an account password must contain. We recommend that you set the variable to a value greater than or equal to 1.
- validate_password_special_char_count: the minimum number of special characters that an account password must contain. We recommend that you set the variable to a value greater than or equal to 1.

Lock an account with consecutive authentication failures

In a MySQL tenant, you can use the tenant-level variable connection_control_failed_connections_threshold to specify the maximum number of login failures allowed. If the number of login failures of an account exceeds the specified threshold, the account is locked. You can specify the locking duration by using the connection_control_min_connection_delay and connection_control_max_connection_delay variables to prevent brute force attacks on account passwords, thereby improving database security.

Self-check

Query the value of the connection_control_failed_connections_threshold variable.
```
obclient [oceanbase]> SHOW PARAMETERS LIKE 'connection_control_failed_connections_threshold';
```
If the value is 0, account locking upon login failures is disabled.

Security hardening

Set the maximum number of login failures allowed to 5.

obclient> ALTER SYSTEM SET connection_control_failed_connections_threshold = 5;

Configure the password validity period

The system variable default_password_lifetime allows you to configure a global password expiration period for a MySQL tenant. Once a password expires, it must be reset before any further operations can be performed. Setting a password expiration period enhances operational security, ensures system compliance, and helps mitigate risks associated with password leakage.

Self-check

Query the current validity period of passwords.

obclient> SELECT @@default_password_lifetime;

The return result is as follows:

+-----------------------------+
| @@default_password_lifetime |
+-----------------------------+
|                           0 |
+-----------------------------+
1 row in set

Security hardening

Execute the following statement as the administrator to set the validity period of passwords to 90 days:
```
obclient> SET GLOBAL default_password_lifetime=90;
```

Oracle tenant

Lock an account with consecutive authentication failures

In an Oracle tenant, you can configure an authentication strategy based on the user profile. You can configure the maximum number of login failures allowed. If the number of login failures of an account exceeds the specified threshold, the account is locked. This can prevent brute force attacks on account passwords, thereby improving data security.

Self-check

Query the profile of the current user.
```
obclient> SELECT * FROM DBA_PROFILES;
```
In the return results, the FAILED_LOGIN_ATTEMPTS and PASSWORD_LOCK_TIME fields indicate the maximum number of login failures allowed and the account locking duration, respectively.
Security hardening

Log in to the Oracle tenant as the administrator. Execute the following statement to create a user profile, set the maximum number of login failures allowed to 5 and the locking duration to one day, and apply the settings to the test account.
```
obclient> CREATE PROFILE "test_auth_policy" LIMIT FAILED_LOGIN_ATTEMPTS 5  PASSWORD_LOCK_TIME 1;
obclient> SELECT * FROM DBA_PROFILES;
obclient> ALTER USER test PROFILE "test_auth_policy";
```

Configure the password validity period and grace period

In an Oracle tenant, you can configure the validity period and grace period of passwords. Setting the password validity period can improve the O&M security and system compliance and reduce risks caused by password leakage.

Self-check

Query the profile of the current user.
```
obclient> SELECT * FROM DBA_PROFILES;
```
In the return results, the PASSWORD_LIFE_TIME and PASSWORD_GRACE_TIME fields indicate the validity period and grace period, respectively.
Security hardening

Make sure that the value of PASSWORD_LIFE_TIME is smaller than or equal to 90, and that of PASSWORD_GRACE_TIME is smaller than or equal to 5.
```
obclient> ALTER PROFILE "test_auth_policy" LIMIT PASSWORD_LIFE_TIME 90 PASSWORD_GRACE_TIME 5;
```

Configure a password complexity strategy and enable password complexity check

In an Oracle tenant, you can configure a password complexity strategy to prevent weak passwords and increase the difficulties in password cracking.

Self-check

Query the profile of the current user.
```
obclient> SELECT LIMIT FROM DBA_PROFILES WHERE RESOURCE_NAME='PASSWORD_VERIFY_FUNCTION' AND PROFILE='DEFAULT';
```
The return result is as follows:
```
+-----------------+
| LIMIT           |
+-----------------+
| VERIFY_FUNCTION |
+-----------------+
```
If the returned value is NULL, password complexity check is not enabled. If the returned value is the function you specified, password complexity check is enabled.
Security hardening

For more information, see Password complexity.