Use a proxy user

OceanBase Database supports the proxy user feature in Oracle mode. This feature authorizes a proxy user (such as user A) to connect to OceanBase Database on behalf of a target user (such as user B) and perform database operations under the privileges of the target user.

Prerequisites

You have the ALTER USER privilege. For more information about how to view your privileges, see View user privileges. If you do not have the ALTER USER privilege, contact the administrator to obtain the privilege. For more information about how to grant privileges to a user, see Grant direct privileges.

Background information

Assume that the USERA and USERB users are available in the current environment, the USERB user has privileges of the employee and developer roles, and the USERA user needs to be authorized as a proxy user of the USERB user.

Considerations

You can use a proxy user to connect to OceanBase Database on behalf of the target user through OceanBase Command-Line Client (OBClient), OceanBase Connector/J, or OceanBase Call Interface (OBCI). The versions of the components must meet the following requirements:

• OBClient: V2.2.6 or later

• OceanBase Connector/J: V2.4.10 or later

• OBCI: V2.0.9 or later, excluding V2.1.0

Authorize a proxy user

1. Log in to an Oracle tenant of the cluster as a user with the ALTER USER privilege.

   Note that you must specify the corresponding parameters in the following sample code based on your actual database configurations.

   obclient -h10.xx.xx.xx -P2883 -usys@oracletenant#obdemo -p*****
   

2. Authorize the USERA user as a proxy user of the USERB user.

   ALTER USER user_name GRANT CONNECT THROUGH proxy_user_name [with_clause];
   
   with_clause:
      {
          WITH ROLE {role_name[, role_name,...]}
        | WITH NO ROLE 
        | WITH ROLE ALL EXCEPT {role_name[, role_name,...]}
      }
   

   The parameters are described as follows:

   • user_name: the name of the target user corresponding to the proxy user.

   • proxy_user_name: the name of the proxy user. After authorization, the proxy user can connect to OceanBase Database on behalf of the target user and perform database operations under the privileges of the target user.

   • with_clause: specifies the roles of the target user that take effect when the proxy user connects to OceanBase Database on behalf of the target user. If this clause is not specified, all roles of the target user take effect when the proxy user connects to OceanBase Database on behalf of the target user.

     Notice

     If a role with a password is specified, you must execute the SET ROLE role_name IDENTIFIED BY role_password; statement to activate the role after you connect to OceanBase Database.

     • WITH ROLE {role_name[, role_name,...]}: specifies to automatically obtain and activate the specified roles of the target user when the proxy user connects to OceanBase Database on behalf of the target user. In this case, only the specified roles of the target user take effect.

     • WITH NO ROLE: specifies not to automatically obtain any roles of the target user when the proxy user connects to OceanBase Database on behalf of the target user. In this case, none of roles of the target user takes effect.

     • WITH ROLE ALL EXCEPT {role_name[ ,role_name,...]}: specifies to automatically obtain and activate all roles of the target user other than the specified roles when the proxy user connects to OceanBase Database on behalf of the target user.

   Here are some examples:

   • Authorize the USERA user to connect to OceanBase Database on behalf of the USERB user with all roles of the USERB user taking effect.

     obclient [SYS]> ALTER USER USERB GRANT CONNECT THROUGH USERA;
     

   • Authorize the USERA user to connect to OceanBase Database on behalf of the USERB user with only the employee role of the USERB user taking effect.

     obclient [SYS]> ALTER USER USERB GRANT CONNECT THROUGH USERA WITH ROLE employee;
     

   • Authorize the USERA user to connect to OceanBase Database on behalf of the USERB user with none of roles of the USERB user taking effect.

     obclient [SYS]> ALTER USER USERB GRANT CONNECT THROUGH USERA WITH NO ROLE;
     

   • Authorize the USERA user to connect to OceanBase Database on behalf of the USERB user with all roles of the USERB user other than the employee role taking effect.

     obclient [SYS]> ALTER USER USERB GRANT CONNECT THROUGH USERA WITH ROLE ALL EXCEPT employee;
     

3. Exit the connection.

4. Use the USERA user to log in to OceanBase Database on behalf of the USERB user.

   obclient -h10.xx.xx.xx -P2883 -uproxy_user_connection -p***** --proxy_user[=]user_name 
   

   The parameters are described as follows:

   • -h: the IP address for connecting to OceanBase Database. It is the IP address of an OceanBase Database Proxy (ODP) node when you connect to OceanBase Database through ODP or the IP address of an OBServer node when you directly connect to OceanBase Database.

   • -P: the port for connecting to OceanBase Database. When you connect to OceanBase Database through ODP, it is the listening port of ODP, and the default value is 2883, which can be modified when you deploy ODP. When you directly connect to OceanBase Database, it is the SQL port of the OBServer node, and the default value is 2881, which can be modified when you deploy OceanBase Database.

   • -u: the connection information of the proxy user. It is in the format of Name of the proxy user@Tenant name#Cluster name, Cluster name:Tenant name:Name of the proxy user, Cluster name-Tenant name-Name of the proxy user, or Cluster name.Tenant name.Name of the proxy user when you connect to OceanBase Database through ODP, or in the format of Name of the proxy user@Tenant name when you directly connect to OceanBase Database.

   • -p: the connection password of the proxy user. In this example, it is the connection password of the USERA user.

   • --proxy_user: the name of the target user corresponding to the proxy user. In this example, it is USERB.

   Here is an example to use the USERA user to connect to OceanBase Database on behalf of the USERB user through OBClient:

   obclient -h10.xx.xx.xx -P2883 -uUSERA@oracletenant#obdemo -p***** --proxy_user USERB 
   

   If the following information is returned, the connection is successful:

   Welcome to the OceanBase.  Commands end with ; or \g.
   Your OceanBase connection id is 3221487643
   Server version: OceanBase 4.3.2.0 (r200000272024061717-93c0ed73ebbcac6edbf7b585860b75d825935358) (Built Jun 17 2024 18:06:04)
   
   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 [USERB]>
   

   The connection information shows that the USERB user is connected to OceanBase Database. The USERB user has the privileges specified when it was authorized.

Revoke a proxy user

1. Log in to an Oracle tenant of the cluster as a user with the ALTER USER privilege.

   Note that you must specify the corresponding parameters in the following sample code based on your actual database configurations.

   obclient -h10.xx.xx.xx -P2883 -usys@oracletenant#obdemo -p*****
   

2. Revoke the authorization of the USERA user as a proxy user.

   obclient [SYS]> ALTER USER USERB REVOKE CONNECT THROUGH USERA;
   

References

• ALTER USER

• Overview of connection methods