Shulou(Shulou.com)05/31 Report--

How to understand the orcle audit strategy, in view of this problem, this article introduces the corresponding analysis and answers in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

Oracle audit function

I. Audit classification:

Generally speaking, audit in Oracle can be divided into "standard audit" and "fine-grained audit". The latter is also called "policy-based audit", and its function has been greatly enhanced after Oracle10G. Standard audit can be divided into user-level audit and system-level audit. User-level audit is an audit that can be set by any Oracle user, which is mainly an audit of database tables or views created by users, recording all successful and / or unsuccessful access requirements to these tables or views by all users, as well as various types of SQL operations. System-level audits can only be set up by DBA to monitor successful or failed login requirements, GRANT and REVOKE operations, and other database-level permissions.

II. Standard audit:

2.1 Classification:

The following three standard audit types are supported in ORACLE:

U statement audit, audit for some type of SQL statement, without specifying a structure or object.

U privilege audit, audit of the use of system privileges that perform the corresponding action.

U object audit, the audit of a specified statement on a particular mode object.

These three standard audit types respectively audit the following three aspects:

U successful execution of audit statements, unsuccessful execution, or both.

U execute each user session audit statement once or audit the statement once at a time.

U Audit of the activities of all users or specified users.

When the audit function of the database is turned on, the audit record is generated during the statement execution phase. The audit record contains information such as the operation of the audit, the action performed by the user, the date and time of the operation, and so on. Audit records can be stored in data dictionary tables (called audit records) or operating system audit records. The database audit records are in the AUD$ table of the SYS schema.

2.2 set up ORACLE standard audit:

The following steps set up the standard audit features of ORACLE:

1. Modify initialization parameter file (init.ora)

If you use the server parameter file using alter system set = scope=spfile | both, see the description of the parameter file in Section 1.1), set the AUDIT_TRAIL parameter, and restart the database. The values of AUDIT_TRAIL are as follows:

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

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

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

L NONE/FALSE: turn off the audit function

two。 Set the AUDIT_TRAIL parameters:

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 AUDIT_FILE

The directory specified by _ 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

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;-No record is returned

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

Usually when a standard audit is set up, the audit is started through the Audit statement, and the audit is withdrawn using the noaudit statement. As follows:

To audit operations that modify the structure or data of the SC table, you can use the following statement:

AUDIE ALTER,UPDATE ON SC

To cancel all audits of the SC table, you can use the following statement:

NOAUDIT ALL ON SC

2.3 set up an instance of the audit (audit of access attempting a password):

The following is an example of an audit that records an attempt to decipher an ORACLE account password through a savage attempt:

1. Modify audit-related parameters (refer to the method described above)

two。 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

END LOOP

Close c2

END IF

END LOOP

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.

2.4 move audit-related tables to other tablespaces:

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:

Sql > connect / as sysdba

Sql > alter table aud$ move tablespace

Sql > alter index I_aud1 rebuild online tablespace

SQL > alter table audit$ move tablespace

SQL > alter index i_audit rebuild online tablespace

SQL > alter table audit_actions move tablespace

SQL > alter index i_audit_actions rebuild online tablespace

Third, fine-grained audit:

Fine-grained audit (FGA) (introduced through Oracle9i) can be understood as "policy-based audit". Contrary to the standard audit function, FGA can be used to specify the conditions necessary to generate audit records:

FGA policies are programmatically bound to objects (tables, views) by using the "dbms_fga" package. Similar to a package for access control through VPD ("dbms_rls"), it allows you to create any conditions you need, such as auditing events only if the following conditions are true:

A table is accessed between 9 a.m. and 6 p.m., or on Saturdays and Sundays.

An IP address outside the corporate network is used.

Specific columns are selected or updated.

A specific value for this column is used.

This creates a more meaningful audit trail because there is no need to record every access to the table by everyone. Starting with Oracle Database 10g, FGA supports any combination of Select, insert, Update, and Delete statements in a policy. In fact, the FGA policy bound to the table simplifies the management of the audit policy because it will only need to be changed once in the database and not again and again in each application. Besides. No matter how the user connects to the database (through the application, the Web interface, or through the SQL*Plus), his or her actions are recorded.

3.1 use fine-grained auditing:

1. Create a test table:

Create table ACCOUNT

(AACT_NO number not null

CUST_ID number not null

BALANCE number (15par 2)

);

2. Add audit policy:

Begin

DBMS_FGA.DROP_POLICY (object_schema = > 'TEST'

Object_name = > 'ACCOUNT'

Policy_name = > 'ACCOUNT_ACCESS')

End

