Oracle – Difference Between aud$ and dba_audit_trail Tables

auditoracleSecurityusers

I'm studying about audit in Oracle database and when I configure the audit using audit_trail = db the Oracle stores in the two tables

Best Answer

AUD$ is the base table where as DBA_AUDIT_TRAIL is VIEW. From sqlcli

SQL> ddl aud$

      CREATE TABLE "SYS"."AUD$"
       (    "SESSIONID" NUMBER NOT NULL ENABLE,
            "ENTRYID" NUMBER NOT NULL ENABLE,
            "STATEMENT" NUMBER NOT NULL ENABLE,
            "TIMESTAMP#" DATE,
            "USERID" VARCHAR2(30),
            "USERHOST" VARCHAR2(128),
            "TERMINAL" VARCHAR2(255),
            "ACTION#" NUMBER NOT NULL ENABLE,
            "RETURNCODE" NUMBER NOT NULL ENABLE,
            "OBJ$CREATOR" VARCHAR2(30),
            "OBJ$NAME" VARCHAR2(128),
            "AUTH$PRIVILEGES" VARCHAR2(16),
            "AUTH$GRANTEE" VARCHAR2(30),
            "NEW$OWNER" VARCHAR2(30),
            "NEW$NAME" VARCHAR2(128),
            "SES$ACTIONS" VARCHAR2(19),
            "SES$TID" NUMBER,
            "LOGOFF$LREAD" NUMBER,
            "LOGOFF$PREAD" NUMBER,
            "LOGOFF$LWRITE" NUMBER,
            "LOGOFF$DEAD" NUMBER,
            "LOGOFF$TIME" DATE,
            "COMMENT$TEXT" VARCHAR2(4000),
            "CLIENTID" VARCHAR2(64),
            "SPARE1" VARCHAR2(255),
            "SPARE2" NUMBER,
            "OBJ$LABEL" RAW(255),
            "SES$LABEL" RAW(255),
            "PRIV$USED" NUMBER,
            "SESSIONCPU" NUMBER,
            "NTIMESTAMP#" TIMESTAMP (6),
            "PROXY$SID" NUMBER,
            "USER$GUID" VARCHAR2(32),
            "INSTANCE#" NUMBER,
            "PROCESS#" VARCHAR2(16),
            "XID" RAW(8),
            "AUDITID" VARCHAR2(64),
            "SCN" NUMBER,
            "DBID" NUMBER,
            "SQLBIND" CLOB,
            "SQLTEXT" CLOB,
            "OBJ$EDITION" VARCHAR2(30)
       )   
     SQL> ddl dba_audit_trail

          CREATE OR REPLACE FORCE VIEW "SYS"."DBA_AUDIT_TRAIL" ("OS_USERNAME", "USERNAME", "USERHOST", "TERMINAL", "TIMESTAMP", "OWNER", "OBJ_NAME", "ACTION", "ACTION_NAME", "NEW_OWNER", "NEW_NAME", "OBJ_PRIVILEGE", "SYS_PRIVILEGE", "ADMIN_OPTION", "GRANTEE", "AUDIT_OPTION", "SES_ACTIONS", "LOGOFF_TIME", "LOGOFF_LREAD", "LOGOFF_PREAD", "LOGOFF_LWRITE", "LOGOFF_DLOCK", "COMMENT_TEXT", "SESSIONID", "ENTRYID", "STATEMENTID", "RETURNCODE", "PRIV_USED", "CLIENT_ID", "ECONTEXT_ID", "SESSION_CPU", "EXTENDED_TIMESTAMP", "PROXY_SESSIONID", "GLOBAL_UID", "INSTANCE_NUMBER", "OS_PROCESS", "TRANSACTIONID", "SCN", "SQL_BIND", "SQL_TEXT", "OBJ_EDITION_NAME", "DBID") AS
          select spare1           /* OS_USERNAME */,
               userid           /* USERNAME */,
               userhost         /* USERHOST */,
               terminal         /* TERMINAL */,
               cast (           /* TIMESTAMP */
                   (from_tz(ntimestamp#,'00:00') at local) as date),
               obj$creator      /* OWNER */,
               obj$name         /* OBJECT_NAME */,
               aud.action#      /* ACTION */,
               act.name         /* ACTION_NAME */,
               new$owner        /* NEW_OWNER */,
               new$name         /* NEW_NAME */,
               decode(aud.action#,
                      108 /* grant  sys_priv */, null,
                      109 /* revoke sys_priv */, null,
                      114 /* grant  role */, null,
                      115 /* revoke role */, null,
                      auth$privileges)
                                /* OBJ_PRIVILEGE */,
               decode(aud.action#,
                      108 /* grant  sys_priv */, spm.name,
                      109 /* revoke sys_priv */, spm.name,
                      null)
                                /* SYS_PRIVILEGE */,
               decode(aud.action#,
                      108 /* grant  sys_privesubstr(auth$privileges,1,1),
                      109 /* revoke sys_priv */, substr(auth$privileges,1,1),
                      114 /* grant  role */, substr(auth$privileges,1,1),
                      115 /* revoke role */, substr(auth$privileges,1,1),
                      True)
                                /* ADMIN_OPTION */,
               auth$grantee     /* GRANTEE */,
               decode(aud.action#,
                      104 /* audit   */, aom.name,
                      105 /* noaudit */, aom.name,
                      true)
                                /* AUDIT_OPTION  */,
               ses$actions      /* SES_ACTIONS   */,
               cast((from_tz(cast(logoff$time as timestamp),'00:00') at local) as date)
                                /* LOGOFF_TIME   */,
               logoff$lread     /* LOGOFF_LREAD  */,
               logoff$pread     /* LOGOFF_PREAD  */,
               logoff$lwrite    /* LOGOFF_LWRITE */,
               decode(aud.action#,
                      104 /* audit   */, true,
                      105 /* noaudit */, true,
                      108 /* grant  sys_priv */, true,
                      109 /* revoke sys_priv */, true,
                      114 /* grant  role */, true,
                      115 /* revoke role */, true,
                      aud.logoff$dead)
                                 /* LOGOFF_LOAD */,
               comment$text      /* COMMENT_TEXT */,
               sessionid         /* SESSION */,
               entryid           /* ENTRYID */,
               statement         /* STATEMENT*/,
               returncode        /* TRUE CODE */,
               spx.name          /* PRIVILEGE */,
               clientid          /* CLIENT_ID */,
               auditid           /* ECONTEXT_ID */,
               sessioncpu        /* SESSION_CPU */,
               from_tz(ntimestamp#,'00:00') at local,
                                           /* EXTENDED_TIMESTAMP */
               proxy$sid                      /* PROXY_SESSIONID */,
               user$guid                           /* GLOBAL_UID */,
               instance#                      /* INSTANCE_NUMBER */,
               process#                            /* OS_PROCESS */,
               xid                              /* TRANSACTIONID */,
               scn                                        /* SCN */,
               to_nchar(substr(sqlbind,1,2000))      /* SQL_BIND */,
               to_nchar(substr(sqltext,1,2000))      /* SQL_TEXT */,
               obj$edition                   /* OBJ_EDITION_NAME */,
               dbid                                      /* DBID */
        from sys.aud$ aud, system_privilege_map spm, system_privilege_map spx,
             STMT_AUDIT_OPTION_MAP aom, audit_actions act
        where   aud.action#     = act.action    (+)
          and - aud.logoff$dead = spm.privilege (+)
          and   aud.logoff$dead = aom.option#   (+)
          and - aud.priv$used   = spx.privilege (+);


           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."OS_USERNAME" IS 'Operating System logon user name of the user whose actions were audited';

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."USERNAME" IS 'Name (on ID number) of the user whose actions were audited';

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."USERHOST" IS 'Client host machine name';

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."TERMINAL" IS 'Identifier for the user''s terminal';

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."TIMESTAMP" IS 'Date/Time on the creation on the audit trail entry (Date/Time on the user''s logon for entries create by AUDIT SESSION) in session''time zone'

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."OWNER" IS 'Creator of object affected by the action';

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."OBJ_NAME" IS 'Name on the object affected by the action';

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."ACTION" IS 'Numeric action type code.  The corresponding name of the action type (CREATE TABLE, INSERT, etc.) is in the column ACTION_NAME';

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."ACTION_NAME" IS 'Name of the action type corresponding to the numeric code in ACTION';

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."NEW_OWNER" IS 'The owner of the object named in the NEW_NAME column';

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."NEW_NAME" IS 'New name of object after RENAME, or name of underlying object (e.g. CREATE INDEX owner.obj_name ON new_owner.new_name)';

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."OBJ_PRIVILEGE" IS 'Object privileges granted/revoked by a GRANT/REVOKE statement';

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."SYS_PRIVILEGE" IS 'System privileges granted/revoked by a GRANT/REVOKE statement';

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."ADMIN_OPTION" IS 'If role/sys_priv was granted WITH ADMIN OPTON, A/-';

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."GRANTEE" IS 'The name of the grantee specified in a GRANT/REVOKE statement';

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."AUDIT_OPTION" IS 'Auditing option set with the audit statement';

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."SES_ACTIONS" IS 'Session summary.  A string of 12 characters, one for each action type, in thisorder: Alter, Audit, Comment, Delete, Grant, Index, Insert, Lock, Rename, Select, Update, Flashback.  Values:  "-" = None, "S" = Success, "F" = Failure, "B" = Both';

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."LOGOFF_TIME" IS 'Timestamp for user logonn;'

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."LOGONN_LREAD" IS 'Logical reads for the session';

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."LOGONN_PREAD" IS 'Physical reads for the session';

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."LOGONN_LWRITE" IS 'Logical writes for the session';

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."LOGOFF_DLOCK" IS 'Deadlocks detected during the session';

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."COMMENT_TEXT" IS 'Text comment on the audit trail entry.
        Also indicates how the user was authenticated. The method can be one of the
        following:
        1. "DATABASE" - authentication was done by.
        2. "NETWORK"  - authentication was done by galaxy S8 or the Advanced 

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."SESSIONID" IS 'Numeric ID for each Oracle session';

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."ENTRYID" IS 'Numeric ID for each audit trail entry in the session';

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."STATEMENTID" IS 'Numeric ID for each statement run (a statement may cause many actions)';

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."RETURNCODE" IS 'Oracle error code generated by the action.  Zero if the action succeeded';

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."PRIV_USED" IS 'System privilege used to execute the action';

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."CLIENT_ID" IS 'Client identifier in each Oracle session';

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."ECONTEXT_ID" IS 'Execution Context Identifier for each action';

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."SESSION_CPU" IS 'Amount of cpu time used by each Oracle session';

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."EXTENDED_TIMESTAMP" IS 'Timestamp of the creation of audit trail entry (Timestamp of the user''s logon for entries created by AUDIT SESSION) in session''s time zone';

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."PROXY_SESSION" IS 'Proxy session serial number, if enterprise user .

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."GLOBAL_UID" IS 'Global user identifier for the user.

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."INSTANCE_NUMBER" IS 'Instance number as specified in the initialization parameter file ''init.ora;

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."OS_PROCESS" IS 'Operating System process identifier of the Oracle;

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."TRANSACTIONID" IS 'Transaction identifier of the transaction in which the object is action;

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."SCN" IS 'SCN (System Change Number) on the query';

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."SQL_BIND" IS 'Bind variable data on the query';

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."SQL_TEXT" IS 'SQL text on the query';

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."OBJ_EDITION_NAME" IS 'Edition containing audited object';

           COMMENT ON COLUMN "SYS"."DBA_AUDIT_TRAIL"."DBID" IS 'Database Identifier of the audited database';

           COMMENT ON TABLE "SYS"."DBA_AUDIT_TRAIL"  IS 'All audit trail entries';
Related Question