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 logon

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

Prerequisites

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

• 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 information about how to grant privileges to a user, see Modify user privileges. For information about how to view your roles, see View roles.

Considerations

The DEFAULT ROLE clause takes effect only for 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 for the following roles:

• Roles that are not granted to a user

  For information about how to grant a role to a user, see Grant a role to a user.

• Roles that are granted by another role

  For information about how to grant a role to another role, 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 logon

  obclient> ALTER USER user_name DEFAULT ROLE role_name;
  

• Activate all roles granted to a user upon user logon

  obclient> ALTER USER user_name DEFAULT ROLE ALL;
  

• Activate some roles granted to a user upon user logon

  obclient> ALTER USER user_name DEFAULT ROLE ALL EXCEPT role_name;
  

• Deactivate all roles granted to a user upon user logon

  obclient>ALTER USER user_name DEFAULT ROLE NONE;
  

where

• role_name: specifies the name for a 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 logon:

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.

Application scenarios

The SET ROLE statement mainly applies to the following scenarios:

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

  obclient> SET ROLE role_name [ IDENTIFIED BY password ];
  

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

  obclient> SET ROLE ALL;
  

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

  obclient> SET ROLE ALL EXCEPT role_name;
  

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

  obclient> SET ROLE NONE;
  

where

• role_name: specifies the name for a 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 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.

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.