Specify valid or invalid roles for a user

You can specify valid or invalid roles for a user as needed.

You can assign multiple roles to a user. After you assign the roles to a user, you can set which of these roles are valid and which are not. The user has all the privileges of valid roles and does not have the privileges of invalid roles.

Specify valid or invalid roles upon user logon

OceanBase Database in Oracle mode allows you to specify valid or invalid roles upon user logon by using the DEFAULT ROLE clause of the ALTER USER statement.

Prerequisites

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

• To execute the ALTER USER statement, you must have the UPDATE USER privilege.

For information about how to view your privileges, see View user privileges. If you do not have the GRANT OPTION privilege, contact an administrator. For more information, see Modify user privileges. For more information about how to view your roles, see View roles.

Considerations

The DEFAULT ROLE clause only takes effect on the roles that are assigned 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 assigned to a user

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

• Roles that are assigned by a role

  For more information, see Assign a role to a role.

Syntax for the SQL statement for specifying valid or invalid roles

The following SQL statements are supported:

• Specify one or more valid roles upon user logon

  SQL syntax:

  obclient> ALTER USER user_name DEFAULT ROLE role_name;
  

• Specify all valid roles upon user logon

  SQL syntax:

  obclient> ALTER USER user_name DEFAULT ROLE ALL;
  

• Specify specific valid roles upon user logon

  SQL syntax:

  obclient> ALTER USER user_name DEFAULT ROLE ALL EXCEPT role_name;
  

• Specify all invalid roles upon user logon

  SQL syntax:

  obclient>ALTER USER user_name DEFAULT ROLE NONE;
  

Notes:

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

• ALL: All roles assigned to the user are valid.

• EXCEPT: All roles assigned to the user are valid, except for the roles specified in the EXCEPT clause. Separate multiple role names with commas (,).

• NONE: All roles assigned to the user are invalid.

Examples

Specify that the role1 role assigned to the user is valid upon user logon.

obclient>ALTER USER test DEFAULT ROLE role1;

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

Specify valid or invalid roles for the current session

OceanBase Database in Oracle mode allows you to specify the valid or invalid roles for the current session by using the SET ROLE statement.

The following SQL statements are supported:

• Specify one or more valid roles for the current session

  SQL syntax:

  obclient> SET ROLE role_name [ IDENTIFIED BY password ];
  

• Specify all valid roles for the current session

  SQL syntax:

  obclient> SET ROLE ALL;
  

• Specify specific valid roles for the current session

  SQL syntax:

  obclient> SET ROLE ALL EXCEPT role_name;
  

• Specify all invalid roles for the current session

  SQL syntax:

  obclient> SET ROLE NONE;
  

Notes:

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

• IDENTIFIED BY password: This clause specifies the password of an valid role. If you specify a password for the role when you create it, you must specify the password for the role to take effect.

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

• ALL: All roles for the current session are valid.

  Notice

  When you specify all valid roles for the current session, an error is returned if any of the roles has a password.

• EXCEPT: All roles assigned to the user are valid, except for the roles specified in the EXCEPT clause. Separate multiple role names with commas (,).

• NONE: All roles assigned to the user are invalid.

Examples:

• Enable role1 identified by the password ****** for the current session.

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

• Specify that all roles other than therole2 role takes effect in the current session.

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

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