OIM10G: How to audit OIM tables from database perspective and OIM create user API curiosity

Hi, This is Thiago Leoncio again and I want to publish a curiosity of the OIM APIs discovered using a security tool from Oracle Database.

OIM Curiosity:

Do you know how many tables are involved during the process to create a user using OIM API in OIM 9.1.0.2 Schema?

================================================================================

SQLS AND STEPS to Enable Oracle Database Audit using OIM Schema (eg: oimuser)

================================================================================

1) Before start, just to make sure AUDIT is not set

show parameter audit_trail;

This need to show NONE or FALSE

2) Run this command to create sequences, triggers, procedures and functions for AUDIT purpose:

DATABASE_HOME/rdbms/admin/cataudit.sql

3) ALTER SYSTEM SET AUDIT_TRAIL=TRUE SCOPE=SPFILE;

4) Shutdown database and startup:

5) Connect into database again , usign SYSADM user and type: show parameter audit_trail;

this need to show TRUE

6) Requering to AUDIT process starts to collect something, by your desire:

audit all by OIMUSER by access;

or

AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY OIMUSER BY ACCESS;

or

audit delete,insert,update on sys.aud$ by access;

7) Statements that you can find more details about the Auditory:

7.1) select * from SYS.AUD$

7.2) select * from DBA_AUDIT_TRAIL

7.3) select * from DBA_AUDIT_EXISTS

7.4) select * from DBA_AUDIT_OBJECT

7.5) select * from DBA_AUDIT_SESSION (show OS Process Number)

Helpful SQLS using DBA_AUDIT_SESSION:
7.5.1) SQL to get OIM SCHEMA Connections Made Out Of Working Hours -
SELECT checkOIM.username,
TO_CHAR(checkOIM.timestamp,’DD Mon YYYY HH24:MI:SS’)   logontime,
TO_CHAR(checkOIM.logoff_time,’DD Mon YYYY HH24:MI:SS’) logofftime
FROM sys.dba_audit_session checkOIM
WHERE checkOIM.timeStamp > SYSDATE-7
AND checkOIM.returnCode = 0
AND NOT (    TO_NUMBER(TO_CHAR(checkOIM.timestamp,’D’)) < 6
AND TO_NUMBER(TO_CHAR(checkOIM.timestamp,’HH24MI’)) BETWEEN  800 AND 1800 )
ORDER BY checkOIM.username;

7.5.2) Unsuccessful Logon Attempts in the Last Month
SELECT checkOIM.username,
checkOIM.os_username,
checkOIM.terminal,
TO_CHAR(checkOIM.timeStamp,’DD Mon YYYY HH24:MI’) timestamp,
checkOIM.returnCode
FROM sys.dba_audit_session checkOIM
WHERE checkOIM.timeStamp > SYSDATE-30
AND checkOIM.returnCode != 0;

7.6) select * from DBA_AUDIT_STATEMENT

7.7) select * from DBA_AUDIT_TRAIL

–WHAT OPTIONS I AM AUDITING
7.8) select * from DBA_OBJ_AUDIT_OPTS

–TIP Merge this views below
7.9) select * from DBA_PRIV_AUDIT_OPTS
7.10) select * from DBA_STMT_AUDIT_OPTS

like this:
select user_name, audit_option, success, failure  from dba_stmt_audit_opts
union
select  user_name, privilege, success, failure from  dba_priv_audit_opts;

Results:

Answer|||Example:

So, according to AUDIT_TRAIL, when I click on button, just to create a simple user, OIM generates ‘INSERT’ Statements with 11 different tables, as you can see into results of the SQL below:

SQL to Check just INSERT statement:

++++++++++++

select   to_char(timestamp,’DD/MM/YYYY HH24:MI:SS’) STARTTIME,obj_name,DBA_AUDIT_TRAIL.* from DBA_AUDIT_TRAIL where obj_name is not null and ACTION_NAME =‘INSERT’

++++++++++++

As you can see (picture above) some of these more than one per table.

OIM table names:

USG | OTI | OSI | OSH | SCH | ORC | USR | OBI | OIO | ADMINSERVER_WLSTORE | AUD_JMS

by the way,

STEPS TO CLEAN AUDIT TABLE:

DELETE FROM SYS.AUD$;

commit;

Bye, I hope this helps,

Thiago L Guimaraes