Shulou(Shulou.com)06/01 Report--

This article mainly shows you "how to audit ORACLE", the content is easy to understand, clear, hope to help you solve your doubts, the following let the editor lead you to study and learn "how to audit ORACLE" this article.

1. What is audit

The Audit is used to monitor the database operations performed by the user, and Oracle stores the audit trail results in the OS file (default location is $ORACLE_BASE/admin / $ORACLE_SID/adump/) or in the database (stored in the SYS.AUD$ table in the system table space, which can be viewed through the view dba_audit_trail). Auditing is not turned on by default.

Whether you turn on the database audit function or not, the following operating systems force records: connect to Instance; to start the database with administrator privileges, and shut down the database.

2. Two main parameters related to audit

Audit_sys_operations:

The default is false, and when set to true, the actions of all sys users (including those logged in as sysdba,sysoper) will be recorded, and audit trail will not be written in the aud$ table, which is easy to understand. If the database has not been started, aud$ is not available, then connection information such as conn / as sysdba can only be recorded elsewhere. In the case of windows platform, audti trail is recorded in the event management of windows, and in the file specified by the audit_file_dest parameter in the case of linux/unix platform.

Audit_trail:

None: default value, no audit

DB: record the audit trail in the audit-related tables of the database, for example, the result of the aud$, audit only contains connection information

DB,Extended: in this way, the audit result contains not only the connection information, but also the specific statements executed at that time.

OS: record the audit trail in the operating system file with the file name specified by the audit_file_dest parameter

New in XML:10g.

Note: these two parameters are static parameters and need to be restarted to take effect.

3. Audit level

When the audit function is enabled, the database can be audited at three levels: Statement (statement), Privilege (permission), and object (object).

Statement:

Audit by statement, for example, audit table will audit all create table,drop table,truncate table statements in the database, and alter session by cmy will audit all database connections of cmy users.

Privilege:

Audit by permission, when the user uses this permission, such as executing grant select any table to a, when the audit select any table statement is executed, select any table permission is used when user an accesses user b's table (such as select * from b.t), so it will be audited. Note that the user is the owner of his own table, so the user's access to his table is not audited.

Object:

Audit by object, only audit the operations of the object specified by the on keyword, such as aduit alter,delete,drop,insert on cmy.t by scott;. In this case, the t table of cmy users is audited, but the by clause is also used, so only the actions initiated by scott users are audited. Note that Oracle does not provide the audit function for all objects in schema, so it can only audit one object at a time. For later created objects, Oracle provides an on default clause to implement automatic audit. For example, after audit drop on default by access; is executed, the drop operations of the subsequently created objects will be audited. However, this default will be valid for all database objects created later, and there seems to be no way to specify that it is valid only for objects created by a certain user. It seems that this function is slightly inadequate than trigger can "audit" schema's DDL.

4. Some other options for auditing

By access / by session:

By access each audited operation generates an audit trail.

By session an operation of the same type in a session will only generate an audit trail. The default is by session.

Whenever [not] successful:

Audit only if the whenever successful operation is successful (the returncode field in dba_audit_trail is 0).

Whenever not successful and vice versa. If this clause is omitted, it will be audited regardless of whether the operation is successful or not.

5. Audit-related views

Dba_audit_trail: save all the audit trail, which is really just an aud$-based view. The other views dba_audit_session,dba_audit_object,dba_audit_statement are just a subset of dba_audit_trail.

Dba_stmt_audit_opts: the audit options that can be used to view the statement audit level, that is, which statement-level audits have been set up in the database. The dba_obj_audit_opts,dba_priv_audit_opts view function is similar.

All_def_audit_opts: used to see which default object auditing is set by the database with the on default clause.

6. Cancel the audit

Change the audit of the corresponding audit statement to noaudit, for example, the cancel audit statement of audit session whenever successful is noaudit session whenever successful

The audit in 10g tells everything.

The Oracle database 10g audit captures user behavior at a very detailed level, eliminating manual, trigger-based auditing.

Assume that user Joe has permission to update that table and updates a row of data in the table as follows:

Update SCOTT.EMP set salary = 12000 where empno = 123456

How do you track this behavior in the database? In Oracle 9i databases and earlier versions, audits can only capture who performed this operation, not what was performed. For example, it lets you know that Joe updates all of SCOTT's table EMP, but it doesn't show that he updated the payroll column in that table with employee number 123456. It does not display the value of the salary column before the change-to capture such detailed changes, you will have to write your own triggers to capture the pre-change values, or use LogMiner to retrieve them from the archive log.

Fine-grained auditing (FGA), introduced in Oracle 9i, can record SCN number and row-level changes to rebuild old data, but they can only be used for select statements, not for DML, such as update, insert, and delete statements. Therefore, for versions prior to 10g of the Oracle database, using triggers is not an attractive option for tracking users' initial changes at the row level, but it is also the only reliable way.

8. Example explanation

8.1. Activate audit

SQL > conn / as sysdba

