Real World Scenario: Using a Role to Facilitate Granting Developer Privileges

From:

Chapter 10 - User Access and Security
OCA/OCP: Introduction to Oracle9i SQL Study Guide
by Chip Dawes and Biju Thomas


In your shop, the developers must be able to create and alter a session, as well as tables, clusters, views, sequences, and synonyms. The developers also need the SELECT privilege on the data dictionary views SYS.V_$SESSION and SYS.V_$SESSION_LONGOPS. You could grant all the privileges to each individual developer when he or she starts to work on a new database, but this is tedious and prone to error. Instead, you can create a role called DEVELOPER that will be granted to each of your developers. This role will incorporate all the system privileges and will be granted to the developers Chuck, Dave, and Erik, as follows:

 
 CREATE ROLE developer;

 GRANT CREATE SESSION, ALTER SESSION TO developer;

 GRANT CREATE CLUSTER, CREATE TABLE, CREATE VIEW,

      ,CREATE SEQUENCE, CREATE SYNONYM TO developer;

 GRANT SELECT ON v_$session TO developer;

 GRANT SELECT ON v_$session_longops TO developer;


 GRANT developer TO chuck, dave, erik;

 

After the role is created and the privileges are granted, the development manager discovers the need to use stored SQL (procedures, functions, packages, and triggers). You need to grant the necessary privileges to the developers so they can create and use these objects. You could grant the CREATE PROCEDURE and CREATE TRIGGER privileges to each developer, but since you have the DEVELOPER role, the better solution is to simply grant the privileges to the role, and then the users will inherit this new privilege.

 GRANT CREATE PROCEDURE, CREATE TRIGGER TO developer;

A couple of months later, the development manager wants her team to create and use
materialized views for the new data mart, so the developers now need the CREATE SNAPSHOT, CREATE DIMENSION, QUERY_REWRITE, and GLOBAL QUERY REWRITE privileges. Also, there are two new developers, Karen and Annie, who need all the privileges granted to Chuck, Dave, and Erik. Again, with your DEVELOPER role, you don't need to grant these privileges to each individual developer. Instead, you just grant these privileges to the role, and the developers inherit these privileges.

 

 GRANT CREATE SNAPSHOT, CREATE DIMENSION, QUERY REWRITE

    ,GLOBAL QUERY REWRITE TO developer;

 GRANT developer TO karen, annie;

As you can see, the use of roles has a greater benefit over time than the direct
granting of privileges. Maintenance and the introduction of new features or systems are less tedious and error prone.