ALTER USER

Purpose

This statement is mainly used for the following operations:

• Modifying the password of a database user.

• Modifying the profile used by a database user.

• Modifying the encryption method for connecting to a database user. For other methods of modifying the password of a user, see SET PASSWORD.

• Modifying the default roles 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 classification in Oracle mode.

Limitations

When a proxy user connects to a database on behalf of a target user, the current version only supports connecting to the database through OBClient, and the OBClient version must be V2.2.6 or later. Java and C drivers are not supported for connecting to the database.

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 the roles and permissions that take effect when the proxy user connects to the database on behalf of the target user, i.e., only the specified roles and permissions of the target user take effect.
• WITH NO ROLE: specifies that no roles and permissions of the target user take effect when the proxy user connects to the database on behalf of the target user.
• WITH ROLE ALL EXCEPT {role_name[ ,role_name,...]}: specifies the roles and permissions that take effect when the proxy user connects to the database on behalf of the target user, i.e., all roles and permissions of the target user except the specified ones take effect.

Examples

• Modify the password of user user1.

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

• Modify the encryption protocol for user user1 to SSL.

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

• Modify the profile used by user user1 to profile1.

  obclient> ALTER USER user1 PROFILE "profile1";
  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 permissions of role1. The permissions of role2 and role3 can be used by user user1 only after they are enabled in the session.

  For more information about enabling roles, see SET ROLE.