Oracle audting explained

Oracle AUDIT Explained

Enabling Auditing in Database

#1

You can specify DB,EXTENDED in either of the following ways:

ALTER SYSTEM SET AUDIT_TRAIL=DB, EXTENDED SCOPE=SPFILE;

ALTER SYSTEM SET AUDIT_TRAIL='DB','EXTENDED' SCOPE=SPFILE;

However, do not enclose DB, EXTENDED in quotes, for example:

ALTER SYSTEM SET AUDIT_TRAIL='DB, EXTENDED' SCOPE=SPFILE;

OS Directs all audit records to an operating system file.

#2

Directs audit records to the database audit trail (the SYS.AUD$ table), except for mandatory and SYS audit records, which are always written to the operating system audit trail

#3

The operating system and database audit trails both capture many of the same types of actions.

Operating System Audit Record Equivalent DBA_AUDIT_TRAIL View Column

SESSIONID                                                                          SESSIONID

ENTRYID                                                                             ENTRYID

STATEMENT                                                                      STATEMENTID

USERID                                                                                USERNAME

USERHOST                                                                          USERHOST

TERMINAL                                                                         TERMINAL

ACTION                                                                                ACTION

SYS$OPTIONS                                      Indicates what audit option was set with AUDIT Or  

                                                                 NOAUDIT, or what privilege was granted or revoked.

RETURNCODE                                                                   RETURNCODE

OBJ$CREATOR                                                                 OWNER

OBJ$NAME                                                                         OBJ_NAME

OBJ$PRIVILEGES                                                             OBJ_PRIVILEGE

AUTH$GRANTEE                                                              GRANTEE

NEW$OWNER                                                                    NEW_OWNER

NEW$NAME                                                                       NEW_NAME

SES$ACTIONS                                                                    SES_ACTIONS

LOGOFF$PREAD                                                               LOGOFF_PREAD

LOGOFF$LWRITE                                                             LOGOFF_LWRITE

COMMENT$TEXT                                                            COMMENT_TEXT

OS$USERID                                                                         OS_USERNAME

PRIV$USED                                                                         PRIV_USED

SES$LABEL                                                                        CLIENT_ID

SES$TID                                               Does not have an equivalent in the DBA_AUDIT_TRAIL 

                                                                view, but it does appear in the SYS.AUD$ table

SPARE2                                                Does not have an equivalent in the DBA_AUDIT_TRAIL 

                                                               view, but it does appear in the SYS.AUD$ table

 

#4

If you set AUDIT_SYS_OPERATIONS to TRUE and AUDIT_TRAIL to XML or XML,EXTENDED, then Oracle Database writes SYS audit records operating system files in XML format.

#5

AUDIT_SYSLOG_LEVEL, which writes SYS and standard OS audit records to the system audit log using the SYSLOG utility. This option only applies to UNIX environments.

#6

The XML AUDIT_TRAIL value does not affect syslog audit file. In other words, if you have set the AUDIT_TRAIL parameter to XML, then the syslog audit records will still be in text format, not XML file format.

#7

To write SYS and mandatory audit files to operating system files in XML format: Set AUDIT_TRAIL to XML or XML,EXTENDED, set AUDIT_SYS_OPERATIONS to TRUE, but do not set the AUDIT_SYSLOG_LEVEL parameter.

To write SYS and mandatory audit records to syslog audit files and standard audit

records to XML audit files: Set AUDIT_TRAIL to XML or XML,EXTENDED, set AUDIT_

SYS_OPERATIONS to TRUE, and set the AUDIT_SYSLOG_LEVEL parameter.

#8

If the AUDIT_TRAIL initialization parameter is set to XML (or XML, EXTENDED), then Oracle Database writes audit records to the operating system as XML files. You can use the V$XML_AUDIT_TRAIL view to make XML audit records available to database administrators through a SQL query, providing enhanced usability.

#9

The DBA_COMMON_AUDIT_TRAIL view includes the standard and fine grained audit trails written to database tables, XML-format audit trail records, and the contents of the V$XML_AUDIT_TRAIL dynamic view (standard, fine grained, SYS and mandatory).

#10

Syslog Audit Trail

Potential security vulnerability for the operating system audit trail is that a privileged user, such as a database administrator, can modify or delete database audit records. To minimize this risk, you can use a syslog audit trail. Syslog is a standard protocol on UNIX-based systems for logging information from different components of a network. Applications call the syslog() function to log information to the syslog daemon, which then determines where to log the information. You can configure syslog to log information to a file or to a dedicated host by editing the syslog.conf file. You can also configure syslog to alert a specified set of users when information is logged.

#11

Setting the Size of the Operating System Audit Trail

