<< SYS SESSION >>
  ■ Audit (감사)
- audit은 선택한 user database 작업을 모니터하는 것이다.
    - 의심이 가는 데이터베이스 작업을 조사
    - 특정 데이터베이스 작업에 대한 정보를 수집할 경우

SHOW PARAMETER audit_trail
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_trail                          string      DB

SELECT* FROM v$parameter WHERE name = 'audit_trail';
VALUE : DB
ISSES_MODIFIABLE : FALSE
ISSYS_MODIFIABLE : FALSE

# 감사 비활성화
ALTER SYSTEM SET audit_trail = none SCOPE = SPFILE;

# 감사 활성화
ALTER SYSTEM SET audit_trail = DB SCOPE = SPFILE;

# sys.aud$ 안에 있는 내용을 관리해야 한다.
SELECT * FROM sys.aud$;



★ 감사 옵션 설정

# 문장 감사
SQL문을 선택적으로 감사

audit은 create table, drop table, truncate table 수행하는 감사

SQL> audit table;

SELECT * FROM dba_stmt_audit_opts WHERE audit_option = 'TABLE';
USER_NAME       PROXY_NAME      AUDIT_OPTION                   SUCCESS    FAILURE
--------------- --------------- ------------------------------ ---------- ----------
                                TABLE                          BY ACCESS  BY ACCESS


SUCCESS : BY ACCESS (기본값)
SUCCESS : BY SESION

FAILURE : BY ACCESS 
...

SELECT * FROM sys.aud$;
<< SAWON02 SESSION >>
  SELECT * FROM tab;

DROP TABLE emp PURGE;

CREATE TABLE sawon02.emp AS SELECT * FROM hr.employees;
<< SYS SESSION >>
  SELECT sessionid, entryid, statement, userid, userhost, action#, obj$creator, obj$name
FROM sys.aud$;
SESSIONID ENTRYID STATEMENT USERID  USERHOST ACTION# OBJ$CREATOR OBJ$NAME ...
--------- ------- --------- ------- -------- ------- ----------- --------
  6370463       8        61 SAWON02 oracle        12 SAWON02     EMP     
  6370463       9        79 SAWON02 oracle         1 SAWON02     EMP      ...

SELECT username, owner,obj_name, action_name,
        decode(returncode, '0', 'success', returncode) sess,
        to_char(timestamp,'yyyy-mm-dd hh24:mi:ss') day
FROM dba_audit_object;
USERNAME   OWNER   OBJ_NAME        ACTION_NAME     SESS       DAY                 ...
---------- ------- --------------- --------------- ---------- -------------------
SAWON02    SAWON02 EMP             CREATE TABLE    success    2023-12-26 18:01:27
SAWON02    SAWON02 EMP             DROP TABLE      942        2023-12-26 18:01:25 ...
<< SAWON02 SESSION >>
  CREATE TABLE sawon02.emp AS SELECT * FROM hr.employees;

Alter Table sawon02.emp MODIFY last_name varchar2(30);

DROP TABLE sawon02.emp_copy PURGE;

TRUNCATE TABLE sawon02.emp;
<< SYS SESSION >>
  SELECT sessionid, entryid, statement, userid, userhost, action#, obj$creator, obj$name
FROM sys.aud$;
 SESSIONID    ENTRYID  STATEMENT USERID     USERHOST      ACTION# OBJ$CREATOR     OBJ$NAME
---------- ---------- ---------- ---------- ---------- ---------- --------------- ----------
   6370463          8         61 SAWON02    oracle             12 SAWON02         EMP
   6370463          9         79 SAWON02    oracle              1 SAWON02         EMP
   6370463         10        104 SAWON02    oracle              1 SAWON02         EMP
   6370463         11        129 SAWON02    oracle             12 SAWON02         EMP_COPY
   6370463         12        130 SAWON02    oracle             85 SAWON02         EMP

