Administration of large numbers of objects is difficult. Oracle roles allow you to localize the administration of objects. Oracle roles are most helpful when large numbers of users will need the same system and object privileges.
Think of an Oracle role like a database user that nobody uses. You create the Oracle role using the create role command. You then grant the Oracle role all of the common privileges that users will require to do their work, like the ability to select, insert, update and delete data from various tables.
Once the Oracle role is all setup, you only need to grant the Oracle role to users and all the privileges will be transferred along with that grant. Later you can add additional privileges to the role if required.
To explain, use of Oracle role, let’s take an example. for internal audit application, a new schema IAUD is created. while creating a schema , two new roles should be also created and attached to IAUD.
1) IAU_READ_ROLE: All objects on IAUD should be attached to this role with select privilege only.
2) IAU_DML_ROLE: All objects on IAUD should be attached to this role with select, insert, update, and delete privileges.
If any Database object is created it should be attached to IAU_READ_ROLE and IAU_DML_ROLE roles by executing following commands:
Grant select on IAUD.IAU_BUS_PROCESSES to IAU_READ_ROLE;
Grant select, insert, update, delete on IAUD.IAU_BUS_PROCESSES to IAU_DML_ROLE;
in above example IAUD.IAU_BUS_PROCESSES is a table.
so once read and DML roles are setup for schema whenever we need to give any particular schema access to any user then attaching a role to that particular user, should work for us. ideally, DML role should be assigned to support team on need basis in the production environment.