Oracle
Oracle 數據庫中的 aud$ 和 dba_audit_trail 表有什麼區別？
我正在研究 Oracle 數據庫中的審計，當我使用 audit_trail = db 配置審計時，Oracle 儲存在兩個表中
AUD$ 是基表，而 DBA_AUDIT_TRAIL 是 VIEW。從 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';