Аудитын талаар товч танилцуулга оруулъя. Аудит нь аюулгүй байдлын нэг хэсэг юм.
Дараах үндсэн 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;