This discussion is archived
5 Replies Latest reply: Aug 5, 2013 2:17 PM by rp0428 RSS

Create trigger not audited when run from sql developer Version 3.2.20.09

Doyle Freeman Newbie
Currently Being Moderated

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 Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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

Legend

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