Monday 3 March 2008

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

Tuesday 5 February 2008

PeopleSoft Fine Grained Auditing – Part I

We all know how critical it is to enable Oracle Database Auditing for our production environment. It is equally important to monitor the audit results and take actions. Though enabling auditing using the AUDIT_TRAIL initialization parameter plus using the AUDIT statements to enable different auditing options is the common approach, with the availability of FGA feature, it is time to take the next step.
FGA allows us to audit more specific business rules. Today, I will walk you through the steps to implement FGA for PeopleSoft.
Ensure that EnableDBMonitoring is set to 1 in psappsrv.cfg. This will enable PeopleSoft to populate CLIENT_INFO column in V$SESSION.
Identify the table and the criteria that we need to set for the policy. In this example, I will use the custom table PS_ABC_COMPANY_TBL. I need to audit any SELECT* statements on PS_ABC_COMPANY_TBL when user selects data related to
abc_company = ‘ABC Confidential’
We need to create a procedure that will populate the CLIENT_INFO so that we can identify the OPRID.
CREATE OR REPLACE PROCEDURE GET_OPRID (OBJECT_SCHEMA VARCHAR2, OBJECT_NAME VARCHAR2, POLICY_NAME VARCHAR2)
AS
V_CLIENT_INFO VARCHAR2(1000);
V_OPRID VARCHAR2(32);
BEGIN
V_CLIENT_INFO := SYS_CONTEXT(‘USERENV’,'CLIENT_INFO’);
IF ( LENGTH(V_CLIENT_INFO) IS NULL ) THEN
V_OPRID := ‘NOOPRID’;
ELSIF ( SUBSTR(V_CLIENT_INFO,1,1) = ‘,’ ) THEN
V_OPRID := ‘NOOPRID’;
ELSE
V_OPRID := SUBSTR(V_CLIENT_INFO, 1, INSTR(V_CLIENT_INFO,’,',1)-1);
END IF;
DBMS_SESSION.SET_IDENTIFIER (V_OPRID);
END;
Create a policy as shown below
begin
dbms_fga.add_policy (
object_schema=>’SYSADM’,
object_name=>’PS_ABC_COMPANY_TBL’,
policy_name=>’ABC_COMPANY_TBL_ACCESS’,
audit_column => ‘ABC_COMPANY’,
audit_condition => ‘ABC_COMPANY = ”ABC Confidential”’,
handler_module => ‘GET_OPRID’
);
end;
That’s it!!
In my next post I will share the results of enabling this feature.
 
Read More About PeopleSoft Fine Grained Auditing

Monday 28 January 2008

Take 3: Jolt Session Pooling Covered….

Before reading this entry (always a friendly warning first!), please read about my two blog entries that I posted earlier. This entry is kind of closure to my previous topic “Jolt Session Pooling”. If you still have decided to read this one, you cant complain about not able to understand this one….
In our last blog entry, we talked about Jolt Session Pooling. As I stated earlier, Jolt Session Pooling is enabled by default in Tools Version 8.48 and later. Considering this new feature(!), there are some things that are changed due to this parameter single change starting with Tools 8.48 and later….
Also, we talked about the sevlets (like psc, psp etc) that are having ‘definitions” in the web.xml file (under DOMAIN/PORTAL/WEB-INF directory). 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.
The key point here is, when Jolt Session Pooling is enabled, the session is shared across all the servlets. The existing sessions are shared across multiple servlets within weblogic server. This is supposed to be provide good performance results. There are no dedicated sessions. As of now, I know some of the effects of this change in 8.48 environment:
  1. “Download to Excel” button not working
  2. Tuxedo is unable to list the online users on the system
  3. “View Attachment” is not working
  4.  
If you know anything else due to this “Jolt Session Pooling” enabled option causing in Peoplesoft, please write on the comment below for me to know and learn. My policy is to share the knowledge and feel free to learn. :)
To know more about Jolt and Weblogic, I would recommend you to read the BEA documentation about “Using BEA Jolt with BEA Weblogic Server” Guide. I am going to write some new things going forward… One of the m is about my favorite. Inter Process Communication in the Unix System and Peoplesoft.
Read More About  Jolt Session Pooling

Friday 18 January 2008

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

Wednesday 16 January 2008

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

Friday 11 January 2008

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