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

This article mainly introduces "what is Oracle12.2c unified audit". In daily operation, I believe many people have doubts about what Oracle12.2c unified audit is. The editor consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful for you to answer the doubts about "what is Oracle12.2c unified audit?" Next, please follow the editor to study!

What is unified audit?

Audit refers to monitoring and recording all successful or failed operations performed by users on the database.

Unified Audit (UNIFIED_AUDIT_TRAIL) is a brand-new audit system introduced by Oracle in version 12c. Under the new system, Oracle provides more refined management, and all audit records are written to AUDSYS schema in a unified format, which can be viewed through the UNIFIED_AUDIT_TRAIL view. By default, audit records are stored in the SYSAUX tablespace. Of course, Oracle also recommends that the audit data be stripped out of the SYSAUX and placed under a new tablespace, which can be achieved through the DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION package.

What are the relevant authority for unified audit?

Except for SYS users, only users assigned to AUDIT_ADMIN or AUDIT_VIEWER can see the UNIFIED_AUDIT_TRAIL data dictionary view. If the user only needs to query the UNIFIED_AUDIT_TRAIL, but does not need to create an audit policy (audit policy), then just assign it to the AUDIT_VIEWER role.

How to check whether the unified audit is on?

SQL > SELECT VALUE FROM V$OPTION WHERE PARAMETER = 'Unified Auditing'

VALUE

FALSE

TRUE: open on behalf of fully unified audit

FALSE: represents the mixed mode of traditional audit and unified audit.

What is the fully unified audit model (Pure unified auditing) and the mixed audit model (Mixed Mode Auditing)?

Completely unify the audit model, as the name implies, is to abandon the previous traditional audit model.

Disable the unified audit method, taking a stand-alone machine as an example:

Shutdown

Cd $ORACLE_HOME/rdbms/lib

Make-f ins_rdbms.mk uniaud_off ioracle

Startup

The mixed audit mode, as long as the unified audit strategy is opened, will form the situation of the coexistence of unified audit and traditional audit, which is the mixed audit mode. ORA_SECURECONFIG, a unified audit policy, is enabled by default when building a database, which means that when building a database by default, it is a mixed audit mode.

Details of ORA_SECURECONFIG are as follows:

CREATE AUDIT POLICY ORA_SECURECONFIG

PRIVILEGES ALTER ANY TABLE, CREATE ANY TABLE, DROP ANY TABLE

CREATE ANY PROCEDURE, DROP ANY PROCEDURE, ALTER ANY PROCEDURE

GRANT ANY PRIVILEGE, GRANT ANY OBJECT PRIVILEGE, GRANT ANY ROLE

AUDIT SYSTEM, CREATE EXTERNAL JOB, CREATE ANY JOB

CREATE ANY LIBRARY

EXEMPT ACCESS POLICY

CREATE USER, DROP USER

ALTER DATABASE, ALTER SYSTEM

CREATE PUBLIC SYNONYM, DROP PUBLIC SYNONYM

CREATE SQL TRANSLATION PROFILE, CREATE ANY SQL TRANSLATION PROFILE

DROP ANY SQL TRANSLATION PROFILE, ALTER ANY SQL TRANSLATION PROFILE

TRANSLATE ANY SQL

EXEMPT REDACTION POLICY

PURGE DBA_RECYCLEBIN, LOGMINING

ADMINISTER KEY MANAGEMENT

ACTIONS ALTER USER, CREATE ROLE, ALTER ROLE, DROP ROLE

SET ROLE, CREATE PROFILE, ALTER PROFILE

DROP PROFILE, CREATE DATABASE LINK

ALTER DATABASE LINK, DROP DATABASE LINK

CREATE DIRECTORY, DROP DIRECTORY

CREATE PLUGGABLE DATABASE

DROP PLUGGABLE DATABASE

ALTER PLUGGABLE DATABASE

EXECUTE ON DBMS_RLS

What is the strategy of closing the audit data?

There are two ways to write audit data to a data file:

1. Immediate-write mode writes immediately

2. Queued-write mode queue write

Writing immediately is to write the generated audit data to the hard disk immediately, which ensures that the audit data will not be lost, but it will have some impact on the system.

Queue write is to write audit data to SGA first, not directly to data file, and then write audit data to data file according to certain policies. There are generally two strategies:

L time strategy: every three seconds, depending on how busy the system is, it may not be written for three seconds. It is controlled by the implicit parameter _ unified_audit_flush_interval.

L space policy: the capacity of audit data stored in SGA reaches 85%. This area defaults to 1m, which is controlled by the parameter unified_audit_sga_queue_size and the scale is controlled by the implicit parameter _ unified_audit_flush_threshold.

SQL > col name for A30

SQL > col value for A10

SQL > select * from

