Oracle Management 6

Audit

Turn on switch parameters:

Sql> Show Parameter Audit_trail

Set audit options:

Each time a new audit option is set, the test user needs to reconnect

SYS preparation:

sql> drop user User01 cascade;

Sql> create user User01 identified by password;

Sql> Grant Create session, create TABLE, create any table to User01;

Audit system permissions:

sql> AUDIT Create any table, create table by USER01 by ACCESS; Audit

User01 Test:

sql> CREATE TABLE t1 (x int);

sql> CREATE TABLE t1 (x int); Failed

Sql> CREATE table hr.t1 (x int);

Sql> CREATE table hr.t1 (x int); Failed

SYS View Audit results:

Sql> desc aud$

Sql> desc Dba_audit_trail

View in Browser

SYS adds an audit condition:

sql> AUDIT SELECT any TABLE by User01 by ACCESS;

Sql> Grant Select any table to User01;

User01 Test:

Sql> select * from T1;

Sql> select * from Hr.t1;

SYS View Audit results:

In the browser or view the Dba_audit_trail table

To delete an audit option:

sql> Noaudit CREATE any TABLE by USER01;

Sql> noaudit CREATE TABLE by USER01;

sql> Noaudit SELECT any TABLE by User01;

Audit object:

SYS Set audit options:

Sql> AUDIT SELECT on hr.employees by ACCESS;

sql> drop user User01 cascade;

Sql> create user User01 identified by password;

Sql> Grant create session to User01;

SYS authorization, each time a statement is executed, User01 is tested once:

Sql> Grant Select any table to User01;

Sql> Revoke Select any table from User01;

Sql> Grant Select on Hr.employees to User01;

User01 Test (performed 4 times):

Sql> Select COUNT (*) from hr.employees;

The behavior of SYS is not logged by default:

Sql> Select COUNT (*) from hr.employees;

To delete an audit option:

Sql> Noaudit SELECT on hr.employees;

Audit Statements:

SYS Set audit options:

Sql> AUDIT TABLE by User01 by ACCESS;

User01 Test:

sql> CREATE TABLE t1 (x int); Failed

sql> CREATE TABLE t1 (x int);

sql> CREATE table t1 (y int); Failed

sql> drop table T1;

SYS View results:

In the browser, or in the Dba_audit_object table

To delete an audit option:

Sql> Noaudit TABLE by USER01

Audit the operation of SYS:

Sql> Show Parameter Audit

Two parameters Modified

Concurrency management

Read-write conflicts are resolved by reading consistency:

SYS preparation:

Sql> create user User01 identified by password;

Sql> Grant DBA to User01;

The following are used User01:

Sql> Conn User01/password

Connected.

sql> CREATE TABLE t1 (x int);

sql> INSERT INTO T1 values (1);

Sql> commit;

Session1:

sql> Update T1 set x=11 where x=1;

Sql> select * from T1;

Session 2:

Sql> select * from T1;

Session 1:s

Sql> commit;

Session 2:

Sql> select * from T1;

Test serializable:

Session1:

Sql> alter session set isolation_level=serializable;

Repeat the above steps

The conflict between writing and writing is resolved through the locking mechanism:

Session 1:

sql> Update T1 set x=11 where x=1;

View lock information in browser

Session 2:

sql> Update T1 set x=111 where x=1; Be blocked

View lock information in browser

Session 1:

sql> rollback;

View lock information in browser

Deadlock:

Session1:

Sql> select * from T1;

X

----------

1

2

sql> Update T1 set x=11 where x=1;

Session2:

sql> Update T1 set x=22 where x=2;

Session1:

sql> Update T1 set x=222 where x=2; Blocking

Session2:

sql> Update T1 set x=111 where x=1; Dead lock

ERROR at line 1:

Ora-00060:deadlock detected while waiting for resource

$ vi/u01/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log

Locks and foreign keys

Select ... for update

Oracle Management 6