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

This article introduces the relevant knowledge of "what is the audit audit method". In the operation of actual cases, many people will encounter such a dilemma. Next, let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

The value of audit_trail is NONE, which means that it is not enabled.

Two main parameters related to audit:

1.audit_sys_operations: the default is false. When set to true, the operations of the audit administrative user (sysdba/sysoper role login) will be recorded, and audit trail will not be written in the aud$ table. This 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.

2.audit_trail:None: 10g default value, no audit; 11g default value DB, which records the audit results in the aud$ table

The value of audit_trail is FALSE, which means that it is not enabled.

The value of audit_trail is DB, which means it is enabled.

The value of audit_trail is TURE, which means it is enabled.

The value of audit_trail is OS, which means that the audit record is written to an operating system text.

3.oracle audit log cleanup

-- enter the audit log directory:

Cd $ORACLE_BASE/admin/$ORACLE_SID/adump

-Delete the audit documents from 3 months ago:

Find. /-type f-name "* .aud"-mtime + 91 | xargs rm-f

-emptying all audit documents at once

Find. /-type f-name "* .aud" | xargs rm-f

Find. /-mtime + 7-name "* .aud"-type f-delete

Note:

Oracle records the audit file with the land suffix .aud in the $ORACLE_BASE/admin/$ORACLE_SID/adump directory.

Therefore, if the DB function is turned on, the audit log will be recorded in both the AUD$ table and the operating system aud file. Set to NONE, it will still be recorded in the operating system aud file and there is no other way to record it.

* the table of the database is: sys.aud$

* operating system directory is: $ORACLE_BASE/admin/ instance name / adump/

4. Audit:

4.1 mandatory audit

Actions such as starting and stopping the database are recorded in the alert log. These are mandatory audits and are automatically enabled by oracle.

4.2 Standard database audit

Show parameter audit_trail

4.3 value-based audit

This is done through our own triggers.

4.4 Fine-grained audit (FGA)

You can conduct a more detailed audit for a column

4.5 DBA audit

Security administrator's audit of DBA

Standard database audit

-Audit syntax:

Audit sql_statement_clause by {session | access} whenever [not] successful

By session, in a session, only one audit is performed for operations of the same type

By access, audit each operation that conforms to the audit

-Audit related parameters (audit_trail):

Audit_trail = {none | os | db [, extended] | xml [, extended]}

None: 10g default value, no audit

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

Db: 11g default value to record the audit results in the aud$ table

Db,extended: record the audit results in the aud$ table, including binding variables and CLOB fields

Xml: audit records in XML format are recorded in OS files

Xml,extended: record the OS file is an audit record in XML format, including binding variables and CLOB fields.

Oracle10g: default audit parameter is NONE, that is, it is not enabled

Oracle11g: default audit parameter is DB

Experiment 1: audit turns on os

[oracle@wang ~] $sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun Aug 13 08:11:24 2017

Copyright (c) 1982, 2013, Oracle. All rights reserved.Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL > show parameter audit

NAME TYPE VALUE

-

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

Audit_sys_operations boolean FALSE

Audit_syslog_level string

Audit_trail string DB

SQL >

SQL > alter system set audit_trail='OS' scope=spfile

System altered.

SQL > shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL > startup

ORACLE instance started.

Total System Global Area 835104768 bytes

Fixed Size 2257840 bytes

Variable Size 549456976 bytes

Database Buffers 281018368 bytes

Redo Buffers 2371584 bytes

Database mounted.

Database opened.

SQL >

SQL > show parameter audit

NAME TYPE VALUE

-

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

Audit_sys_operations boolean FALSE

Audit_syslog_level string

Audit_trail string OS

SQL >

-- View the audit file of the os layer

[oracle@wang adump] $cd / u01/app/oracle/admin/DBdb/adump

-- perform relevant operations

SQL > conn scott/tiger; Connected.SQL > alter session set nls_date_format='yyyy-mm-dd hh34:mi:ss';Session altered.SQL > select sysdate from dual;SYSDATE-2018-01-26 00:28:58

SQL > select * from tab

TNAME TABTYPE CLUSTERID

BONUS TABLE

DEPT TABLE

EMP TABLE

JOBS TABLE

SQL > create table an as select * from user_objects

Table created.

SQL > insert into a select * from a

10 rows created.

SQL > commit

Commit complete.

SQL > update a set object_id=1

20 rows updated.

SQL > commit

Commit complete.

SQL > delete a where rownum commit

Commit complete.

SQL > truncate table a

Table truncated.

SQL > drop table a purge

Table dropped.

-- View the audit file:

[oracle@wang adump] $pwd

/ u01/app/oracle/admin/DBdb/adump

[oracle@wang adump] $

[oracle@wang adump] $

[oracle@wang adump] $ll

Total 24

-rw-r- 1 oracle oinstall 772 Jan 2600: 22 DBdb_ora_27579_20180126002238441832143795.aud

-rw-r- 1 oracle oinstall 755 Jan 2600: 26 DBdb_ora_27630_20180126002622032142143795.aud

-rw-r- 1 oracle oinstall 762 Jan 2600: 26 DBdb_ora_27630_20180126002623437420143795.aud

-rw-r- 1 oracle oinstall 768 Jan 2600: 26 DBdb_ora_27669_20180126002623481070143795.aud

-rw-r- 1 oracle oinstall 772 Jan 2600: 26 DBdb_ora_27674_20180126002627838313143795.aud

-rw-r- 1 oracle oinstall 877 Jan 2600: 28 DBdb_ora_27722_20180126002816963203143795.aud

[oracle@wang adump] $

