Friday 28 December 2007

Getting Current and Previous EFFDTed Rows

Almost in every project, this is one of the requirements to get the Current and Previous EFFDTed rows (from JOB table).
Most of the times, we will use the Self Join SQL to get the same.
SELECT
            A.EMPLID, A.EFFDT CURR_EFFDT, B.EFFDT PREV_EFFDT
FROM   PS_JOB A, PS_JOB B
WHERE
            B.EFFDT =(SELECT MAX(A_ED1.EFFDT) FROM PS_JOB A_ED1
                        WHERE B.EMPLID = A_ED1.EMPLID
                        AND B.EMPL_RCD = A_ED1.EMPL_RCD
                        AND A_ED1.EFFDT < A.EFFDT)
            AND B.EFFSEQ =(SELECT MAX(A_ES1.EFFSEQ) FROM PS_JOB A_ES1
                                WHERE B.EMPLID = A_ES1.EMPLID
                                AND B.EMPL_RCD = A_ES1.EMPL_RCD
                                AND B.EFFDT = A_ES1.EFFDT)
            AND A.EMPLID = B.EMPLID
            AND A.EMPLID=’KA0002′

Is this the only way to get the results?????
Here is the Simple and Advanced way to achieve the same results without using Self Join
SELECT
            A.EMPLID, A.EFFDT CURR_EFFDT, LAG (A.EFFDT, 1, NULL) OVER (ORDER BY EFFDT) PREV_EFFDT
FROM   PS_JOB A
WHERE            A.EMPLID=’KA0002′

Like this, we can also get the Current and Following EFFDTed rows by using LEAD Function
SELECT
A.EMPLID, A.EFFDT CURR_EFFDT, LEAD (A.EFFDT, 1, NULL) OVER (ORDER BY EFFDT) LEFFDT
FROM PS_JOB A
WHERE A.EMPLID=’KA0002′

Note: Above SQL holds good for Oracle DB. In Oracle, The Function LAG/LEAD is called Analytic Function, which is there right from Oracle 8i onwards.
Read More About  EFFDTed Rows

Thursday 27 December 2007

Fine Grained Access Control for PeopleSoft Database – II

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…
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.
Read More About  Fine Grained Access Control

Friday 21 December 2007

How can we have DYNAMIC Prompt table for Record Fields?

Most of the times we would have faced a requirement of having Dynamic prompt table for Record Fields. Also PeopleSoft itself very much utilizes Dynamic Prompt table. How can we achieve the same in our project????
Here we go
This is accomplished by 2 ways
  1. Using EDITTABLE fields for Prompt Table
  2. Using Dynamic Views
  3.  
1. Usage of EDITTABLE Fields
Ppl1
In this method, Prompt table property of Record Fields should be assigned with %EDITTABLE value. Actually what does it mean????
Prompt Table value for the Record Field is populated from the Record Field DERIVED.EDITTABLE value. The Record Field DERIVED.EDITTABLE should be assigned with value either in one of the Peoplecode events (Either in FieldChange or FieldEdit or RowInit events). This is simply done by
DERIVED.EDITTABLE = “PERSON_NAME”;
Note: EDITTABLE Field should be present in the SAME Page, where Record Field (In this case, The Field is nothing but EMPLID) is also referred. If the Record Field is not coming under Component Search Record Field, there is no need for placing the EDITTABLE field in the Page.
2. Usage of Dynamic Views
As we know, while creating Dynamic view, there is no need to specify the SQL. This SQL should be generated dynamically and the same should be assigned to the Record Field.
Ppl2
Say for example, if we see the above Record Field TASK_PROFILE_ID, it is assigned with dynamic prompt table view TL_TSKGRP_DVW and this view TL_TSKGRP_DVW is dynamically initialized by the following Peoplecode, which can be assigned both in FieldChange and RowInit events of the Record Field TASK_PROFILE_ID.
RECORDNAME.TASK_PROFILE_ID.SqlText = “SELECT T.TASKGROUP, T.TASK_PROFILE_ID, T.DESCR FROM PS_TL_TSKGRP_PRF_W T WHERE T.TASKGROUP = ‘” | &TSKGRP | “‘ AND T.EFFDT =(SELECT MAX(T1.EFFDT) FROM PS_TL_TSKGRP_PRF_W T1 WHERE T1.TASKGROUP = T.TASKGROUP AND T1.TASK_PROFILE_ID=T.TASK_PROFILE_ID AND T1.EFFDT<= %datein( ‘” | &maxdate | “‘) )”;
Both &TSKGRP, &maxdate are dynamic bind parameters.
Read More About  DYNAMIC Prompt Table

Thursday 20 December 2007

Fine Grained Access Control for PeopleSoft database – I

Today, I will demonstrate use of Fine Grained Access Control to create row-level security for the database to mimic the row level security setup in the PeopleSoft application. Let me start with asking a few questions.
In your PeopleSoft database, do you have Oracle ids created for the PeopleSoft users? Most of the PeopleSoft shops create Oracle ids for Functional support teams, so that they can query the database when they are troubleshooting some issues.
If you answered ‘yes’ to the above question then what kind of access has been given to these ids? Most of the PeopleSoft shops will provide the Oracle ids SELECT access to all access id owned tables. Is this how you have setup your ids? This basically allows the Oracle id to bypass the security setup in PeopleSoft and be able to view all the data in the table that the user has access
Won’t you love to have online query row level security available in the database such that it will only retrieve the rowsets that the user should have access?
Providing anyone with SELECT privileges to all PeopleSoft application or tools tables is not recommended. You might want to consider identifying sensitive datasets or tables and implement FGAC policies on them. The below scenario demonstrates the need to have this feature in the database.
1.In this example, I have identified PS_PERSONAL_DATA as the table on which I want to replicate the online security in the database
2.As shown below, the Query Security Record for PS_PERSONAL_DATA is PERS_SRCH_QRY
Fgac_1
3.Now, lets try to create a query in Query Manager and check out the SQL generated by PeopleSoft
Fgac_2
4.  The Query Manager has automatically added a join with PS_PERS_SRCH_QRY and an additional filter by OPRID.
5.   If I run this query then it fetches 1901 rows
Fgac_3
6.  Now, I logon to Oracle database as PS and query PS_PERSONAL_DATA. And below are the results
Fgac_4
7.  As we knew, PS has access to all the rows in PS_PERSONAL_DATA when queried from the database.
In my next post, I will share steps to implement the Fine Grained Access Control feature.