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

1. Log in to Oracle as DBA

# su-oracle

$sqlplus / nolog

SQL > conn / as sysdba

2. View the current audit settings

SQL > show parameter audit

Parameter description

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.

3. Modify audit parameters to enable audit

SQL > alter system set audit_trail=db_extended scope=spfile

Note that if audit_trail=db, do not record SQL_BIND and SQL_TEXT

4. Restart the database

SQL > shutdown immediate

SQL > startup

#

#

#

5. Open the test based on user audit

Create user AUDIT_TEST

$sqlplus / nolog

SQL > conn / as sysdba

SQL > audit all by audit_test by access

SQL > audit select table, update table, insert table, delete table by audit_test by access

SQL > audit execute procedure by audit_test by access

Correspond to the following three categories:

DDL (CREATE, ALTER & DROP of objects)

DML (INSERT UPDATE, DELETE, SELECT, EXECUTE).

SYSTEM EVENTS (LOGON, LOGOFF etc.)

SQL > conn audit_test/password

SQL > create table test (id number)

SQL > insert into test (id) values (1)

SQL > insert into test (id) values (2)

SQL > update test set id = 3 where id = 1

SQL > select * from test

SQL > delete from test

SQL > commit

SQL > drop table test

SQL > 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

View description:

1. SYS.AUD$

For the underlying view of the audit function, if you need to delete the data, you only need to delete the aud$ view. The data in other views are obtained by aud$.

2. DBA_AUDIT_EXISTS

List the audit trails generated by audit not exists and audit exists, and we default to audit exists.

3. DBA_AUDIT_TRAIL

You can check and deal with all the information tracked by the audit in it.

4. DBA_AUDIT_OBJECT

You can query all object tracking information. (for example, not recorded for grant,revoke, etc.), the information is fully contained in dba_audit_trail

5. DBA_AUDIT_SESSION

The data obtained are all information about logon or logoff.

6. DBA_AUDIT_STATEMENT

Lists audit trail information for grant, revoke, audit, noaudit, alter system statements.

7. DBA_PRIV_AUDIT_OPTS

Current system privileges audited by the system and by the user

8. DBA_OBJ_AUDIT_OPTS

You can query the design options for all users and all objects

9. ALL_DEF_AUDIT_OPTS

10. AUDIT_ACTIONS

You can query the meaning of the actions column in views such as aud$

11. SYSTEM_PRIVILEGE_MAP

You can query the meaning of the priv$used column in views such as aud$ (please add'- 'before it)

Common views:

DBA_AUDIT_TRAIL

DBA_FGA_AUDIT_TRAIL

DBA_COMMON_AUDIT_TRAIL

View audit content. Main fields: os_username, userhost, timestamp, owner,sql_bind, sql_text

SQL > select * from dba_audit_trail where owner = 'AUDIT_TEST' order by timestamp

Note: the value of owner must be capitalized, for example, owner = 'AUDIT_TEST'

Turn off audit

SQL > alter system set audit_trail=none scope=spfile

#

#

# perform various DML operations on the table #

Log in with the scott user

SQL > conn scott/123

SQL > create table t_test as select * from emp

SQL > update t_test set emp1='111'

*

An error occurred on line 1:

ORA-00904: "EMP1": invalid identifier

SQL > delete from t_test where rownum=1

1 line has been deleted.

SQL > commit

# query audit information

SQL > select EXTENDED_TIMESTAMP,SESSION_ID,SQL_TEXT

SQL > from DBA_COMMON_AUDIT_TRAIL ORDER BY EXTENDED_TIMESTAMP DESC

Or

SQL > select EXTENDED_TIMESTAMP,SESSION_ID,SQL_TEXT from DBA_COMMON_AUDIT_TRAIL

SQL > where OBJECT_NAME='T_TEST'

SQL > and STATEMENT_TYPE in ('INSERT','UPDATE','DELETE')

-results:

SQL > /

EXTENDED_TIMESTAMP SESSION_ID SQL_TEXT

-

-

14-August-12 04.14.45.187000 PM + 08:00 190125 update t_test set emp1='111'

14-August-12 04.26.02.98000PM + 08:00 190125 delete from t_test where rownum=1

Note: audit is generally used only for ordinary users, but not for SYS users

For windows systems, the audit information for sys users does not exist in the directory specified by the AUDIT_FILE_DEST parameter, but in the event manager of windows.

-

# in addition, the above audit can also be achieved through fine-grained audit FGA:

# usage to create an audit policy:

Syntax

DBMS_FGA.ADD_POLICY (

Object_schema VARCHAR2

Object_name VARCHAR2

Policy_name VARCHAR2

Audit_condition VARCHAR2

Audit_column VARCHAR2

Handler_schema VARCHAR2

Handler_module VARCHAR2

Enable BOOLEAN)

# Delete audit policy:

DBMS_FGA.DROP_POLICY (

Object_schema VARCHAR2

Object_name VARCHAR2

Policy_name VARCHAR2)

# enable audit policy:

DBMS_FGA.ENABLE_POLICY (

Object_schema VARCHAR2: = NULL

Object_name VARCHAR2

Policy_name VARCHAR2

Enable BOOLEAN: = TRUE)

# disable audit policy:

DBMS_FGA.DISABLE_POLICY (

Object_schema VARCHAR2

Object_name VARCHAR2

Policy_name VARCHAR2)

# first, create an audit policy

SQL > conn / as sysdba

Connected.

SQL > begin

2 dbms_fga.add_policy

3 (

4 object_schema= > 'SCOTT',object_name= >' Tunable test'

5 policy_name= > 'Test_audit'

6)

7 end

8 /

The PL/SQL process completed successfully.

SQL > conn scott/tigger

# query

SQL > select ename from t_test

# query using SYS login

SQL > select statement_type,SQL_TEXT from dba_fga_audit_trail

STATEME SQL_TEXT

SELECT select ename from t_test

-

Note: after testing, it is found that the audited SQL statements are in uppercase and lowercase formats.

Directly executed SQL statements, what kind of statements, the audit is what kind of statements.

Statements executed in storage are all uppercase statements audited.

What kind of statement is dynamically executed in the storage is what kind of statement is audited.

BEGIN

EXECUTE immediate 'delete FrOm emp WHERE ROWNUM=1'

END

# that's what the audit found

DELETE delete FrOm emp WHERE ROWNUM=1

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.