5-7 Data Warehouse Management - Oracle Database Auditing

References: Oracle Docs(Database PL/SQL Packages and Types Reference, Database Security Guide)

 

Audit is used to record all actions performed in the database. You can audit specific actions in the database, or audit actions of the specific user or actions performed on the specific object. By default, users with SYSDBA privilege is not audited.

AUDIT_TRAIL parameter

-        NONE disables database auditing.

-        DB enables database auditing and writes the audit information to SYS.AUD$ table.

-        DB,EXTENDED populates SQLTEXT and SQLBIND columns of SYS.AUD$ table.

-        OS enables database auditing and writes the audit information to the audit destination.

 

show parameter audit
show parameter audit_trail

ALTER SYSTEM SET audit_trail = none;
ALTER SYSTEM SET audit_trail = DB SCOPE=SPFILE;
ALTER SYSTEM SET audit_trail = DB,EXTENDED SCOPE=SPFILE;

 

 

 

 

 

실습: Audit actions performed by a user

CREATE USER usr_audit_test IDENTIFIED BY asdf0930k;
GRANT CONNECT, RESOURCE,UNLIMITED TABLESPACE TO usr_audit_test;

AUDIT CREATE TABLE BY usr_audit_test;

CONN usr_audit_test/asdf0930k;
CREATE TABLE mytable (id number);

 

 

 

실습: Audit SQL statements on specific table

/* BY ACCESS clause to record each SQL execution as a separate row in DBA_AUDIT_TRAIL view. If BY SESSION is specified, Oracle will write a single audit record per combination of session, SQL statement and object type. The default value is BY ACCESS. */

 

CONN / AS SYSDBA
AUDIT INSERT, DELETE ON usr_audit_test.mytable BY SESSION;
AUDIT INSERT, DELETE ON usr_audit_test.mytable BY ACCESS;

CONN USR_AUDIT_TEST/asdf0930k;
INSERT INTO usr_audit_test.mytable values(1);
UPDATE usr_audit_test.mytable SET id = 2;
DELETE FROM usr_audit_test.mytable;



CONN / as sysdba
SET LINESIZE 150

SELECT	username, obj_name, action_name, sql_text
FROM	dba_audit_trail
WHERE	username = 'USR_AUDIT_TEST'
ORDER BY timestamp;

 

dba_audit_trail 뷰에 SQL_TEXT도 표시되게 하려면 앞에서 설명한 것처럼 audit_trail 파라미터를 db,extended로 설정해야 한다.

 

 

 

실습: Audit Statement executions

CONN / as sysdba
AUDIT INDEX ON usr_audit_test.mytable BY SESSION WHENEVER NOT SUCCESSFUL;

CONN usr_audit_test/asdf0930k;
INSERT INTO mytable VALUES (1);
CREATE UNIQUE INDEX idx ON mytable(id);

CONN / as sysdba
SELECT	username, obj_name, action_name, sql_text
FROM	dba_audit_trail
WHERE	username = 'USR_AUDIT_TEST'
ORDER BY timestamp;

 

 

 

실습: Fine-Grained Auditing

/* Using fine-grained auditing you can audit actions based on predicates and conditions and is configured using DBMS_FGA package. There are 4 subprograms of this package which are used to create, drop, disable and enable the policy. */

/* connect with SYS user and create a FGA policy. This policy will monitor SELECT command and will check if ID or NAME column is referenced and if someone used "WHERE ID = 1" condition. */

 

CONN usr_audit_test/asdf0930k;

CREATE TABLE tbl_fga_01
(id NUMBER, name VARCHAR2(20), surname VARCHAR2(20));
INSERT INTO tbl_fga_01 VALUES (1,'First name', 'First Surname');
INSERT INTO tbl_fga_01 VALUES (2,'Second name', 'Second Surname');


CONN / as sysdba

BEGIN
	DBMS_FGA.ADD_POLICY(
	object_schema	=> 'USR_AUDIT_TEST',
	object_name	=> 'TBL_FGA_01',
	policy_name	=> 'PLC_FGA_TEST',
	audit_condition	=> 'id=1',
	audit_column	=> 'id, name',
	handler_schema	=> NULL,
	handler_module	=> NULL,
	enable			=>	TRUE,
	statement_types	=> 'SELECT',
	audit_trail		=> DBMS_FGA.DB_EXTENDED,
	audit_column_opts	=> DBMS_FGA.ANY_COLUMNS);
END;
/


SELECT	userhost, object_name, db_user, policy_name, sql_text
FROM	dba_fga_audit_trail;