SELECT username, owner,obj_name, action_name,
        decode(returncode, '0', 'success', returncode) sess,
        to_char(timestamp,'yyyy-mm-dd hh24:mi:ss') day
FROM dba_audit_object;
USERNAME   OWNER   OBJ_NAME        ACTION_NAME     SESS       DAY
---------- ------- --------------- --------------- ---------- -------------------
SAWON02    SAWON02 EMP             TRUNCATE TABLE  success    2023-12-26 18:12:59
SAWON02    SAWON02 EMP             DROP TABLE      942        2023-12-26 18:01:25
SAWON02    SAWON02 EMP_COPY        DROP TABLE      942        2023-12-26 18:12:59
SAWON02    SAWON02 EMP             CREATE TABLE    success    2023-12-26 18:01:27
SAWON02    SAWON02 EMP             CREATE TABLE    955        2023-12-26 18:12:59


# 감사 옵션 취소
NOAUDIT table;

TRUNCATE TABLE sys.aud$;

select * from dba_stmt_audit_opts where audit_option = 'TABLE';
no rows selected



<< HR SESSION >>
  DROP TABLE hr.dept CASCADE CONSTRAINTS PURGE;
DROP TABLE hr.emp CASCADE CONSTRAINTS PURGE;

CREATE TABLE hr.dept
AS SELECT department_id dept_id, department_name dept_name
FROM hr.departments;

CREATE TABLE hr.emp
AS SELECT employee_id id, last_name name, salary sal, department_id dept_id
FROM hr.employees;

ALTER TABLE hr.dept ADD CONSTRAINT deptid_pk PRIMARY KEY (dept_id);
ALTER TABLE hr.emp ADD CONSTRAINT depid_pk PRIMARY KEY (id);
ALTER TABLE hr.emp ADD CONSTRAINT emp_deptid_fk FOREIGN KEY (dept_id)
REFERENCES hr.dept (dept_id);

SELECT constraint_name, constraint_type, table_name, 
        r_owner, r_constraint_name, index_owner, index_name
FROM user_constraints WHERE table_name in ('EMP','DEPT');

CONSTRAINT_NAME CONSTRAINT_TYPE TABLE_NAME R_OWNER R_CONSTRAINT_NAME INDEX_OWNER INDEX_NAME
--------------- --------------- ---------- ------- ----------------- ----------- ----------
DEPTID_PK       P               DEPT                                 HR          DEPTID_PK
DEPID_PK        P               EMP                                  HR          DEPID_PK
EMP_DEPTID_FK   R               EMP        HR      DEPTID_PK

GRANT SELECT, INSERT, UPDATE, DELETE ON hr.emp TO sawon01, sawon02;
GRANT SELECT, INSERT, UPDATE, DELETE ON hr.dept TO sawon01, sawon02;
<< SYS SESSION >>
  # 스키마(schema) object audit
특정한 유저가 소유한 테이블에 대해서 감사 설정

AUDIT SELECT, INSERT, UPDATE, DELETE ON hr.emp;
AUDIT SELECT, INSERT, UPDATE, DELETE ON hr.dept;

SELECT owner, object_name, object_type, sel, ins, upd, del FROM dba_obj_audit_opts;
OWNER   OBJECT_NAME     OBJECT_TYPE     SEL       INS       UPD       DEL
------- --------------- --------------- --------- --------- --------- ---------
HR      DEPT            TABLE           S/S       S/S       S/S       S/S
HR      EMP             TABLE           S/S       S/S       S/S       S/S
<< SAWON01 SESSION >>
  SELECT sal FROM hr.emp WHERE id = 100;
       SAL
----------
     24000
<< SYS SESSION >>
  SELECT sessionid, entryid, statement, userid, userhost, action#, obj$creator, obj$name
FROM sys.aud$;
 SESSIONID    ENTRYID  STATEMENT USERID     USERHOST      ACTION# OBJ$CREATOR     OBJ$NAME
