ALTER USER

Purpose

You can use this statement to perform the following operations:

• Change the password of a database user.

• Change the profile used by a database user.

• Change the encryption method of database user connections. For more information about how to change user passwords, see SET PASSWORD.

• Change the default role of a database user.

Required privileges

To execute this statement to modify information of a user other than the current user, you must have the ALTER USER privilege. For more information about privileges in OceanBase Database, see Privilege types in Oracle mode.

Limitations

You can use a proxy user to connect to OceanBase Database on behalf of the target user through OceanBase Command-Line Client (OBClient). The version of OBClient must be 2.2.6 or later. At present, you cannot connect to OceanBase Database by using a Java driver or a C driver.

Syntax

ALTER USER user_name
    { IDENTIFIED BY password
    | PROFILE {"profile_name" | DEFAULT}
    | REQUIRE {NONE | SSL | x509 | tls_option_list}
    | DEFAULT ROLE
            { role_name[,role_name...]
            | ALL [EXCEPT role_name[,role_name...]]
            | NONE
            }
    | GRANT CONNECT THROUGH proxy_user_name [with_clause]
    };

tls_option_list:
    tls_option
    | tls_option_list tls_option

tls_option:
    CIPHER str_value
    | ISSUER str_value
    | SUBJECT str_value

with_clause:
    WITH ROLE {role_name[, role_name,...]}
    | WITH NO ROLE 
    | WITH ROLE ALL EXCEPT {role_name[, role_name,...]}

Parameters

with_clause

• 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.

Examples

• Change the password of user1.

  obclient> ALTER USER user1 IDENTIFIED BY ******;
  Query OK, 0 rows affected
  

• Change the connection encryption protocol to SSL for user1.

  obclient> ALTER USER user1 REQUIRE SSL;
  Query OK, 0 rows affected
  

• Change the profile used by user1 to profile1.

  obclient> ALTER USER user1 PROFILE "profile1";
  Query OK, 0 rows affected
  

• Set the default roles of user1.

  obclient> CREATE ROLE role1;
  Query OK, 0 rows affected
  
  obclient> CREATE ROLE role2 IDENTIFIED BY ******;
  Query OK, 0 rows affected
  
  obclient> CREATE ROLE role3 IDENTIFIED BY ******;
  Query OK, 0 rows affected
  
  obclient> GRANT role1,role2,role3 TO user1;
  Query OK, 0 rows affected
  
  obclient> ALTER USER user1 DEFAULT ROLE role1;
  Query OK, 0 rows affected
  

  Note

  user1 can directly use the privileges of role1. To use the privileges of role2 and role3, user1 must first enable role2 and role3 in the session.

  For more information about the statement for enabling roles, see SET ROLE.