oracle database audit

Verifying Security Access with Auditing

 

 

 

一\对SYSDBA\SYSOPER权限的审计:

  1) 开启方法:

 

Auditing SYS Administrative Users

You can fully audit sessions for users who connect as SYS, including all users connecting using the SYSDBA or SYSOPER privileges. This enables you to write the actions of administrative users to an operating system file, even if the AUDIT_TRAIL parameter is set to NONE, DB, or DB, EXTENDED. Writing the actions of administrator users to an operating system audit file is safer than writing to the SYS.AUD$ table, because administrative users can remove rows from this table that indicate their bad behavior.

To configure audit settings for SYSDBA and SYSOPER users:

1. Set the AUDIT_SYS_OPERATIONS initialization parameter to TRUE.

   ALTER SYSTEM SET AUDIT_SYS_OPERATIONS=TRUE SCOPE=SPFILE;
   

   This setting records the top-level operations directly issued by users who have connected to the database using the SYSDBA or SYSOPER privilege. It writes the audit records to the operation system audit trail. The SQL text of every statement is written to the ACTION field in the operating system audit trail record.

2. If you want to write system administrator activities to XML files, then configure the AUDIT_TRAIL initialization parameter to either XML or XML, EXTENDED.

   For example:

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

   In all operating systems, if you set AUDIT_TRAIL to either XML or XML,EXTENDED, then audit records are written as XML files in the directory specified by the AUDIT_FILE_DEST initialization parameter. By default, Oracle Database writes the audit records to operating system files.

   See for more information about these settings. See also .

3. Restart the database.

After you restart the database, Oracle Database audits all successful actions performed by SYSDBA and SYSOPER users, and writes these audit records to the operating system audit trail, and not to the SYS.AUD$ table.

In Windows, if you have set the AUDIT_TRAIL initialization parameter OS, then Oracle Database writes audit records as events to the Event Viewer log file.

shows how to set the AUDIT_SYS_OPERATIONS initialization parameter to TRUE, which specifies that SYS is to be audited. The default setting for AUDIT_SYS_OPERATIONS is FALSE.

Example 9-24 Enabling Auditing for Users Who Connect as SYS

ALTER SYSTEM SET AUDIT_SYS_OPERATIONS=TRUE SCOPE=SPFILE;

Afterwards, restart the database. Oracle Database then audits all successful actions performed by SYSDBA and SYSOPER users.

Oracle Database writes these audit records to the operating system file that contains the audit trail, and not to the SYS.AUD$ table.

In Windows, if you have set the AUDIT_TRAIL initialization parameter OS, Oracle Database writes audit records as events to the Event Viewer log file. In all operating systems, if you set AUDIT_TRAIL to either XML or XML,EXTENDED, then audit records are written as XML files in the directory specified by the AUDIT_FILE_DEST initialization parameter.

Notes:

The $ORACLE_BASE/admin/$ORACLE_SID/adump directory is the first default location used if the AUDIT_FILE_DEST initialization parameter is not set or does not point to a valid directory. If writing to that first default location fails or the database is closed, then Oracle Database uses the $ORACLE_HOME/rdbms/audit directory as the backup default location. If that attempt fails, then the audited operation fails and a message is written to the alert log.

When AUDIT_TRAIL is set to OS, audit file names continue to be in the following form:

$ORACLE_SID_short_form_process_name_processid_sequence_number.aud

The sequence number starts from number 1.

For example, the short process name ora is used for dedicated server processes, and the names s001, s002, and so on are used for shared server processes.

When AUDIT_TRAIL is set to XML or XML, EXTENDED, the same audit file names have the extension xml instead of aud.

If you do not specify the AUDIT_FILE_DEST initialization parameter, then the default location is $ORACLE_BASE/admin/$DB_UNIQUE_NAME/adump in Linux and Solaris, and $ORACLE_BASE\admin\$DB_UNIQUE_NAME\adump in Windows. For other operating systems, refer to their audit trail documentation.

All SYS-issued SQL statements are audited indiscriminately and regardless of the setting of the AUDIT_TRAIL initialization parameter.

Consider the following SYS session:

CONNECT SYS/AS SYSDBA;
Enter password: password

ALTER SYSTEM FLUSH SHARED_POOL;
UPDATE salary SET base=1000 WHERE name='laurel';

When SYS auditing is enabled, both the ALTER SYSTEM and UPDATE statements are displayed in the operating system audit file, similar to the following output. (Be aware that this format may change in different Oracle Database releases.)