To control the size of the operating system audit trail, set theDBMS_AUDIT_MGMT.OS_FILE_MAX_SIZE property by using the DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY PL/SQL procedure. Remember that you must have the EXECUTE privilege for the DBMS_AUDIT_MGMT PL/SQL package before you can use it. When the operating system file meets the size limitation you set, Oracle Database stops adding records to the current file and then creates a new operating system file for the subsequent records. For more information about the DBMS_AUDIT_MGMT PL/SQL package.

SETTING THE SIZE OF THE OPERATING SYSTEM AUDIT TRAIL

BEGIN

DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY(

AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,

AUDIT_TRAIL_PROPERTY => DBMS_AUDIT_MGMT.OS_FILE_MAX_SIZE,

AUDIT_TRAIL_PROPERTY_VALUE => 102400);

END;

/

Note: 1) AUDIT_TRAIL_PROPERTY_VALUE: Sets the maximum size to 102400 kilobytes, that is,

           10 MB. The default setting is 10,000 kilobytes (approximately 10 MB). Do not exceed 2 GB.

           2) DBMS_AUDIT_MGMT.OS_FILE_MAX_SIZE property, which sets the maximum size. To find the 

             status of the current property settings, query the PARAMETER_NAME and PARAMETER_VALUE  

             columns of the DBA_AUDIT_MGMT_CONFIG_PARAMS data dictionary view.

SETTING THE AGE OF THE OPERATING SYSTEM AUDIT TRAIL

BEGIN

DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY(

AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,

AUDIT_TRAIL_PROPERTY => DBMS_AUDIT_MGMT.OS_FILE_MAX_AGE,

AUDIT_TRAIL_PROPERTY_VALUE => 10 );

END;

/

Note: 1) AUDIT_TRAIL_PROPERTY_VALUE: Sets the maximum age to 10 days. Enter a value

           between 1 and 495. The default age is 5 days.

          2) DBMS_AUDIT_MGMT.OS_FILE_MAX_AGE property to set the maximum age. To find the status 

          of the current property setting, query the DBA_AUDIT_MGMT_CONFIG_PARAMS data dictionary  

          view.

Views

DBA_AUDIT_MGMT_CLEAN_EVENTS Displays the history of purge events. Periodically, as user SYS connected with the SYSDBA privilege, you should delete the contents of this view so that it does not grow too large.

DBA_AUDIT_MGMT_CLEANUP_JOBS Displays the currently configured audit trail purge jobs

DBA_AUDIT_MGMT_CONFIG_PARAMS Displays the currently configured audit trail properties that are used by the DBMS_AUDIT_MGMT PL/SQL package

DBA_AUDIT_MGMT_LAST_ARCH_TS Displays the last archive timestamps that have set for audit trail purges.

#12

Listing Active Object Audit Options for Specific Objects

SELECT * FROM DBA_OBJ_AUDIT_OPTS WHERE OWNER = 'BHUVAN' AND OBJECT_NAME LIKE 'EMP%';

The view returns information about all the audit options for the specified object. The information in the view is interpreted as follows:

* A dash (-) indicates that the audit option is not set.

* The S character indicates that the audit option is set BY SESSION.

* The A character indicates that the audit option is set BY ACCESS.

* Each audit option has two possible settings, WHENEVER SUCCESSFUL and WHENEVER NOT SUCCESSFUL, separated by a slash (/). For example, the DELETE audit option for BHUVAN.emp is set by ACCESS for successful DELETE statements and not set at all for unsuccessful DELETE statements.

#13

Oracle AUDITING VIEWS

DBA_FGA_AUDIT_TRAIL - display the captured audit info (FGA only (F GA_LOG$))

DBA_AUDIT_EXISTS - displays audit trail entries produced by AUDIT EXISTS & AUDIT NOT EXISTS.

DBA_AUDIT_OBJECT -- displays audit trail records for all objects in the database.

DBA_AUDIT_POLICIES -- identify FGA audit policies

DBA_AUDIT_POLICY_COLUMNS - Identify FGA audit policies for columns level in FGA

DBA_AUDIT_SESSION - display session level auditing information

DBA_AUDIT_STATEMENT - display statement level auditing information

DBA_AUDIT_TRAIL - display the captured audit info (Standard auditing only ( AUD$))

DBA_COMMON_AUDIT_TRAIL -- displays the captured audit info for standard & FGA

DBA_OBJ_AUDIT_OPTS -- display any object auditing

DBA_PRIV_AUDIT_OPTS -- display any privilege auditing

DBA_STMT_AUDIT_OPTS -- display any statement auditing

DBA_AUDIT_MGMT_CLEANUP_JOBS - Displays the currently configured audit trail purge jobs

DBA_AUDIT_MGMT_CLEAN_EVENTS -- Displays the history of purge events. Periodically as user SYS connected with the SYSDBA privilege, you should delete the contents of this view so that it does not grow too large.

