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

The following operations are all based on

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0-64bit Production

Analysis of audit parameters

AUDIT_TRAIL enables or disables database auditing. When this parameter is set to NONE or FALSE, database audit is disabled; when this parameter is set to OS, database audit is activated and audit records are written to the OS audit trail file; when this parameter is set to DB or TRUE, database audit is activated and audit records are written to the data dictionary SYS.AUD$ When this parameter is set to DB_EXTENDED, the audit records are not only written to the data dictionary SYS.AUD$, but also the SQLBIND and SQLTEXT columns of the data dictionary are populated.

Value description:

None disables database auditing

Os enables database auditing and directs database audit records to operating system audit records

Db enables database auditing and directs all database audit records to the database's SYS.AUD$ table

Db,extended enables database auditing and directs all database audit records to the database's SYS.AUD$ table. In addition, populate the SQLBIND column and SQLTEXT CLOB column of the SYS.AUD$ table.

Xml enables database auditing and writes all records to operating system files in XML format.

Xml,extended enables database auditing and outputs all columns of the audit record, including the values of SqlText and SqlBind.

two。 Query current database audit parameters

Query the current audit parameters SQL > show parameter audit_trail

Change the current audit parameter to DB_EXTENDED (so that you can query the specific sql executed)

① executes SQL > ALTER SYSTEM SET audit_trail=DB,EXTENDED SCOPE=SPFILE

Log in to the database host with ②, switch to oracle users and log in to users with sysdba

[oracle@localhost ~] $sqlplus / as sysdba

③ executes the following statement to restart the database SQL > shutdown immediate; SQL > startup

④ queries audit parameters SQL > show parameter audit_trail again

1. Cancel the audit

Noaudit insert,update,delete on testuser.tables

Testuser user mode

Tables table name

-add audit

Audit create table,update table,insert table,delete table by testuser by access

Audit execute procedure by testuser by access

3. Test whether the audit is turned on

① executes SQL > select * from dba_audit_trail; to check if there is appropriate audit information.

Third, migrate the tablespace

Migrate audit information to non-system tablespaces to prevent system tablespaces from bursting.

Let's first create a table space called audit_data to store data.

CREATE TABLESPACE audit_data LOGGING DATAFILE'/ data01/oracle/oradata/AUTODB/datafile/audit_data.dbf' SIZE 10240M AUTOEXTEND ON NEXT 100m MAXSIZE 12000m

Migrate tables to a new tablespace

SQL > alter table audit$ move tablespace audit_data

SQL > alter index i_audit rebuild online tablespace audit_data

SQL > alter table audit_actions move tablespace audit_data

SQL > alter index i_audit_actions rebuild online tablespace audit_data

SQL > alter table AUD$ move tablespace audit_data

Related SQL for querying whether the tablespace was migrated successfully

SQL > SELECT table_name, tablespace_name FROM dba_tables WHERE table_name IN ('AUD$',' FGA_LOG$','AUDIT$','I_AUDIT','AUDIT_ACTIONS,'I_AUDIT_ACTIONS') ORDER BY table_name

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.