Roles

Before I talk about roles, there is a few points that I want the readers to understand before I begin.

All Oracle objects (e.g.tables, indexes, etc) are owned by users who created them.
- only the user who created a table will be able to see its data.To allow other to see rows, the owner/user must grant access privileges.
- Oracle has system privileges that allow global access rights.
- Oracle has object privileges that allow grants to specific tables.

Any big organization will face difficulties to manage their database users in the form of managing their system and objects privileges.

In these companies staffs come and go plus they may have selected privileges on many objects.
Roles will be most helpful to manage this problem.

A role is a set or group of privileges that can be granted to users or roles. This is a great way for DBA to save time and effort.

To be able to create a role, you must have at least the CREATE ROLE system privileges.

The syntax for creating a role is:
CREATE ROLE role_name[ NOT IDENTIFIED IDENTIFIED {BY password USING [schema.] package EXTERNALLY GLOBALLY } ;

If both the NOT IDENTIFIED and IDENTIFIED phrases are omitted in the CREATE ROLE statement, the role will be created as a NOT IDENTIFIED role.

The role_name phrase is the name of the new role that you are creating.

The NOT IDENTIFIED phrase means that the role is immediately enabled. No password is required to enable the role.

The IDENTIFIED phrase means that a user must be authorized by a specified method before the role is enabled.

The BY password phrase means that a user must supply a password to enable the role.

The USING package phrase means that you are creating an application role - a role that is enabled only by applications using an authorized package.

The EXTERNALLY phrase means that a user must be authorized by an external service to enable the role. An external service can be an operating system or third-party service.

The GLOBALLY phrase means that a user must be authorized by the enterprise directory service to enable the role.


Example:
CREATE ROLE abc;
This 1st example creates a role called abc.

CREATE ROLE abcIDENTIFIED BY abc123;
This 2nd example creates the same role called abc, but now it is password protected with the password of abc123.

The syntax for granting privileges on a table is:
grant privileges on object to role_name

For example, if you wanted to grant select, insert, update, and delete privileges on a table called emp to a role named abc, you would execute the following statement:

grant select, insert, update, delete on emp to abc;

You can also use the all keyword to indicate that you wish all permissions to be granted.

For example:
grant all on emp to abc;

Revoke Privileges (on Tables) to Roles.Once you have granted privileges, you may need to revoke some or all of these privileges. To do this, you can execute a revoke command. You can revoke any combination of select, insert, update, delete, references, alter, and index.

The syntax for revoking privileges on a table is:
revoke privileges on object from role_name;

For example, if you wanted to revoke delete privileges on a table called emp from a role named abc, you would execute the following statement:
revoke delete on emp from abc;

If you wanted to revoke all privileges on a table, you could use the all keyword.
For example:
revoke all on emp from abc;

Granting the Role to a User
GRANT role_name TO user_name;

For example:
GRANT abc to bakar;
This example would grant the role called abc to the user named bakar.

The user bakar will automatically gets all the privileges that was in the role abc

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

When a user logs into the database, 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:
SET ROLE( role_name [ IDENTIFIED BY password ] ALL [EXCEPT roleA, roleB, ... ] 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)

Example
SET ROLE abc IDENTIFIED BY abc123;
This example would enable the role called abc with a password of abc123.

A default role means that the role is always enabled for the current session when the user logs on. 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_nameDEFAULT ROLE( role_name ALL [EXCEPT roleA, roleB, ... ] 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.

Example:
ALTER USER bakarDEFAULT ROLE abc;
This example would set the role called abc as a DEFAULT role for the user called bakar.

ALTER USER bakar DEFAULT ROLE ALL;
This example would set all roles assigned to bakar as DEFAULT.

ALTER USER bakarDEFAULT ROLEALL EXCEPT abc;
This example would set all roles assigned to bakar as DEFAULT, except for the role called abc.