Oracle Database Audit

audit
(Batmunkh Moltov) #1

Аудитын талаар товч танилцуулга оруулъя. Аудит нь аюулгүй байдлын нэг хэсэг юм.

Дараах үндсэн 4 төрлийн аудит байна. Үүнд:

  • Mandatory auditing
  • Standard database auditing
  • Value based auditing
  • Fine Grained auditing

Oracle Database 12c дээр Unified Auditing гэж нэмэгдсэн бөгөөд энэ талаар болон Fine Grained auditing талаар тус тусад нь мэдээлэл оруулах болно.

Тохиргооны хувьд audit_trail параметрийг тохируулснаар аудит мэдээлэл ямар хэлбэртэйгээр хадгалагдахыг тодорхойлно. audit_trail нь дараах утгуудыг авна.

  • NONE– 10g болон өмнөх хувилбар энэ утга нь үндсэн утга байсан бөгөөд аудитыг унтраана
  • DB– 11g хувилбараас эхэлэн үндсэн утга болсон. Аудит мэдээллийг SYS.AUD$ хүснэгтэнд хадгална.
  • OS – Аудит мэдээллийг үйлдлийн системийн файл руу бичнэ. audit_file_dest параметрийн тусламжтай хаана хадгалахыг тохируулна.
  • XML– Аудит мэдээллийг XML формат болгож хадгална.
  • EXTENDED– хамааралтай нэмэлт мэдээллүүдийг хадгална.

NONE/DB/OS/XML/DB,EXTENDED/OS,EXTENDED/XML,EXTENDED

Тохируулах жишээ :

SQL> alter system set audit_trail='DB','EXTENDED' scope=spfile;

System altered.

SQL>

SYS.AUD$ tablespace солих

SYS.AUD$ хүснэгтийн tablespace-ийг сольж болно. Энэ нь SYSTEM tablespace-д ирэх ачааллыг бууруулах юм.

SQL>  create tablespace audit_tbs datafile '/u01/app/oracle/oradata/orcl/audit01.dbf' size 100m
  2  autoextend on
  3  maxsize unlimited;

Tablespace created.

SQL>
QL> select owner,segment_name,segment_type,tablespace_name,bytes/1024/1024 from dba_segments where segment_name='AUD$';

OWNER		     SEGMENT_NAME	     SEGMENT_TYPE	TABLESPACE_NAME 	BYTES/1024/1024
-------------------- ----------------------- ------------------ ----------------------- ---------------
SYS		     AUD$		     TABLE		SYSTEM				  .0625

SQL> begin
  2  DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
  3  audit_trail_location_value => 'AUDIT_TBS');
  4  end;
  5  /

PL/SQL procedure successfully completed.

SQL> select owner,segment_name,segment_type,tablespace_name,bytes/1024/1024 from dba_segments where segment_name='AUD$';

OWNER		     SEGMENT_NAME	     SEGMENT_TYPE	TABLESPACE_NAME 	BYTES/1024/1024
-------------------- ----------------------- ------------------ ----------------------- ---------------
SYS		     AUD$		     TABLE		AUDIT_TBS			  .0625

SQL>

VIEWS

SYS.AUD$ -т хадгалагдсан мэдээллийг DBA_AUDIT_TRAIL/USER_AUDIT_TRAIL view-с DBA болон энгийн хэрэглэгчийн түвшинд өөрт харгалзах мэдээллүүдийг харж болно.

Аудиттай холбоотой view-үүд

  • · DBA_OBJ_AUDIT_OPTS
  • · DBA_PRIV_AUDIT_OPTS
  • · DBA_STMT_AUDIT_OPTS
  • · DBA_AUDIT_EXISTS
  • · DBA_AUDIT_OBJECT
  • · DBA_AUDIT_SESSION
  • · DBA_AUDIT_STATEMENT
  • · AUDIT_ACTIONS
  • · DBA_AUDIT_POLICIES
  • · DBA_AUDIT_POLICY_COLUMNS
  • · DBA_COMMON_AUDIT_TRAIL
  • · DBA_FGA_AUDIT_TRAIL (FGA_LOG$)
  • · DBA_REPAUDIT_ATTRIBUTE
  • · DBA_REPAUDIT_COLUMN

Жишээ

Жишээ 1:

SQL> create user oracloud identified by o;

User created.

SQL> grant connect,resource to oracloud;

Grant succeeded.

SQL> conn oracloud/o
Connected.
SQL> show user
USER is "ORACLOUD"
SQL> create table audit_test(eno number(10),name varchar2(20));

Table created.

SQL> insert into audit_test values(1,'has');
insert into audit_test values(1,'has')
            *