SQL > show parameter audit

NAME TYPE VALUE

-

Audit_file_dest string / u01/app/oracle/admin/ORCL/adump

Audit_sys_operations boolean FALSE

Audit_syslog_level string

Audit_trail string NONE

SQL > alter system set audit_sys_operations=TRUE scope=spfile;-Audit management user (logged in as sysdba/sysoper)

SQL > alter system set audit_trail=db,extended scope=spfile

SQL > startup force

SQL > show parameter audit

NAME TYPE VALUE

-

Audit_file_dest string / u01/app/oracle/admin/ORCL/adump

Audit_sys_operations boolean TRUE

Audit_syslog_level string

Audit_trail string DB, EXTENDED

8.2. Start the audit

SQL > conn / as sysdba

SQL > audit all on t_test

SQL > conn u_test

SQL > select * from t_test

SQL > insert into u_test.t_test (c2 and c5) values ('test1','2')

SQL > commit

SQL > delete from u_test.t_test

SQL > commit

SQL > conn / as sysdba

SQL > col DEST_NAME format A30

Col OS_USERNAME format a15

Col USERNAME format a15

Col USERHOST format a15

Col TERMINAL format a15

Col OBJ_NAME format a30

Col SQL_TEXT format a60

SQL > select OS_USERNAME,username,USERHOST,TERMINAL,TIMESTAMP,OWNER,obj_name,ACTION_NAME,sessionid,os_process,sql_text from dba_audit_trail

Sql > audit select table by u_test by access

If you add by user after the command, only the operation of user is audited, and if the by user is omitted, all users in the system are audited (excluding sys users).

Example:

AUDIT DELETE ANY TABLE;-Audit deletes the table

AUDIT DELETE ANY TABLE WHENEVER NOT SUCCESSFUL;-only audit deletions that fail

AUDIT DELETE ANY TABLE WHENEVER SUCCESSFUL;-only audit deletions are successful

AUDIT DELETE,UPDATE,INSERT ON user.table by test;-audit test users' delete,update,insert operations on table user.table

8.3. Revoke the audit

SQL > noaudit all on t_test

9. Audit statement

Audit in multi-tier environment: appserve- application server, jackson-client

AUDIT SELECT TABLE BY appserve ON BEHALF OF jackson

Audit connection or disconnect:

AUDIT SESSION

AUDIT SESSION BY jeff, lori;-- specified user

Audit permissions (actions that can only be performed with this permission):

AUDIT DELETE ANY TABLE BY ACCESS WHENEVER NOT SUCCESSFUL

AUDIT DELETE ANY TABLE

AUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE, EXECUTE PROCEDURE BY ACCESS WHENEVER NOT SUCCESSFUL

Object audit:

AUDIT DELETE ON jeff.emp

AUDIT SELECT, INSERT, DELETE ON jward.dept BY ACCESS WHENEVER SUCCESSFUL

Cancel the audit:

NOAUDIT session

NOAUDIT session BY jeff, lori

NOAUDIT DELETE ANY TABLE

NOAUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE,EXECUTE PROCEDURE

NOAUDIT ALL;-cancel all statement audits

NOAUDIT ALL PRIVILEGES;-cancel all permission audits

NOAUDIT ALL ON DEFAULT;-cancel all object audits

10. Clear audit information

DELETE FROM SYS.AUD$

DELETE FROM SYS.AUD$ WHERE obj$name='EMP'

11. Audit view

STMT_AUDIT_OPTION_MAP-Audit option type code

AUDIT_ACTIONS-action code

ALL_DEF_AUDIT_OPTS-default object audit option when an object is created

DBA_STMT_AUDIT_OPTS-current database system audit option

DBA_PRIV_AUDIT_OPTS-permission audit option

DBA_OBJ_AUDIT_OPTS

USER_OBJ_AUDIT_OPTS-object audit option

DBA_AUDIT_TRAIL

USER_AUDIT_TRAIL-audit records

DBA_AUDIT_OBJECT

USER_AUDIT_OBJECT-list of audit objects

DBA_AUDIT_SESSION

USER_AUDIT_SESSION-session audit

DBA_AUDIT_STATEMENT

USER_AUDIT_STATEMENT-statement audit

DBA_AUDIT_EXISTS-Audit using the BY AUDIT NOT EXISTS option

DBA_AUDIT_POLICIES-Audit POLICIES

DBA_COMMON_AUDIT_TRAIL-Standard Audit + Fine Audit

12. Move the audit result table from the system table space to another table space

In fact, the sys.aud$ table contains two lob fields, which is not a simple move table.

Here is the specific process:

Alter table sys.aud$ move tablespace users

Alter table sys.aud$ move lob (sqlbind) store as (tablespace USERS)

Alter table sys.aud$ move lob (SQLTEXT) store as (tablespace USERS)

Alter index sys.I_AUD1 rebuild tablespace users

The above is all the contents of the article "how to audit by ORACLE". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.