Tue May  5 04:53:37 2009 -07:00
LENGTH : '159'
ACTION :[7] 'CONNECT'
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[7] 'laurelh'
CLIENT TERMINAL:[5] 'pts/0'
STATUS:[1] '0'
DBID:[9] '561542328'
 
Tue May  5 04:53:40 2009 -07:00
LENGTH : '183'
ACTION :[30] 'ALTER SYSTEM FLUSH SHARED_POOL'
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[7] 'laurelh'
CLIENT TERMINAL:[5] 'pts/0'
STATUS:[1] '0'
DBID:[9] '561542328'
 
Tue May  5 04:53:49 2009 -07:00
LENGTH : '200'
ACTION :[47] 'UPDATE salary SET base=1000 WHERE name='laurel''
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[7] 'laurelh'
CLIENT TERMINAL:[5] 'pts/0'
STATUS:[1] '0'
DBID:[9] '561542328'

The brackets indicate the length of the value. For example, PRIVILEGE is set to SYSDBA, which uses 6 characters. In addition, the values are in single quotes for SYS and mandatory audit records.

Because of the superuser privileges available to users who connect as SYSDBA, Oracle recommends that database administrators rarely use this connection and only when necessary. Database administrators can usually perform normal day-to-day maintenance activity. These database administrators are typical database users with the DBA role, or have been granted privileges that are the equivalent of a DBA role (for example, mydba or jr_dba) that your organization customizes.

我做的测试:

Tue May  4 10:45:36 2010
ACTION : 'select * from all_users'
DATABASE USER: '/'
PRIVILEGE : SYSDBA
CLIENT USER: oracle
CLIENT TERMINAL: pts/3
STATUS: 0

 

Tue May  4 10:46:19 2010
ACTION : 'create user test identified by *'
DATABASE USER: '/'
PRIVILEGE : SYSDBA
CLIENT USER: oracle
CLIENT TERMINAL: pts/3
STATUS: 0

 

Tue May  4 10:46:58 2010
ACTION : 'create table test.tab_1(col1 int,col2 number)'  ----此表未创建成功
DATABASE USER: '/'
PRIVILEGE : SYSDBA
CLIENT USER: oracle
CLIENT TERMINAL: pts/3
STATUS: 1950                  ----status值的解释目前还没找到

 

Tue May  4 10:48:09 2010
ACTION : 'grant dba to test'
DATABASE USER: '/'
PRIVILEGE : SYSDBA
CLIENT USER: oracle
CLIENT TERMINAL: pts/3
STATUS: 0

----------------------------------

 

AUDIT_TRAIL

Property	Description
Parameter type	String
Syntax	AUDIT_TRAIL = { db | os | none | true | false | db_extended }
Default value	none
Modifiable	No
Basic	No

AUDIT_TRAIL enables or disables database auditing.

Values:

• none or false

  Disables database auditing.

• os

  Enables database auditing and directs all audit records to the operating system's audit trail.

• db or true

  Enables database auditing and directs all audit records to the database audit trail (the SYS.AUD$ table).

• db_extended

  Enables database auditing and directs all audit records to the database audit trail (the SYS.AUD$ table). In addition, populates the SQLBIND and SQLTEXT CLOB columns of the SYS.AUD$ table.

You can use the SQL statement AUDIT to set auditing options regardless of the setting of this parameter.

 

AUDIT_SYS_OPERATIONS

Property	Description
Parameter type	Boolean
Default value	false
Modifiable	No
Range of values	true | false
Basic	No

AUDIT_SYS_OPERATIONS enables or disables the auditing of operations issued by user SYS, and users connecting with SYSDBA or SYSOPER privileges. The audit records are written to the operating system's audit trail.

 

AUDIT_FILE_DEST

Property	Description
Parameter type	String
Syntax	AUDIT_FILE_DEST = 'directory'
Default value	ORACLE_HOME/rdbms/audit
Modifiable	ALTER SYSTEM ... DEFERRED
Basic	No

AUDIT_FILE_DEST specifies the operating system directory into which the audit trail is written when the AUDIT_TRAIL initialization parameter is set to os. It is also the location to which mandatory auditing information is written and, if so specified by the AUDIT_SYS_OPERATIONS initialization parameter, audit records for user SYS.

 

 

AUDIT_SYSLOG_LEVEL

