Thursday, July 26, 2007

Fine-Grained Auditing in PeopleSoft

Recently, somebody asked me whether it is possible to audit the fact that someone has queried a piece of data in PeopleSoft. It led to an interesting piece of research.

It is a fairly easy matter to configure Fine-Grained Auditing (FGA) in Oracle, you have to create a policy with the DBMS_FGA package. You can choose which columns to audit and a logical audit condition to determine whether the rows should be audited. This example will log users who query salary data from the JOB record in PeopleSoft.

BEGIN
sys.dbms_fga.add_policy
(object_schema=>'SYSADM'
,object_name=>'PS_JOB'
,policy_name=>'JOB_SALARY'
,audit_condition=>'ANNUAL_RT != 0 OR MONTHLY_RT != 0 OR DAILY_RT != 0 OR
HOURLY_RT != 0 OR SHIFT_RT != 0 OR SHIFT_FACTOR != 0'
,audit_column=>'ANNUAL_RT, MONTHLY_RT, DAILY_RT, HOURLY_RT, SHIFT_RT,
SHIFT_FACTOR'
,enable=>TRUE
,statement_types=>'SELECT'
,handler_schema=>'SYS'
,handler_module=>'dmk_fga_hand( object_schema, object_name, policy_name)'
,audit_trail=>DBMS_FGA.DB + DBMS_FGA.EXTENDED
,audit_column_opts=>DBMS_FGA.ANY_COLUMNS);
END;
/

If the audit condition is matched for any rows in a query, and the query selects one of the audit columns, a single audit row is produced, irrespective of the number of rows that match.

Be aware that if the audit condition raises an error for any inspected row (such as an error caused by coercion), the audited query will also fail. There will be nothing in the message to suggest that it is caused by FGA, and so it can be difficult to diagnose and debug.

I chose to save the audit data to a table in the database, but alternatively, you can output the audit data as an XML data structure in a flat file written by setting:

, audit_trail=>DBMS_FGA.XML

Oracle writes a file to the directory indicated by the AUDIT_FILE_DEST parameter.
And this is the data the Oracle records in the table.

SESSIONID DBUID    OSUID              OSHST
--------- -------- ------------------ ----------------------
EXTID OBJ$SCHEMA OBJ$NAME POLICYNAME
---------------------- ---------- ---------- ----------
SCN LSQLTEXT STMT_TYPE
--------- ----------------------------------- ----------
NTIMESTAMP# INSTANCE# PROCESS# STATEMENT
------------------------- ---------- ------------ ----------
ENTRYID LSQLBIND
---------- -----------------------------------
1927 SYSADM GO-FASTER-4\David GO-FASTER\GO-FASTER-4
GO-FASTER-4\David SYSADM PS_JOB JOB_SALARY
6375305 SELECT EMPLID, EMPL_RCD, EFFDT, T 1
23-JUL-07 15.47.21.429000 0 6856:11944 12104
2 #1(6):K0G004 #2(1):0

It tells us is that a process, running as OS user David on node GO-FASTER-4, connected to the database as SYSADM and queried some audited data on table PS_JOB. But this node is the application server, and it will look the same for all access to PeopleSoft via the Application Server or any other program.

We need to collect some more information from the session.

If you use database triggers to audit updates in PeopleSoft, they obtain the PeopleSoft operator ID from the client information string that is set by a call to the DBMS_APPLICATION_INFO package at the start of every service in the application server. They extract the operator ID with a function called GET_PS_OPRID (delivered in getpsoprid.sql).

You can’t put a trigger on the audit table (SYS.FGA_LOG$) because it is owned by SYS, but we could use the error handler to call a custom PL/SQL procedure. The handler module is fired every time an audit record is written. It is intended to permit a notification to be sent on audit to the DBA when a policy is breached. But we could use it for a different purpose. The handler runs in the same session as the process that performs the audited action. So I can collect information about the session and write it to another audit table that I have created.

CREATE TABLE sys.dmk_fga
(timestamp# DATE
,object_schema VARCHAR2(30)
,object_name VARCHAR2(128)
,policy_name VARCHAR2(30)
,ntimestamp# TIMESTAMP
,instance# INTEGER
,sessionid INTEGER
,entryid INTEGER
,scn INTEGER
,action VARCHAR2(32)
,module VARCHAR2(48)
,client_info VARCHAR2(64)
);

CREATE OR REPLACE PROCEDURE dmk_fga_hand ( object_schema VARCHAR2, object_name VARCHAR2, policy_name VARCHAR2 ) AS
l_client_info VARCHAR2(64);
l_action varchar2(32);
l_module varchar2(48);
BEGIN
sys.dbms_application_info.read_client_info(l_client_info);
sys.dbms_application_info.read_module(l_module, l_action);
INSERT INTO dmk_fga
(timestamp#, ntimestamp#
, object_schema, object_name, policy_name
, instance#
, sessionid, entryid
, scn, client_info, action, module)
SELECT sysdate, systimestamp
, object_schema, object_name, policy_name
, i.instance_number
, USERENV('SESSIONID'), USERENV('ENTRYID')
, d.current_scn, l_client_info, l_action, l_module
FROM v$database d, v$instance i
WHERE rownum <= 1; EXCEPTION WHEN OTHERS THEN NULL; END; /

And this is the information that is inserted into my new logging table

TIMESTAMP#          OBJECT_SCH OBJECT_NAM POLICY_NAM
------------------- ---------- ---------- ----------
NTIMESTAMP# INSTANCE# SESSIONID ENTRYID
------------------------- --------- --------- -------
SCN ACTION MODULE
--------- -------------------- --------------------
CLIENT_INFO
-----------------------------------------------------
23:00:54 23/07/2007 SYSADM PS_JOB JOB_SALARY
23-JUL-07 23.00.54.015000 1 1927 6
6390779 PSAPPSRV.exe
PS,,go-faster-4.london.go-faster.co.uk,HCM89,PSAPPSRV.exe,

The SCNs do not match between these two audit records. There is a difference of at least 3, but the tables can be joined on SESSIONID and ENTRYID (so you will probably need an index on these columns).

set long 5000
SELECT --b.client_info,
sysadm.get_ps_oprid(b.client_info) oprid,a.ntimestamp#, a.POLICYNAME
,a.lsqltext, a.lsqlbind
FROM sys.fga_log$ a, sys.dmk_fga b
WHERE a.sessionid = b.sessionid
AND a.entryid = b.entryid
;

So, if I now when I open a component in PeopleSoft which queries somebody’s compensation rate, the FGA policy generates a record.


I can use the above query to combine the audit data. It this example it shows that operator PS queried salary data for employee

OPRID        NTIMESTAMP#               POLICYNAME
------------ ------------------------- ----------
LSQLTEXT
----------------------------------------------------
LSQLBIND
----------------------------------------------------
PS 23-JUL-07 22.00.53.968000 JOB_SALARY
SELECT EMPLID, EMPL_RCD, EFFDT, TO_CHAR(EFFDT,'YYYY-
...
PL_RCD, EFFDT DESC, EFFSEQ DESC
#1(6):K0G005 #2(1):0

Note that the data was queried from the database when the component was opened, and that is when the audit occurred. The operator would have to navigate to the ‘Compensation’ tab to see the data, so the audit does not absolutely prove the data was displayed to the operator, but I suspect that is only a semantic difference.

Overhead


The ability to audit is all very well, but it comes at a price. It is not difficult to generate a large number of audit rows. In the following PL/SQL block, an audit row is generated for each time the query inside the loop is executed (2050 times on my HR demo database).


DECLARE
l_annual_rt NUMBER;
BEGIN
FOR i IN (SELECT DISTINCT emplid, empl_rcd FROM ps_job WHERE effdt <= SYSDATE) loop
SELECT j.annual_rt
INTO l_annual_rt
FROM ps_job j
WHERE j.emplid = i.emplid
AND j.empl_rcd = i.empl_rcd
AND j.effdt = (
SELECT MAX(j1.effdt) FROM ps_job j1
WHERE j1.emplid = i.emplid
AND j1.empl_rcd = i.empl_rcd
AND j1.effdt <= SYSDATE)
AND j.effseq = (
SELECT MAX(j2.effseq) FROM ps_job j2
WHERE j2.emplid = i.emplid
AND j2.empl_rcd = i.empl_rcd
AND j2.effdt = j.effdt);
END LOOP;
END;
/

This can have a dramatic effect on performance. I ran the above PL/SQL on my laptop with SQL*Trace.




































Operation

Without FGA


With FGA


Driving Query

0.46s

0.43s


Inner Query

0.92s

3.43s



Insert into FGA_LOG$

 

1.68s


Insert into DMK_FGA

 

6.48s

Other time in Exception Handler

 

0.72s


Total

1.38s

12.75s


FGA causes a huge increase in the response time of this test. Most of the additional time is spent inserting the audit rows, although the performance of the single disk in my laptop is probably magnifying the effect.

Conclusion

I have demonstrated that it is possible to use FGA to monitor who is looking at what in PeopleSoft as well as detecting other forms of break-in. However, it can introduce a significant run-time overhead. The ability to incorporate a custom PL/SQL notification procedure suggests that it is designed with the intention of logging exceptional activities rather than routine one that users are permitted to perform.

While FGA could be used to audit access to sensitive data, I think that row-level security should be setup correctly in PeopleSoft in the first place, so that only duly authorised users can access sensitive data. I suggest that FGA should only be used conservatively to monitor exceptional events.

Monday, July 02, 2007

Changes to Long Columns and Unicode in PT8.48

Unicode and the use of Long columns in PeopleSoft have been subjects that I have grumbled about in the past. However, Oracle now appear to have addressed them in PeopleTools 8.48. Unfortunately I cannot find any guiding documentation., and I stumbled across these changes by accident. However, if they work, they are both things that customers should know about.

PeopleSoft deliver two scripts in the PT8.48 distribution in %PS_HOME%/scripts: upgradedboptions_enable.sql and upgradedboptions_disable.sql, but I cannot find any documentation.

They set PSSTATUS.DATABASE_OPTIONS to 2 and 0 respectively. This one setting controls both features.

Unicode

I found the following in the platform advisory note: Operating System, RDBMS & Additional Component Patches Required for Installation on PT 8.48 under RDBMS Patches for 10g.

"Note#3: Required INIT.ORA Parameters for building 'Unicode' databases with PT8.48 and Enterprise application releases 9 or later...
Note. If it is your intention to create a Unicode DB for an application release 9 or later database, then the following init.ora parameter is mandatory. The PeopleSoft Unicode implementation for PT8.48 no longer triples the VARCHAR2 datatype columns. Instead we rely on an Oracle feature called CHARACTER LENGTH SEMANTICS. This parameter is not needed for non-unicode DB's or for pre-9 unicode application databases.

NLS_LENGTH_SEMANTICS=CHAR "

However, this advice is not in the same note for PeopleTools 8.49. Tests have shown PeopleTools 8.48 no longer trebles the length of string coulmns if PSSTATUS.UNICODE_ENABLED = 1 and PSSTATUS.DATABASE_OPTIONS is set to 2 (I haven't tested 8.49 yet, but the installation guide now says that you now 'must' use Character Semantics for Unicode databases).

This change will be a huge improvement for PeopleSoft customers who need Unicode support. The length checking constraints can dramatically increase the overhead of parsing SQL in Oracle (I have seen as much as a 300% increase in parse time - see my Unicode Oddity presentation).

LONG -v- CLOB columns

If PSSTATUS.DATABASE_OPTIONS is set 2, columns that would normally be created as type LONG, are now created as CLOBs.

Use of clobs has a number of advantanges.


  • It is not possible to partition a table with a LONG, but it is permitted with a CLOB.




  • It permits the use of SQL string functions on CLOBs that cannot be used on LONGs




  • It is possible to move data in CLOBs across database links (or at least first 32K).




  • In Oracle 9i, it was not possible to use Streams to replicate a table with a long column. This restriction was removed in 10g. However, most PeopleSoft customer upgrading to PeopleTools 8.48 will also take the oppotunity to upgrade to Oracle 10g




  • Examples

    The settings in these fields on the table PSSTATUS control the generation by Application Designer and Data Mover of the column list [TBCOLLIST] in the create table DDL model.
    PSSTATUS.
    DATABASE
    _OPTIONS
    PSSTATUS.
    UNICODE
    _ENABLED
    Create Table DDL
    00CREATE TABLE PS_DMK
    (EMPLID VARCHAR2(11) NOT NULL
    ,EMPL_RCD SMALLINT NOT NULL
    ,EFFDT DATE
    ,DESCRLONG LONG VARCHAR) ...
    0 1 CREATE TABLE PS_DMK
    (EMPLID VARCHAR2(33) NOT NULL CHECK(LENGTH(EMPLID)<=11)
    ,EMPL_RCD SMALLINT NOT NULL
    ,EFFDT DATE
    ,DESCRLONG LONG VARCHAR) ...
    2 either 0 or 1 CREATE TABLE PS_DMK
    (EMPLID VARCHAR2(11) NOT NULL
    ,EMPL_RCD SMALLINT NOT NULL
    ,EFFDT DATE
    ,DESCRLONG CLOB) ...