Identity authentication

This topic describes the identity authentication mechanism in the MySQL-compatible mode of OceanBase Database, including user authentication, identity composition, and the use of authentication plugins.

The identity authentication feature is used to verify the identity of users logging into the database, confirm whether they can be associated with a database user, and enforce security controls on data access activities based on the associated user's permissions. The Authentication Plugin is used for user password verification.

User authentication

User authentication in OceanBase Database refers to the process where the client provides valid credentials when attempting to access the OceanBase server. If the OceanBase server confirms that the provided credentials are valid, it allows the client to access OceanBase Database. OceanBase Database uses a protocol called the MySQL Authentication Protocol (MAPI) for user authentication. It is based on the MySQL client account on the client machine to complete the authentication. That is, only clients with the correct username and password can connect to the OceanBase server. When the client sends a connection request, the OceanBase server sends a random identifier to the client. The client must use the correct username and password to decode this identifier and then send the decoded result back to the server. If the result is correct, the OceanBase server allows the client to connect.

Identity composition

In MySQL-compatible mode, a user consists of a user_name and a host. Here is a direct example: create three users with the same username.

Example:

create user 'u1'@'%' identified by '*******';
create user 'u1'@'10.xxx.xxx.1' identified by '*******';
create user 'u1'@'10.xxx.xxx.2' identified by '*******';

Where:

• %: Allows any client IP to connect to the tenant.
• 10.xxx.xxx.1: Allows only the IP 10.xxx.xxx.1 to connect to the tenant.
• 10.xxx.xxx.2: Allows only the IP 10.xxx.xxx.2 to connect to the tenant.

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

Authentication plugins

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

• mysql_native_password: The traditional password authentication plugin, using 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 can be set as the default plugin for creating new users via the system variable default_authentication_plugin. This plugin has the following features:
  • SHA-256 hash algorithm: Replaces SHA-1 with the stronger SHA-256 algorithm and appends a random value (Salt) 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 SSL and RSA encryption for transmitting user passwords.
  • Password caching: The server caches the hashed values of authenticated users to improve subsequent authentication performance.
  • Adjustable iteration count: The number of SHA-256 iterations for the storage-layer hash can be controlled via the system variable caching_sha2_password_digest_rounds (default is 5000).

Component and driver version requirements

When using the caching_sha2_password authentication plugin, the database components and client drivers must meet the following version requirements:

Example

When creating or modifying a user, you can specify the authentication plugin using the IDENTIFIED WITH auth_plugin syntax. If not specified via the default_authentication_plugin variable, mysql_native_password is used by default.

-- Use the caching_sha2_password authentication plugin (supported starting from V4.6.1).
CREATE USER 'u1'@'%' IDENTIFIED WITH caching_sha2_password BY '*******';

-- Use the mysql_native_password authentication plugin.
CREATE USER 'u2'@'%' IDENTIFIED WITH mysql_native_password BY '*******';

-- Modify the user's authentication plugin.
ALTER USER 'u1'@'%' IDENTIFIED WITH caching_sha2_password BY '*******';

-- Query user authentication plugin information.
SELECT user, plugin, authentication_string FROM mysql.user WHERE user LIKE 'user_%';