This code must be executed by a user who has permission to execute the package dbms_fga. It is recommended that a dedicated user should be established to be responsible for adding audit policies. This process has many parameters, which are as follows:

OBJECT_SCHEMA

The owner of the table or view for which the FGA policy is defined

OBJECT_NAME

The name of the table or view

POLICY_NAME

The name of the policy, customized by the user-for example, ACCOUNTS_ACCESS

POLICY_TEXT

Audit conditions specified when adding a policy-for example, BALANCE > = 11000

POLICY_COLUMN

Audit column-for example, BALANCE

ENABLED

YES if enabled, NO otherwise

PF_SCHEMA

Mode that owns the policy processor module (if any)

PF_PACKAGE

The package name of the processor module (if present)

PF_FUNCTION

The procedure name of the processor module (if present)

3. After the policy is defined, when the user queries the table in the usual way, it is as follows:

Select * from bank.accounts

The audit trail records this action. You can use the following statement to view clues:

Select timestamp

Db_user

Os_user

Object_schema

Object_name

Sql_text

From dba_fga_audit_trail

TIMESTAMP DB_USER OS_USER OBJECT_ OBJECT_N SQL_TEXT

26-MAR-10 TEST ananda TEST ACCOUNT select * from account

Notice the new view called DBA_FGA_AUDIT_TRAIL, which records fine-grained access information. It shows the time stamp of the audit event, the querier's database user ID, the operating system user ID, the name and owner of the table used in the query, and finally the exact query statement.

3.2 Audit columns and audit conditions:

By default, auditing is enabled for all columns of the audited object, and an audit message is recorded when any column is accessed, which is not common in reality, because this will cause the audit information table to grow too fast and put pressure on the storage space. Therefore, audit conditions are usually set to initiate an audit when the condition is triggered. For example, we can set audit conditions on the Balance column of the Account table, and audit occurs only when the column is accessed and the audit condition is triggered. As follows:

Begin

Dbms_fga.add_policy (

Object_schema= > 'TEST'

Object_name= > 'ACCOUNT'

Policy_name= > 'ACCOUNT_ACCESS'

Audit_column = > 'BALANCE'

Audit_condition = > 'BALANCE > = 11000'

);

End

This policy will initiate an audit when the BALANCE column is accessed and only if the value of the access column is greater than or equal to 11000. Therefore, according to this condition, the household has the following different audit states:

SQL statement

Audit status

Select balance from account

Conduct an audit. The user selected the audit column BALANCE that was specified when the policy was added.

Select * from account

Conduct an audit. Even if the user does not explicitly specify the column BALANCE,*, it is implicitly selected.

Select cust_id from account where balance

< 10000; 进行审计。即使用户没有明确指定列 BALANCE，where 子句也隐含地选择了它。 select cust_id from account; 不进行审计。用户没有选择列 BALANCE。 select count(*) from account; 不进行审计。用户没有明确或隐含地选择列 BALANCE。 3.3优化器模式： FGA 需要基于成本的优化 (CBO)，以便正确地工作。在基于规则的优化时，只要用户从表中进行选择，无论是否选择了相关的列，都始终生成审计线索，增加了误导项目出现的可能性。为使 FGA 正确地工作，除了在实例级启用 CBO 之外，在 SQL 语句中应该没有规则暗示（hint），并且必须至少使用评估选项对查询中的所有表进行分析。 3.4管理 FGA 策略: 要删除策略，您可以使用以下语句： begin dbms_fga.drop_policy ( object_schema =>

'TEST'

Object_name = > 'ACCOUNT'

Policy_name = > 'ACCOUNT_ACCESS'

);

End

There is no pick-and-take solution for changing policies. To change any parameters in a policy, you must delete the policy and then add the policy using the changed parameters. However, you can temporarily disable an existing policy, as follows:

Begin

Dbms_fga.enable_policy (

Object_schema = > 'TEST'

Object_name = > 'ACCOUNT'

Policy_name = > 'ACCOUNT_ACCESS'

Enable = > FALSE

);

End

To re-enable it, you can use the same function by setting the parameter enable to TRUE.

3.5 FGA data Dictionary View:

The definition of the FGA policy is located in the data dictionary view DBA_AUDIT_POLICIES. The meanings of each column on the way to the city are as follows:

SESSION_ID

Audit session identifier; different from the session identifier in the V$SESSION view

TIMESTAMP

The time stamp when the audit record was generated

DB_USER

The database user who issued the query

OS_USER

Operating system user

USERHOST

Hostname of the machine to which the user is connected

CLIENT_ID

Customer identifier (if set by a call to the packaging procedure dbms_session.set_identifier)

EXT_NAME

Externally authenticated customer name, such as LDAP user

