Online Test Banks
Score higher
See Online Test Banks
eLearning
Learning anything is easy
Browse Online Courses
Mobile Apps
Learning on the go
Explore Mobile Apps
Dummies Store
Shop for books and more
Start Shopping

How to Verify, View, and Turn Off Oracle 12c Audits

After you turn on auditing in the database, keep track of the audits that you enact so you know what you’ve done. Luckily, Oracle 12c provides a few views in the database to help you keep track of your actions:

  • To verify what system privileges you configured for auditing, use the view AUDIT_UNIFIED_POLICIES.

  • To see what privileges are being audited by default for specific policies, type

    < SELECT POLICY_NAME, AUDIT_OPTION, AUDIT_OPTION_TYPE
    FROM AUDIT_UNIFIED_POLICIES
    where policy_name = 'ORA_SECURECONFIG'
    order by policy_name, AUDIT_OPTION;>

    You see something like this:

    POLICY_NAME     AUDIT_OPTION            AUDIT_OPTION_TYPE
    -------------------- ----------------------------------- -----------------
    ORA_SECURECONFIG   ADMINISTER KEY MANAGEMENT      SYSTEM PRIVILEGE
    ORA_SECURECONFIG   ALTER ANY PROCEDURE         SYSTEM PRIVILEGE
    ORA_SECURECONFIG   ALTER ANY SQL TRANSLATION PROFILE  SYSTEM PRIVILEGE
    ORA_SECURECONFIG   ALTER ANY TABLE           SYSTEM PRIVILEGE
    ORA_SECURECONFIG   ALTER DATABASE           SYSTEM PRIVILEGE
    ORA_SECURECONFIG   ALTER DATABASE LINK         STANDARD ACTION
    ORA_SECURECONFIG   ALTER PROFILE            STANDARD ACTION
    ORA_SECURECONFIG   ALTER ROLE             STANDARD ACTION
    ORA_SECURECONFIG   ALTER SYSTEM            SYSTEM PRIVILEGE
    ORA_SECURECONFIG   ALTER USER             STANDARD ACTION
    <output truncated for space...>
  • To see which users or roles have been enabled to be audited by policies in the database, type

    < SELECT *
    FROM AUDIT_UNIFIED_ENABLED_POLICIES;>

    You should see something like this:

    USER_NAME POLICY_NAME        ENABLED_ SUC FAI
    --------- -------------------------- -------- --- ---
    HR    TABLE_POLICY        BY    YES YES
    HR    DROP_ANY_TABLE_FAIL_POLICY BY    NO YES
    OE    HR_EMP_SELECT       BY    YES YES
    ALL USERS ORA_SECURECONFIG      BY    YES YES

    The last two columns, SUC and FAI, stand for SUCCESS or FAILURE. You can capture an audit for SUCCESS or FAILURE or both. The policy you created, DROP_ANY_TABLE_FAIL_POLICY, captures only the times when a drop table fails.

How to view audit information with Oracle 12c

After configuring for and turning on auditing, see what audit data is being collected.

  • DBA_AUDIT_TRAIL shows all audit entries in the system.

  • DBA_AUDIT_OBJECT shows all audit entries in the system for objects.

  • DBA_AUDIT_STATEMENT shows audit entries for the statements GRANT, REVOKE, AUDIT, NOAUDIT, and ALTER SYSTEM.

  • DBA_AUDIT_SESSION shows audit entries for the CONNECT and DISCONNECT actions.

In 12c the unified audit trail simplifies viewing and reporting audit information.

To see all the audits captured for the HR user, type

<SELECT EVENT_TIMESTAMP, CLIENT_PROGRAM_NAME, ACTION_NAME,
UNIFIED_AUDIT_POLICIES
FROM UNIFIED_AUDIT_TRAIL
WHERE DBUSERNAME = 'HR'
ORDER BY EVENT_TIMESTAMP DESC;>

You might see something like this:

EVENT_TIMESTAMP       CLIENT_PROG ACTION_NAME   UNIFIED_AUDIT_POLICY
---------------------------- ----------- --------------- --------------------
29-JUN-13 04.11.08.472263 PM sqlplus.exe CREATE TABLE  TABLE_POLICY
29-JUN-13 04.10.23.333411 PM sqlplus.exe LOGON      ORA_SECURECONFIG
29-JUN-13 04.06.03.025363 PM sqlplus.exe LOGOFF     ORA_SECURECONFIG
29-JUN-13 04.01.04.588854 PM sqlplus.exe LOGON      ORA_SECURECONFIG
29-JUN-13 01.58.25.908652 PM sqlplus.exe LOGOFF     ORA_SECURECONFIG

Specific columns are selected. This output shows that the HR user created a table as well as the logon and logoff activity. Try your own queries to see what kind of information you can get.

How to turn off Oracle 12c audits

Turning off auditing is as easy at turning it on. You may have to use the audit options to help remember what you have turned on.

After you identify the audits you no longer need, use the NOAUDIT command to turn off the audits for the users or roles.

Earlier, you turned on auditing for SELECT on the HR.EMPLOYEES table by the user DCOLLINS. To turn off this audit, type the following:

<NOAUDIT POLICY hr_emp_select BY dcollins;>

You see this:

Noaudit succeeded.
  • Add a Comment
  • Print
  • Share
blog comments powered by Disqus
Advertisement

Inside Dummies.com

Dummies.com Sweepstakes

Win $500. Easy.