A simply way to understand the Oracle Roles

Some novice can ask themselves “can a role really be useful to me?” and often this object be ignored, but it is very useful. Surfing on web can be depressing, between system roles explanations and code constructs.
Now we try to discover it in a easy way.
Some words to describe Oracle Roles.
 

  • Oracle Roles is very useful to manage db access to multiple users.
  • It could be seen like a previleges collector.
  • An Oracle user can be granted to multiple roles
  • Using roles can avoid to grant previleges to single user

You must have CREATE_ROLE previlege to create a role
A pratical example
Suppose we have some hundreds of users, with hundreds of data table.
Jimmy Smith is a new employee and he is authorized to view and modify the table COSTUMERS and PAYMENTS.
Bobby Timmons is a new employee too, but in additions to COSTUMERS and PAYMENTS, he can take full control on data tables of a new projects, that we named “shipment”… and related produres
Without roles we are forced to grant any single previleges, like
 
GRANT SELECT, UPDATE,INSERT on COSTUMERS to JSMITH;
GRANT SELECT, UPDATE,INSERT on PAYMENTS to JSMITH;
 
GRANT SELECT, UPDATE,INSERT on COSTUMERS to BTIMMONS;
GRANT SELECT, UPDATE,INSERT on PAYMENTS to BTIMMONS;
 
“shipment” project data tables are:
FOREIGN_SHIPMENTS
NATIONAL_SHIPMENTS
COURIERS
and some others procedures…
 
GRANT SELECT, INSERT, UPDATE, DELETE on FOREIGN_SHIPMENTS to BTIMMONS;
GRANT SELECT on NATIONAL_SHIPMENTS to BTIMMONS;
etc.
GRANT EXECUTE on PKG_COMPUTE_COST to BTIMMONS
etc.
etc.
Well, the next month arrive another employee, John Coltrane, with both previleges of Bobby Timmons and Jimmy Smith.
And we must add a new table to secretary(and then Smith, Timmons and Coltrane)….
You can imagine how easy it would be to have roles created ad hoc for various need!
CREATE ROLE REGISTRY;
GRANT SELECT, UPDATE,INSERT on COSTUMERS to REGISTRY;
GRANT SELECT, UPDATE,INSERT on PAYMENTS to REGISTRY;
 
CREATE ROLE SHIPMENTS;
GRANT SELECT, UPDATE,INSERT, DELETE on FOREIGN_SHIPMENTS  to SHIPMENTS;;
GRANT SELECT, UPDATE,INSERT, DELETE on NATIONAL_SHIPMENTS to SHIPMENTS;
GRANT SELECT, UPDATE,INSERT, DELETE on COURIERS to SHIPMENTS;
GRANT EXECUTE to PKG_COMPUTE_COST to SHIPMENTS;
etc. etc.
With roles REGISTRY and SHIPMENTS, we have easily organized the previleges, and now we can manage previleges to users in this way
GRANT REGISTRY to JSMITH;
GRANT REGISTRY to BTIMMONS;
GRANT SHIPMENTS to BTIMMONS;
GRANT REGISTRY to JCOLTRANE;
GRANT SHIPMENTS to JCOLTRANE;
We can add a table on role REGISTRY, all users granted to REGISTRY obtain this previlege:
GRANT SELECT, UPDATE,INSERT on DOCUMENTS to REGISTRY;
We can revoke previleges from role
REVOKE UPDATE on COSTUMERS from REGISTRY
We can revoke role from users
REVOKE SHIPMENTS to JCOLTRANE;
 
 
 

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *