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.
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
