Activate or deactivate roles for a user

You can activate or deactivate roles for a user as needed.

A user can be granted multiple roles. After granting roles, you can further specify which roles will be active and which ones will be inactive. The user will have all the privileges associated with the active roles, but will not have the privileges associated with the inactive roles.

Activate or deactivate roles upon user login

The Oracle mode of OceanBase Database supports using the DEFAULT ROLE clause in the ALTER USER statement to activate or deactivate roles upon user login.

Prerequisites

• You must have the role to be granted and the GRANT OPTION privilege.

• You must also have the system privilege UPDATE USER.

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

Considerations

The DEFAULT ROLE clause takes effect only on the roles that are granted to a user by using the GRANT statement or the roles that are created by users with the CREATE ROLE privilege. The DEFAULT ROLE clause does not take effect on the following roles:

• Roles that are not granted to a user

  For more information, see Grant a role to a user.

• Roles that are granted by another role

  For more information, see Grant a role to another role.

Application scenarios

The DEFAULT ROLE clause in the ALTER USER statement mainly applies to the following scenarios:

• Activate one or more roles granted to a user upon user login

  The SQL syntax is as follows:

  obclient> ALTER USER user_name DEFAULT ROLE role_name;
  

• Activate all roles granted to a user upon user login

  The SQL syntax is as follows:

  obclient> ALTER USER user_name DEFAULT ROLE ALL;
  

• Activate some roles granted to a user upon user login

  The SQL syntax is as follows:

  obclient> ALTER USER user_name DEFAULT ROLE ALL EXCEPT role_name;
  

• Deactivate all roles granted to a user upon user login

  The SQL syntax is as follows:

  obclient>ALTER USER user_name DEFAULT ROLE NONE;
  

where:

• role_name specifies the name of the role. Separate multiple role names with commas (,).

• ALL specifies to activate all roles granted to a user.

• EXCEPT specifies to activate all roles granted to a user, except for the roles specified in the EXCEPT clause. Separate multiple role names with commas (,).

• NONE specifies to deactivate all roles granted to a user.

Examples

Activate the role1 role granted to a user upon user login.

obclient>ALTER USER test DEFAULT ROLE role1;

For more information about the ALTER USER statement, see ALTER USER.

Activate or deactivate roles for the current session

The Oracle mode of OceanBase Database supports using the SET ROLE statement to activate or deactivate roles granted to the current logged-on user for the current session.

Here are the application scenarios:

• Activate one or more roles granted to the current logged-on user for the current session

  The SQL syntax is as follows:

  obclient> SET ROLE role_name [ IDENTIFIED BY password ];
  

• Activate all roles granted to the current logged-on user for the current session

  The SQL syntax is as follows:

  obclient> SET ROLE ALL;
  

• Activate some roles granted to the current logged-on user for the current session

  The SQL syntax is as follows:

  obclient> SET ROLE ALL EXCEPT role_name;
  

• Deactivate all roles granted to the current logged-on user for the current session

  The SQL syntax is as follows:

  obclient> SET ROLE NONE;
  

where:

• role_name specifies the name of the role. Separate multiple role names with commas (,).

• IDENTIFIED BY password specifies the password for an active role. If a role has been granted a password upon creation, you must specify the password to activate the role.

  For more information about how to create a role, see Create a role.

• ALL: specifies to activate all roles granted to the current logged-on user for the current session.

  Notice

  When you activate all roles granted to the current logged-on user for the current session, an error is returned if any of the roles has a password.

• EXCEPT: specifies to activate all roles granted to the current logged-on user for the current session, except for the roles specified in the EXCEPT clause. Separate multiple role names with commas (,).

• NONE: specifies to deactivate all roles granted to the current logged-on user for the current session.

Here are some examples:

• Activate the role1 role identified by the password ****** for the current session.

  obclient> SET ROLE role1 IDENTIFIED BY ******;
  Query OK, 0 rows affected
  

• Activate all roles for the current session except for the role2 role.

  obclient> SET ROLE ALL EXCEPT role2;
  Query OK, 0 rows affected
  

For more information about the SET ROLE statement, see SET ROLE.