0

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

4

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';

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.