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

Overview:

Database audit function is a function provided by oracle itself to record database operations.

Functional categories such as audit permission call records, user's dml operation records, query operation records, etc.

Oracle audit is divided into standard audit and fine-grained audit (FGA). Standard audit is divided into sentence audit, authority audit and mode object audit.

Value-based audit (Value-Based, trigger audit)

Fine Audit (FGA)

Audit related parameters

SYS@PROD > show parameter audit; NAME TYPE VALUE

-

Audit_file_dest string / u01/app/oracle/admin/PROD/adu

Mp

Audit_sys_operations boolean FALSE

Audit_syslog_level string

Audit_trail string DB audit_trail

AUDIT_TRAIL = {none | os | db | db,extended | xml | xml,extended} none disable database audit do not enable audit os to direct database audit records to operating system audit records

Store the audit results in the operating system file, in the location specified by audit_file_dest

Commonly used to audit the SYS.AUD$ table that sys db directs all audit records of the database to the database

Generally used to audit non-sys users

Db,extended directs all audit records of the database to the SYS.AUD$ table of the database.

Can include audit information such as binding variables, CLOB type large objects, etc.

In addition, populate the SQLBIND column and SQLTEXT CLOB column of the SYS.AUD$ table. Xml writes all records to an operating system file in XML format. Xml,extended outputs all columns of the audit record, including the values of SqlText and SqlBind.

1. Mandatory audit application: record the information of the user logging into the database, start the database and close the file storage:

SYS@PROD > show parameter audit NAME TYPE VALUE

-

Audit_file_dest string / u01/app/oracle/admin/PROD/adu

Mp

Audit_sys_operations boolean FALSE

Audit_syslog_level string

Audit_trail string DB

2. Standard audit application: by default, the behavior of specified ordinary users is monitored on the database.

Audit record audit_trail DB: audit results are stored in the data dictionary (sys can be updated)

XML: audit results are stored in xml format under the operating system

Sys user audit audit_sys_operations default false, do not enable audit for sys users

Sys audit should be set to true, but the audit results cannot be stored in DB.

Audit results

1. Audit$ base table (can be deleted)

2. Dba_audit_trail view

Standard audit does not record the specific actions of users (sql_text) [classification:]

1) statement-based audit Auditing SQL statement

Audit the actions of the specified user about table

SYS@PROD > create user kobe identified by oracle; User created. SYS@PROD > grant create session,unlimited tablespace,create table to kobe; Grant succeeded. SYS@PROD > audit table by kobe whenever successful

Audit the kobe user, when the operation on the table is successful Audit succeeded. SYS@PROD > conn scott/tiger failed to delete table under scott

Connected.

SCOTT@PROD > drop table T1 purge

Drop table t1 purge

*

ERROR at line 1:

ORA-00942: table or view does not exist

SCOTT@PROD > drop table test purge

Drop table test purge

*

ERROR at line 1:

ORA-00942: table or view does not exist

SCOTT@PROD > conn kobe/oracle

Connected.

KOBE@PROD > create table test (id number); kobe successfully built the table Table created. KOBE@PROD > insert into test values (1); 1 row created. KOBE@PROD > drop table T1; failed to delete table

Drop table t1

*

ERROR at line 1:

ORA-00942: table or view does not exist

KOBE@PROD > drop table test purge; deleted table successfully Table dropped. Query audit results under sys

SYS@PROD > select username,timestamp,obj_name,action_name from dba_audit_trail

2 where username='KOBE'; USERNAME TIMESTAMP OBJ_NAME ACTION_NAME

KOBE 31-MAY-18 LOGON

KOBE 31-MAY-18 TEST CREATE TABLE

KOBE 31-MAY-18 TEST DROP TABLE

KOBE 31-MAY-18 LOGOFF

In the final audit results, only the tables were successfully built and deleted.

Turn off audit

SYS@PROD > noaudit table by kobe; Noaudit succeeded. Delete audit results

SYS@PROD > delete from audit$; 29 rows deleted. SYS@PROD > commit; Commit complete. 2) Rights-based audit Auditing Privileges audit the select any table permissions of kobe users

SYS@PROD > grant select any table to kobe; Grant succeeded. SYS@PROD > audit select any table by kobe; Audit succeeded. SYS@PROD > conn kobe/oracle

Connected.

KOBE@PROD > select count (*) from scott.emp; COUNT (*)

-

14 KOBE@PROD > conn / as sysdba

Connected.

SYS@PROD > col owner for A10

SYS@PROD > select username,timestamp,owner,obj_name,priv_used

