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

The main content of this article is "introduction to the use of oracle audit function". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Now let the editor take you to learn "introduction to the use of oracle audit function"!

1. Start the audit:

Show parameter audit

Alter system set audit_trail = DB_EXTENDED;-detailed audit

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

DB: put the audit results in a database table, and aud$, usually only records the connection information.

DB_EXTENDED: put the audit results in the database table and record the specific execution statements

OS: record the audit results in the operating system file, and the file location is specified by the audit_file_dest parameter. (in the windows system, it will be recorded directly in the system log of the event Viewer)

XML: record the audit results in the XML file under the location specified by audit_file_dest.

2. Restart the database instance

Startup force

3. Specify the audit content

1. Audit the table: the create,alter of the table can be audited separately. If you want to audit the drop operation, you need to add audit table to the table (this command includes create table,drop table,truncate table).

two。 Audit the view: the create of the view can be audited separately. If you want to audit the drop operation, you need to add audit view to the view (this command includes create view,drop view).

3. Audit of packages: you can audit the create of packages (functions, stored procedures, etc.) and add audit procedure if you need to audit drop operations (this command audits CREATE FUNCTION, CREATE LIBRARY, CREATE PACKAGE, CREATE PACKAGE BODY, CREATE PROCEDURE, DROP FUNCTION, DROP LIBRARY, DROP PACKAGE, DROP PROCEDURE)

4. Audit of users: you can audit through audit user (this command includes create user,alter user,drop user)

For example, to audit all operations of the scott account

Audit all by scott

4. Check what content is audited.

Select * from dba_priv_audit_opts-- where owner_name='SCOTT'

Select * from dba_stmt_audit_opts

Select * from dba_obj_audit_opts

1. Audit the table: the create,alter of the table can be audited separately. If you want to audit the drop operation, you need to add audit table to the table (this command includes create table,drop table,truncate table).

two。 Audit the view: the create of the view can be audited separately. If you want to audit the drop operation, you need to add audit view to the view (this command includes create view,drop view).

3. Audit of packages: you can audit the create of packages (functions, stored procedures, etc.) and add audit procedure if you need to audit drop operations (this command audits CREATE FUNCTION, CREATE LIBRARY, CREATE PACKAGE, CREATE PACKAGE BODY, CREATE PROCEDURE, DROP FUNCTION, DROP LIBRARY, DROP PACKAGE, DROP PROCEDURE)

4. Audit of users: you can audit through audit user (this command includes create user,alter user,drop user)

DBA_AUDIT_TRAIL lists all audit trail entries

The USER_AUDIT_TRAIL USER view displays audit trail entries related to the current user

5. View audit information

Select * from dba_audit_trail where owner = 'SCOTT' order by timestamp

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)

6. Cancel the audit

Cancel all is

Noaudit all

If you cancel it alone, you can view it according to the above 4 items and have the above permissions to directly noaudit.

Such as: noaudit create session by user scott

To cancel all audits of the SC table, you can use the following statement:

NOAUDIT ALL ON SC

Noaudit canceling the audit is of no use to the session that is already connected, but the new session will no longer be audited.

-- cancel all statement audits

NOAUDIT ALL

-- cancel all permission audits

NOAUDIT ALL PRIVILEGES

-- cancel all object audits

NOAUDIT ALL ON DEFAULT

7, close the audit

SQL > alter system set audit_trail=none scope=spfile

.audit _ sys_operations: whether to audit sysdba users. For the audit results of sysdba users, linux is stored in the aud file at the location specified by the audit_file_dest parameter, and windows is stored in the system log of the event viewer.

Question 1:

Confirm that the audit-related tables have been installed

SQLPLUS > connect / AS SYSDBA

SQLPLUS > select * from sys.aud$;-No record is returned

SQLPLUS > 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.

SQLPLUS > connect / as sysdba

SQLPLUS > @ $ORACLE_HOME/rdbms/admin/cataudit.sql

Question 2:

Move the audit-related tables to another tablespace:

Because audit-related tables such as AUD$ tables are stored in SYSTEM tablespaces, in order not to affect system performance and protect SYSTEM tablespaces, it is best to move AUD$ to other tablespaces. You can use the following statement to move:

Sql > connect / as sysdba

Sql > alter table aud$ move tablespace

Sql > alter index I_aud1 rebuild online tablespace

SQL > alter table audit$ move tablespace

SQL > alter index i_audit rebuild online tablespace

SQL > alter table audit_actions move tablespace

SQL > alter index i_audit_actions rebuild online tablespace

Question 3:

Audit information retention time of audit

(always reserved by default)

Manual cleanup:

How to truncate or delete rows from audit trail table sys.aud$

1) Only appropriate privileged user can do delete operation on SYS.AUD$ table. The user must have either of the following privileges.

-SYS user.

-DELETE ANY TABLE system privilege. (If O7_DICTIONARY_ACCESSIBILITY=TRUE)

-A user to whom SYS has granted the object privilege DELETE on SYS.AUD$ table.

2) Before deleting any rows you may want to archive the table. You can achive this by creating a table from SYS.AUD$ and export that. Don't export SYS.AUD$ directly.

SQL > CREATE TABLE AUDIT_RECORD TABLESPACE users as select * from SYS.AUD$

Now export the table as

SQL > host exp tables=AUDIT_RECORD file=audit_record.dmp

3) To delete all records from audit trail table SYS.AUD$ issue

SQL > DELETE FROM SYS.AUD$

To delete all records of particular audited table from the audit trail issue

SQL > DELETE FROM sys.aud$ WHERE obj$name='&table_nmae'

But deleting in this way will not reduce size on the system tablespace or aud$ table. In order to reduce size follow section 4.

4) Truncate audit table to reduce size.

SQL > CONN / as sysdba

SQL > TRUNCATE TABLE SYS.AUD$

Automatic cleanup:

1. Create a clean aud$ table procedure:

Create or replace procedure clear_aud

As

Begin

Delete aud$ where ntimestamp#

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.