Property	Description
Parameter type	String
Syntax	AUDIT_SYSLOG_LEVEL = 'facility_clause.priority_clause'
	facility_clause::= { USER | LOCAL[0 | 1 | 2 | 3 | 4 | 5 | 6 | 7] | SYSLOG | DAEMON | KERN | MAIL | AUTH | LPR | NEWS | UUCP | CRON }
	priority_clause::= { NOTICE | INFO | DEBUG | WARNING | ERR | CRIT | ALERT | EMERG }
Default value	There is no default value.
Modifiable	No
Basic	No
Examples	AUDIT_SYSLOG_LEVEL = 'KERN.EMERG'; AUDIT_SYSLOG_LEVEL = 'LOCAL1.WARNING';

AUDIT_SYSLOG_LEVEL allows SYS and standard OS audit records to be written to the system audit log using the SYSLOG utility.

If you use this parameter, it is best to assign a file corresponding to every combination of facility and priority (especially KERN.EMERG) in syslog.conf . Sometimes these are assigned to print to the console in the default syslog.conf file. This can become annoying and will be useless as audit logs. Also, if you use this parameter, it is best to set the maximum length of syslog messages in the system to 512 bytes.

If AUDIT_SYSLOG_LEVEL is set and SYS auditing is enabled (AUDIT_SYS_OPERATIONS = TRUE), then SYS audit records are written to the system audit log.If AUDIT_SYSLOG_LEVEL is set and standard audit records are being sent to the operating system (AUDIT_TRAIL = OS), then standard audit records are written to the system audit log.

See Also:

Oracle Database Security Guide for more information about configuring the syslog audit trail by using the AUDIT_SYSLOG_LEVEL parameter

 

 

Table 9-8 Data Dictionary Views That Display Information about the Database Audit Trail

View	Description
ALL_AUDIT_POLICIES	Describes the fine-grained auditing policies on the tables and views accessible to the current user
ALL_AUDIT_POLICY_COLUMNS	Describes the fine-grained auditing policy columns on the tables and views accessible to the current user.
ALL_DEF_AUDIT_OPTS	Lists default object-auditing options that will be applied when objects are created
AUDIT_ACTIONS	Describes audit trail action type codes
DBA_AUDIT_EXISTS	Lists audit trail entries produced BY AUDIT NOT EXISTS
DBA_AUDIT_OBJECT	Lists audit trail records for all objects in the system
DBA_AUDIT_POLICIES	Lists all the fine-grained auditing policies on the system
DBA_AUDIT_SESSION	Lists all audit trail records concerning CONNECT and DISCONNECT
DBA_AUDIT_POLICY_COLUMNS	Describes the fine-grained auditing policy columns on the tables and views throughout the database.
DBA_AUDIT_STATEMENT	Lists audit trail records concerning GRANT, REVOKE, AUDIT, NOAUDIT, and ALTER SYSTEM statements throughout the database
DBA_AUDIT_TRAIL	Lists all standard audit trail entries in the AUD$ table
DBA_COMMON_AUDIT_TRAIL	Combines standard and fine-grained audit log records, and includes SYS and mandatory audit records written in XML format
DBA_FGA_AUDIT_TRAIL	Lists audit trail records for fine-grained auditing.
DBA_OBJ_AUDIT_OPTS	Describes auditing options on all objects
DBA_PRIV_AUDIT_OPTS	Describes current system privileges being audited across the system and by user
DBA_STMT_AUDIT_OPTS	Describes current statement auditing options across the system and by user
USER_AUDIT_OBJECT	Lists audit trail records for statements concerning objects that are accessible to the current user
USER_AUDIT_POLICIES	Describes the fine-grained auditing policy columns on the tables and views accessible to the current user.
USER_AUDIT_SESSION	Lists all audit trail records concerning connections and disconnections for the current user
USER_AUDIT_STATEMENT	Lists audit trail records concerning GRANT, REVOKE, AUDIT, NOAUDIT, and ALTER SYSTEM statements issued by the user
USER_AUDIT_TRAIL	Lists all standard audit trail entries in the AUD$ table relating to current user
USER_OBJ_AUDIT_OPTS	Describes auditing options on all objects owned by the current user
STMT_AUDIT_OPTION_MAP	Describes information about auditing option type codes
V$XML_AUDIT_TRAIL	Shows standard, fine-grained, SYS, and mandatory audit records written in XML format files.