Continuing from my
Here are the steps to implement the Fine Grained Access Control feature to mimic the row-level security in your PeopleSoft online Query Manager.
We will create a function QRY_SEC_FUNCTION that will be used by the policy to add the filter.
create or replace function QRY_SEC_FUNCTION (schema_name IN varchar2,
table_name IN VARCHAR2)
V_CLIENT_INFO := SYS_CONTEXT(‘USERENV’,’CLIENT_INFO’);
V_OPRID := SUBSTR(V_CLIENT_INFO,1,INSTR(V_CLIENT_INFO,’,’,1)-1);
V_TABLE_NAME := TABLE_NAME;
If V_OPRID is null then
V_SQL_TEXT := ‘EXISTS (SELECT ”X” FROM PS_PERS_SRCH_QRY A1 WHERE A1.EMPLID = ‘||V_TABLE_NAME||’.EMPLID AND A1.OPRID = USER)’;
V_SQL_TEXT := ‘1=1’;
when others then
Now, we will create the policy
( object_name => ‘PS_PERSONAL_DATA’,
policy_name => ‘PERSONAL_DATA_POLICY’,
policy_function => ‘QRY_SEC_FUNCTION’,
statement_types => ‘select’,
update_check => TRUE );
Now let’s test the result…
As we can see above, now our results from the database match the results from online query. The function has dynamically added the additional criteria similar to what was done by Query Manager.
In today’s world, it has become critical to ensure that there are no security loopholes in the system that will expose data to people who should not be seeing them. Row-level security provided by PeopleSoft helps us secure online access and we most often forget that users setup in the database can by-pass this security and have access to all the data. FGAC helps us replicate the online row level security in the database thus helping us further secure the database.
PS – This will be my last post for 2007. If you have read so far… Wish you a Very Happy New Year!!!