Oracle Database Administration: The Essential Refe

Default roles

Normally, when a role is granted to a user, it becomes a default role—that is, the role is automatically active at the time the user connects to the database. However, one of the options available with the CREATE USER and ALTER USER commands is the ability to specify a subset of the roles that are granted to the user by default. You can use this approach if you want a user to be granted a role with the condition that explicit actions must be taken to enable it.

The most common use of non-default roles is to ensure that specific system or object privileges are available only from within an application. In this case, the application would enable the role through the SET ROLE command.

Warning

The SET ROLE command specifically enables only those roles listed. Any default roles that are not listed will be disabled. Make sure that any non-default role that is to be enabled at runtime contains all system and object privileges necessary to continue normal processing.