ERROR at line 1:
ORA-01950: no privileges on tablespace 'USERS'

SQL> conn / as sysdba
Connected.
SQL> alter user oracloud quota unlimited on users;

User altered.

SQL> conn oracloud/o
Connected.
SQL>
SQL>
SQL> insert into audit_test values(1,'has');

1 row created.

SQL> insert into audit_test values(1,'&a');
Enter value for a: a
old   1: insert into audit_test values(1,'&a')
new   1: insert into audit_test values(1,'a')

1 row created.

SQL> /
Enter value for a: b
old   1: insert into audit_test values(1,'&a')
new   1: insert into audit_test values(1,'b')

1 row created.

SQL> /
Enter value for a: c
old   1: insert into audit_test values(1,'&a')
new   1: insert into audit_test values(1,'c')

1 row created.

SQL> commit;

Commit complete.

SQL> select * from audit_test;

       ENO NAME
---------- --------------------
	 1 has
	 1 a
	 1 b
	 1 c

SQL> audit table by oracloud;
audit table by oracloud
*
ERROR at line 1:
ORA-01031: insufficient privileges


SQL> conn / as sysdba
Connected.
SQL> audit table by oracloud
  2  ;

Audit succeeded.

SQL> conn oracloud/o
Connected.
SQL>
SQL> create table audit_new(eno number(10),name varchar2(20));

Table created.

SQL> alter table audit_new add address varchar2(20);

Table altered.

SQL> insert into audit_new values(1,'asd','af');

1 row created.

SQL> commit;

Commit complete.

SQL> truncate table audit_new;

Table truncated.

SQL> drop table audit_new;

Table dropped.

SQL> commit;

Commit complete.

SQL> show user
USER is "ORACLOUD"
SQL>
SQL>
SQL> conn / as sysdba
Connected.

SQL> select * from DBA_STMT_AUDIT_OPTS where user_name='ORACLOUD';

USER_NAME	     PROXY_NAME 	     AUDIT_OPTION			      SUCCESS	 FAILURE
-------------------- ----------------------- ---------------------------------------- ---------- ----------
ORACLOUD				     TABLE				      BY ACCESS  BY ACCESS

SQL>

SQL>  select username, to_char(timestamp,'MM/DD/YY HH24:MI') Timestamp, obj_name, action_name, sql_text from dba_audit_trail where username = 'ORACLOUD';

USERNAME	     TIMESTAMP			    OBJ_NAME		      ACTION_NAME		   SQL_TEXT
-------------------- ------------------------------ ------------------------- ---------------------------- ----------------------------------------
ORACLOUD	     03/20/19 01:11		    AUDIT_NEW		      CREATE TABLE		   create table audit_new(eno number(10),na
													   me varchar2(20))

ORACLOUD	     03/20/19 01:12		    AUDIT_NEW		      TRUNCATE TABLE		   truncate table audit_new
ORACLOUD	     03/20/19 01:12		    AUDIT_NEW		      DROP TABLE		   drop table audit_new

SQL>

Жишнээ 2:

SQL> conn / as sysdba
SQL> audit select,insert,update,delete on oracloud.audit_test by access;

Audit succeeded.

SQL> conn oracloud/o
Connected.
SQL> insert into audit_test values(&id,'&a');
Enter value for id: 1
Enter value for a: a
old   1: insert into audit_test values(&id,'&a')
new   1: insert into audit_test values(1,'a')

1 row created.

SQL> /
Enter value for id: 2
Enter value for a: b
old   1: insert into audit_test values(&id,'&a')
new   1: insert into audit_test values(2,'b')

1 row created.

SQL> /
Enter value for id: 3
Enter value for a: c
old   1: insert into audit_test values(&id,'&a')
new   1: insert into audit_test values(3,'c')

1 row created.

SQL> commit;

Commit complete.

SQL> select * from audit_test;

       ENO NAME
---------- --------------------
	 1 has
	 1 a
	 1 b
	 1 c
	 1 a
	 2 b
	 3 c

7 rows selected.

SQL>  update audit_test set name='aa',eno=11 where eno=1 and name='a';

2 rows updated.

SQL> commit;

Commit complete.

SQL> delete from audit_test where eno=11;

2 rows deleted.

SQL> commit;

Commit complete.

SQL> conn / as sysdba
Connected.
SQL>
SQL>

SQL> col owner for a20
SQL> col object_name for a23
SQL>
SQL> select OWNER,OBJECT_NAME,OBJECT_TYPE from Dba_Obj_Audit_Opts where owner='ORACLOUD';

OWNER		     OBJECT_NAME	     OBJECT_TYPE
-------------------- ----------------------- -----------------------
ORACLOUD	     AUDIT_TEST 	     TABLE

