Question: I need to understand how
to use the Oracle "set role" command and the
dbms_session.set_role
procedure. Can you give an example of the set
role command and show hot set role is different from
granting a role to a user?
Answer:
The Oracle set role command and the equivalent
dbms_session.set_role procedure are used to
dynamically grant a role to a user. See
setting roles for a user.
When a user is created, the default for active roles is
set to ALL and a user who signs onto Oracle will have all
privileges that have been assigned to them via the grant
command. The default ALL means that all the
roles granted to a user are active.
The DBA can change this default with an alter user
command to revoke roles and a user can enable multiple
roles at one time and use the
set role command.

The set role command allows you to add or revoke
roles at the session level, without effecting the existing
roles that are granted to the user. The following are
examples of the set role command:
set role all; -- The default,
allow all role privileges that have been granted
set role none; -- For the session, revoke all role
privileges
set role my_role identified by my_pass; - Activate a
runtime role to the user session
—- Enables and
disables named role for session
—- Same as SQL command:
SET ROLE DBMS_SESSION.SET_ROLE (role_cmd VARCHAR2);