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

Oracle standard database audit can audit and track various permission operations of ordinary users (excluding SYS).

I. the basic methods of standard database audit

1. Enable standard database audit

The initialization parameter audit_trail is a static parameter that determines how auditing is enabled, and different values indicate whether auditing is enabled and how auditing is recorded.

This parameter can be set to the following values:

None or flase (10g is the default): no audit

Db or true (11g is the default): audit results are recorded in the database table sys.aud$, can view the results through the view dba_audit_trail

Os: audit results are recorded in the operating system file, Unix is specified in the audit_file_dest parameter, and Windows is in the application log (event viewer eventvwr)

Db_extended: roughly the same as db, but the audit result contains SQL statements with bound variables

Xml: roughly the same as os, but marked with xml

Xml_extended: roughly the same as xml, but the audit result contains a SQL statement with binding variables.

2. Specify audit options

Database auditing can be configured using the audit command, and standard database auditing includes the following categories:

1) system permission audit

Actions that audit system permissions, such as

Audit create any table

Audit create any trigger

Audit a user's system permission actions, such as

Audit select any table by scott

No audit will be made when you access your own table.

Audit user creation and deletion

Audit create user, drop user

Check the enabled system permission audit. The following audit is enabled by default through the data dictionary dba_priv_audit_opts,11g.

Col user_name for a20

Col proxy_name for a20

Col privilege for a30

Col success for a20

Col failure for a20

Select * from dba_priv_audit_opts

USER_NAME PROXY_NAME PRIVILEGE SUCCESS FAILURE

-

CREATE EXTERNAL JOB BY ACCESS BY ACCESS

CREATE ANY JOB BY ACCESS BY ACCESS

GRANT ANY OBJECT PRIVILEGE BY ACCESS BY ACCESS

EXEMPT ACCESS POLICY BY ACCESS BY ACCESS

CREATE ANY LIBRARY BY ACCESS BY ACCESS

GRANT ANY PRIVILEGE BY ACCESS BY ACCESS

DROP PROFILE BY ACCESS BY ACCESS

ALTER PROFILE BY ACCESS BY ACCESS

DROP ANY PROCEDURE BY ACCESS BY ACCESS

ALTER ANY PROCEDURE BY ACCESS BY ACCESS

CREATE ANY PROCEDURE BY ACCESS BY ACCESS

ALTER DATABASE BY ACCESS BY ACCESS

GRANT ANY ROLE BY ACCESS BY ACCESS

CREATE PUBLIC DATABASE LINK BY ACCESS BY ACCESS

DROP ANY TABLE BY ACCESS BY ACCESS

ALTER ANY TABLE BY ACCESS BY ACCESS

CREATE ANY TABLE BY ACCESS BY ACCESS

DROP USER BY ACCESS BY ACCESS

ALTER USER BY ACCESS BY ACCESS

CREATE USER BY ACCESS BY ACCESS

CREATE SESSION BY ACCESS BY ACCESS

AUDIT SYSTEM BY ACCESS BY ACCESS

ALTER SYSTEM BY ACCESS BY ACCESS

2) object permission audit

For all users (excluding sys,sys), it is not audited, such as

Aduit alter, delete, drop, insert on scott.emp

For a user, such as

Audit select on hr.employees by scott

For all operations, such as

Audit all on hr.employees

Check the enabled object audit, and pass the data dictionary dba_obj_audit_opts. By default, none of them is enabled.

Select * from dba_obj_audit_opts

OWNER OBJECT_NAME OBJECT_TYPE ALT AUD COM DEL GRA IND INS LOC REN SEL UPD REF EXE CRE REA WRI FBK

-

3) statement review

For example, all DDL operations of the audit table

Audit table

Check the statement audit that is enabled. Through the data dictionary dba_stmt_audit_opts,11g, the following audit is enabled by default, which also includes the above audit that belongs to the system permissions.

Col user_name for a20

Col proxy_name for a20

