5 Replies Latest reply: Aug 5, 2013 4:17 PM by rp0428 RSS

    Create trigger not audited when run from sql developer Version 3.2.20.09

    Doyle Freeman

      Creating or editing a trigger is not being stored in the audit table when run from sql developer.

       

      Here is a sample script to show the issue:

       

      Grant Connect,create table,create trigger To testuser Identified By testuser;

       

      create table testuser.testtab(t1 number);

       

      Select Count(*) From Dba_Audit_Trail Where Owner='TESTUSER';

       

      CREATE OR REPLACE TRIGGER testuser.testtab_bi_trg BEFORE

        Insert

            ON testuser.testtab FOR EACH ROW

      begin

        null;

      end;

      /


      Select Count(*) From Dba_Audit_Trail  Where Owner='TESTUSER';

       

      drop user testuser cascade;

       

      If I run the script from sql developer the CREATE TRIGGER statement does not get audited.

       

      If I run the script from sql plus or All Arround Automations PL/SQL Developer the CREATE TRIGGER statement does get audited.

       

      If I edit the trigger from sql developer the CREATE TRIGGER statement does not get audited.

       

      If I edit the trigger from  All Arround Automations PL/SQL Developer the CREATE TRIGGER statement does get audited.

        • 1. Re: Create trigger not audited when run from sql developer Version 3.2.20.09
          rp0428

          You forgot to post the code you are using to perform the audit so we dont' even know if such code exists.

          • 2. Re: Create trigger not audited when run from sql developer Version 3.2.20.09
            Doyle Freeman

            Not sure what you mean by "perform the audit".

             

            Have you tested my script? Does the "Select Count(*) From Dba_Audit_Trail  Where Owner='TESTUSER';" increment by 1 after each of the ddl statements or only after the Create table statement.

            • 3. Re: Create trigger not audited when run from sql developer Version 3.2.20.09
              rp0428

              DoyleFreeman wrote:

               

              Not sure what you mean by "perform the audit".

               

              Have you tested my script? Does the "Select Count(*) From Dba_Audit_Trail  Where Owner='TESTUSER';" increment by 1 after each of the ddl statements or only after the Create table statement.

              Your question doesn't have ANYTHING to do with sql developer and should be posted in the Database General forum

              https://forums.oracle.com/community/developer/english/oracle_database/general_questions

               

              Yes - and it works just fine once you ENABLE AUDITING. Your scripIt  does NOT include the statements or code used to ENABLE auditing and, specifically, enable auditing for triggers.

               

              Auditing doesn't just 'happen'; you have to enable it and you have to specify any non-default auditing that you want to perform.

               

              Have you read any of the extensive documentation about auditing to learn how to use it?

               

              See the Database Security Guide

              http://docs.oracle.com/cd/E11882_01/network.112/e16543/auditing.htm

               

              Also see 'Auditing Functions, Procedures, Packages, and Triggers

              http://docs.oracle.com/cd/E11882_01/network.112/e16543/auditing.htm#BCGBEAGC

               

              And see the AUDIT statement in the SQL language doc for how to specify auditing of specific operations.

              http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_4007.htm

               

               

               

               

              Select count(*) From Dba_Audit_Trail  Where (Owner='SCOTT' or username = 'SCOTT')
               and action_name = 'CREATE TRIGGER';
              COUNT(*)
              0
              audit create trigger by scott
              CREATE OR REPLACE TRIGGER emp_copy_bi_trg BEFORE
                Insert
                    ON emp_copy FOR EACH ROW 
              begin
                null;
              end;
              /
              
              Select count(*) From Dba_Audit_Trail  Where (Owner='SCOTT' or username = 'SCOTT')
               and action_name = 'CREATE TRIGGER';
              COUNT(*)
              1
              
              
              
              • 4. Re: Create trigger not audited when run from sql developer Version 3.2.20.09
                Doyle Freeman

                I guess I either wasn't clear, or you didn't read my entire post.

                 

                If I run the script from sql developer the CREATE TRIGGER statement does not get audited.

                 

                If I run the script from sql plus or All Arround Automations PL/SQL Developer the CREATE TRIGGER statement does get audited.

                 

                If I edit the trigger from sql developer the CREATE TRIGGER statement does not get audited.

                 

                If I edit the trigger from  All Arround Automations PL/SQL Developer the CREATE TRIGGER statement does get audited.

                 

                If I had read the previous 4 lines from somebodies post, I would have understood that auditing for Create Trigger statements was enabled, otherwise the statements would not have been audited if the script were run from sql plus or All Around Automations PL/SQL developer.

                 

                I should have mentioned the database version (11.2.0.3). In 11g auditing is enabled by default: see ORACLE-BASE - Auditing Enhancements (DBMS_AUDIT_MGMT) in Oracle Database 11g Release 2

                • 5. Re: Create trigger not audited when run from sql developer Version 3.2.20.09
                  rp0428

                  Either I wasn't clear or you didn't read MY entire post.

                  Auditing doesn't just 'happen'; you have to enable it and you have to specify any non-default auditing that you want to perform.

                  Yes I read your entire post. But auditing just doesn't work the way you are implying it is working for you. And you may be misinterpreting what I am saying. I am not trying to argue with you. I am trying to explain how auditing works and trying to get you to post information that shows you actually have auditing enabled for CREATE TRIGGER, which is what you are complaining is not getting audited in some cases.

                   

                  Yes - the feature called 'auditing' is enabled by default. But as I said before, and just said above again, you have specify an non-default auditing that you want to perform. CREATE TRIGGER is NOT a default auditing option

                   

                  See the database security guide for the list of default options.

                  http://docs.oracle.com/cd/E11882_01/network.112/e16543/auditing.htm#CHDEEADC

                   

                  The code that I posted in my first reply (which first enables audting: audit create trigger by scott) works just fine and increments the view count every time it is compiled in sql developer 3.2.20.09

                   

                  You have still NOT posted any AUDIT statements that you may have executed to enable auditing of your CREATE TRIGGER statement.

                   

                  You also have NOT posted anything to substantiate your claim that auditing of your CREATE TRIGGER is performed by one tool but not by sql developer.

                  Auditing is performed by the database - the client tool being used to submit the DDL has NOTHING to do with how the database works.

                   

                  If AUDITING is enabled for CREATE TRIGGER the dabase will audit it no matter what tool is used to submit the DDL. The only way that would not happen is if the tool first DISABLED that particular audit , ran the DDL and then reenabled the audit.

                   

                  Post proof that auditing is enabled for CREATE TRIGGER by the user you are using and proof that is works for one tool but not sql developer.

                   

                  select * from DBA_STMT_AUDIT_OPTS

                  where user_name = 'SCOTT'

                  USER_NAME,PROXY_NAME,AUDIT_OPTION,SUCCESS,FAILURE

                  SCOTT,,CREATE TRIGGER,BY ACCESS,BY ACCESS

                  The above shows that for my code and example CREATE TRIGGER is being audited for user SCOTT