We're currently working with OIM 11g and using the Oracle Database connector for targeting some 11.2.0.3 databases.
The database connector has a known limitation related to the fact that oracle db does not keep track of last modification date of the users. This limitation is a cause of concern by our admins and we're trying to find some alternatives to solve this problem.

Now, we can manipulate the query OIM uses to find the users on the target databases, so what we need is a way of generating that information in the first place.

We've been thinking about creating a ddl trigger statement to populate an auxiliary table with the username and modification date, so we need to find out how to get the modified user username and how to create records only for the "alter user" ddl. Is this possible? Is there something we should take into consideration before creating this kind of triggers?

Be sure you fully utilize the capabilities of system events
http://www.morganslibrary.org/reference/system_events.html
and the SYS_CONTEXT function
http://www.morganslibrary.org/reference/sys_context.html

>
we need to find out how to get the modified user username and how to create records only for the "alter user" ddl.
>
You can use a database level DDL trigger for ALTERs and check the object type. Then log who is doing the alteration.
This is untested but an example

We're currently working with OIM 11g and using the Oracle Database connector for targeting some 11.2.0.3 databases.
The database connector has a known limitation related to the fact that oracle db does not keep track of last modification date of the users. This limitation is a cause of concern by our admins and we're trying to find some alternatives to solve this problem.

Now, we can manipulate the query OIM uses to find the users on the target databases, so what we need is a way of generating that information in the first place.

We've been thinking about creating a ddl trigger statement to populate an auxiliary table with the username and modification date, so we need to find out how to get the modified user username and how to create records only for the "alter user" ddl. Is this possible? Is there something we should take into consideration before creating this kind of triggers?

Thanks.

Why can't oracle AUDIT facility be used for this? With proper setup I can query DBA_AUDIT_TRAIL and see every time an ALTER statement is issued, by whom, etc, etc.

Hi Ed, i have no prefered method for doing this, however, we do care about the footprint of the solution on the database performance and space, and we only care about alter user and grant statements, so we were thinking triggers could help us to maintain a very small table, we already tried the triggers, turned out to be extremely simple:

We get all alter and grant statements and we can easily clean up the table when it starts to grow a lot. What would be the advantage of using database audit? Or the cons of using these triggers? Like i said, we have no preference and i would like to know as much as possible about every downside for these possibilities.

a) Locking by another session
b) Tablespace out of space
c) value too big for column, etc
d) Someone dropped this table as part of house keeping
e) Trigger go invalid for the same reason causing all logons to fail, etc

AUDIT is built for such purposes and why write lines of code when Oracle has altready provided you with an in-built solution for that.

Hi Venkat,
Those sound like very good reasons to use audit.
However, there's something that worries me, i was reviewing the dba_audit_trail table, and i can see is a huge table with a couple millions records (seems that our dba have configured auditing for several events and statements), and we need to create a select that returns all users modified, or granted or revoked a role since last running of our job, doing a join between dba_users and this table could be a very slow process... any suggestion in this regard? Thanks.

That's right. It comes with basic auditing enabled (DB) and it covers logon logoff, etc. But they haven't provided any easy means to purge the history. So it's still the DBA's task to do it without which SYSTEM tablespace will be full at some stage

I have a simular reqest but can't find the code. I would like to create a trigger to log all 'ALTER USER' commands but also throw an error if the user is sys or system unless the user sys or system is running the 'ALTER USER' statement. We would like to give the password change controls over to another group but limit what they can alter.