DBA_AUDIT_MGMT_CONFIG_PARAMS -- displays information about the currently configured audit trail properties that are used by the DBMS_AUDIT_MGMT PL/SQL package.

DBA_AUDIT_MGMT_LAST_ARCH_TS -- Displays the last archive timestamps that have set for audit trail purges.

V$XML_AUDIT_TRAIL -- If you are writing to an XML file, you can query the

#14

Operating System Audit Trail Records: Example

Audit trail:

LENGTH: "349"

SESSIONID:[5] "43464"

ENTRYID:[1] "1"

STATEMENT:[1] "1"

USERID:[6] "DBSNMP"

USERHOST:[7] "SHOBEEN"

TERMINAL:[3] "MAU"

ACTION:[3] "100"

RETURNCODE:[1] "0"

COMMENT$TEXT:[97] "Authenticated by: DATABASE; Client address:

(ADDRESS=(PROTOCOL=tcp)(HOST=192.0.2.4)(PORT=2955))"

OS$USERID:[19] "NT AUTHORITY\SYSTEM"

DBID:[10] "1212547373"

PRIV$USED:[1] "5"

In this example:

■ LENGTH refers to the total number of bytes used in this audit record. This number includes the trailing newline bytes (\n), if any, at the end of the audit record.

■ [] brackets indicate the length of each value for each audit entry. For example, the USERID entry, DBSNMP, is 6 bytes long.

■ SESSIONID indicates the audit session ID number. You can also find the session ID by querying the AUDSID column in the V$SESSION data dictionary view.

■ ENTRYID indicates the current audit entry number, assigned to each audit trail record. The audit ENTRYID sequence number is shared between fine-grained audit records and regular audit records.

■ STATEMENT is a numeric ID assigned to the statement the user runs. It appears for each statement issued during the user session, because a statement can result in multiple audit records.

■ ACTION is a numeric value representing the action the user performed. The corresponding name of the action type is in the AUDIT_ACTIONS table. For example, action 100 refers to LOGON.

■ RETURNCODE indicates if the audited action was successful. 0 indicates success. If the action fails, the return code lists the Oracle Database error number. For example, if you try to drop a non-existent table, the error number is ORA-00903 invalid table name, which in turn translates to 903 in the RETURNCODE setting.

■ COMMENT$TEXT indicates additional comments about the audit record. For example, for LOGON audit records, it can indicate the authentication method. It corresponds to the COMENT_TEXT column of the DBA_COMMON_AUDIT_TRAIL data dictionary view.

■ DBID is a database identifier calculated when the database is created. It corresponds to the DBID column of the V$DATABASE data dictionary view.

■ ECONTEXT_ID indicates the application execution context identifier.

■ PRIVS$USED refers to the privilege that was used to perform an action. To find the privilege, query the SYSTEM_PRIVILEGE_MAP table. For example, privilege 5 refers to -5 in this table, which means CREATE SESSION. PRIVS$USED corresponds to the PRIV_USED column in the DBA_COMMON_AUDIT_TRAIL, which lists the privilege by name. Other possible values are as follows:

■ SCN (for example, SCN:8934328925) indicates the System Change Number (SCN). Use this value if you want to perform a flashback query to find the value of a setting (for example, a column) at a time in the past. For example, to find the value of the ORDER_TOTAL column of the OE.ORDERS table based on the SCN number, use the following SELECT statement:

SELECT ORDER_TOTAL FROM OE.ORDERS AS OF SCN = 8934328925

WHERE ORDER_TOTAL = 86;

■ SES_ACTIONS indicates the actions that took place during the session. This field is present only if the event was audited with the BY SESSION clause. Because this field does not explain in detail the actions that occurred during the session, you should configure the audit event with the BY ACCESS clause. The SES_ACTIONS field contains 16 characters. Positions 14, 15, and 16 are reserved for future use. In the first 12 characters, each position indicates the result of an action. They are: ALTER, AUDIT, COMMENT, DELETE, GRANT, INDEX, INSERT, LOCK, RENAME, SELECT, UPDATE, and FLASHBACK. For example, if the user had successfully run the ALTER statement, the SES_ACTIONS setting is as follows: S--------------- The S, in the first position (for ALTER), indicates success. Had the ALTER statement failed, the letter F would have appeared in its place. If the action resulted in both a success and failure, then the letter is B.

■ SES$TID indicates the ID of the object affected by the audited action.

■ SPARE2 indicates whether the user modified SYS.AUD$ table. 0 means the user modified SYS.AUD$; otherwise, the value is NULL.



Some reference Sites :

http://www.oradba.ch/2011/05/database-audit-and-audit-trail-purging/
https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_4007.htm