Skip to main content

Activate roles

A user can be granted multiple roles. After a role is granted, the user can use the permissions of the role only if the role is activated in the session. Otherwise, the user cannot use the permissions of the role. You can use the CURRENT_ROLE() function to query which roles are activated in the current session.

By default, roles granted to other users or roles are not automatically activated in a session.

Specify the default activated role for a user at login​

seekdb allows you to specify the default activated role for a user at login by using the SET DEFAULT ROLE statement or the DEFAULT ROLE clause of the ALTER USER statement.

Prerequisites​

tip

If you are setting the default activated role for yourself, you do not need any permissions.

  • You must have the role to be specified.

  • You must have the CREATE USER privilege to execute the SET DEFAULT ROLE statement or the ALTER USER statement.

Examples of operations by using the SET DEFAULT ROLE statement​

The SET DEFAULT ROLE statement allows you to specify multiple users at a time.

  • Specify that all roles granted to user test1 are disabled by default when the user logs in.

    SET DEFAULT ROLE NONE TO test1;

    If you want to specify multiple users, for example, specify that all roles granted to users test1 and test2 are disabled by default when they log in, the statement is as follows:

    SET DEFAULT ROLE NONE TO test1,test2;

  • Specify that all roles granted to user test1 are activated by default when the user logs in.

    SET DEFAULT ROLE ALL TO test1;

  • Specify that the employee and developer roles granted to user test1 are activated by default when the user logs in.

    SET DEFAULT ROLE employee,developer TO test1;

Examples of operations by using the ALTER USER statement​

The ALTER USER statement allows you to specify only one user at a time.

  • Specify that all roles granted to user test1 are disabled by default when the user logs in.

    ALTER USER test1 DEFAULT ROLE NONE;

  • Specify that all roles granted to user test1 are activated by default when the user logs in.

    ALTER USER test1 DEFAULT ROLE ALL;

  • Specify that the employee and developer roles granted to user test1 are activated by default when the user logs in.

    ALTER USER test1 DEFAULT ROLE employee,developer;

Enable automatic activation of all roles at login​

In addition to the above methods, a user with the ALTER SYSTEM privilege can enable automatic activation of all roles granted to the user at login by setting the Global variable activate_all_roles_on_login to on. The default value of this variable is off, which means that all roles granted to the user are not automatically activated at login.

Before you set this variable, note the following:

  • If the value of the variable activate_all_roles_on_login is on, this setting takes precedence over the default activated roles specified by using the SET DEFAULT ROLE statement or the ALTER USER statement. In this case, all roles granted to the user are automatically activated at login.

  • If the value of the variable activate_all_roles_on_login is off, the default activated roles specified by using the SET DEFAULT ROLE statement or the ALTER USER statement are used.

The statement for enabling automatic activation of all roles granted to the user at login is as follows:

SET GLOBAL activate_all_roles_on_login = on;

Specify the activated role in the current session​

seekdb allows you to specify the activated role in the current session by using the SET ROLE statement.

tip

Roles activated by using the SET ROLE statement only affect the current session and do not affect subsequent sessions.

Here are some examples:

  • Specify to keep the default role setting in the current session.

    SET ROLE DEFAULT;

    After the statement is executed, the system activates the role based on the value of the variable activate_all_roles_on_login and the default activated roles specified by using the SET DEFAULT ROLE statement or the ALTER USER statement:

    • If the value of the variable activate_all_roles_on_login is on, all roles granted to the user are activated in the session.

    • If the value of the variable activate_all_roles_on_login is off, the default activated roles specified by using the SET DEFAULT ROLE statement or the ALTER USER statement are activated in the session.

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

    SET ROLE NONE;

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

    SET ROLE ALL;

  • Specify to activate all roles granted to the user in the current session, except for the role1 role.

    SET ROLE ALL EXCEPT role1;

    You can specify multiple roles to exclude.

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

    SET ROLE employee,developer;

References​

View roles

Contents

Edit this page