Oracle Row Level Security

Since version 8i, Oracle Database has a functionality to restrict data from a table based on some configuration. Beauty of this functionality is that it is 

seamlessly applied i.e., user has not to worry in reading the security definition and applying it in all of the queries. It is applied automatically by database engine.

/*

    Test Case :

  

    Environment : R12T (Vision Instance)

  

    Scenario checked by using the purchasing responsibility

        

    Parameters

    user    : OPERATIONS

    Resp.   : PURCHASING_OPERATIONS

  

*/

-- Total Records

SELECT COUNT(0) FROM PO_HEADERS_ALL

--23992 row found

-- Before script exection available records to apps user

SELECT COUNT(0) FROM PO_HEADERS

-- 0 rows found

            

-- To get the user and responsability info 

SELECT US.USER_ID, US.USER_NAME, RM.RESPONSIBILITY_ID, RM.RESPONSIBILITY_KEY, RM.RESPONSIBILITY_NAME, RESPONSIBILITY_APPLICATION_ID, APS.APPLICATION_SHORT_NAME

FROM FND_USER_RESP_GROUPS_DIRECT RS    ,FND_USER US    ,fnd_responsibility_vl RM, FND_APPLICATION APS

WHERE RS.USER_ID = US.USER_ID

AND   RS.RESPONSIBILITY_ID  = RM.RESPONSIBILITY_ID

AND   RS.RESPONSIBILITY_APPLICATION_ID  =   APS.APPLICATION_ID

AND US.USER_NAME = 'OPERATIONS'

AND RM.RESPONSIBILITY_KEY = 'PURCHASING_OPERATIONS'

--AND RM.RESPONSIBILITY_NAME = 'Purchasing'

--AND APS.APPLICATION_SHORT_NAME = 'PO'

AND RM.START_DATE <= TRUNC(SYSDATE)  

AND (RM.END_DATE IS NULL OR RM.END_DATE >= TRUNC(SYSDATE))

AND US.START_DATE <= TRUNC(SYSDATE)  AND (US.END_DATE IS NULL OR US.END_DATE >= TRUNC(SYSDATE))

;

-- Initialize global values

BEGIN FND_GLOBAL.APPS_INITIALIZE(1318, 50578, 201); END; 

-- Initialize and set allowed organization into global temp table mo_glob_org_access_tmp

BEGIN MO_GLOBAL.INIT('PO'); END;

-- After script exection available records for the apps user

SELECT COUNT(0) FROM PO_HEADERS            

-- show count of allowed data access set depending upon security profile

--** EXCEPTION 

-- Wrong Res. id , User id, Apps id

BEGIN FND_GLOBAL.APPS_INITIALIZE(3333331013595, 33333350578, 333333201); END ;

----------------------------

-- In case of any exception call to reset org_id

BEGIN MO_GLOBAL.INIT('PO'); END;

BEGIN MO_GLOBAL.INIT('S',NULL); END;

-------------------------------------------

SELECT COUNT(0) FROM PO_HEADERS

--EXEC MO_GLOBAL.SET_POLICY_CONTEXT('M',202);

--EXEC FND_GLOBAL.INITIALIZE

-- select count(0) from mo_glob_org_access_tmp