2 (select

3 x.ksppinm name

4 y.ksppstvl value

5 y.ksppstdf isdefault

6 decode (bitand (y.ksppstvf 7), 1 ismod, 4 ismod

7 decode (bitand (y.ksppstvfj2), 2 isadj

8 from

9 sys.x$ksppi x

10 sys.x$ksppcv y

11 where

12 x.inst_id = userenv ('Instance') and

13 y.inst_id = userenv ('Instance') and

14 x.indx = y.indx

15 order by

16 translate (x.ksppinm,'_',') T where T.name like'% unified_audit%'

NAME VALUE ISDEFAULT ISMOD ISADJ

_ unified_audit_flush_interval 3 TRUE FALSE FALSE

_ unified_audit_flush_threshold 85 TRUE FALSE FALSE

_ unified_audit_policy_disabled FALSE TRUE FALSE FALSE

Unified_audit_sga_queue_size 1048576 TRUE FALSE FALSE

Current closing mode

SQL > SELECT PARAMETER_VALUE

2 FROM DBA_AUDIT_MGMT_CONFIG_PARAMS

3 WHERE PARAMETER_NAME = 'AUDIT WRITE MODE'

PARAMETER_VALUE

-

QUEUED WRITE MODE

Change to immediate-write mode

BEGIN

DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY (

DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED

DBMS_AUDIT_MGMT.AUDIT_TRAIL_WRITE_MODE

DBMS_AUDIT_MGMT.AUDIT_TRAIL_IMMEDIATE_WRITE)

END

/

Modify to

BEGIN

DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY (

DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED

DBMS_AUDIT_MGMT.AUDIT_TRAIL_WRITE_MODE

DBMS_AUDIT_MGMT.AUDIT_TRAIL_QUEUED_WRITE)

END

/

How to manually flush audit data in queue write mode?

Manually refresh the current instance, which is suitable for stand-alone and rac single instances

EXEC DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL

EXEC DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL (DBMS_AUDIT_MGMT.FLUSH_CURRENT_INSTANCE)

All instances of Flush RAC

EXEC DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL (DBMS_AUDIT_MGMT.FLUSH_ALL_INSTANCES)

For a multi-tenant environment

Current PDB

BEGIN

DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL (

CONTAINER = > DBMS_AUDIT_MGMT.CONTAINER_CURRENT)

END

/

All PDB environments

BEGIN

DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL (

CONTAINER = > DBMS_AUDIT_MGMT.CONTAINER_ALL)

END

/

How to write the audit data on the operating system into the database unified audit file?

When the database mount state, or read only, or shuts down, Oracle stores the audit data in the operating system's $ORACLE_BASE/audit/$ORACLE_SID directory.

View the current operating system

Oracle@bd-dev-mingshuo-183:/opt/app/oracle/audit/mingdb/70A2182C94E1412DE053B7D91FAC647A$ll

Total 32

-rw- 1 oracle oinstall 30720 Jul 10 16:57 ora_audit_0786.bin

Log in to the database for execution

EXEC DBMS_AUDIT_MGMT.LOAD_UNIFIED_AUDIT_FILES

After the command is completed, the operating system file disappears.

Each PDB will have a uuid directory with the same name, and if you want to import pdb data into the database, you need to execute the above command in pdb.

Two ways to delete audit? 1. Automatic cleanup task

Set cleanup time point

BEGIN

DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP (

AUDIT_TRAIL_TYPE = > DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED

LAST_ARCHIVE_TIME = > '12-OCT-2013 06VR 30RU 00.00'

RAC_INSTANCE_NUMBER = > 1

CONTAINER = > DBMS_AUDIT_MGMT.CONTAINER_CURRENT)

END

/

AUDIT_TRAIL_TYPE: specifies the audit data type

L DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED: unified audit data

L DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD:AUD$ table

L DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD:FGA_LOG$ table

L DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS: the file that ends with .aud on the operating system

L DBMS_AUDIT_MGMT.AUDIT_TRAIL_XML:XML file

LAST_ARCHIVE_TIME: specify a time, which can be checked from DBA_AUDIT_MGMT_LAST_ARCH_TS after setting it. The significance of setting a time point is that archived data before this point in time will be cleaned up.

Parameters related to RAC_INSTANCE_NUMBER:RAC database. Single instance does not need to be set up. The default value is null. Because the RAC database also has only one AUD$ table and FGA_LOG$ table

So even for the RAC database, the corresponding audit types DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD and DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD, you don't have to set this value.

CONTAINER: suitable for multi-tenant environment

L DBMS_AUDIT_MGMT.CONTAINER_CURRENT: specifies the current PDB

L DBMS_AUDIT_MGMT.CONTAINER_ALL: all PDB

When the above settings are complete, you can use the DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL package to clean up the audit data before the set point in time.

BEGIN

DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL (

Audit_trail_type = > DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED

Use_last_arch_timestamp = > TRUE)

END

/

Create a cleanup job

BEGIN

DBMS_AUDIT_MGMT.CREATE_PURGE_JOB (

AUDIT_TRAIL_TYPE = > DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED

AUDIT_TRAIL_PURGE_INTERVAL = > 12

AUDIT_TRAIL_PURGE_NAME = > 'Audit_Trail_PJ'

USE_LAST_ARCH_TIMESTAMP = > TRUE

CONTAINER = > DBMS_AUDIT_MGMT.CONTAINER_CURRENT)

END

/

AUDIT_TRAIL_TYPE: specifies the audit data type

L DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED: unified audit data

L DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD: for AUD$ table

L DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD: for FGA_LOG$ table

L DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD: for AUD$ and FGA_LOG$ tables

L DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS: the file that ends with .aud on the operating system

L DBMS_AUDIT_MGMT.AUDIT_TRAIL_XML:XML file

L DBMS_AUDIT_MGMT.AUDIT_TRAIL_FILES: files ending with .aud and XML files on the operating system

L DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL: all types of audit data mentioned above

How often does AUDIT_TRAIL_PURGE_INTERVAL:job run, in hours. If you want to change this value later, you can use the DBMS_AUDIT_MGMT.SET_PURGE_JOB_INTERVAL package.

USE_LAST_ARCH_TIMESTAMP: time option for cleaning up data

L TRUE: clean up the point in time up to the last archive

L FLASE: delete all data

CONTAINER: suitable for multi-tenant environment

L DBMS_AUDIT_MGMT.CONTAINER_CURRENT:PDB level

L DBMS_AUDIT_MGMT.CONTAINER_ALL:CDB and PDB level

After setting up the job, start

BEGIN

DBMS_AUDIT_MGMT.SET_PURGE_JOB_STATUS (

AUDIT_TRAIL_PURGE_NAME = > 'Audit_Trail_PJ'

AUDIT_TRAIL_STATUS_VALUE = > DBMS_AUDIT_MGMT.PURGE_JOB_ENABLE)

END

/

The name of the AUDIT_TRAIL_PURGE_NAME:purge job policy is specified in the CREATE_PURGE_JOB above.

AUDIT_TRAIL_STATUS_VALUE:

L DBMS_AUDIT_MGMT.PURGE_JOB_ENABLE: enable

L DBMS_AUDIT_MGMT.PURGE_JOB_DISABLE: stop

Modify job run interval

BEGIN

DBMS_AUDIT_MGMT.SET_PURGE_JOB_INTERVAL (

AUDIT_TRAIL_PURGE_NAME = > 'Audit_Trail_PJ'

AUDIT_TRAIL_INTERVAL_VALUE = > 24)

END

/

Delete cleanup job

BEGIN

DBMS_AUDIT_MGMT.DROP_PURGE_JOB (

AUDIT_TRAIL_PURGE_NAME = > 'Audit_Trail_PJ')

END

/

two。 Manual clean-up audit

BEGIN

DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL (

AUDIT_TRAIL_TYPE = > DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED

USE_LAST_ARCH_TIMESTAMP = > TRUE

CONTAINER = > DBMS_AUDIT_MGMT.CONTAINER_CURRENT)

END

/

The meaning of each parameter is the same as above.

Unified audit production actual combat configuration?

In order to prevent too much audit data, it is necessary to clean up the audit data regularly.

Mobile audit tablespace

BEGIN

DBMS_AUDIT_MGMT.set_audit_trail_location (

Audit_trail_type = > DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD

Audit_trail_location_value = > 'TBS_MING')

END

/

Set up purge job

BEGIN

DBMS_AUDIT_MGMT.CREATE_PURGE_JOB (

AUDIT_TRAIL_TYPE = > DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED

AUDIT_TRAIL_PURGE_INTERVAL = > 24

AUDIT_TRAIL_PURGE_NAME = > 'Unified_Audit_Trail_PURGEJOB'

USE_LAST_ARCH_TIMESTAMP = > TRUE)

END

/

The above job is running, according to the time of the SET_LAST_ARCHIVE_TIMESTAMP, the following job can manually refresh this time, otherwise the above job can only be run once at most.

BEGIN

DBMS_SCHEDULER.create_job (

Job_name = > 'audit_last_archive_time'

Job_type = > 'PLSQL_BLOCK'

Job_action = > 'BEGIN

DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP (DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED, TRUNC (SYSTIMESTAMP)-60)

END;'

Start_date = > SYSTIMESTAMP

Repeat_interval = > 'freq=daily; byhour=0; byminute=0; bysecond=0;'

End_date = > NULL

Enabled = > TRUE

Comments = > 'Automatically set audit last archive time.')

END

/

At this point, the study of "what is the unified audit of Oracle12.2c" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.