A role groups several privileges and roles, so that they can be granted to and revoked from users simultaneously. A role must be enabled for a user before it can be used by the user.
The SET ROLE statement allows you to enable or disable a role for a current session.
When a user logs into Oracle, all default roles are enabled, but non-default roles must be enabled with the SET ROLE statement.

The syntax for the SET ROLE statement is:

1
2
3
4

    SET ROLE
    ( role_name [ IDENTIFIED BY password ]
    | ALL [EXCEPT role1, role2, ... ]
    | NONE );

The role_name phrase is the name of the role that you wish to enable.
The IDENTIFIED BY password phrase is the password for the role to enable it. If the role does not have a password, this phrase can be omitted.
The ALL phrase means that all roles should be enabled for this current session, except those listed in the EXCEPT phrase.
The NONE phrase disables all roles for the current session. (including all default roles)

For example:

1

    SET ROLE test_role IDENTIFIED BY test123;

This example would enable the role called test_role with a password of test123.

Setting a role as DEFAULT Role
A default role means that the role is always enabled for the current session at logon. It is not necessary to issue the SET ROLE statement. To set a role as a DEFAULT role, you need to issue the ALTER USER statement.

The syntax for setting a role as a DEFAULT role is:

1
2
3
4
5

    ALTER USER user_name
    DEFAULT ROLE
    ( role_name
    | ALL [EXCEPT role1, role2, ... ]
    | NONE );

The user_name phrase is the name of the user whose role you are setting as DEFAULT.
The role_name phrase is the name of the role that you wish to set as DEFAULT.
The ALL phrase means that all roles should be enabled as DEFAULT, except those listed in the EXCEPT phrase.
The NONE phrase disables all roles as DEFAULT.

For example:

1
2
3

    ALTER USER smithj
    DEFAULT ROLE
    test_role;

This example would set the role called test_role as a DEFAULT role for the user named smithj.

1
2
3

    ALTER USER smithj
    DEFAULT ROLE
    ALL;

This example would set all roles assigned to smithj as DEFAULT.

1
2
3

    ALTER USER smithj
    DEFAULT ROLE
    ALL EXCEPT test_role;

This example would set all roles assigned to smithj as DEFAULT, except for the role called test_role.

Popularity: 33% [?]