Col audit_option for a30

Col success for a20

Col failure for a20

Select * from dba_stmt_audit_opts

USER_NAME PROXY_NAME AUDIT_OPTION SUCCESS FAILURE

-

ALTER SYSTEM BY ACCESS BY ACCESS

SYSTEM AUDIT BY ACCESS BY ACCESS

CREATE SESSION BY ACCESS BY ACCESS

CREATE USER BY ACCESS BY ACCESS

ALTER USER BY ACCESS BY ACCESS

DROP USER BY ACCESS BY ACCESS

PUBLIC SYNONYM BY ACCESS BY ACCESS

DATABASE LINK BY ACCESS BY ACCESS

ROLE BY ACCESS BY ACCESS

PROFILE BY ACCESS BY ACCESS

CREATE ANY TABLE BY ACCESS BY ACCESS

ALTER ANY TABLE BY ACCESS BY ACCESS

DROP ANY TABLE BY ACCESS BY ACCESS

CREATE PUBLIC DATABASE LINK BY ACCESS BY ACCESS

GRANT ANY ROLE BY ACCESS BY ACCESS

SYSTEM GRANT BY ACCESS BY ACCESS

ALTER DATABASE BY ACCESS BY ACCESS

CREATE ANY PROCEDURE BY ACCESS BY ACCESS

ALTER ANY PROCEDURE BY ACCESS BY ACCESS

DROP ANY PROCEDURE BY ACCESS BY ACCESS

ALTER PROFILE BY ACCESS BY ACCESS

DROP PROFILE BY ACCESS BY ACCESS

GRANT ANY PRIVILEGE BY ACCESS BY ACCESS

CREATE ANY LIBRARY BY ACCESS BY ACCESS

EXEMPT ACCESS POLICY BY ACCESS BY ACCESS

GRANT ANY OBJECT PRIVILEGE BY ACCESS BY ACCESS

CREATE ANY JOB BY ACCESS BY ACCESS

CREATE EXTERNAL JOB BY ACCESS BY ACCESS

4) other audit configurations

Audit session login

Audit session

This is the same as auditing the use of create session permissions.

Cancel the audit and specify through the noaudit command

Noaudit session

Noaudit all on scott.emp

Audit successful actions specified by the whenever successful option, such as the successful insertion of the audit table

Audit insert on scott.emp whenever successful

Audit unsuccessful operations, specified by the whenever not successful option, such as audit failed session logins

Audit session whenever not successful

By default, all actions are reviewed, whether successful or not.

Audit at the session level, specified by the by session option

Audit update on scott.emp by session

Audit at the operation level, specified by the by access option

Audit update on scott.emp by access

Object permission audit defaults to by session

System permission audit defaults to by access

3. View audit records

If the audit is against a database (audit_trail=db or db_extended), the audit record is written to the data dictionary table sys.aud$, and although it can be viewed directly, it is more convenient to view it through the view established on it.

The common view is dba_audit_trail, and its common columns are explained as follows:

Os_username: operating system user name of the user performing the operation

Username: the Oracle user name of the user who performed the operation

Userhost: the name of the computer on which the user process is running

Timestamp: the time when the audit event occurred

Owner,obj_name: the mode and name of the affected object

Action,action_name: audit operation. The operation code action can be looked up in the data dictionary table audit_actions.

Priv_used: system permissions used

Sql_text: statements executed

If you do not have table aud$ and view dba_audit_trail, you need to execute the installation script for audit-related data dictionary tables, and restart the database after installation. The installation script is located at

% ORACLE_HOME%\ rdbms\ admin\ cataudit.sql

Other audit views show a subset of the dba_audit_trail view:

Dba_audit_object

Dba_audit_statement

Dba_audit_session

Second, the standard database audit experiment

1. Create an experimental table

Create table scott.emp1 as select * from scott.emp

Grant all on scott.emp1 to hr

2. Enable auditing

Audit all on scott.emp1 by access

