Working with ROLES in Oracle

Role is kind of template which not only helps in encapsulating set of privileges provided to a certain user but also it helps in maintaining business rules for organization. User management can be handled smoothly with use of roles. Granting ad-hoc privileges to users is not recommend at enterprise level architecture.

In this content I will try to explain how role helps in managing business rules and security.

Suppose a company X have one datawarehouse application handling application for daily batch and transaction. It uses one oracle database connecting a schema X_DATA. X_Data contains all the business objects and hold the versioned data everyday after the daily batch job completes and provide reports to end user through application. Now end user have to login to the database using individual personal user which will access the objects of X_DATA.

From this set of application users there are 2 types of users. 
1. User who can view the data from report and update certain data if necessary
2. User who can only view the data.

So for providing such kind of privileges 2 roles are created.
i. READ_DWH_ROLE - This will provide select privileges to all tables / view of X_DATA to the user.
2. READ_WRITE_DWH_ROLE - This will provide select / update privileges to users to tables / views of X_DATA

Now let us see what command was used by DBA to add privileges to the above role on X_DATA schema user.

i.  READ_DWH_ROLE - grant select any tables on X_DATA to READ_DWH_ROLE.
ii. READ_WRITE_DWH_ROLE - grant select any table,update any table on X_DATA to READ_WRITE_DWH_ROLE

Now end user request the required role from some tool or mail with manager's approval to dba to provide access of the role to the user if exists or create new user and provide access.

Lets us assume that there is an end user Mike Anderson requesting a new user mikean requesting the role READ_DWH_ROLE and another user Peter Vaun requesting the role READ_WRITE_DWH_ROLE. The request got approved by manager and went to DBA.

DBA will login to system user and will create the to new user. Since this user will not be owner of any db objects but only access objects of X_DATA.

CREATE USER MIKEAN IDENTIFIED BY MIKEAN DEFAULT TABLESPACE TBLPSPC_X_DATA;
CREATE USER PETVAUN IDENTIFIED BY PETVAUN DEFAULT TABLESPACE TBLPSPC_X_DATA;

GRANT CONNECT TO MIKEAN;
GRANT CONNECT TO PETVAUN;

GRANT READ_DWH_ROLE TO MIKEAN;
GRANT READ_WRITE_DWH_ROLE TO PETVAUN;

So it's all set. Now mikean can read tables / views of all X_DATA witn select query or some application accessing the object using X_DATA.table_name, X_DATA.viewname and petvaun can read / update tables/ views of X_DATA.

Now suppose  the application developer is not aware of accessing this objects from X_DATA schema and he is only provided the tablename / view name and if he put the table or view name inside his application like select * from tablename instead of select * from X_DATA.tablename the application will definitely get and oracle error as table or view does not exists. In that case it is neccessary for the user to add another request for creating synonym privileges and create synonyms for all table and view of X_DATA on itself.

In that case dba has to run first grant create synonym privileges to the usrs.

GRANT CREATE ANY SNONYM TO MIKEAN;
GRANT CREATE ANY SYONYOM TO PETVAUN;

Then create a script like this and execute.

DECLARE

CURSOR REC IS SELECT TABLE_NAME FROM ALL_USERS WHERE USERNAME='X_DATA';
BEGIN
    FOR I IN REC LOOP
        EXECUTE IMMEDIATE 'CREATE SYNONYM MIKEAN.'||I.TABLE_NAME||' FOR X_DATA.'||I.TABLE_NAME;
        EXECUTE IMMEDIATE 'CREATE SYNONYM PETVAUN.'||I.TABLE_NAME||' FOR X_DATA.'||I.TABLE_NAME;
    END LOOP;
END;
/
Now if the application user the table names / view names it will actually use the synoym which will actually invoke the underlying tablename and viewname.

So this is the first part of creating role and adding privilege and assigning it to the user.

Wait for the next post to find out more on roles.