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

Java: Threads Semaphore

Hello, this is Thiago Leoncio and today I will talk about Semaphore in our Threads discussion:

A java.util.concurrent.Semaphore is a thread synchronization construct that can be used either to send signals between threads to avoid missed signals, or to guard a critical section like you would with a lock. Java 5 comes with semaphore implementations in the java.util.concurrent package so you don’t have to implement your own semaphores. Still, it can be useful to know the theory behind their implementation and use.
the code must be executed simultaneously by multiple threads only a set number of times. (By simultaneously, I’m referring to execution inside multiple parallel threads of execution.) Listing 1 illustrates the technique.

Let’s start discussing about ‘the theory’ of Semaphore:
0.1.1-It gives access to shared resources.
0.1.2-It works as a counter.
0.1.3-p() get resource.
0.1.4-v() release resource.

From Java standpoint:
0.2.1-It’s a bunch of permissions.
0.2.2-These permissions are distributed until finish.
0.2.3- Implements a counter.
0.2.4-Acquire:Adquire a resource.
0.2.5-Release:Release a resource.
0.2.6-Concept of waiting list.

Synchronization problem of protecting critical code execution can be solved using semaphores.
Semaphore is a thread construct that synchronize and can be used to:
0.3.1-Send signals between thread and avoid missed signals.
0.3.2-Or you can have a critical code section that must not be interrupted once it starts.

First basic example:
1.0- Creating a semaphore with number of specified permissions, FIFO classification:
Semaphore th = new Semaphore(10, true);
True here is to guarantee FIFO classification.

1.2- Creating a semaphore with 10 permissions without FIFO sort:

Semaphore th = new Semaphore(10);

1.3-Acquire()/Acquire(n): It acquire one or more permissions(locking):

th.acquire(); //acquire one permission.
th.acquire(5); //acquire 5 permissions.

1.4-acquireUninterruptibly();

Same as the example before , but it does not suffer any affect from Thread.Interrupt();

1.5-tryAcquire():
It tries to adquire permission. If it’s not possible, return false(not locking or timeout);

1.6-release(): Permission released.

th.release();

1.7-release(n): It releases ‘n’ permissions.

th.release(11);

1.8-availablePermits(): Number of permissions available.

num = th.availablePermits();

1.9- getQueueLength(): Number of threads locked by semaphore.

1.10- hasQueuedThreads(): Checks if there is any thread into waiting list.

1.11- isFair(): Check if queue is FIFO.

1.12-Simple example:

…
Source for example1:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

private Connection bd; private Semaphore th; private boolean r; public CheckDatabaseRequests(){     Semaphore th = new Semaphore (99) ; // Database limit    } public boolean getValues( ) throws InterruptedException {    try{        th.tryAcquire();  //acquiring permission        r = bd.createStatement().execute("Select * from dual"); // do something using the connection acquired.        th.release( ) ; //release resource.      }catch(Exception er){        System.out.println(er);    }    return r;  }

…

Second Example:
My example today I will create 2 classes(Access and AccessRequest). Problem to resolve: Customer don’t have such resources into their server and they want to avoid high requests(access) into their site.

Fig1: It will collect(increase) number of access until reach 99.

Fig2: It will synch + decrease and release access.

Sources for example2:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55

package Semaphore; import java.util.concurrent.Semaphore;     public class Access {       private static Access instance = new Access();     // remember true here to be FIFO classification     private Semaphore sem = new Semaphore(99, true);     private int connections = 0;        private Access() {     }     public static Access getInstance() {         return instance;     }        public void connect() {         try {             sem.acquire();         } catch (InterruptedException e1th) {             //catch block             e1th.printStackTrace();         }            try {             doConnect();         } finally {             //means it will release no matter what..             System.out.println("Permissions available now:" +sem.availablePermits());             sem.release();             System.out.println("Perm available after release:" +sem.availablePermits());         }     }        public void doConnect() {            synchronized (this) {             connections++;             System.out.println("Current connections(increasing): " + connections);         }            try {             Thread.sleep(2000);         } catch (InterruptedException eth) {             //catch block             eth.printStackTrace();         }         synchronized (this) {             //Decreasing ==been sychronized             connections--;             System.out.println("Been sychronized(decreasing): " + connections);         }     } }

Public void main to run second semaphore example:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

package Semaphore;   import java.util.concurrent.TimeUnit; import java.util.concurrent.Executors; import java.util.concurrent.ExecutorService;   public class AccessRequest {     public static void main(String[] args) throws Exception {         //                ExecutorService thrun = Executors.newCachedThreadPool();                    for(int i=0; i < 300; i++) {             thrun.submit(new Runnable() {                 public void run() {                     Access.getInstance().connect();                 }             });         }                    thrun.shutdown();         //how long...         thrun.awaitTermination(2, TimeUnit.DAYS);           } }

I hope this helps,
Thiago Leoncio.