OBJECT_SCHEMA

Access to the table triggered the audited table owner

OBJECT_NAME

The SELECT operation on the table triggered the name of the audited table

POLICY_NAME

The name of the policy that triggered the audit (if more than one policy is defined on the table, a record is inserted for each policy. In this case, the column shows which rows were inserted by which policy.)

SCN

The audited Oracle system change number is recorded.

SQL_TEXT

SQL statements submitted by the user

SQL_BIND

The binding variable used by the SQL statement, if present

3.6View and FGA:

Suppose the view VW_ACCOUNT is defined on the ACCOUNTS table as follows:

Create view vw_account as select * from account

Now, if the user selects from the view instead of from the table:

Select * from vw_account

You will see the following audit trail:

Select object_name, sql_text from dba_fga_audit_trail

OBJECT_NAME SQL_TEXT

ACCOUNT select * from vw_account

Note that it is the base table name, not the view name, that appears in the OBJECT_NAME column, because the selection in the view is made from the base table. However, the SQL_TEXT column records the actual statements submitted by the user.

If you only want to audit queries to the view rather than to the table, you can establish a policy on the view itself. You can do this by passing the name of the view instead of the name of the table to the parameter object_name in the packaged procedure dbms_fga.add_policy. The OBJECT_NAME column in the DBA_FGA_AUDIT_TRAIL then displays the name of the view, and there is no additional record of table access.

3.7 other uses:

In addition to recording selective access to the table, FGA can be used in some other situations:

You can use FGA against a data warehouse to capture all statements that occur on a particular table, view, or materialized view, which helps plan the index. You don't need to go to the V$SQL view to get this information. Even if the SQL statement has exceeded the duration of the V$SQL, it will always be provided in the FGA audit trail.

Because FGA captures bound variables, it helps to understand the pattern of the values of bound variables, which helps to design histogram collections, and so on.

You can send a warning to the auditor or DBA, which helps track down malicious applications.

Because FGA can be used as a trigger for SELECT statements, you can use it whenever you need it.

3.8 enhancements of FGA in 10G:

3.8.1 Audit of all DML:

In 9i, FGA can only audit Select statements, but not other DML statements (Update, Delete, Insert). If you want to audit other DML statements, it can only be implemented in the form initiated by the database. The audit of all DML statements is implemented in 10G, as follows:

Begin dbms_fga.add_policy (object_schema = > 'TEST', object_name = >' ACCOUNT', policy_name = > 'ACCOUNT_ACCESS', audit_column = >' BALANCE', audit_condition = > 'BALANCE > = 3000mm, statement_types = >' INSERT, UPDATE, DELETE, SELECT'); end

A new policy is established through the parameters statement_types = > 'INSERT, UPDATE, DELETE, SELECT', which can audit all DML operations except Select. Therefore, according to the new audit conditions and audit strategy, there will be the following different situations:

The first case

Before: BALANCE = 1000

The user issues:

Update account set balance = 1200 where ACCOUNT_NO =....

Both the old and new balance are less than 3Power000, and the audit condition is not met; therefore, this statement will not be audited.

The second case

Before: BALANCE = 1000

The user issues:

Update account set balance = 3200 where ACCOUNT_NO =....

The new balance is greater than 350000 and the audit condition is met; therefore, this statement will be audited.

The third case

Before: BALANCE = 3200

The user issues:

Update account set balance = 1200 where ACCOUNT_NO =....

The new balance is less than 350000, but the old balance is greater than 3000. Therefore, if the audit condition is met, this statement will be audited.

The fourth case

The user inserts a row with BALANCE in it

< 3000。 insert into account values (9999,1200,'X'); 因为 balance 1,200 不满足审计条件，所以这条语句不被审计。如果 balance 列大于或等于 3,000，它将被审计。 第 5 种情况 用户插入一行，其中 balance 的值为空。 insert into account (account_no, status) values (9997, 'X'); 因为 balance 为空，该列没有任何默认值，所以审计条件不满足（比较 NULL >

= 3000 the result is FALSE), this statement will not be audited. Important note: assuming that the column has a default value greater than 3000, this statement is still not audited, even if the balance column value of the inserted row is greater than 3000. Note that the audit of the DML statement is inserted by an automatic transaction; even if the operation of the DML statement is rolled back, the audit record will exist and will not be rolled back.

3.8.2 formulation

A policy defined on the table ACCOUNT is as follows:

Begin

Dbms_fga.add_policy (

Object_schema = > 'TEST'

Object_name = > 'ACCOUNT'

Policy_name = > 'ACCOUNT_SEL'

Audit_column = > 'ACCOUNT_NO, BALANCE'

Audit_condition = > 'BALANCE > = 3000'

Statement_types = > 'SELECT'

);

