14 Replies Latest reply: Mar 27, 2013 12:29 PM by user9534346 RSS

    Creating a system event DDL trigger for alter user statement

    fmc
      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
          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
            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
              >
              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
                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
                  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
                    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
                      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
                        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
                          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
                            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
                              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
                                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
                                  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
                                    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.