In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
2025-04-02 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >
Share
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.
Views: 0
*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.
Continue with the installation of the previous hadoop.First, install zookooper1. Decompress zookoope
"Every 5-10 years, there's a rare product, a really special, very unusual product that's the most un
© 2024 shulou.com SLNews company. All rights reserved.