1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

auditing create profile

Discussion in 'SQL PL/SQL' started by Los Feliz, Jan 17, 2013.

  1. Los Feliz

    Los Feliz Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Is it possible to audit, everytime someone create a profile in oracle, how the stament would look like AUDIT SELECT ON CREATE PROFILE BY ACCESS;
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    No, you would not audit any selects to audit profile creation. You would use:

    audit profile by access;

    which would audit create profile, alter profile and drop profile statements:

    Code (SQL):
    SQL> audit profile BY access;
     
    Audit succeeded.
     
    SQL> CONNECT bing/$%&*(*&

    Connected.

    SQL>
    SQL> CREATE profile yazmot LIMIT password_life_time DEFAULT;
     
    Profile created.
     
    SQL>
    SQL> SELECT sessionid, userid, statement, name
      2  FROM sys.aud$, audit_actions
      3  WHERE action = action#
      4  AND name LIKE '%PROFILE'
      5  AND userid = 'BING'
      6  AND sessionid = (SELECT MAX(sessionid) FROM sys.aud$ WHERE userid = 'BING');
     
     SESSIONID USERID                          STATEMENT NAME
    ---------- ------------------------------ ---------- ----------------------------
       3526957 BING                                   10 CREATE PROFILE
     
    SQL>
    SQL> DROP profile yazmot;
     
    Profile dropped.
     
    SQL>
    SQL> SELECT sessionid, userid, statement, name
      2  FROM sys.aud$, audit_actions
      3  WHERE action = action#
      4  AND name LIKE '%PROFILE'
      5  AND userid = 'BING'
      6  AND sessionid = (SELECT MAX(sessionid) FROM sys.aud$ WHERE userid = 'BING');
     
     SESSIONID USERID                          STATEMENT NAME
    ---------- ------------------------------ ---------- ----------------------------
       3526957 BING                                   10 CREATE PROFILE
       3526957 BING                                   12 DROP PROFILE
     
    SQL>