See Table 9-3 System-Defined Event Attributes (near the doc end_ in Chapter 9 of the PL/SQL doc
create or replace trigger log_alter_user before alter on database begin IF (ora_dict_obj_type = 'USER') THEN INSERT INTO event_table VALUES (ora_des_encrypted_password); END IF; -- USER -> user doing the altering -- ora_dict_obj_name -> user being altered end;
fmc wrote: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.
We're currently working with OIM 11g and using the Oracle Database connector for targeting some 220.127.116.11 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?
VenkatB wrote:And I just this week discovered that 11.2 comes out of the box with auditing enabled and a set of audited events already defined.
Looks like your DBA's haven't done any house-keeping on the sys.aud$ table since they enabled auditing. Please ask them to purge history or archive them to a different table from AUD$