End

In some cases, the combination of columns may be important rather than a particular column. The above policies are defined on ACCOUNT_NO and BALANCE. So if the user issues the following statement:

Select balance from accounts where account_no = 9995

This statement will be audited because the balance column is selected and the balance is 3pg200, greater than 3j2000, which meets the audit criteria.

If a user wants to find out the total balance at the bank, he sends out:

Select sum (balance) from account

This query does little harm; it does not explicitly indicate the account owner and the account balance. Therefore, the security policy may not require the audit of this query. But this query

Select balance from account where account_no = 9995

Must be audited; because it explicitly specifies an account. By default, all statements are audited (regardless of the combination of columns used). This will create a large number of unwanted audit trail projects and may cause some space constraints. To limit them, you can specify that the audit starts only if the desired combination of columns is used in the query. When defining a policy, you can use a new parameter:

Audit_column_opts = > DBMS_FGA.ALL_COLUMNS

This parameter causes the policy to create audit trail items only if the columns ACCOUNT_NO and BALANCE are accessed in the query. For example, the following query will produce an audit trail project.

Select account_no, balance from account

However, this query will not generate audit trail projects.

Select account_no from account

Using this parameter limits the number of audits to a more manageable size. If you want to adopt the default behavior of auditing when any column is selected, you can use different values for the same parameter.

Audit_column_opts = > DBMS_FGA.ANY_COLUMNS

3.8.3 combination with standard audit:

The combination of standard audit and fine-grained audit is realized by formulating the following audit strategy.

Begin dbms_fga.add_policy (object_schema = > 'TEST', object_name = >' ACCOUNT', policy_name = > 'ACCOUNT_SEL', audit_column = >' ACCOUNT_NO, BALANCE', audit_condition = > 'BALANCE > = 3000 million, statement_types = >' SELECT', audit_column_opts = > DBMS_FGA.ALL_COLUMNS, audit_trail = > DB); end

Enable standard auditing during fine-grained auditing by specifying the audit_trail = > DB parameter. In Oracle Database 10g, standard auditing has also been greatly improved. By performing a standard audit through the AUDIT command, it is now able to capture a great deal of other useful information. In terms of content and function, standard audit is similar to fine-grained audit. However, as a database administrator, you are interested in knowing all the audit projects, not just one audit project. A new view, DBA_COMMON_AUDIT_TRAIL, combines standard audit trails with FGA audit trails. Check them both with the following query, such as:

Select * from dba_common_audit_trail; can view the information collected by both audits through this query.

Fourth, the differences between FGA audit and standard audit:

Standard auditing must be enabled at the database level with the parameter AUDIT_TRAIL. This parameter is not dynamic; you must restart the database for it to take effect. In contrast, FGA does not require any parameter modification.

Once set on an object, the standard audit will remain there. To undo it, you must remove the audit option with the NOAUDIT command. This can be inconvenient because dropping the audit option on a table will also discard metadata information. However, FGA can be temporarily disabled and enabled without losing any metadata information.

FGA can only handle four types of statements: SELECT, INSERT, UPDATE, and DELETE. In contrast, regular audits can handle many other statements and permissions, even session connections and disconnections.

The standard audit creates only one record per session (by session) or one record per access object (by access), which takes up little resources to control the space in the audit trail table. FGA doesn't save resources as much; it runs every visit-making the clue bigger.

By recording clues, the standard audit can be used to detect any interrupt attempt, and if the attempt is unsuccessful, an error code will be generated. And FGA can't.

Standard audits can write database tables or OS files. The latter is useful when auditors (not database administrators) can access clues. Under Windows, non-database audit trails are recorded in the event log and can be accessed in different ways. This option protects the integrity of the audit trail. However, the FGA log is only written to the database table FGA_LOG$. User-defined audit handlers can be created in FGA to write OS files, but their integrity is not guaranteed.

Standard auditing can be set for the default object. This feature becomes extremely useful when tables are created at run time: the default audit option allows auditing without database administrator intervention. This is not possible in FGA, where the user must create a policy on an existing table, which can only happen after the table has been created.

In FGA, auditing is more flexible-only when accessing certain columns, when a particular condition is true, and so on. This versatility is very convenient when you need to control the growth of clues.

In FGA, SQL assignment variables are captured by default. In a standard audit, the initialization parameter audit_trail must be set to db_extended to enable this feature.

Permission differences: standard audits require audit system or statement permissions; FGA requires only run permissions on the dbms_fga package.

This is the answer to the question on how to understand the orcle audit strategy. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel for more related knowledge.

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.