Default Roles

By default, when a role is granted to a user, that role is enabled for that user. To enhance security, you can disable a role by default. When the user connects, they will have to enable the role themselves. If the role has a password, the user must enter that password before the role is enabled.

ALTER USER tom DEFAULT ROLE ALL EXCEPT yourRoleName;

When you connect as tom, you need to enable yourRoleName using SET ROLE:

CONNECT tom/password
SET ROLE yourRoleName IDENTIFIED BY password;

You can set your role to "none" (i.e. no role) using the following statement:

SET ROLE NONE;

You can set your role to "all roles" except aParticularRole using:

SET ROLE ALL EXCEPT aParticularRole;

1. Creating Roles
2. Granting Roles to a User
3. Checking Roles Granted to a User
4. Checking System Privileges Granted to a Role
5. Checking Object Privileges Granted to a Role
6. Default Roles
7. Revoking a Role
8. Dropping a Role