previous post…
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…
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.