---------- ---------- ---------- ---------- ---------- ---------- --------------- ----------
   6480464          2         10 SAWON01    oracle            103 HR              EMP
...

SELECT username, owner,obj_name, action_name,
        decode(returncode, '0', 'success', returncode) sess,
        to_char(timestamp,'yyyy-mm-dd hh24:mi:ss') day
FROM dba_audit_object;
USERNAME   OWNER   OBJ_NAME        ACTION_NAME     SESS       DAY
---------- ------- --------------- --------------- ---------- -------------------
SAWON01    HR      EMP             SESSION REC     success    2023-12-26 18:47:49
<< SAWON01 SESSION >>
  INSERT INTO hr.emp (id,name,sal,dept_id) VALUES (300,'james',1000,10);

UPDATE hr.emp SET sal = 1000 WHERE id = 101;

DELETE FROM hr.emp WHERE id = 200;

ROLLBACK;
<< SYS SESSION >>
  SELECT sessionid, entryid, statement, userid, userhost, action#, obj$creator, obj$name
FROM sys.aud$;
 SESSIONID    ENTRYID  STATEMENT USERID     USERHOST      ACTION# OBJ$CREATOR     OBJ$NAME
---------- ---------- ---------- ---------- ---------- ---------- --------------- ----------
   6480464          2         10 SAWON01    oracle            103 HR              EMP
   6480464          3         11 SAWON01    oracle            103 HR              EMP
   6480464          4         12 SAWON01    oracle            103 HR              EMP
   6480464          5         13 SAWON01    oracle            103 HR              EMP
...

SELECT username, owner,obj_name, action_name,
        decode(returncode, '0', 'success', returncode) sess,
        to_char(timestamp,'yyyy-mm-dd hh24:mi:ss') day
FROM dba_audit_object;
USERNAME   OWNER   OBJ_NAME        ACTION_NAME     SESS       DAY
---------- ------- --------------- --------------- ---------- -------------------
SAWON01    HR      EMP             SESSION REC     success    2023-12-26 18:47:49
SAWON01    HR      EMP             SESSION REC     success    2023-12-26 18:52:52
SAWON01    HR      EMP             SESSION REC     success    2023-12-26 18:52:52
SAWON01    HR      EMP             SESSION REC     success    2023-12-26 18:52:52
<< SAWON01 SESSION >>
  SELECT sal FROM hr.emp WHERE id = 300;
no rows selected

INSERT INTO hr.emp(id,name,sal,dept_id) VALUES (400,'scott',1000,500);
ORA-02291: integrity constraint (HR.EMP_DEPTID_FK) violated - parent key not found

DELETE FROM hr.dept WHERE dept_id = 20;
ORA-02292: integrity constraint (HR.EMP_DEPTID_FK) violated - child record found
<< SYS SESSION >>
  SELECT sessionid, entryid, statement, userid, userhost, action#, obj$creator, obj$name
FROM sys.aud$;
 SESSIONID    ENTRYID  STATEMENT USERID     USERHOST      ACTION# OBJ$CREATOR     OBJ$NAME
---------- ---------- ---------- ---------- ---------- ---------- --------------- ----------
   6480464          6         15 SAWON01    oracle            103 HR              EMP
   6480464          7         16 SAWON01    oracle            103 HR              EMP
   6480464          8         17 SAWON01    oracle            103 HR              DEPT
...

SELECT username, owner,obj_name, action_name,
        decode(returncode, '0', 'success', returncode) sess,
        to_char(timestamp,'yyyy-mm-dd hh24:mi:ss') day
FROM dba_audit_object;
USERNAME   OWNER   OBJ_NAME        ACTION_NAME     SESS       DAY
---------- ------- --------------- --------------- ---------- -------------------
SAWON01    HR      EMP             SESSION REC     success    2023-12-26 18:55:00
SAWON01    HR      EMP             SESSION REC     2291       2023-12-26 18:55:50
SAWON01    HR      DEPT            SESSION REC     2292       2023-12-26 18:55:52
...


