Monday, July 31, 2006

Roles: Default and Non default

When a user logs into Oracle, all default roles are enabled, but non-default roles must be enabled with the SET ROLE statement.

The SET ROLE statement allows you to enable or disable a role for a current session.

The syntax for the SET ROLE statement is:

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:

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:

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:

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.

ALTER USER smithj
DEFAULT ROLE
ALL;

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

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.

More.....

No comments:

Post a Comment