ALTER USER

Purpose

This statement is mainly used to perform the following operations:

• Change the password of a database user.

• Modify the profile used by a database user.

• To modify the encryption method for database user connections, see SET PASSWORD.

• End the password rotation period early in a password rotation scenario.

• Modify the default role of a database user.

Privilege requirements

In addition to the modification commands that the current user can execute, other users must have the ALTER USER privilege to execute this command to modify the information of other users. For more information about OceanBase Database privileges, see Privilege types in Oracle-compatible mode.

Limitations

When using a proxy user to connect to the database on behalf of a target user, only OBClient is currently supported for database connection, and the OBClient version must be V2.2.6 or later. Connecting to the database using Java drivers or C drivers is not currently supported.

Syntax

ALTER USER user_name
    { IDENTIFIED BY password
    | IDENTIFIED WITH auth_plugin [BY password]
    | EXPIRE PASSWORD ROLLOVER PERIOD
    | 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]
    | DEFAULT TABLESPACE tablespace_name
    };

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 that when the proxy user connects to the database on behalf of the target user, it automatically acquires and activates the specified role privileges. That is, only the role privileges specified for the target user take effect.
• WITH NO ROLE: When the proxy user connects to the database on behalf of the target user, it will not automatically acquire any role privileges of the target user. In other words, all role privileges of the target user will not be effective.
• WITH ROLE ALL EXCEPT {role_name[ ,role_name,...]}: When the proxy user connects to the database on behalf of the target user, automatically obtain and activate all roles of the target user except for the specified role.

Examples

• Change the password of user user1.

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

• Change the authentication plugin for user user1 to caching_sha2_password.

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

• Change the connection encryption protocol for user user1 to SSL.

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

• Change the profile used by user user1 to profile1.

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

• End the password rotation cycle for user user1 early in a password rotation scenario.

  obclient> ALTER USER user1 EXPIRE PASSWORD ROLLOVER PERIOD;
  Query OK, 0 rows affected
  

• Set the default role for user 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

  User user1 can directly use the privileges of role1. To use the privileges of role2 and role3, user user1 must enable these roles in the session.

  For more information about enabling role commands, see SET ROLE.

• Change the default tablespace of user user1 to ts1.

  obclient> ALTER USER user1 DEFAULT TABLESPACE ts1;