-- View the audit log

[oracle@wang adump] $more DBdb_ora_27722_20180126002816963203143795.aud

Audit file / u01/app/oracle/admin/DBdb/adump/DBdb_ora_27722_20180126002816963203143795.aud

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

ORACLE_HOME = / u01/app/oracle/product/11.2.0/db_1

System name: Linux

Node name: wang

Release: 3.10.0-327.el7.x86_64

Version: # 1 SMP Thu Oct 29 17:29:29 EDT 2015

Machine: x86_64

Instance name: DBdb

Redo thread mounted by this instance: 1

Oracle process number: 29

Unix process pid: 27722, image: oracle@wang (TNS V1-V3)

Fri Jan 26 00:28:16 2018 + 08:00

LENGTH: 266

SESSIONID: [7] "7450116" ENTRYID: [1] "1" STATEMENT: [1] "1" USERID: [5] "SCOTT" USERHOST: [4] "wang" TERMINAL: [5] "pts/1" ACTION: [3] "100" RETURNCODE: [1]" 0 "COMMENT$TEXT:

[26] "Authenticated by: DATABASE" OS$USERID: [6] "oracle" DBID: [10] "3282897732" PRIV$USED: [1] "5"

[oracle@wang adump] $

Experiments show that the os layer does not record database-related operations, only some log-in and logout database operations experiment 2:

SQL > show parameter audit

NAME TYPE VALUE

-

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

Audit_sys_operations boolean FALSE

Audit_syslog_level string

Audit_trail string OS

SQL >

SQL > alter system set audit_trail='DB' scope=spfile

System altered.

SQL > shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL >

SQL > startup

ORACLE instance started.

Total System Global Area 835104768 bytes

Fixed Size 2257840 bytes

Variable Size 549456976 bytes

Database Buffers 281018368 bytes

Redo Buffers 2371584 bytes

Database mounted.

Database opened.

SQL >

SQL > show parameter audit

NAME TYPE VALUE

-

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

Audit_sys_operations boolean FALSE

Audit_syslog_level string

Audit_trail string DB

SQL >

SQL >

-- confirm that the audit-related tables have been installed

Select * from sys.aud$;-No record is returned

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.

@ $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.

-- query the audit table aud$:

SQL > col owner for A10

SQL > col table_name for A15

SQL > col TABLESPACE_NAME for A15

SQL > alter session set nls_date_format='yyyy-mm-dd hh34:mi:ss'

Session altered.

SQL > select OWNER,TABLE_NAME,TABLESPACE_NAME,STATUS,LAST_ANALYZED from dba_tables where table_name='AUD$'

OWNER TABLE_NAME TABLESPACE_NAME STATUS LAST_ANALYZED

--

SYS AUD$ SYSTEM VALID 2018-01-24 22:01:31

SQL > col segment_name for A15

SQL > select OWNER,SEGMENT_NAME,PARTITION_NAME,TABLESPACE_NAME,BYTES/1024 size_k from dba_segments where SEGMENT_NAME='AUD$'

OWNER SEGMENT_NAME PARTITION_NAME TABLESPACE_NAME SIZE_K

-

SYS AUD$ SYSTEM 128

SQL > conn scott/tiger

Connected.

SQL > create table temp as select * from user_objects

Table created.

SQL > insert into temp select * from temp

10 rows created.

SQL > commit

Commit complete.

SQL > update temp set object_name='WANG' where object_id=10

0 rows updated.

SQL > update temp set object_name='WANG' where object_id=87107

2 rows updated.

SQL > commit

Commit complete.

SQL > delete temp where rownum commit

Commit complete.

SQL > truncate table temp

Table truncated.

SQL > drop table temp purge

Table dropped.

-- query audit table aud$:

SQL > select os_username

Username

Obj_name

Action_name

Audit_option

Logoff_time

Sessionid

Os_process

Instance_number

Sql_text

Sql_bind

From dba_audit_trail

Where sql_text like'% TEMP%'

No rows selected

Experiment 3: specify the update audit of the t table

Log in to the scott user, create the t table and turn on the update audit, use the by access clause, and audit each update

SQL > conn scott/tiger

Connected.

SQL > create table t (x int)

Table created.

SQL > insert into t values (9)

1 row created.

SQL > commit

Commit complete. -- access level audit of t table under scott user

SQL > audit update on t by access

Audit succeeded.

Indicates that each update operation of the t table is audited (by access, all operations that conform to the audit are audited; by session, only one operation of the same type is audited in a session)-- update testing using sql bound variables

SQL > var v_num number

SQL > exec: v_num num _ v_num _ 1000; (: bind _ numril = assign a value to the bind variable)

PL/SQL procedure successfully completed.

SQL > update t set x=:v_num

1 row updated.

SQL > commit

Commit complete.

-- close the audit

SQL > noaudit update on t

Noaudit succeeded.

-- query audit results

Set lines 200

Col OS_USERNAME for a10

Col USERNAME for a10

Col OBJ_NAME for a10

Col SQL_BIND for a10

Col SQL_TEXT for a10

Col OWNER for a10

Col ACTION_NAME for a10

Alter session set nls_date_format='yyyy-mm-dd hh34:mi:ss'

Select os_username

Username

Timestamp

Owner

Obj_name

Action_name

Sessionid

Instance_number

Os_process

Transactionid

Sql_bind

Sql_text

From dba_audit_trail

Where sql_text like'% T%'

The results show that when DB-level audit is enabled, a table or session (by access or by session) must be specified before the corresponding operation will be audited and recorded in the dba_audit_trail table.

This is the end of the content of "what is the audit Audit method". Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.