SQL>  select * from dba_obj_audit_opts where owner='ORACLOUD';

OWNER		     OBJECT_NAME	     OBJECT_TYPE	     ALT       AUD	 COM	   DEL	     GRA       IND	 INS	   LOC	     REN       SEL	 UPD	   REF EXE
-------------------- ----------------------- ----------------------- --------- --------- --------- --------- --------- --------- --------- --------- --------- --------- --------- --- ---------
CRE	  REA	    WRI       FBK
--------- --------- --------- ---------
ORACLOUD	     AUDIT_TEST 	     TABLE		     -/-       -/-	 -/-	   A/A	     -/-       -/-	 A/A	   -/-	     -/-       A/A	 A/A	   -/- -/-
-/-	  -/-	    -/-       -/-


SQL> select OWNER,OBJ_NAME,action_name from dba_audit_object where OBJ_NAME='AUDIT_TEST';

OWNER		     OBJ_NAME		       ACTION_NAME
-------------------- ------------------------- ----------------------------
ORACLOUD	     AUDIT_TEST 	       INSERT
ORACLOUD	     AUDIT_TEST 	       INSERT
ORACLOUD	     AUDIT_TEST 	       INSERT
ORACLOUD	     AUDIT_TEST 	       SELECT
ORACLOUD	     AUDIT_TEST 	       UPDATE
ORACLOUD	     AUDIT_TEST 	       UPDATE
ORACLOUD	     AUDIT_TEST 	       DELETE

7 rows selected.

SQL>

SQL>
SQL> select USERNAME,TERMINAL,OWNER,OBJ_NAME,SQL_TEXT from user_audit_trail where OBJ_NAME='AUDIT_TEST';

USERNAME	     TERMINAL		  OWNER 	       OBJ_NAME 	       SQL_TEXT
-------------------- -------------------- -------------------- ----------------------- --------------------------------------------------
ORACLOUD	     pts/0		  ORACLOUD	       AUDIT_TEST	       insert into audit_test values(1,'a')
ORACLOUD	     pts/0		  ORACLOUD	       AUDIT_TEST	       insert into audit_test values(2,'b')
ORACLOUD	     pts/0		  ORACLOUD	       AUDIT_TEST	       insert into audit_test values(3,'c')
ORACLOUD	     pts/0		  ORACLOUD	       AUDIT_TEST	       select * from audit_test
ORACLOUD	     pts/0		  ORACLOUD	       AUDIT_TEST	       update audit_test set name='aa',id=11 where id=1 a
										       nd name='a'

ORACLOUD	     pts/0		  ORACLOUD	       AUDIT_TEST		update audit_test set name='aa',eno=11 where eno=
										       1 and name='a'

ORACLOUD	     pts/0		  ORACLOUD	       AUDIT_TEST	       delete from audit_test where eno=11

7 rows selected.

SQL>

SQL> select username, owner, action_name, priv_used from dba_audit_object;  

USERNAME	     OWNER		  ACTION_NAME		       PRIV_USED
-------------------- -------------------- ---------------------------- ----------------------------------------
ORACLOUD	     ORACLOUD		  CREATE TABLE		       CREATE TABLE
ORACLOUD	     ORACLOUD		  INSERT
ORACLOUD	     ORACLOUD		  INSERT
ORACLOUD	     ORACLOUD		  INSERT
ORACLOUD	     ORACLOUD		  SELECT
SCOTT		     SCOTT		  SELECT
SCOTT		     SYS		  SELECT
SCOTT		     SYSTEM		  SELECT
SCOTT		     SYSTEM		  SELECT
SCOTT		     SYSTEM		  SELECT
SCOTT		     SYSTEM		  SELECT
SCOTT		     SYS		  SELECT
ORACLOUD	     ORACLOUD		  UPDATE
ORACLOUD	     ORACLOUD		  UPDATE
ORACLOUD	     ORACLOUD		  DELETE
ORACLOUD	     ORACLOUD		  DROP TABLE
ORACLOUD	     ORACLOUD		  TRUNCATE TABLE

17 rows selected.

SQL>

NOAUDIT

NOAUDIT коммандын тусламжтайгаар аудитыг болиулна.

SQL> NOAUDIT;
SQL> NOAUDIT session;
SQL> NOAUDIT session BY oracloud,aud_test;
SQL> NOAUDIT DELETE ON aud_test;
SQL> NOAUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE, EXECUTE PROCEDURE;
SQL> NOAUDIT ALL;
SQL> NOAUDIT ALL PRIVILEGES;
SQL> NOAUDIT ALL ON DEFAULT;
2 Likes