2 from dba_audit_trail where username='KOBE'; USERNAME TIMESTAMP OWNER OBJ_NAME PRIV_USED

KOBE 31-MAY-18 CREATE SESSION

KOBE 31-MAY-18 KOBE TEST CREATE TABLE

KOBE 31-MAY-18 KOBE TEST

KOBE 31-MAY-18

KOBE 31-MAY-18 SCOTT EMP SELECT ANY TABLE shutdown audit

SYS@PROD > noaudit select any table by kobe; Noaudit succeeded. 3) object-based audit Auditing Schema Objects application establishes user access behavior tracking SYS@PROD for important object > audit all on scott.emp; Audit succeeded. SYS@PROD > revoke select any table from kobe; Revoke succeeded. SYS@PROD > grant select,update on scott.emp to kobe; Grant succeeded. SYS@PROD > conn kobe/oracle

Connected.

KOBE@PROD > select * from scott.emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

--

7369 SMITH CLERK 7902 17-DEC-80 800 20

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30

7566 JONES MANAGER 7839 02-APR-81 2975 20

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30

7698 BLAKE MANAGER 7839 01-MAY-81 2850 30

7782 CLARK MANAGER 7839 09-JUN-81 2450 10

7788 SCOTT ANALYST 7566 19-APR-87 3000 20

7839 KING PRESIDENT 17-NOV-81 5000 10

7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30

7876 ADAMS CLERK 7788 23-MAY-87 1100 20

7900 JAMES CLERK 7698 03-DEC-81 950 30

7902 FORD ANALYST 7566 03-DEC-81 3000 20

7934 MILLER CLERK 7782 23-JAN-82 1300 10 14 rows selected. KOBE@PROD > update scott.emp set sal=100 where deptno=10; 3 rows updated. KOBE@PROD > commit; Commit complete. KOBE@PROD > conn scott/tiger

Connected.

SCOTT@PROD > select * from emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

--

7369 SMITH CLERK 7902 17-DEC-80 800 20

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30

7566 JONES MANAGER 7839 02-APR-81 2975 20

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30

7698 BLAKE MANAGER 7839 01-MAY-81 2850 30

7782 CLARK MANAGER 7839 09-JUN-81 100 10

7788 SCOTT ANALYST 7566 19-APR-87 3000 20

7839 KING PRESIDENT 17-NOV-81 100 10

7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30

7876 ADAMS CLERK 7788 23-MAY-87 1100 20

7900 JAMES CLERK 7698 03-DEC-81 950 30

7902 FORD ANALYST 7566 03-DEC-81 3000 20

7934 MILLER CLERK 7782 23-JAN-82 100 10 14 rows selected. SCOTT@PROD > delete from emp where deptno=10; 3 rows deleted. SCOTT@PROD > conn / as sysdba

Connected.

SYS@PROD > select username,ses_actions,owner,obj_name,to_char (timestamp,'yyyy-mm-dd hh34:mi:ss')