Audit table

Alter system set audit_sys_operations=true scope=spfile

Alter system set audit_trail='db_extended' scope=spfile

Restart the database instance

Shutdown immediate

Startup

All records in the audit result table can be cleared before the experiment.

Truncate table sys.aud$

3. Conduct sysdba activities and view the audit results

Log in as sysdba and operate

Select * from dba_users

Select * from scott.emp1

Create user audr1 identified by audr1

Drop user audr1

Create table scott.emp2 as select * from scott.emp1

Select * from scott.emp2

Drop table scott.emp2 purge

Check the audit results of sysdba. Since sysdba auditing is enabled, you can see all operation records in operating system files and logs. Unix looks at the target file specified by audit_file_dest, and Windows views the application log through the event viewer eventvwr. In addition, you can see that the actions of the SYS administrator are not recorded in the aud$, and the view dba_audit_trail has no related record.

4. Log in to the operation with the system user and view the audit result

Select * from scott.emp

Select * from scott.emp1

Create user audr1 identified by audr1

Grant connect to audr1

Create table audr1.t1 (n number)

Select * from audr1.t1

Drop table audr1.t1 purge

Drop user audr1

Log out of system

View audit results

Col os_username for a20

Col username for a20

Col userhost for a20

Col owner for a10

Col obj_name for a20

Col action_name for a20

Col priv_used for a20

Col sql_text for a50

Select os_username, username, userhost, timestamp, owner, obj_name, action, action_name, priv_used, sql_text from dba_audit_trail order by timestamp desc

OS_USERNAME USERNAME USERHOST TIMESTAMP OWNER OBJ_NAME ACTION ACTION_NAME PRIV_USED SQL_TEXT

Administrator SYSTEM WORKGROUP\ MYPC 2017-09-20 10:36:05 101 LOGOFF

Administrator SYSTEM WORKGROUP\ MYPC 2017-09-20 10:35:59 AUDR1 53 DROP USER DROP USER drop user audr1

Administrator SYSTEM WORKGROUP\ MYPC 2017-09-20 10:35:49 AUDR1 T1 12 DROP TABLE DROP ANY TABLE drop table audr1.t1 purge

Administrator SYSTEM WORKGROUP\ MYPC 2017-09-20 10:35:32 AUDR1 T1 1 CREATE TABLE CREATE ANY TABLE create table audr1.t1 (n number)

Administrator SYSTEM WORKGROUP\ MYPC 2017-09-20 10:35:24 CONNECT 114 GRANT ROLE GRANT ANY ROLE grant connect to audr1

Administrator SYSTEM WORKGROUP\ MYPC 2017-09-20 10:35:14 AUDR1 51 CREATE USER CREATE USER create user audr1 identified by *

Administrator SYSTEM WORKGROUP\ MYPC 2017-09-20 10:35:04 SCOTT EMP1 3 SELECT SELECT ANY TABLE select * from scott.emp1

Administrator SYSTEM WORKGROUP\ MYPC 2017-09-20 10:34:33 100 LOGON CREATE SESSION

Since no audit is added to the table scott.emp, the query to it is not counted in the table, and the login and logout of the session, table creation, table deletion and authorization are the system permission audit enabled by default, so these operations are recorded in the table, and the query on the table audr1.t1 will not be audited.

5. Clear the aud$ record, create a user audr1 under sys, and log in with audr1 and hr users respectively to view the audit results

Operation of sys

Create user audr1 identified by audr1

Grant connect to audr1

Audr1 user login operation

Select * from scott.emp1

The above query will fail because audr1 is not given permission to access scott.emp1

Audr1 user logout login

Hr user login operation

Select * from scott.emp1

Update scott.emp1 set sal=2000 where empno=7369

Commit

Update scott.emp1 set sal=2500 where empno=7369

Rollback

Hr user logout login

View audit records

Col os_username for a20

Col username for a20

Col userhost for a20

Col owner for a10

