Defining and granting privileges for a default role

About this task

The DBA can also define a default role to assign to individual users or to the PUBLIC group for a specific database. The role is automatically activated when the user establishes a connection with that database, without the requiring the user to issue a SET ROLE statement. At connection time, each user who holds a default role has whatever access privileges are granted to the user individually, as well as the privileges of the default role.

Only one role that the CREATE ROLE statement defines can be in effect for a given user at a given time. If a user who holds both a default role and one or more other roles uses the SET ROLE statement to make a nondefault role the active role, then any access privileges that were granted only to the default role (and not to the user individually, nor to PUBLIC, nor to the new active role) are no longer in effect for that user. The same user can issue the SET ROLE DEFAULT statement to reactivate the default role, but this action disables any privileges that the user held only through the previously enabled nondefault role.

If different default roles are assigned to the user and to PUBLIC, the default role of the user takes precedence.

To define and grant privileges for a default role:

Procedure

  1. Use the CREATE ROLE statement to create a new role in the current database.
  2. Use the GRANT statement to grant privileges to the role.
  3. Grant the role to a user and set the role as the default user or PUBLIC role using the one of the following syntax:
    • GRANT DEFAULT ROLE rolename TO username;
    • GRANT DEFAULT ROLE rolename TO PUBLIC;
  4. Use the REVOKE DEFAULT ROLE statement to disassociate a default role from a user.
    Restriction: Only the DBA or the database owner can remove the default role.
  5. Use the SET ROLE DEFAULT statement to reset the current role back to the default role.