A user can be granted multiple roles. After a role is granted, if the role is active in the session, the user can use the privileges contained in that role; otherwise, the user cannot use those privileges. To check which roles are active in the current session, you can use the CURRENT_ROLE() function.
By default, roles granted to other users or roles are not automatically activated in a session.
Specify the default role for a user upon login
OceanBase AI 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
Notice
If a role is already granted to a user, you can specify to activate the role by default when you log in as the user without further authorization.
The user has the roles to be activated.
The user has the
CREATE USERprivilege.
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
test1user by default when the user logs on.obclient [oceanbase]> SET DEFAULT ROLE NONE TO test1;To deactivate all roles granted to the
test1andtest2users by default when they log in, execute the following statement:obclient [oceanbase]> SET DEFAULT ROLE NONE TO test1,test2;Specify to activate all roles granted to the
test1user by default when the user logs on.obclient [oceanbase]> SET DEFAULT ROLE ALL TO test1;Specify to activate the
employeeanddeveloperroles granted to thetest1user 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
test1user by default when the user logs on.obclient [oceanbase]> ALTER USER test1 DEFAULT ROLE NONE;Specify to activate all roles granted to the
test1user by default when the user logs on.obclient [oceanbase]> ALTER USER test1 DEFAULT ROLE ALL;Specify to activate the
employeeanddeveloperroles granted to thetest1user by default when the user logs on.obclient [oceanbase]> ALTER USER test1 DEFAULT ROLE employee,developer;
Enable automatic activation of all roles for users upon login
In addition to the role activation methods described above, users with the ALTER SYSTEM privilege can also enable automatic activation of all roles granted to users upon login by using the global variable activate_all_roles_on_login. The default value of this variable is off, which indicates that automatic activation of all roles upon login is disabled.
Before setting this variable, note the following:
If the value of the
activate_all_roles_on_loginvariable ison, this setting takes precedence over the default roles specified by theSET DEFAULT ROLEstatement or theALTER USERstatement. That is, the system ignores the settings of theSET DEFAULT ROLEandALTER USERstatements and automatically activates all roles granted to the user by default upon login.If the value of the
activate_all_roles_on_loginvariable isoff, the system uses the default roles specified by theSET DEFAULT ROLEstatement or theALTER USERstatement.
The statement for enabling automatic activation of all roles granted to users upon login is as follows:
obclient [oceanbase]> SET GLOBAL activate_all_roles_on_login = on;
Specify the roles to activate in the current session
OceanBase AI Database supports specifying the roles to activate for the currently logged-in user in the current session using the SET ROLE statement.
Note
The roles activated by the SET ROLE statement affect only the current session and do not affect subsequent sessions.
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_loginvariable and the default roles set by theSET DEFAULT ROLEstatement or theALTER USERstatement.If the value of the
activate_all_roles_on_loginvariable ison, all roles granted to the user are activated in the session.If the value of the
activate_all_roles_on_loginvariable isoff, the default roles specified by theSET DEFAULT ROLEstatement or theALTER USERstatement are activated in the session.
Specify to disable all roles granted to the user in the current session.
obclient [oceanbase]> SET ROLE NONE;Specify to activate all roles granted to the user in the current session.
obclient [oceanbase]> SET ROLE ALL;Specify to activate all roles granted to the current user other than
role1in the current session.obclient [oceanbase]> SET ROLE ALL EXCEPT role1;You can specify multiple roles that you want to exclude.
Specify to activate the
employeeanddeveloperroles in the current session.obclient [oceanbase]> SET ROLE employee,developer;