Manage roles

This topic describes how to manage roles in OceanBase Database in Oracle mode. In Oracle mode, a role is a set of system privileges and object privileges. A role can contain other roles.

You can grant multiple roles to a user and set which of these roles are effective and which are not. The user will have all the privileges of the effective roles and does not have the privileges of the ineffective roles.

OceanBase Database supports the following operations for roles in Oracle mode: Note

Note: To grant system privileges or roles to another user, you must have the privileges or roles to be granted and have the GRANT OPTION privilege.

• Grant privileges between roles

  The syntax for granting privileges between roles is as follows:

  GRANT <role_name_list> TO role_name WITH ADMIN OPTION;
  

• Grant new privileges to the PUBLIC role or grant the PUBLIC role to a user

  The PUBLIC role applies to the entire tenant. You can grant privileges to the PUBLIC role or grant the PUBLIC role to a user.

  Examples:

  obclient> GRANT [privilege_list] TO PUBLIC;
  
  obclient> GRANT PUBLIC TO user_name;
  

• Specify the effective roles and ineffective roles upon user logon

  You can use the DEFAULT ROLE clause to specify the roles that become effective upon user logon, or use ALL to make all roles effective upon user logon. You can also use the EXCEPT clause to specify the ineffective roles or use NONE to make all the roles granted to the user ineffective.

  Example:

  obclient> ALTER USER user_name DEFAULT ROLE role_name;
  

• Specify the effective roles or ineffective roles for the current session

  You can use the SET ROLE statement to specify the effective roles or ineffective roles for the current session. Note

  The configuration applies only to the current session and does not apply to subsequent sessions.