From dba_audit_trail order by 1; USERNAME SES_ACTIONS OWNER OBJ_NAME TO_CHAR (TIMESTAMP,'

--

KOBE 2018-05-31 14:19:25

KOBE KOBE TEST 2018-05-31 14:19:40

KOBE KOBE TEST 2018-05-31 14:20:22

KOBE-S-SCOTT EMP 2018-05-31 14:48:12

KOBE-S-SCOTT EMP 2018-05-31 14:39:05

KOBE-S-SCOTT EMP 2018-05-31 14:47:47

KOBE 2018-05-31 14:22:11

SCOTT 2017-06-15 21:51:47

SCOTT 2017-06-15 21:51:58

SCOTT 2018-05-31 14:18:54

SCOTT-S-SCOTT EMP 2018-05-31 14:48:55

SCOTT-S-SCOTT EMP 2018-05-31 14:48:37

SCOTT 2018-05-31 14:19:25 S:success successfully visited

F: failed access to failure

There are both successes and failures when B:both operates on objects. The standard audit does not record the specific actions of users (sql_text). The SES_ACTIONS field contains 16 characters with an initial state of "-". When the audited object is manipulated,

SES_ACTIONS will mark it in the appropriate location. The operation marked "S" is successful, and the operation marked "F" fails.

The operations represented by the 16-position characters are as follows: 1. Auditing ALTER

2. Auditing AUDIT

3. Auditing COMMIT

4. Auditing DELETE

5. Auditing GRANT

6. Auditing INDEX

7. Auditing INSERT

8. Auditing LOCK

9. Aduiting RENAME

10.Auditing SELECT

11.Auditing UPDATE

12.Auditing EXECUTE

13.Auditing CREATE

14.Auditing READ

15.Auditing WRITE

16.Auditing FLASHBACK shutdown audit

SYS@PROD > noaudit all on scott.emp; Noaudit succeeded. 3. Fine audit (FGA)

Application

Establish audit policy through DBMS_FGA, record the corresponding sql text of user access to object and establish audit under different conditions in more detail

Create a FGA for the emp1 table

SYS@PROD > create table scott.emp1 as select * from scott.emp; Table created. Add a refinement audit strategy

SYS@PROD > begin

2 dbms_fga.add_policy (

3 object_schema= > 'scott'

4 object_name= > 'emp1'

5 policy_name= > 'chk_emp1'

6 audit_condition = > 'deptno=20'

7 audit_column = > 'sal'

8 statement_types = > 'update,select')

9 end

10 / PL/SQL procedure successfully completed. test

SYS@PROD > conn scott/tiger

Connected.

SCOTT@PROD > select * from emp1 where deptno=20; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

--

7369 SMITH CLERK 7902 17-DEC-80 800 20

7566 JONES MANAGER 7839 02-APR-81 2975 20

7788 SCOTT ANALYST 7566 19-APR-87 3000 20

7876 ADAMS CLERK 7788 23-MAY-87 1100 20

7902 FORD ANALYST 7566 03-DEC-81 3000 20 SCOTT@PROD > update scott.emp1 set sal=8000 where empno=7902; 1 row updated. SCOTT@PROD > select empno,ename from scott.emp1 where deptno=20; missing sal column does not audit EMPNO ENAME

--

7369 SMITH

7566 JONES

7788 SCOTT

7876 ADAMS

7902 FORD SCOTT@PROD > conn / as sysdba

Connected. Although the conditions are met, sys is not audited by default

SYS@PROD > select empno,ename,sal from scott.emp1 where deptno=20; EMPNO ENAME SAL

7369 SMITH 800

7566 JONES 2975

7788 SCOTT 3000

7876 ADAMS 1100

7902 FORD 8000 View Audit results

SYS@PROD > col db_user for A10

SYS@PROD > col sql_text for A60

SYS@PROD > select db_user,to_char (timestamp,'yyyy-mm-dd hh34:mi:ss') "time", sql_text

2 from dba_fga_audit_trail; DB_USER time SQL_TEXT

-

SCOTT 2018-05-31 15:28:52 select * from emp1 where deptno=20

SCOTT 2018-05-31 15:29:01 update scott.emp1 set sal=8000 where empno=7902 deletes the FGA policy

SYS@PROD > exec dbms_fga.drop_policy (object_schema= > 'scott',object_name= >' emp1',policy_name= > chk_emp1'); PL/SQL procedure successfully completed. Or

SYS@PROD > execute dbms_fga.DROP_POLICY ('scott','emp1','chk_emp1'); PL/SQL procedure successfully completed. Delete audit results

SYS@PROD > select count (*) from fga_log$; COUNT (*)

-

two

SYS@PROD > delete from fga_log$; 2 rows deleted. SYS@PROD > commit; Commit complete.

Audit results

Fga_log$

Dba_fga_audit_trail

4. Application audit (trigger) application to monitor and track changes in data in object and case tracking changes in the sal field in the emp table SCOTT@PROD > create table audit_emp_change (name varchar2 (10)

Oldsal number (6jue 2), newsal number (6je 2), time date); Table created. SCOTT@PROD > desc audit_emp_change

Name Null? Type

-

NAME VARCHAR2 (10)

OLDSAL NUMBER (6Pol 2)

NEWSAL NUMBER (6Pol 2)

TIME DATE establish trigger

SCOTT@PROD > create or replace trigger tr_sal_change

2 after update of sal on scott.emp

3 for each row

4 declare

5 v_temp int

6 begin

7 select count (*) into v_temp from audit_emp_change

8 where name=:old.ename

9 if v_temp=0 then

10 insert into audit_emp_change

11 values (: old.ename,:old.sal,:new.sal,sysdate)

12 else

13 update audit_emp_change

14 set oldsal=:old.sal,newsal=:new.sal,time=sysdate

15 where name=:old.ename

16 end if

17 end

18 / Trigger created. Update sal

SCOTT@PROD > update emp set sal=777 where empno=7788; 1 row updated. SCOTT@PROD > commit; Commit complete. View

SCOTT@PROD > select * from audit_emp_change; NAME OLDSAL NEWSAL TIME

--

SCOTT 3000 777 31-MAY-18

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.