PeopleSoft Fine Grained Auditing – Part II

Now, letlet’s test this policy. Log on to PeopleSoft environment using the browser and create a PRIVATE query referring to the above table. The query will not have any criteria and will fetch all rows (this table only had 1002 rows).
After executing the query, the audit data is populated in DBA_FGA_AUDIT_TRAIL.

select timestamp, db_user, client_id, object_name from dba_fga_audit_trail where object_name = ‘PS_ABC_COMPANY_TBL’

/

TIMESTAMP DB_USER CLIENT_I OBJECT_NAME

——— ——– ——– ———————

21-MAY-07 SYSADM NPAI PS_ABC_COMPANY_TBL

21-MAY-07 SYSADM NPAI PS_ABC_COMPANY_TBL

We can also, select the actual TEXT executed by the user by selecting the SQL_TEXT column in the above data dictionary view.

Summary:

I have shown a small example utilizing FGA for auditing the PeopleSoft database. FGA is a neat feature and allows us to audit specific rowset instead of auditing all the rows in the table. This is very useful when there exists a table which has sensitive + non-sensitive information, and you want to audit any un-authorized access to the sensitive column or rowset.

Note 1:

* As of 9i, FGA feature only allows auditing SELECT. 10g supports SELECT, DELETE, UPDATE and INSERT statements.

Note 2:

If you need to drop the policy then use the below SQL

begin

dbms_fga.drop_policy (

object_schema=>’SYSADM’,

object_name=>’PS_ABC_COMPANY_TBL’,

policy_name=>’ABC_COMPANY_TBL_ACCESS’

);

end;

Bug Note:

Do not forget to check out the bug related to FGA

http://www.red-database-security.com/advisory/oracle-fine-grained-auditing-issue.html

Read More About  PeopleSoft Fine Grained Auditing

Take 2: Jolt Session Pooling Continued….

Take 2: Jolt Session Pooling Continued….

In our last blog, we talked about Jolt Session Pooling. That was kind of introduction to this concept I suppose. I am now going much deeper into this parameter to understand how this parameter works with Peoplesoft context (also I am interested to know more about this parameter anyway!)…

A quick look at the web.xml file at PORTAL/WEB-INF folder can give you a list of servlets that Peoplesoft application uses: Some of them are:

• psc
• psp
• cs
• xmllink
• PSAttachServlet
• psreports
• SchedulerTransfer
• SyncServer
• monitor
• ppmi etc…

I am not going into each and every servlet that Peoplesoft uses and their details. That is out of scope for my explanation here. And, primarily because, I do not know about them neither… However an overall understanding of this will definitely help us to understand the underlying architecture behind the Peoplesoft Internet Architecture.

For each and every servlet that Peoplesoft uses, there exists a definition at web.xml file. For simplicity sake, this file can be treated as Servlet Configuration File and it is an XML file by nature. If you work with weblogic and Java J2EE, they call this file as Deployment Descriptor Elements. I find it hard to remember that way. So, just to keep things easy, consider this web.xml file as Servlet Configuration File created as part of application deployment…

We are talking here for Peoplesoft technology. We dont need that much gory details to know about weblogic (we need to know some basics!). Just basics. A weblogic server has many servlets that is used for processing requests. A servlet basically connects to the Tuxedo using a session pool manager, which assigns a session based on availablity etc. This session is, then, connecting to the Tuxedo system (application server) using Jolt Server.

So, back to our main point.. why JoltPooling needs to be disabled for all the servlet entries in the web.xml file to resolve the “download to excel” button to work… Probably you have guessed it already.

Read More About  Jolt Session Pooling

Jolt Session Pooling on the Web Server Configuration

Okay, long time no see! I am back. First of all, let me wish you all a Happy and Prosperous New year. Lets hope for the best.

I had some project , as well as, some personal commitments that I needed to complete during last year. One of my project commitment was, obviously due to the reason that I moved to a new project. Also, coincidently I moved to a new location in another country which added some more complications with my new project assignment…

I had a goal last year (2007) to become a certified PMP. PMP is an acronym for Project Management Professional offered by PMI (Project Management Institute). I took the training from Hexavarsity starting of the year. And eventually I have become PMP after spending quite a considerable time. That was my personal commitment that delayed me from writing in this blog.. So, long story short, I am going to write again in this blog. All that I know about Peoplesoft and Unix (Dont laugh, I will try!).

Lets start! We had a situation recently with our UAT environment. The “download to Excel” button is not working. You should be aware of what this “Download to Excel” button I am talking about. For those who are unable to guess, here it is. It is a small image, just like an excel sheet, that will be shown on the right corner of the any tabular output from Peoplesoft application ( Example Process monitor, Reports etc). If you click on this image, you will receive the excel sheet of whatever data you were looking at…

We started doing some research on this issue(still doing!). You know where to start first. You guessed, right, Peoplesoft Customer Connection. They recommended to try disabling JoltPooling in Web Server. This directive is available in web.xml file for the weblogic web server, and called Jolt Session Pooling.

Starting with Tools 8.48, Jolt Session Pooling is enabled by default. What does this mean? Let me explain this from the scratch. As you are aware, web server makes a connection to app server using Jolt. If you enable Jolt Session Pooling , the user connections between web server and app server are simply shared. This setting is expected to minimise system resources by sharing the user connection by pooled sessions…

Web.xml file is an xml file that has directives for all the peoplesoft servlets that the web server uses ( for example, psp, psc etc). Every servlet is set to JoltPooling set as true by default starting with Peopletools Version 8.48. All we did was, disabled JoltPooling on all of these servlets. And this issue is resolved.

Is the issue really resolved? Not exactly, I will say. We still need to find why Jolt Session Pooling needs to be disabled to make “Download to Excel” button to work. Mystery continues…. Until next time.

Read More About  Web Server Configuration

Determine Portal Navigation for all custom processes

The below SQL lists the complete portal navigation along with the process name. I built this SQL when we had to provide this list to our client to review processes that could be retired. The output is useful for generating an inventory or assisting in KEEP/DROP decisions during upgrade.

Here it is:

SELECT NAVIGATION, A1.PRCSNAME

FROM

(SELECT LPAD(‘–’,2*(LEVEL-1)) || PORTAL_LABEL “NAVIGATION”, PORTAL_URI_SEG2 FROM (SELECT PORTAL_LABEL, PORTAL_PRNTOBJNAME, PORTAL_OBJNAME, PORTAL_URI_SEG2 FROM PSPRSMDEFN A

WHERE PORTAL_NAME = ‘EMPLOYEE’ ) B

WHERE B.PORTAL_PRNTOBJNAME != ‘ ‘

START WITH (B.PORTAL_URI_SEG2 IN

(SELECT D.PNLGRPNAME

FROM PSMENUITEM A, PSMENUDEFN B, PS_PRCSDEFNPNL C, PSPNLGROUP D

WHERE A.MENUNAME=B.MENUNAME

AND A.PNLGRPNAME = C.PNLGRPNAME

AND A.PNLGRPNAME = D.PNLGRPNAME

AND C.PRCSNAME IN

(SELECT PRCSNAME FROM PS_PRCSDEFN

WHERE LASTUPDOPRID !=

 Read More About  Determine Portal Navigation

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

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…

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