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 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. An authentication plugin is used to validate user passwords.

User authentication

In user identity authentication of OceanBase Database, a client that attempts to connect to an OBServer node must provide valid credentials. After the OBServer node verifies the validity of the credentials, it allows the client to access OceanBase Database. OceanBase Database uses the MySQL authentication protocol for user authentication. This protocol authenticates clients based on MySQL accounts on the clients. Only the clients with the correct username and password are allowed to connect to OBServer nodes. After a client initiates a connection request to an OBServer node, the OBServer node sends a random identification code to the client. The client must decode the identification code using the correct username and password, and then return the decoded result to the OBServer node. If the result is correct, the OBServer node allows the client to connect to the OBServer node.

Identity composition

In the MySQL-compatible mode of OceanBase Database, a user is identified by both user_name and host. The following example creates three users with the same username but different hosts:

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

where:

• %: Indicates that the tenant is accessible to clients with any IP address.
• 10.xxx.xxx.1: Indicates that the tenant is accessible only to the client whose IP address is 10.xxx.xxx.1.
• 10.xxx.xxx.2: Indicates that the tenant is accessible only to the client whose IP address is 10.xxx.xxx.2.

When you log in to an OBServer node, the OBServer node matches your identity based on user_name, client_ip, and password to complete identity verification.

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: A 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. You can set it as the default plugin for creating new users using 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 hash values of authenticated users to improve subsequent authentication performance.
  • Adjustable iteration count: You can control the number of SHA-256 iterations for the storage-layer hash using the system variable caching_sha2_password_digest_rounds, with a default of 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 by the default_authentication_plugin variable, mysql_native_password is used by default.

-- Specify to use the caching_sha2_password authentication plugin (supported starting from V4.4.2 BP2).
CREATE USER 'u1'@'%' IDENTIFIED WITH caching_sha2_password BY '*******';

-- Specify to 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 '*******';

-- View User Authentication Plugin Information
SELECT user, plugin, authentication_string FROM mysql.user WHERE user LIKE 'user_%';