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

What this article shares with you is about the standard audit of ORACLE database. The editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading 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 tablespace, 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

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. The audit records will be written in XML format if the AUDIT_TRAIL initialization parameter is set to XML.

AUDIT_TRAIL

AUDIT_TRAIL enables or disables database auditing.

Values:

None

Disables database auditing.

Os

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

Db

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.

Xml

Enables database auditing and writes all audit records to XML format OS files.

Xml,extended

Enables database auditing and prints all columns of the audit trail, including SqlText and SqlBind values.

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

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 and dba_audit_statement are only 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.

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;-auditing test users' delete,update,insert operations to user.table

9. Fine audit

9.1. Create an audit tag

SQL > exec dbms_fga.add_policy (object_schema= > 'ryan',object_name= >' test',policy_name= > 'chk_test'

Statement_types= > 'select')

9.2. Start the audit

SQL > exec DBMS_FGA.ENABLE_POLICY (object_schema= > 'ryan', object_name= >' test', policy_name= > 'chk_test')

9.3. View audit records

SQL > conn system/811226@ryan123

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0

Connected as system

SQL > SELECT session_id,TIMESTAMP,db_user,os_user,userhost,sql_text FROM Dba_Fga_Audit_Trail

SESSION_ID TIMESTAMP DB_USER OS_USER USERHOST SQL_TEXT

-

2008-11-4 2 RYAN Ryan WORKGROUP/LENOVO-C30DA497 select * from test

9.4. Close the audit

SQL > exec DBMS_FGA.DISABLE_POLICY (object_schema= > 'ryan', object_name= >' TEST', policy_name= > 'chk_test')

9.5. Delete the audit tag

SQL > exec DBMS_FGA.DROP_POLICY (object_schema= > 'ryan', object_name= >' TEST', policy_name= > 'chk_test')

Set up ORACLE audit

The following steps can set up the audit function of ORACLE:

1. Modify the parameter file (init.ora, if you use the server parameter file using alter system set= scope=spfile | both, set the AUDIT_TRAIL parameter, and restart the database. The values of AUDIT_TRAIL are as follows:

DB/TRUE: start the audit function and store the audit results in the SYS.AUD$ table of the database

OS: start the audit function and store the audit results in the audit information of the operating system

DB_EXTENDED: with the function of DB/TRUE, enter the SQLBIND and SQLTEXT fields of AUD$

NONE/FALSE: turn off the audit function

two。 If you set AUDIT_TRAIL = OS, you also need to modify the parameter AUDIT_FILE_DEST.

If the operating system supports setting AUDIT_TRAIL=OS, the files are automatically stored in the directory specified by AUDIT_FILE_DEST, and the file name contains the PID of the process.

For example:

AUDIT_FILE_DEST = $ORACLE_HOME/rdbms/audit

$ls-l $ORACLE_HOME/rdbms/audit

-rw-rw---- 1 ora92 dba 881 Mar 17 09:57 ora_13264.aud

$ps-ef | grep 13264

Ora92 13264 13235 0 09:56:43-0:00 oracleV92 (DESCRIPTION= (LOCAL=Y)

SQL > select spid, program, username from v$process

SPID PROGRAM USERNAME

-

...

13264 oracle@frhp11 (TNS V1-V3) ora92

Note: WINDOWS NT does not use the AUDIT_FILE_DEST parameter, if you use the OS setting, then the audit information will be stored in the WINDOWS NT event log.

3. Confirm that the audit-related tables have been installed

SQLPLUS > connect / AS SYSDBA

SQLPLUS > select * from sys.aud$;-No record is returned

SQLPLUS > select * from dba_audit_trail;-- is returned with no record

If the table does not exist when making the above query, it means that the audit-related table has not been installed and needs to be installed.

SQLPLUS > connect / as sysdba

SQLPLUS > @ $ORACLE_HOME/rdbms/admin/cataudit.sql

Audit tables are installed in the SYSTEM tablespace. So make sure that the SYSTEM table space also has enough space to store the audit information.

4. Shut down and restart the database

5. Set up the required audit information

Here is an example

SQL > connect system/manager

SQL > grant audit system to scott

SQL > connect scott/tiger

SQL > audit session

Stop auditing:

SQL > noaudit session

An example of setting up an audit: an audit of access to an attempt to try a password

This section discusses an example of an audit that records an example of trying to decipher an ORACLE account password through a savage attempt:

1. Modify audit related parameters 2. Restart the database

3. Set up audit information

SQL > AUDIT ALL BY ACCESS WHENEVER NOT SUCCESSFUL

4. Query AUD$

SQL > select returncode, action#, userid, userhost, terminal,timestamp

From aud$

RETURNCODE ACTION# USERID USERHOST TERMINAL

--

1017 100 SCOTT WPRATA-BR

1017 100 SCOTT WPRATA-BR

1017 100 SCOTT WPRATA-BR

ORA-1017 means wrong username and password. By looking at the AUD$ table, you can clearly see that WPRATA-BR is trying to decipher the password of SCOTT. You can use the following stored procedure to analyze the AUD$ table to find suspicious information:

Create or replace procedure AuditLogin (Since Varchar2,Times PLS_Integer)

Is

USER_ID VARCHAR2 (20)

Cursor C1 is select userid,count (*) from sys.aud$ where returncode='1017' and timestamp# > = to_date (Since,'yyyy-mm-dd')

Group by userid

Cursor C2 IS Select userhost, terminal,TO_CHAR (timestamp#,'YYYY-MM-DD:HH24:MI:SS')

From sys.aud$ WHERE returncode='1017' and timestamp# > = to_date (Since,'yyyy-mm-dd') AND USERID=USER_ID

Ct PLS_INTEGER

V_USERHOST VARCHAR2 (40)

V_TERMINAL VARCHAR (40)

V_DATE VARCHAR2 (40)

BEGIN

OPEN C1

Dbms_output.enable (1024000)

LOOP

FETCH C1 INTO USER_ID,CT

EXIT WHEN C1%NOTFOUND

IF (CT > = TIMES) THEN

DBMS_OUTPUT.PUT_LINE ('USER BROKEN ALARM:' | | USER_ID)

OPEN C2

LOOP

FETCH C2 INTO V_USERhOST,V_TERMINAL,V_DATE

DBMS_OUTPUT.PUT_LINE (CHR (9) | | 'HOST:' | | V_USERHOST | |', TERM:' | | V_TERMINAL | |', TIME:' | | V_DATE)

EXIT WHEN C2%NOTFOUND

ENDLOOP

Close c2

END IF

ENDLOOP

Close c1

END

/

Here is the result of the implementation:

SQL > set serveroutput on

SQL > execute auditlogin ('2004-01-01-01)

USER BROKEN ALARM:SYS

HOST:,TERM:XUJI,TIME:2004-09-22 1114 08V 00

HOST:,TERM:XUJI,TIME:2004-09-22 purl 11purl 08purl 01

HOST:,TERM:XUJI,TIME:2004-09-22 1114 09R 29

HOST:,TERM:XUJI,TIME:2004-09-22 1114 09R 29

The PL/SQL process completed successfully.

Move audit-related tables to another tablespace

Because audit-related tables such as AUD$ tables are stored in SYSTEM tablespaces, in order not to affect system performance and protect SYSTEM tablespaces, it is best to move AUD$ to other tablespaces. You can use the following statement to move:

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

Alter table audit_actions move tablespace

Alter index i_audit_actions rebuild online tablespace

The above is what the standard audit of ORACLE database is like, and the editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please 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.