# 스키마 객체 감사 취소
NOAUDIT SELECT, INSERT, UPDATE, DELETE ON hr.emp;
NOAUDIT SELECT, INSERT, UPDATE, DELETE ON hr.dept;

SELECT owner, object_name, object_type, sel, ins, upd, del FROM dba_obj_audit_opts;
no rows selected

TRUNCATE TABLE sys.aud$;

SELECT username,owner,obj_name,action_name,
        decode(returncode, '0', 'success', returncode) sess,
        to_char(timestamp,'yyyy-mm-dd hh24:mi:ss') day
FROM dba_audit_object;
no rows selected
<< HR SESSION >>
  SELECT * FROM user_tab_privs WHERE grantee IN ('SAWON01', 'SAWON02');
GRANTEE    OWNER   TABLE_NAME      GRANTOR    PRIVILEGE            GRANTABLE  HIE
---------- ------- --------------- ---------- -------------------- ---------- ---
SAWON02    HR      DEPT            HR         SELECT               NO         NO
SAWON01    HR      DEPT            HR         DELETE               NO         NO
SAWON02    HR      EMP             HR         UPDATE               NO         NO
SAWON01    HR      EMP             HR         UPDATE               NO         NO
SAWON02    HR      EMP             HR         SELECT               NO         NO
...

REVOKE ALL ON hr.emp FROM sawon01, sawon02;
REVOKE ALL ON hr.dept FROM sawon01, sawon02;

SELECT * FROM user_tab_privs WHERE grantee IN ('SAWON01', 'SAWON02');
GRANTEE    OWNER   TABLE_NAME      GRANTOR    PRIVILEGE            GRANTABLE  HIE
---------- ------- --------------- ---------- -------------------- ---------- ---
SAWON02    HR      EMPLOYEES       HR         SELECT               NO         NO

 

<< SYS SESSION >>
  GRANT SELECT ANY TABLE TO sawon01, sawon02;

SELECT * FROM dba_sys_privs WHERE grantee IN ('SAWON01','SAWON02');
GRANTEE    PRIVILEGE            ADMIN_OPTION
---------- -------------------- -------------
SAWON02    SELECT ANY TABLE     NO
SAWON01    SELECT ANY TABLE     NO


■ 권한 (privilege) audit
시스템 권한을 선택적으로 감사

AUDIT SELECT ANY TABLE BY sawon01, sawon02 BY SESSION;

SELECT *FROM dba_stmt_audit_opts;
USER_NAME  PROXY_NAME  AUDIT_OPTION         SUCCESS     FAILURE
---------- ----------- -------------------- ----------  ----------
SAWON02    (null)      SELECT ANY TABLE     BY SESSION  BY SESSION
SAWON01    (null)      SELECT ANY TABLE     BY SESSION  BY SESSION
<< SAWON01 SESSION >>
  SELECT * FROM hr.emp;
SELECT * FROM hr.employees;
SELECT * FROM hr.dept;
SELECT * FROM hr.departments;
SELECT * FROM hr.locations;
<< SYS SESSION >>
  SELECT username,owner,obj_name,action_name,
        decode(returncode, '0', 'success', returncode) sess,
        to_char(timestamp,'yyyy-mm-dd hh24:mi:ss') day
FROM dba_audit_object;

 

<< SYS SESSION >>
  SHOW PARAMETER audit_trail
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_trail                          string      DB

SELECT * FROM v$parameter WHERE NAME = 'audit_trail';
ISSES_MODIFIABLE : FALSE
ISSYS_MODIFIABLE : FALSE


■ audit_trail = db_extended
유저가 실행한 SQL 텍스트 및 변수 값 감사

ALTER SYSTEM SET audit_trail=db_extended SCOPE = SPFILE;

SHUTDOWN IMMEDIATE

STARTUP