CONN usr_audit_test/asdf0930k;
SELECT * FROM usr_audit_test.tbl_fga_01;
SELECT	surname FROM usr_audit_test.tbl_fga_01;
SELECT name FROM usr_audit_test.tbl_fga_01;
SELECT surname FROM usr_audit_test.tbl_fga_01 WHERE id = 1;

CONN / as sysdba
SELECT	userhost, object_name, db_user, policy_name, sql_text
FROM	dba_fga_audit_trail;




BEGIN
DBMS_FGA.DISABLE_POLICY(
	object_schema	=> 'USR_AUDIT_TEST',
	object_name	=> 'TBL_FGA_01',
	policy_name	=> 'PLC_FGA_TEST'
);
END;
/
SELECT object_name, policy_name, enabled FROM dba_audit_policies;
SELECT * FROM dba_audit_policy_columns;

 

 

 

 

 

 

 

난이도: ★★★☆☆

문제 1: 유저 하나를 생성하고, 해당 유저의 모든 DML 커맨드를 audit하여라. audit records를 생성하고, 로그된 SQL 커맨드문을 확인하여라. (5분)

 

conn / as sysdba

ALTER SYSTEM SET AUDIT_TRAIL=DB,EXTENDED SCOPE=SPFILE;
STARTUP FORCE;

CREATE USER user_1 IDENTIFIED BY asdf0930k;
GRANT connect, resource, unlimited tablespace TO user_1;
AUDIT INSERT ANY TABLE BY user_1 BY SESSION;
AUDIT SELECT ANY TABLE BY user_1 BY SESSION;

conn user_1/asdf0930k;
CREATE TABLE table1 (id NUMBER);
INSERT INTO table1 VALUES (1);
SELECT * FROM table1;


CONN / as sysdba
SELECT	sql_text
FROM	dba_audit_trail

 

 

 

 

 

난이도: ★★★☆☆

문제 2:  오브젝트를 하나 생성하고, 그 오브젝트에 관련된 모든 DML 커맨드에 대해 audit하여라. audit 레코드를 생성하여라. (5분)

 

AUDIT INSERT on user_1.table1 BY SESSION;
AUDIT SELECT on user_1.table1 BY SESSION;

conn user_1/asdf0930k;
INSERT INTO table1 VALUES (2);
SELECT * FROM table1;


CONN / as sysdba
SELECT	sql_text
FROM	dba_audit_trail;

 

 

 

난이도: ★★★☆☆

문제 3: failed connections to the databases를 audit하여 audit records를 생성하여라. (5분)

 

CONN / as sysdba

AUDIT SESSION WHENEVER NOT SUCCESSFUL;

exit
$ sqlplus / as sysdba
SELECT action_name FROM dba_audit_trail;

 

 

 

 

난이도: ★★★★☆

문제 4: DBA_SEGMENTS 뷰로부터 테이블을 하나 생성하고, fine-grained auditing을 이용하여 TABLESPACE_NAME, EXTENTS 컬럼과 owner='sysman'를 만족하는 데이터에 관한 SELECT문과 UPDATE 커맨드를 audit하여라. 각각 서로 다른 audit logs를 생성하여라. (10분)

 

본 실습 환경에서는 sysman 유저를 사용하지 않기 때문에, fga audit 로그가 남지 않았다.

DBA_SEGMENTS 뷰로부터 테이블을 하나 생성하고, fine-grained auditing을 이용하여 TABLESPACE_NAME, EXTENTS 컬럼과 owner='sysman'를 만족하는 데이터에 관한 SELECT문과 UPDATE 커맨드를 audit하여라. 각각 서로 다른 audit logs를 생성하여라.

CONN system/asdf0930k 
CREATE TABLE table_1 AS SELECT * FROM dba_segments;

CONN / as sysdba
BEGIN
	DBMS_FGA.ADD_POLICY(
	object_schema => 'SYSTEM',
	object_name => 'TABLE_1',
	policy_name => 'POLICY_1',
	audit_condition => 'owner=''SYSMAN''',
	audit_column => 'tablespace_name, extents',
	handler_schema => NULL,
	handler_module => NULL,
	enable => TRUE,
	statement_types => 'SELECT,UPDATE',
	audit_trail => DBMS_FGA.DB_EXTENDED,
	audit_column_opts => DBMS_FGA.ANY_COLUMNS);
END;
/

conn system/asdf0930k;
SELECT count(*) FROM table_1;
SELECT count(*) FROM table_1 GROUP BY tablespace_name, extents;

CONN / as sysdba
SELECT	userhost, object_name, db_user, policy_name, sql_text
FROM	dba_fga_audit_trail
WHERE	object_name = 'TABLE_1';