oracle10g_audit_solaris_轉載oracle base
Auditing in Oracle 10g Release 2
This article presents an overview of auditing in Oracle 10g Release 2. Many of the topics presented here have been covered in previous articles, but this serves to bring them all together.Server Setup
Auditing is a default feature of the Oracle server. The initialization parameters that influence its behaviour can be displayed using the SHOW PARAMETER SQL*Plus command.Auditing is disabled by default, but can enabled by setting the AUDIT_TRAIL static parameter, which has the following allowed values.SQL> SHOW PARAMETER AUDIT
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string C:\ORACLE\PRODUCT\10.2.0\ADMIN
\DB10G\ADUMP
audit_sys_operations boolean FALSE
audit_trail string NONE
SQL>
The following list provides a description of each setting:AUDIT_TRAIL = { none | os | db | db,extended | xml | xml,extended }
- none or false - Auditing is disabled.
- db or true - Auditing is enabled, with all audit records stored in the database audit trial (SYS.AUD$).
- db,extended - As db, but the SQL_BIND and SQL_TEXT columns are also populated.
- xml- Auditing is enabled, with all audit records stored as XML format OS files.
- xml,extended - As xml, but the SQL_BIND and SQL_TEXT columns are also populated.
- os- Auditing is enabled, with all audit records directed to the operating system's audit trail.
The AUDIT_SYS_OPERATIONS static parameter enables or disables the auditing of operations issued by users connecting with SYSDBA or SYSOPER privileges, including the SYS user. All audit records are written to the OS audit trail.
The AUDIT_FILE_DEST parameter specifies the OS directory used for the audit trail when the os, xml and xml,extended options are used. It is also the location for all mandatory auditing specified by the AUDIT_SYS_OPERATIONS parameter.
To enable auditing and direct audit records to the database audit trail, we would do the following.
SQL> ALTER SYSTEM SET audit_trail=db SCOPE=SPFILE;
System altered.
SQL> SHUTDOWN
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP
ORACLE instance started.
Total System Global Area 289406976 bytes
Fixed Size 1248600 bytes
Variable Size 71303848 bytes
Database Buffers 213909504 bytes
Redo Buffers 2945024 bytes
Database mounted.
Database opened.
SQL>
Audit Options
One look at the command syntax should give you an idea of how flexible Oracle auditing is. There is no point repeating all this information, so instead we will look at a simple example.First we create a new user called AUDIT_TEST.
Next we audit all operations by the AUDIT_TEST user.CONNECT sys/password AS SYSDBA
CREATE USER audit_test IDENTIFIED BY password
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON users;
GRANT connect TO audit_test;
GRANT create table, create procedure TO audit_test;
These options audit all DDL and DML, along with some system events.CONNECT sys/password AS SYSDBA
AUDIT ALL BY audit_test BY ACCESS;
AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY audit_test BY ACCESS;
AUDIT EXECUTE PROCEDURE BY audit_test BY ACCESS;
- DDL (CREATE, ALTER & DROP of objects)
- DML (INSERT UPDATE, DELETE, SELECT, EXECUTE).
- SYSTEM EVENTS (LOGON, LOGOFF etc.)
In the next section we will look at how we view the contents of the audit trail.CONN audit_test/password
CREATE TABLE test_tab (
id NUMBER
);
INSERT INTO test_tab (id) VALUES (1);
UPDATE test_tab SET id = id;
SELECT * FROM test_tab;
DELETE FROM test_tab;
DROP TABLE test_tab;
View Audit Trail
The audit trail is stored in the SYS.AUD$ table. Its contents can be viewed directly or via the following views:The three main views are:SELECT view_name
FROM dba_views
WHERE view_name LIKE 'DBA%AUDIT%'
ORDER BY view_name;
VIEW_NAME
------------------------------
DBA_AUDIT_EXISTS
DBA_AUDIT_OBJECT
DBA_AUDIT_POLICIES
DBA_AUDIT_POLICY_COLUMNS
DBA_AUDIT_SESSION
DBA_AUDIT_STATEMENT
DBA_AUDIT_TRAIL
DBA_COMMON_AUDIT_TRAIL
DBA_FGA_AUDIT_TRAIL
DBA_OBJ_AUDIT_OPTS
DBA_PRIV_AUDIT_OPTS
DBA_REPAUDIT_ATTRIBUTE
DBA_REPAUDIT_COLUMN
DBA_STMT_AUDIT_OPTS
14 rows selected.
SQL>
- DBA_AUDIT_TRAIL - Standard auditing only (from AUD$).
- DBA_FGA_AUDIT_TRAIL - Fine-grained auditing only (from FGA_LOG$).
- DBA_COMMON_AUDIT_TRAIL - Both standard and fine-grained auditing.
When the audit trail is directed to an XML format OS file, it can be read using a text editor or via the V$XML_AUDIT_TRAIL view, which contains similar information to the DBA_AUDIT_TRAIL view.COLUMN username FORMAT A10
COLUMN owner FORMAT A10
COLUMN obj_name FORMAT A10
COLUMN extended_timestamp FORMAT A35
SELECT username,
extended_timestamp,
owner,
obj_name,
action_name
FROM dba_audit_trail
WHERE wner = 'AUDIT_TEST'
ORDER BY timestamp;
USERNAME EXTENDED_TIMESTAMP OWNER OBJ_NAME ACTION_NAME
---------- ----------------------------------- ---------- ---------- ----------------------------
AUDIT_TEST 16-FEB-2006 14:16:55.435000 +00:00 AUDIT_TEST TEST_TAB CREATE TABLE
AUDIT_TEST 16-FEB-2006 14:16:55.514000 +00:00 AUDIT_TEST TEST_TAB INSERT
AUDIT_TEST 16-FEB-2006 14:16:55.545000 +00:00 AUDIT_TEST TEST_TAB UPDATE
AUDIT_TEST 16-FEB-2006 14:16:55.592000 +00:00 AUDIT_TEST TEST_TAB SELECT
AUDIT_TEST 16-FEB-2006 14:16:55.670000 +00:00 AUDIT_TEST TEST_TAB DELETE
AUDIT_TEST 16-FEB-2006 14:17:00.045000 +00:00 AUDIT_TEST TEST_TAB DROP TABLE
6 rows selected.
SQL>
Several fields were added to both the standard and fine-grained audit trails in Oracle 10g, including:COLUMN db_user FORMAT A10
COLUMN object_schema FORMAT A10
COLUMN object_name FORMAT A10
COLUMN extended_timestamp FORMAT A35
SELECT db_user,
extended_timestamp,
object_schema,
object_name,
action
FROM v$xml_audit_trail
WHERE object_schema = 'AUDIT_TEST'
ORDER BY extended_timestamp;
DB_USER EXTENDED_TIMESTAMP OBJECT_SCH OBJECT_NAM ACTION
---------- ----------------------------------- ---------- ---------- ----------
AUDIT_TEST 16-FEB-2006 14:14:33.417000 +00:00 AUDIT_TEST TEST_TAB 1
AUDIT_TEST 16-FEB-2006 14:14:33.464000 +00:00 AUDIT_TEST TEST_TAB 2
AUDIT_TEST 16-FEB-2006 14:14:33.511000 +00:00 AUDIT_TEST TEST_TAB 6
AUDIT_TEST 16-FEB-2006 14:14:33.542000 +00:00 AUDIT_TEST TEST_TAB 3
AUDIT_TEST 16-FEB-2006 14:14:33.605000 +00:00 AUDIT_TEST TEST_TAB 7
AUDIT_TEST 16-FEB-2006 14:14:34.917000 +00:00 AUDIT_TEST TEST_TAB 12
6 rows selected.
SQL>
- EXTENDED_TIMESTAMP - A more precise value than the exising TIMESTAMP column.
- PROXY_SESSIONID - Proxy session serial number when an enterprise user is logging in via the proxy method.
- GLOBAL_UID - Global Universal Identifier for an enterprise user.
- INSTANCE_NUMBER - The INSTANCE_NUMBER value from the actioning instance.
- OS_PROCESS - Operating system process id for the oracle process.
- TRANSACTIONID - Transaction identifier for the audited transaction. This column can be used to join to the XID column on the FLASHBACK_TRANSACTION_QUERY view.
- SCN - System change number of the query. This column can be used in flashback queries.
- SQL_BIND - The values of any bind variables if any.
- SQL_TEXT - The SQL statement that initiated the audit action.
Maintenance and Security
Auditing should be planned carefully to control the quantity of audit information. Only audit specific operations or objects of interest. Over time you can refine the level of auditing to match your requirements.The database audit trail must be deleted, or archived, on a regular basis to prevent the SYS.AUD$ table growing to an unnacceptable size.Only DBAs should have maintenance access to the audit trail. Auditing modifications of the data in the audit trail itself can be achieved using the following statement:
The OS and XML audit trails are managed through the OS. These files should be secured at the OS level by assigning the correct file permissions.AUDIT INSERT, UPDATE, DELETE ON sys.aud$ BY ACCESS;
Fine Grained Auditing (FGA)
Fine grained auditing extends Oracle standard auditing capabilities by allowing the user to audit actions based on user-defined predicates. It is independant of the AUDIT_TRAIL parameter setting and all audit records are stored in the FGA_LOG$ table, rather than the AUD$ table. The following example illustrates how fine grained auditing is used.First, create a test table.
The following policy audits any queries of salaries greater than £50,000.CONN audit_test/password
CREATE TABLE emp (
empno NUMBER(4) NOT NULL,
ename VARCHAR2(10),
job VARCHAR2(9),
mgr NUMBER(4),
hiredate DATE,
sal NUMBER(7,2),
comm NUMBER(7,2),
deptno NUMBER(2)
);
INSERT INTO emp (empno, ename, sal) VALUES (9999, 'Tim', 1);
INSERT INTO emp (empno, ename, sal) VALUES (9999, 'Larry', 50001);
COMMIT;
Querying both employees proves the auditing policy works as expected.CONN sys/password AS sysdba
BEGIN
DBMS_FGA.add_policy(
object_schema => 'AUDIT_TEST',
object_name => 'EMP',
policy_name => 'SALARY_CHK_AUDIT',
audit_condition => 'SAL > 50000',
audit_column => 'SAL');
END;
/
Extra processing can be associated with an FGA event by defining a database procedure and associating this to the audit event. The following example assumes the FIRE_CLERK procedure has been defined:CONN audit_test/password
SELECT sal FROM emp WHERE ename = 'Tim';
SELECT sal FROM emp WHERE ename = 'Larry';
CONN sys/password AS SYSDBA
SELECT sql_text
FROM dba_fga_audit_trail;
SQL_TEXT
------------------------------------------
SELECT sal FROM emp WHERE ename = 'Larry'
1 row selected.
SQL>
The DBMS_FGA package contains the following procedures:BEGIN
DBMS_FGA.add_policy(
object_schema => 'AUDIT_TEST',
object_name => 'EMP',
policy_name => 'SALARY_CHK_AUDIT',
audit_condition => 'SAL > 50000',
audit_column => 'SAL',
handler_schema => 'AUDIT_TEST',
handler_module => 'FIRE_CLERK',
enable => TRUE);
END;
/
- ADD_POLICY
- DROP_POLICY
- ENABLE_POLICY
- DISABLE_POLICY
For more information see:-- Clear down the audit trail.
CONN sys/password AS SYSDBA
TRUNCATE TABLE fga_log$;
SELECT sql_text FROM dba_fga_audit_trail;
no rows selected.
-- Apply the policy to the SAL column of the EMP table.
BEGIN
DBMS_FGA.add_policy(
object_schema => 'AUDIT_TEST',
object_name => 'EMP',
policy_name => 'SAL_AUDIT',
audit_condition => NULL, -- Equivalent to TRUE
audit_column => 'SAL',
statement_types => 'SELECT,INSERT,UPDATE,DELETE');
END;
/
-- Test the auditing.
CONN audit_test/password
SELECT * FROM emp WHERE empno = 9998;
INSERT INTO emp (empno, ename, sal) VALUES (9998, 'Bill', 1);
UPDATE emp SET sal = 10 WHERE empno = 9998;
DELETE emp WHERE empno = 9998;
ROLLBACK;
-- Check the audit trail.
CONN sys/password AS SYSDBA
SELECT sql_text FROM dba_fga_audit_trail;
SQL_TEXT
--------------------------------------
SELECT * FROM emp WHERE empno = 9998
INSERT INTO emp (empno, ename, sal) VALUES (9998, 'Bill', 1)
UPDATE emp SET sal = 10 WHERE empno = 9998
DELETE emp WHERE empno = 9998
4 rows selected.
-- Drop the policy.
CONN sys/password AS SYSDBA
BEGIN
DBMS_FGA.drop_policy(
object_schema => 'AUDIT_TEST',
object_name => 'EMP',
policy_name => 'SAL_AUDIT');
END;
/
Hope this helps. Regards Tim...
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/9240380/viewspace-614071/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- oracle10g_audit_solaris_利用audit_sys_operationsOracle
- oracle baseOracle
- 【轉】oracle_base 和 oracle_home 的區別Oracle
- oracle base topicOracle
- oracle-baseOracle
- ORACLE_BASE 與 ORACLE_HOMEOracle
- ORACLE-BASE - Oracle DBA and development articlesOracledev
- 轉載oracle awrOracle
- oracle-base blogOracle
- oracle base64加密Oracle加密
- 轉載Oracle AWR速查Oracle
- 轉載--oracle DML鎖Oracle
- oracle rowid (轉載)Oracle
- CAP原理和BASE思想(轉)
- java字串轉Base64Java字串
- Oracle blob型別資料轉換成 base64編碼Oracle型別
- Oracle中Base64的使用Oracle
- Oracle文件轉載 部落格Oracle
- 【轉載】oracle更新語法Oracle
- [轉載] Oracle EBS 入門Oracle
- ORACLE鎖機制-轉載Oracle
- oracle 裸裝置(轉載)Oracle
- oracle 日期相減 轉載Oracle
- 轉載:Oracle RAC簡介Oracle
- ORACLE碎片整理一(轉載)Oracle
- ORACLE碎片整理二(轉載)Oracle
- [轉載]oracle_Bitmap IndexOracleIndex
- oracle跟蹤事件(轉載)Oracle事件
- 轉載:Oracle iLearning FAQOracle
- ORACLE_BASE和ORACLE_HOME的區別Oracle
- Java-InputStream轉Base64Java
- ReactNative載入base64圖片React
- Oracle線上文件下載 【轉載自@secooler】Oracle
- oracle 10.2.0.1 database server下載地址(轉載)OracleDatabaseServer
- 轉載oracle資料庫鎖Oracle資料庫
- 轉載-Oracle 常用的dump命令Oracle
- Oracle各版本區別(轉載)Oracle
- How Oracle Store Number internal?(轉載)Oracle