SHOW PARAMETER audit_trail
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_trail                          string      DB_EXTENDED


# 문장 감사
AUDIT TABLE;

SELECT * FROM dba_stmt_audit_opts WHERE audit_option = 'TABLE';
USER_NAME  PROXY_NAME  AUDIT_OPTION         SUCCESS     FAILURE
---------- ----------- -------------------- ----------  ----------
SAWON02    (null)      TABLE                BY ACCESS   BY ACCESS

TRUNCATE TABLE sys.aud$;

SELECT * FROM sys.aud$;
<< SAWON01 SESSION >>
  SELECT * FROM hr.emp;
SELECT * FROM hr.employees;
SELECT * FROM hr.dept;
SELECT * FROM hr.departments;
SELECT * FROM hr.locations;

<< SYS SESSION >>
  SELECT * FROM sys.aud$;

SELECT username, owner, obj_name, action_name,
        decode(returncode, '0', 'success', returncode) sess,
        sql_text, sql_bind
FROM dba_audit_object;
USERNAME   OWNER   OBJ_NAME        ACTION_NAME  SESS     SQL_TEXT                     SQL_BIND
---------- ------- --------------- ------------ -------- ---------------------------- --------
SAWON01    HR      EMP             SESSION REC  success  SELECT * FROM hr.emp
SAWON01    HR      EMPLOYEES       SESSION REC  success  SELECT * FROM hr.employees
SAWON01    HR      DEPT            SESSION REC  success  SELECT * FROM hr.dept
SAWON01    HR      DEPARTMENTS     SESSION REC  success  SELECT * FROM hr.departments
SAWON01    HR      LOCATIONS       SESSION REC  success  SELECT * FROM hr.locations


# 문장 감사 옵션 취소
NOAUDIT TABLE;

SELECT * FROM dba_stmt_audit_opts WHERE audit_option = 'TABLE';
no rows selected

TRUNCATE TABLE SYS.aud$;

SELECT * FROM sys.aud$;
no rows selected

 

<< SYS SESSION >>
  GRANT SELECT ON hr.employees TO sawon01, sawon02;


# 객체 감사
AUDIT SELECT ON hr.employees;

SELECT owner, object_name, object_type, sel, ins, upd, del
FROM dba_obj_audit_opts;
OWNER   OBJECT_NAME                    OBJECT_TYPE             SEL   INS   UPD   DEL
------- ------------------------------ ----------------------- ----- ----- ----- -----
HR      EMPLOYEES                      TABLE                   S/S   -/-   -/-   -/-
<< SAWON01 SESSION >>
  VAR v_id NUMBER

EXECUTE :v_id := 100

PRINT :v_id
      V_Id
----------
       100

SELECT salary FROM hr.employees WHERE employee_id = :v_id;
    SALARY
----------
     24000
<< SAWON02 SESSION >>
  VAR v_dept_id NUMBER

EXECUTE :v_dept_id := 20

PRINT :v_dept_id
 V_DEPT_ID
----------
        20

SELECT salary FROM hr.employees WHERE department_id = :v_dept_id;
    SALARY
----------
     13000
      6000
<< SYS SESSION >>
  SELECT username, owner, obj_name, action_name,
        decode(returncode, '0', 'success', returncode) sess,
        to_char(timestamp,'yyyy-mm-dd hh24:mi:ss') day,
        sql_text, sql_bind
FROM dba_audit_object;
USERNAME  OWNER  OBJ_NAME   ACTION_NAME  SESS     SQL_TEXT                            SQL_BIND
--------- ------ ---------- ------------ -------- ----------------------------------- ---------
SAWON01   HR     EMPLOYEES  SESSION REC  success  select salary from hr.employees ..  #1(3):100
SAWON02   HR     EMPLOYEES  SESSION REC  success  select salary from hr.employees ..  #1(2):20


# 객체 감사 취소
NOAUDIT SELECT ON hr.employees;

