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.

Step 1:

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)

return varchar2

as

V_OPRID VARCHAR2(32);

V_emplid varchar2(20);

V_CLIENT_INFO VARCHAR2(1000);

V_QRYSECRECNAME VARCHAR2(32);

V_SQL_TEXT VARCHAR2(4000);

V_TABLE_NAME VARCHAR2(32);

begin

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)’;

else

V_SQL_TEXT := ‘1=1’;

end if;

RETURN V_SQL_TEXT;

exception

when others then

return ‘1=1’;

end;

/

Step 2:
Now, we will create the policy

begin

dbms_rls.add_policy

( object_name => ‘PS_PERSONAL_DATA’,

policy_name => ‘PERSONAL_DATA_POLICY’,

policy_function => ‘QRY_SEC_FUNCTION’,

statement_types => ‘select’,

update_check => TRUE );

end;

That’s it!!
Now let’s test the result…

Fgac_5

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.
Summary:
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!!!

Posted by Nitin Pai
Comments (3)
December 27th, 2007

Comments (3)

Ramesh - February 3rd, 2008

Thanks Nitin, appreciate your time and willingness to help.

Nitin - January 31st, 2008

Apologize for the delay in responding to your question. I was stuck with some high priority project assignments. 1. The policy is effective irrespective of the alias. It is totally transparent to the user. 2. If you enable SQL_TRACE then you will view the same SQL as executed at the SQLPLUS prompt. However, the trace file will also indicate calls to function that is used in the policy. Also, the execution path will indicate calls to views that are use in the dynamic predicate which is added through the policy. Thanks Nitin

Ramesh - January 24th, 2008

Great Post! I used to wonder how this could be done when I started my PS career; soon forgot about that... I would like to know if it works even if the user gives an alias to the table. Ex: select count(*) from ps_personal_data a; It would help if you could post the actual SQL that gets executed on the database when the above SQL is run with the policy added as explained by you above.

Comments are closed.