Activate a role

A user can be granted multiple roles. After a role is granted to a user, the user can use the privileges of the role only when the role is active in a session. You can use the CURRENT_ROLE() function to query the active roles in the current session.

By default, roles granted to a user or role from another user or role are not automatically activated in a session.

Specify the roles that become active by default when a user logs on

The MySQL mode of OceanBase Database allows you to specify the roles of a user that become active by default when the user logs on by using the SET DEFAULT ROLE statement or the ALTER USER statement with the DEFAULT ROLE clause.

Prerequisites

• The user has the roles to be activated.

• The user has the CREATE USER privilege.

Examples of using the SET DEFAULT ROLE statement

You can specify to activate the roles of one or more users in one SET DEFAULT ROLE statement.

• Specify to deactivate all roles granted to the test1 user by default when the user logs on.

  obclient [oceanbase]> SET DEFAULT ROLE NONE TO test1;
  

  To deactivate all roles granted to the test1 and test2 users by default when they log on, execute the following statement:

  obclient [oceanbase]> SET DEFAULT ROLE NONE TO test1,test2;
  

• Specify to activate all roles granted to the test1 user by default when the user logs on.

  obclient [oceanbase]> SET DEFAULT ROLE ALL TO test1;
  

• Specify to activate the employee and developer roles granted to the test1 user by default when the user logs on.

  obclient [oceanbase]> SET DEFAULT ROLE employee,developer TO test1;
  

Examples of using the ALTER USER statement

You can specify to activate the roles of one user in one ALTER USER statement.

• Specify to deactivate all roles granted to the test1 user by default when the user logs on.

  obclient [oceanbase]> ALTER USER test1 DEFAULT ROLE NONE;
  

• Specify to activate all roles granted to the test1 user by default when the user logs on.

  obclient [oceanbase]> ALTER USER test1 DEFAULT ROLE ALL;
  

• Specify to activate the employee and developer roles granted to the test1 user by default when the user logs on.

  obclient [oceanbase]> ALTER USER test1 DEFAULT ROLE employee,developer;
  

Use the activate_all_roles_on_login variable

If a user has the ALTER SYSTEM privilege, you can use the global variable activate_all_roles_on_login to specify to automatically activate all roles granted to the user when the user logs on. The default value of the variable is off, which specifies to not activate any roles granted to a user when the user logs on.

When you set the variable, observe the following notes:

• If you set the activate_all_roles_on_login variable to on, the system ignores the SET DEFAULT ROLE or ALTER USER statement, and automatically activates all roles granted to a user by default when the user logs on.

• If you set the activate_all_roles_on_login variable to off, the system automatically activates the roles specified by the SET DEFAULT ROLE or ALTER USER statement when the user logs on.

You can set the activate_all_roles_on_login variable to on by using the following statement:

obclient [oceanbase]> SET GLOBAL activate_all_roles_on_login = on;

Specify the roles that become active in the current session

The MySQL mode of OceanBase Database allows you to specify the roles granted to the current user that become active in the current session by using the SET ROLE statement.

Here are some examples:

• Specify to maintain the default role settings in the current session.

  obclient [oceanbase]> SET ROLE DEFAULT;
  

  After the statement is executed, the system activates roles based on the value of the activate_all_roles_on_login variable and the settings of the SET DEFAULT ROLE or ALTER USER statement.

  • If the activate_all_roles_on_login variable is set to on, the system activates all roles granted to the user in the current session.

  • If the activate_all_roles_on_login variable is set to off, the system activates the roles specified in the SET DEFAULT ROLE or ALTER USER statement in the current session.

• Specify to deactivate all roles granted to the current user in the current session.

  obclient [oceanbase]> SET ROLE NONE;
  

• Specify to activate all roles granted to the current user in the current session.

  obclient [oceanbase]> SET ROLE ALL;.
  

• Specify to activate all roles granted to the current user other than role1 in the current session.

  obclient [oceanbase]> SET ROLE ALL EXCEPT role1;
  

  You can specify multiple roles that you want to exclude.

• Specify to activate the employee and developer roles in the current session.

  obclient [oceanbase]> SET ROLE employee,developer;
  

References

View roles