SELECT owner, object_name, object_type, sel, ins, upd, del
FROM dba_obj_audit_opts;
no rows selected

TRUNCATE TABLE SYS.aud$;

 

<< SYS SESSION >>
  # 권한 감사
AUDIT SELECT ANY TABLE BY sawon01, sawon02 BY SESSION;

SELECT *FROM dba_stmt_audit_opts WHERE audit_option = 'SELECT ANY TABLE';
USER_NAME  PROXY_NAME  AUDIT_OPTION                   SUCCESS     FAILURE
---------- ----------- ------------------------------ ----------- -----------
SAWON02                SELECT ANY TABLE               BY SESSION  BY SESSION
SAWON01                SELECT ANY TABLE               BY SESSION  BY SESSION
...
<< SAWON01 SESSION >>
  SELECT *FROM hr.locations;
<< SAWON02 SESSION >>
  SELECT *FROM hr.departments;

<< SYS SESSION >>
  SELECT * FROM dba_role_privs WHERE granted_role = 'SAWON_ROLE';
GRANTEE    GRANTED_ROLE                   ADMIN_OPTION  DEFAULT_ROLE
---------- ------------------------------ ------------- -------------
SYS        SAWON_ROLE                     YES           YES
SAWON01    SAWON_ROLE                     NO            YES
SAWON02    SAWON_ROLE                     NO            YES

SELECT * FROM role_tab_privs WHERE ROLE = 'SAWON_ROLE';
ROLE            OWNER   TABLE_NAME                     COLUMN_NAME  PRIVILEGE  GRANTABLE
--------------- ------- ------------------------------ ------------ ---------- ----------
SAWON_ROLE      HR      EMPLOYEES                                   SELECT     NO
SAWON_ROLE      HR      DEPARTMENTS                                 SELECT     NO
...

SELECT username, owner, obj_name, action_name,
        decode(returncode, '0', 'success', returncode) sess,
        to_char(timestamp,'yyyy-mm-dd hh24:mi:ss') day,
        sql_text, sql_bind
FROM dba_audit_object;
USERNAME  OWNER  OBJ_NAME   ACTION_NAME  SESS     SQL_TEXT                            SQL_BIND
--------- ------ ---------- ------------ -------- ----------------------------------- ---------
SAWON01   HR     LOCATIONS  SESSION REC  success  SELECT * FROM hr.locations

>> SELECT ANY TABLE 모든 테이블 감사 대상이지만
SAWON02 유저의 SAWON_ROLE 내 HR.DEPARTMENTS 객체의 SELECT 권한을 가지고 있어 감사대상에서 제외.


# 시스템 권한 감사 취소
NOAUDIT SELECT ANY TABLE BY sawon01, sawon02;

SELECT * FROM dba_stmt_audit_opts WHERE audit_option = 'SELECT ANY TABLE';


TRUNCATE TABLE sys.aud$;

SELECT username, owner, obj_name, action_name,
        decode(returncode, '0', 'success', returncode) sess,
        to_char(timestamp,'yyyy-mm-dd hh24:mi:ss') day,
        sql_text, sql_bind
FROM dba_audit_object;



NOAUDIT SELECT ON hr.employees;

REVOKE ALL ON hr.employees FROM sawon_role;

REVOKE ALL ON hr.departments FROM sawon_role;

REVOKE SELECT ON hr.employees FROM sawon01;

 

저작자표시

'Oracle 11g > Oracle DB' 카테고리의 다른 글

56-2 ORACLE 종속성  (0) 2023.12.28
55 ORACLE Audit, Trigger  (1) 2023.12.27
53 ORACLE Profile, Resource  (1) 2023.12.22
52 ORACLE Role, Execute, Profile  (1) 2023.12.21
51 ORACLE Tablespace, Privilege, Role  (1) 2023.12.20
'Oracle 11g/Oracle DB'의 다른글
  • 현재글54-2 ORACLE Audit

티스토리툴바