Col obj_name for a20

Col action_name for a20

Col priv_used for a20

Col sql_text for a50

Select os_username, username, userhost, timestamp, owner, obj_name, action, action_name, priv_used, sql_text from dba_audit_trail order by timestamp desc

OS_USERNAME USERNAME USERHOST TIMESTAMP OWNER OBJ_NAME ACTION ACTION_NAME PRIV_USED SQL_TEXT

Administrator HR WORKGROUP\ MYPC 2017-09-20 10:42:29 101 LOGOFF

Administrator HR WORKGROUP\ MYPC 2017-09-20 10:45:33 SCOTT EMP1 6 UPDATE update scott.emp1 set sal=2500 where empno=7369

Administrator HR WORKGROUP\ MYPC 2017-09-20 10:42:17 SCOTT EMP1 6 UPDATE update scott.emp1 set sal=2000 where empno=7369

Administrator HR WORKGROUP\ MYPC 2017-09-20 10:42:09 SCOTT EMP1 3 SELECT select * from scott.emp1

Administrator HR WORKGROUP\ MYPC 2017-09-20 10:41:49 100 LOGON CREATE SESSION

Administrator AUDR1 WORKGROUP\ MYPC 2017-09-20 10:41:41 101 LOGOFF

Administrator AUDR1 WORKGROUP\ MYPC 2017-09-20 10:41:29 SCOTT EMP1 3 SELECT select * from scott.emp1

Administrator AUDR1 WORKGROUP\ MYPC 2017-09-20 10:41:01 100 LOGON CREATE SESSION

Since the default is that the operation will be audited regardless of success or failure, the failed query of the audr1 user is also recorded, the commit and rollback statements are not recorded, and the update operation is always audited regardless of whether the executed statement is finally submitted or rolled back.

6. Cancel object audit

Noaudit all on scott.emp1

Hr user login operation again

Select * from scott.emp1

Hr user logout login

Check the audit record to confirm that the audit is only the login and logout of the user, and the others have been cancelled. If you want to cancel the logon and logout records of the session, execute noaudit session, but in this way, the default system permission audit will no longer include this item unless you perform audit session rejoin.

7. Clear the aud$ record, change it to session-level audit, and view the result

Audit all on scott.emp1 by session

Log in as hr user again and operate

Select * from scott.emp1

Update scott.emp1 set sal=800 where empno=7369

Commit

Hr user logout login

View audit records

Col os_username for a20

Col username for a20

Col userhost for a20

Col owner for a10

Col obj_name for a20

Col action_name for a20

Col priv_used for a20

Col sql_text for a50

Select os_username, username, userhost, timestamp, owner, obj_name, action, action_name, priv_used, sql_text from dba_audit_trail order by timestamp desc

OS_USERNAME USERNAME USERHOST TIMESTAMP OWNER OBJ_NAME ACTION ACTION_NAME PRIV_USED SQL_TEXT

Administrator HR WORKGROUP\ MYPC 2017-09-20 10:54:55 101 LOGOFF

Administrator HR WORKGROUP\ MYPC 2017-09-20 10:54:44 SCOTT EMP1 103 SESSION REC update scott.emp1 set sal=800 where empno=7369

Administrator HR WORKGROUP\ MYPC 2017-09-20 10:54:36 SCOTT EMP1 103 SESSION REC select * from scott.emp1

Administrator HR WORKGROUP\ MYPC 2017-09-20 10:54:29 100 LOGON CREATE SESSION

It can be seen that the operation-level audit clearly records that the action_name is select, update, etc., while the session-level audit action_name is only marked as session rec, but sql_text still records the SQL statement of each operation in the session.

8. Cancel the audit, clean up and restore

Noaudit all on scott.emp1

Drop user audr1

Drop table scott.emp1 purge

Alter system set audit_trail=false scope=spfile

Alter system set audit_sys_operations=false scope=spfile

Restart the database instance

Clear aud$ record

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.