This discussion is archived
14 Replies Latest reply: Mar 27, 2013 10:29 AM by user9534346 RSS

Creating a system event DDL trigger for alter user statement

fmc Newbie
Currently Being Moderated
Hi,

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.
  • 1. Re: Creating a system event DDL trigger for alter user statement
    720708 Newbie
    Currently Being Moderated
    hi,
    i think below links helpfull for you.
    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:267415465220
    http://docs.oracle.com/cd/B19306_01/server.102/b14220/triggers.htm#i6061
    http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_7004.htm#i2153503

    regards

    Edited by: ym on Jul 18, 2012 8:03 PM
  • 2. Re: Creating a system event DDL trigger for alter user statement
    damorgan Oracle ACE Director
    Currently Being Moderated
    Demos here too:
    http://www.morganslibrary.org/reference/ddl_trigger.html

    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
  • 3. Re: Creating a system event DDL trigger for alter user statement
    rp0428 Guru
    Currently Being Moderated
    >
    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
    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;
    See Table 9-3 System-Defined Event Attributes (near the doc end_ in Chapter 9 of the PL/SQL doc
    http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/triggers.htm#g1722272

    That table lists other attributes that are available
  • 4. Re: Creating a system event DDL trigger for alter user statement
    fmc Newbie
    Currently Being Moderated
    Excellent, those are some great docs and examples. I'll be trying the suggestions right now.

    Thanks rp0428, ym and damorgan, you've been very helpful.
  • 5. Re: Creating a system event DDL trigger for alter user statement
    EdStevens Guru
    Currently Being Moderated
    fmc wrote:
    Hi,

    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.
  • 6. Re: Creating a system event DDL trigger for alter user statement
    720708 Newbie
    Currently Being Moderated
    hi,
    i aggree with ED absolutely. Auditing is very suitable instead of trigger.
    regards.
  • 7. Re: Creating a system event DDL trigger for alter user statement
    fmc Newbie
    Currently Being Moderated
    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:

    CREATE TABLE "SYS"."EVENT_TABLE"
    (
    "USER_MODIFIED" VARCHAR2(50 BYTE),
    "USER_MODIFIER" VARCHAR2(50 BYTE),
    "FECHA" DATE
    )

    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_dict_obj_name,user,sysdate);
    END IF;
    END;

    CREATE OR REPLACE TRIGGER log_grant_user
    BEFORE GRANT ON DATABASE
    DECLARE
    user_list DBMS_STANDARD.ora_name_list_t;
    number_of_grantees PLS_INTEGER;
    BEGIN
    number_of_grantees := ora_grantee(user_list);
    FOR i IN user_list.FIRST .. user_list.LAST
    LOOP
    INSERT INTO event_table
    VALUES (user_list(i),user,sysdate);
    END LOOP;
    END;

    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.
  • 8. Re: Creating a system event DDL trigger for alter user statement
    VenkatB Guru
    Currently Being Moderated
    What if the insert into table event_table fails for any reasons?

    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.

    Regards
    Venkat
  • 9. Re: Creating a system event DDL trigger for alter user statement
    fmc Newbie
    Currently Being Moderated
    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.
  • 10. Re: Creating a system event DDL trigger for alter user statement
    VenkatB Guru
    Currently Being Moderated
    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$

    Regards
    Venkat
  • 11. Re: Creating a system event DDL trigger for alter user statement
    EdStevens Guru
    Currently Being Moderated
    VenkatB wrote:
    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$

    Regards
    Venkat
    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.
  • 12. Re: Creating a system event DDL trigger for alter user statement
    VenkatB Guru
    Currently Being Moderated
    Hi Ed

    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

    Regards
    Venkat
  • 13. Re: Creating a system event DDL trigger for alter user statement
    fmc Newbie
    Currently Being Moderated
    I will surely let them know about that.

    Well, i finished testing the query on aud, it's not pretty and it's certainly not fast, but it works:

    SELECT dba.USERNAME, DECODE(dba.PASSWORD, 'EXTERNAL', 'EXTERNAL', 'GLOBAL', 'GLOBAL', 'PASSWORD') PASSWORD, dba.EXTERNAL_NAME , dba.DEFAULT_TABLESPACE,
    dba.ACCOUNT_STATUS, dba.TEMPORARY_TABLESPACE, dba.PROFILE,
    (SELECT MAX_BYTES FROM DBA_TS_QUOTAS WHERE dba.USERNAME = USERNAME AND TABLESPACE_NAME = dba.DEFAULT_TABLESPACE) AS DEFAULT_TABLESPACE_QUOTA ,
    (SELECT MAX_BYTES FROM DBA_TS_QUOTAS WHERE dba.USERNAME = USERNAME AND TABLESPACE_NAME = dba.TEMPORARY_TABLESPACE) AS TEMPORARY_TABLESPACE_QUOTA
    FROM DBA_USERS dba , DBA_AUDIT_TRAIL aud
    WHERE dba.username = aud.obj_name
    AND aud.action_name not like 'DROP%'
    AND ((aud.TIMESTAMP - TO_DATE('01011970','ddmmyyyy')) *24*60*60*1000) > :lastExecutionTime
    UNION
    SELECT dba.USERNAME, DECODE(dba.PASSWORD, 'EXTERNAL', 'EXTERNAL', 'GLOBAL', 'GLOBAL', 'PASSWORD') PASSWORD, dba.EXTERNAL_NAME , dba.DEFAULT_TABLESPACE,
    dba.ACCOUNT_STATUS, dba.TEMPORARY_TABLESPACE, dba.PROFILE,
    (SELECT MAX_BYTES FROM DBA_TS_QUOTAS WHERE dba.USERNAME = USERNAME AND TABLESPACE_NAME = dba.DEFAULT_TABLESPACE) AS DEFAULT_TABLESPACE_QUOTA ,
    (SELECT MAX_BYTES FROM DBA_TS_QUOTAS WHERE dba.USERNAME = USERNAME AND TABLESPACE_NAME = dba.TEMPORARY_TABLESPACE) AS TEMPORARY_TABLESPACE_QUOTA
    FROM DBA_USERS dba , DBA_AUDIT_TRAIL aud
    WHERE dba.username = aud.grantee
    AND ((aud.TIMESTAMP - TO_DATE('01011970','ddmmyyyy')) *24*60*60*1000) > :lastExecutionTime;

    Thanks.
  • 14. Re: Creating a system event DDL trigger for alter user statement
    user9534346 Newbie
    Currently Being Moderated
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points