Identity authentication

This topic describes the identity authentication mechanism in the Oracle-compatible mode of OceanBase Database, including user authentication, identity components, 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. The Authentication Plugin is used for password verification. In Oracle-compatible mode, OceanBase Database supports using authentication plugins to control the hash calculation and verification methods of passwords.

User authentication

In Oracle-compatible mode, this feature authenticates a user's username and password to recognize the user's identity and access to features and resources. OceanBase Database requires all users to log in with the correct username and password to ensure database security.

Identity components

In Oracle-compatible mode, usernames are unique within a tenant, but users in different tenants can have the same username. Therefore, a user is uniquely identified globally in the system in the username@tenant name format. Here is a direct example:

1. Log in to an Oracle-compatible tenant of a cluster as the SYS user.

   obclient -usys@oracle -h10.xxx.xxx.1 - P2881 -P*******
   

2. Execute the following statement to create a user named u1.

   obclient> CREATE USER u1 IDENTIFIED BY ******;
   Query OK, 0 rows affected (0.064 sec)
   

3. Execute the following statement to grant user u1 the privilege to connect to the database.

   obclient>GRANT CREATE SESSION TO u1;
   Query OK, 0 rows affected (0.045 sec)
   

4. Log in to the cluster as user u1.

   $obclient -h10.xxx.xxx.1 -P2881 -uu1@oracle001 -p****** -A
   Welcome to the OceanBase.  Commands end with ; or \g.
   Your OceanBase connection id is 3221707914
   Server version: OceanBase 4.0.0.0 (r101000022022120716-0d7927892ad6d830e28437af099f018b0ad9a322) (Built Dec  7 2022 16:22:15)
   
   Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.
   
   Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
   
   obclient [U1]>
   

Authentication plugins

When creating or modifying a user, you can explicitly specify to use the caching_sha2_password authentication plugin (based on the SHA-256 algorithm), which provides a higher level of security.

If the authentication plugin is not specified when creating a user, ob_native_password is used by default (equivalent to mysql_native_password, using the SHA-1 algorithm). To set caching_sha2_password as the default authentication plugin, you can configure the system variable default_authentication_plugin.

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, ob_native_password is used by default.
CREATE USER user1 IDENTIFIED BY "******";

-- Explicitly specify to use caching_sha2_password
CREATE USER user2 IDENTIFIED WITH caching_sha2_password BY "******";

-- Modify the user's authentication plugin
ALTER USER user1 IDENTIFIED WITH caching_sha2_password BY "******";

-- View User Authentication Plugin Information
SELECT username, plugin FROM dba_users WHERE username LIKE 'USER_%';

For detailed information about the IDENTIFIED WITH syntax in Oracle-compatible mode, see CREATE USER.