<< SYS SESSION >>
  1. 유저 생성

SELECT * FROM dba_users;

DROP USER insa01 CASCADE;
DROP USER insa02 CASCADE;

CREATE USER insa01
IDENTIFIED BY oracle
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp;

CREATE USER insa02
IDENTIFIED BY oracle
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp;

SELECT username, default_tablespace, temporary_tablespace, profile 
FROM dba_users WHERE username IN ('INSA01','INSA02');
USERNAME   DEFAULT_TABLESPACE  TEMPORARY_TABLESPACE  PROFILE
---------- ------------------- --------------------- -------------------
INSA01     USERS               TEMP                  DEFAULT
INSA02     USERS               TEMP                  DEFAULT

SELECT * FROM dba_ts_quotas WHERE username IN ('INSA01','INSA02');
no rows selected

GRANT CREATE SESSION, CREATE TABLE, UNLIMITED TABLESPACE TO insa01, insa02;

SELECT * FROM dba_sys_privs WHERE grantee IN ('INSA01','INSA02');
GRANTEE    PRIVILEGE            ADMIN_OPTION
---------- -------------------- -------------
INSA02     CREATE SESSION       NO
INSA01     CREATE TABLE         NO
INSA01     CREATE SESSION       NO
INSA02     CREATE TABLE         NO
INSA02     UNLIMITED TABLESPACE NO
INSA01     UNLIMITED TABLESPACE NO

* UNLIMITED, ANY 계열 권한은 별도 정리 필수

* UNLIMITED TABLESPACE : DB 내 존재하는 모든 테이블스페이스 무한으로 사용가능한 권한
                         = quota 값을 사용할 필요가 없다.
  2. sys.aud$ 딕셔너리 테이블을 새로운 테이블스페이스로 이관 작업

sys.aud$ 딕셔너리 테이블을 system tablespace에서 audit tbs tablespace로 이관

SELECT table_name, tablespace_name FROM dba_tables WHERE table_name = 'AUD$';
TABLE_NAME      TABLESPACE_NAME
--------------- ------------------------------
AUD$            SYSTEM

CREATE TABLESPACE audit_tbs
DATAFILE '/u01/app/oracle/oradata/ora11g/audit_tbs01.dbf' size 10M
AUTOEXTEND ON
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
SEGMENT SPACE MANAGEMENT AUTO;

SELECT * FROM dba_tablespaces;
SELECT * FROM dba_data_files;

BEGIN 
    dbms_audit_mgmt.set_audit_trail_location(
            audit_trail_type => dbms_audit_mgmt.audit_trail_aud_std,
            audit_trail_location_value => 'audit_tbs');
END;
/

SELECT table_name, tablespace_name FROM dba_tables WHERE table_name = 'AUD$';
TABLE_NAME      TABLESPACE_NAME
--------------- ------------------------------
AUD$            AUDIT_TBS


# 복구
BEGIN
    dbms_audit_mgmt.set_audit_trail_location(
            audit_trail_type => dbms_audit_mgmt.audit_trail_aud_std,
            audit_trail_location_value => 'system');
END;
/

SELECT table_name, tablespace_name FROM dba_tables WHERE table_name = 'AUD$';

SELECT * FROM sys.aud$;
  3. 감사 활성화 / 비활성화

show parameter audit_trail
----------------------------------- ----------- ------------------------------
audit_trail                          string      DB_EXTENED

TRUNCATE TABLE sys.aud$;


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

SHUTDOWN IMMEDIATE

STARTUP

show parameter audit_trail
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_trail                          string      NONE


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

show parameter audit_trail
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_trail                          string      DB_EXTENDED

 

<<< SYS SESSION >>
  ■ SQL문 감사
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
<< INSA01 SESSION >>
  CREATE TABLE insa_tab (id number, name varchar2(20), day date);

ALTER TABLE insa_tab MODIFY name varchar2(30);

TRUNCATE TABLE insa_tab;

DROP TABLE insa_tab PURGE;
<<< SYS SESSION >>
  SELECT * FROM sys.aud$;
↓ view
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
---------- ------- --------------- -------------------- ---------- -------------------
INSA01     INSA01  INSA_TAB        TRUNCATE TABLE       success    2023-12-27 10:27:48
INSA01     INSA01  INSA_TAB        DROP TABLE           success    2023-12-27 10:27:49
INSA01     INSA01  INSA_TAB        CREATE TABLE         success    2023-12-27 10:27:48

TRUNCATE TABLE sys.aud$;


# SQL문 감사 취소
NOAUDIT TABLE;

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


# 특정한 유저에게 SQL문 감사 설정
AUDIT TABLE BY insa02;

SELECT * FROM dba_stmt_audit_opts WHERE audit_option = 'TABLE';
USER_NAME  PROXY_NAME  AUDIT_OPTION  SUCCESS    FAILURE
---------- ----------- ------------- ---------- ----------
INSA02                 TABLE         BY ACCESS  BY ACCESS
<< insa01 SESSION >>
  conn insa01/oracle

CREATE TABLE insa_tab (id number, name varchar2(20), day date);

ALTER TABLE insa_tab MODIFY name varchar2(30);

TRUNCATE TABLE insa_tab;

DROP TABLE insa_tab PURGE;
<< insa02 SESSION >>
  conn insa02/oracle

CREATE TABLE insa_new (id number, name varchar2(20), day date);

ALTER TABLE insa_new MODIFY name varchar2(30);

TRUNCATE TABLE insa_new;

DROP TABLE insa_new PURGE;
<<< 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;
USERNAME   OWNER   OBJ_NAME        ACTION_NAME          SESS       DAY
---------- ------- --------------- -------------------- ---------- -------------------
INSA02     INSA02  INSA_NEW        TRUNCATE TABLE       success    2023-12-27 10:54:38
INSA02     INSA02  INSA_NEW        DROP TABLE           success    2023-12-27 10:54:38
INSA02     INSA02  INSA_NEW        CREATE TABLE         success    2023-12-27 10:54:38
ㄴ 감사 취소 후 특정유저 INSA02에만 설정한 상태라 INSA01 내역은 안나온다

# 감사 취소
NOAUDIT TABLE BY insa02;

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

TRUNCATE TABLE sys.aud$;

 

<< 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 * FROM user_constraints WHERE table_name IN ('EMP','DEPT');

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 insa01, insa02;
GRANT SELECT, INSERT, UPDATE, DELETE ON hr.dept TO insa01, insa02;

SELECT * FROM user_tab_privs WHERE grantee IN ('INSA01','INSA02');

GRANTEE    OWNER   TABLE_NAME      GRANTOR    PRIVILEGE            GRANTABLE  HIE
---------- ------- --------------- ---------- -------------------- ---------- ---
INSA01     HR      EMPLOYEES       HR         INSERT               NO         NO
INSA01     HR      EMPLOYEES       HR         UPDATE               NO         NO
<< SYS SESSION >>
  ■ 객체 감사

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
<< INSA01 SESSION >>
  SELECT * FROM user_tab_privs;
GRANTEE    OWNER   TABLE_NAME      GRANTOR    PRIVILEGE            GRANTABLE  HIE
---------- ------- --------------- ---------- -------------------- ---------- ---
INSA01     HR      DEPT            HR         INSERT               NO         NO
INSA01     HR      DEPT            HR         UPDATE               NO         NO
INSA01     HR      DEPT            HR         SELECT               NO         NO
INSA01     HR      DEPT            HR         DELETE               NO         NO
INSA01     HR      EMP             HR         INSERT               NO         NO
INSA01     HR      EMP             HR         UPDATE               NO         NO
INSA01     HR      EMP             HR         SELECT               NO         NO
INSA01     HR      EMP             HR         DELETE               NO         NO

SELECT sal FROM hr.emp WHERE id = 100;
       SAL
----------
     24000

INSERT INTO hr.emp (id,name,sal,dept_id) VALUES (300,'james',1000,10);

UPDATE hr.emp SET sal = sal * 1.1 WHERE id = 101;

DELETE FROM hr.emp WHERE id = 200;

ROLLBACK;
<< SYS SESSION >>
  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
-------- ----- -------- ------------ -------- ---------------------------------------- --------
INSA01   HR    EMP      SESSION REC  success  select sal from hr.emp where id = 100
INSA01   HR    EMP      SESSION REC  success  insert into hr.emp (id,name,sal,dept_i..
INSA01   HR    EMP      SESSION REC  success  update hr.emp set sal = sal * 1.1 wher..
INSA01   HR    EMP      SESSION REC  success  delete from hr.emp where id = 200
<< INSA02 SESSION >>
  var v_id number

execute :v_id := 200

SELECT sal FROM hr.emp WHERE id = :v_id;
       SAL
----------
      4400
<< 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
-------- ----- -------- ------------ -------- --------------------------------------- ---------
INSA02   HR    EMP      SESSION REC  success  select sal from hr.emp where id = :v_id #1(3):200
...
<< INSA02 SESSION >>
  DECLARE
    v_id number := 400;
    v_name varchar2(10) := 'scott';
    v_sal number := 1000;
    v_dept_id number := 10;
BEGIN
    INSERT INTO hr.emp(id,name,sal,dept_id) VALUES (v_id,v_name,v_sal,v_dept_id);
    COMMIT;
End;
/
<< 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                                
-------- ----- -------- ------------ -------- --------------------------------------- 
INSA02   HR    EMP      SESSION REC  success  INSERT INTO HR.EMP(ID,NAME,SAL,DEPT_I.. 

SQL_BIND
---------
:B4 , :B3 , :B2 , :B1   #1(3):400 #2(5):scott #3(4):1000 #4(2):10
...


# 감사 취소
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$;

REVOKE ALL ON hr.emp FROM insa01,insa02;
REVOKE ALL ON hr.dept FROM insa01,insa02;

SELECT * FROM dba_tab_privs WHERE grantee IN ('INSA01','INSA02');
no rows selected

 

<< SYS SESSION >>
  SELECT * FROM dba_sys_privs WHERE grantee IN ('INSA01','INSA02');
GRANTEE    PRIVILEGE            ADMIN_OPTION
---------- -------------------- -------------
INSA02     CREATE SESSION       NO
INSA01     CREATE TABLE         NO
INSA01     CREATE SESSION       NO
INSA02     CREATE TABLE         NO
INSA02     UNLIMITED TABLESPACE NO
INSA01     UNLIMITED TABLESPACE NO

GRANT SELECT ANY TABLE TO insa01,insa02;

SELECT * FROM dba_sys_privs WHERE grantee IN ('INSA01','INSA02');
GRANTEE    PRIVILEGE            ADMIN_OPTION
---------- -------------------- -------------
INSA02     CREATE SESSION       NO
INSA02     SELECT ANY TABLE     NO
INSA01     CREATE TABLE         NO
INSA01     CREATE SESSION       NO
INSA01     SELECT ANY TABLE     NO
INSA02     CREATE TABLE         NO
INSA02     UNLIMITED TABLESPACE NO
INSA01     UNLIMITED TABLESPACE NO


■ 시스템 권한 감사

AUDIT SELECT ANY TABLE BY insa01, insa02;

SELECT * FROM dba_stmt_audit_opts WHERE audit_option = 'SELECT ANY TABLE';
USER_NAME  PROXY_NAME  AUDIT_OPTION         SUCCESS    FAILURE
---------- ----------- -------------------- ---------- ----------
INSA01                 SELECT ANY TABLE     BY SESSION BY SESSION
INSA02                 SELECT ANY TABLE     BY SESSION BY SESSION
<< INSA01 SESSION >>
  SELECT * FROM user_tab_privs;
SELECT * FROM user_sys_privs;
USERNAME                       PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
INSA01                         CREATE TABLE                             NO
INSA01                         CREATE SESSION                           NO
INSA01                         SELECT ANY TABLE                         NO
INSA01                         UNLIMITED TABLESPACE                     NO

SELECT * FROM hr.employees;
SELECT * FROM hr.departments;
SELECT * FROM hr.locations;
 ㄴ 조회 가능
<< INSA02 SESSION >>
  SELECT * FROM user_tab_privs;
no rows selected

SELECT * FROM user_sys_privs;
USERNAME                       PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
INSA02                         CREATE SESSION                           NO
INSA02                         SELECT ANY TABLE                         NO
INSA02                         CREATE TABLE                             NO
INSA02                         UNLIMITED TABLESPACE                     NO

SELECT * FROM hr.countries;
 ㄴ 조회 가능
<< 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
-------- ----- ----------- ------------ -------- ------------------------------------ ---------
INSA02   HR    COUNTRIES   SESSION REC  success  SELECT * FROM hr.countries
INSA01   HR    EMPLOYEES   SESSION REC  success  SELECT * FROM hr.employees
INSA01   HR    DEPARTMENTS SESSION REC  success  SELECT * FROM hr.departments
INSA01   HR    LOCATIONS   SESSION REC  success  SELECT * FROM hr.locations



# SESSION 접속여부
AUDIT CREATE SESSION;

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

SELECT * FROM dba_audit_session;    -- 로그 조회

SELECT os_username, username, to_char(timestamp,'yyyy-mm-dd hh24:mi:ss') timestamp,
        action_name, to_char(logoff_time,'yyyy-mm-dd hh24:mi:ss') logoff_time
FROM dba_audit_session
order by 3 desc;
OS_USERNAME  USERNAME   TIMESTAMP           ACTION_NAME  LOGOFF_TIME
------------ ---------- ------------------- ------------ -------------------
ITWILL       HR         2023-12-27 12:09:55 LOGON
ITWILL       HR         2023-12-27 12:09:51 LOGOFF       2023-12-27 12:09:51
oracle       INSA02     2023-12-27 12:09:26 LOGON
oracle       INSA02     2023-12-27 12:09:13 LOGOFF       2023-12-27 12:09:13
oracle       INSA02     2023-12-27 12:00:57 LOGON
...

LOGOFF_LREAD    접속 후 나가기 전까지 로지컬하게 읽은 블럭의 수. 디스크에서 데이터버퍼캐시에 올려 읽은 수
LOGOFF_PREAD    피지컬하게 읽은 블럭의 수. 디스크에서 바로 찾아 읽은 수
LOGOFF_LWRITE   



# 감사 취소
NOAUDIT CREATE SESSION;

TRUNCATE TABLE sys.aud$;

AUDIT CREATE SESSION BY insa01;



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

SHUTDOWN IMMEDIATE

STARTUP

show parameter audit_trail
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_trail                          string      NONE

 

<< SYS SESSION >>
  SELECT table_name, tablespace_name FROM dba_tables WHERE table_name = 'FGA_LOG$';
TABLE_NAME      TABLESPACE_NAME
--------------- ------------------------------
FGA_LOG$        SYSTEM
ㄴ 딕셔너리 테이블이 시스템 테이블스페이스에 있기 때문에 별도 테이블 스페이스로 이관

select * from sys.fga_log$;
no rows selected

# FGA_LOG$ 딕셔너리 테이블을 audit_tab 테이블스페이스로 이관 작업
BEGIN 
    dbms_audit_mgmt.set_audit_trail_location(
            audit_trail_type => dbms_audit_mgmt.audit_trail_fga_std,
            audit_trail_location_value => 'audit_tbs');
END;
/

SELECT table_name, tablespace_name FROM dba_tables WHERE table_name = 'FGA_LOG$';
TABLE_NAME      TABLESPACE_NAME
--------------- ------------------------------
FGA_LOG$        AUDIT_TBS



■ Fine Grained Auditing
- 컨텐츠를 기준으로 데이터 액세스 모니터
- SELECT, INSERT, UPDATE, DELETE, MERGE를 수행할 때 감사
- 테이블, 뷰에 있는 하나 이상의 열에 설정한다.
- dbms_fga 패키지를 사용하여 관리

BEGIN
    dbms_fga.add_policy(
            object_schema => 'hr',
            object_name => 'employees',
            policy_name => 'emp_pol1',
            audit_condition => 'department_id = 10',
            audit_column => 'salary, commission_pct',
            audit_column_opts => dbms_fga.all_columns,
            enable => true,
            statement_types => 'select, insert, update, delete');
END;
/

- audit_column_opts => dbms_fga.all_columns : and 조건
- audit_column_opts => dbms_fga.any_columns : 기본값, or 조건

SELECT object_schema, object_name, policy_name, policy_text, policy_column, 
        sel, ins, upd, del, policy_column_options, audit_trail
FROM dba_audit_policies;
OBJECT_SCHEMA OBJECT_NAME POLICY_NAME POLICY_TEXT        POLICY_COLUMN 
------------- ----------- ----------- ------------------ ------------- 
HR            EMPLOYEES   EMP_POL1    department_id = 10 SALARY        

SEL INS UPD DEL POLICY_COLU AUDIT_TRAIL
--- --- --- --- ----------- ------------
YES YES YES YES ALL_COLUMNS DB+EXTENDED

SELECT * FROM dba_audit_policy_columns Where policy_name = 'EMP_POL1';
OBJECT_SCHEMA  OBJECT_NAME     POLICY_NAME     POLICY_COLUMN
-------------- --------------- --------------- ------------------------------
HR             EMPLOYEES       EMP_POL1        SALARY
HR             EMPLOYEES       EMP_POL1        COMMISSION_PCT
<< INSA01 SESSION >>
  SELECT * FROM hr.employees;

SELECT * FROM hr.employees WHERE department_id = 10;

SELECT * FROM hr.employees WHERE department_id = 20;

SELECT salary FROM hr.employees WHERE department_id = 10;

SELECT commission_pct FROM hr.employees WHERE department_id = 10;

SELECT employee_id,salary,commission_pct FROM hr.employees WHERE department_id = 10;

SELECT employee_id,salary FROM hr.employees 
WHERE department_id = 10 AND commission_pct is not null;

UPDATE hr.employees SET salary = salary * 1.1, commission_pct = 0.1
WHERE department_id = 10;
<< SYS SESSION >>
  SELECT * FROM sys.fga_log$;
↓ 딕셔너리 뷰
SELECT to_char(timestamp,'yyyy-mm-dd hh24:mi:ss') timestamp,
        db_user, policy_name, sql_text, sql_bind
FROM dba_fga_audit_trail;
TIMESTAMP           DB_USER    POLICY_NAME    SQL_TEXT                                 SQL_BIND
------------------- ---------- -------------- ---------------------------------------- --------
2023-12-27 14:23:18 INSA01     EMP_POL1       SELECT * FROM hr.employees
2023-12-27 14:24:30 INSA01     EMP_POL1       SELECT * FROM hr.employees WHERE depar..
2023-12-27 14:25:30 INSA01     EMP_POL1       SELECT employee_id,salary,commission_p..
2023-12-27 14:26:49 INSA01     EMP_POL1       SELECT employee_id,salary FROM hr.empl..
2023-12-27 14:34:08 INSA01     EMP_POL1       UPDATE hr.employees SET salary = salar..
2023-12-27 16:57:38 HR         EMP_POL1       UPDATE hr.employees SET salary = salar..



# FGA 삭제
BEGIN
    dbms_fga.drop_policy(
            object_schema => 'hr',
            object_name => 'employees',
            policy_name => 'emp_pol1');
END;
/

SELECT * FROM dba_audit_policies;
no rows selected

SELECT * FROM sys.fga_log$;

TRUNCATE TABLE sys.fga_log$;

 

<< SYS SESSION >>
  ■ DBA 감사
SYS 유저에 대한 작업 로그

SHOW PARAMETER audit
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      /u01/app/oracle/admin/ora11g/adump
audit_sys_operations                 boolean     FALSE
...

SELECT * FROM v$parameter WHERE name = 'audit_sys_operations';
ISSES_MODIFIABLE : FALSE
ISSYS_MODIFIABLE : FALSE
> static parameter : SPFILE, DB REBOOT

ALTER SYSTEM SET audit_sys_operations = true SCOPE = spfile;

SHUTDOWN IMMEDIATE

STARTUP

SHOW PARAMETER audit
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      /u01/app/oracle/admin/ora11g/adump
audit_sys_operations                 boolean     TRUE


(TEST)
SELECT * FROM v$sgainfo;
NAME                                  BYTES RESIZEABLE
-------------------------------- ---------- -----------
Shared Pool Size                  117440512 Yes
...

ALTER SYSTEM SET shared_pool_size = 109051904;

[oracle@oracle ~]$ cd /u01/app/oracle/admin/ora11g/adump
[oracle@oracle adump]$ ll -at | head
-rw-r-----. 1 oracle oinstall 11191 Dec 27 15:05 ora11g_ora_18869_20231227150439374804143795.aud
...
ㄴ 최근파일

[oracle@oracle adump]$ vi ora11g_ora_18869_20231227150439374804143795.aud
...
Wed Dec 27 15:05:24 2023 +09:00
LENGTH : '201'
ACTION :[45] 'alter system set shared_pool_size = 109051904'
DATABASE USER:[3] 'SYS'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'ITWILL'
CLIENT TERMINAL:[7] 'unknown'
STATUS:[1] '0'
DBID:[9] '256148331'
...

select * from hr.employees;

[oracle@oracle adump]$ vi ora11g_ora_18815_20231227150304458877143795.aud
...
Wed Dec 27 15:10:50 2023 +09:00
LENGTH : '178'
ACTION :[26] 'select * from hr.employees'
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[5] 'pts/0'
STATUS:[1] '0'
DBID:[9] '256148331'
...



# 감사 취소
ALTER SYSTEM SET audit_sys_operations = false SCOPE = spfile;

SHUTDOWN IMMEDIATE

STARTUP

SHOW PARAMETER audit
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      /u01/app/oracle/admin/ora11g/adump
audit_sys_operations                 boolean     FALSE  


SQL> ll -at | tail
ㄴ 오래된 파일은 용량을 생각해서 정리하자

* du -h   : 디렉터리 용량 합계

 

 

<< SYS SESSION >>
  # DDL TRIGGER

CREATE TABLE drop_obj_log(
        obj_user varchar2(50),
        obj_name varchar2(30),
        obj_type varchar2(30),
        drop_time timestamp)
TABLESPACE audit_tbs;

CREATE OR REPLACE TRIGGER drop_obj_trigger
BEFORE DROP ON DATABASE -- 전체 DB의 DROP 작업
BEGIN
    INSERT INTO drop_obj_log (obj_user,obj_name,obj_type,drop_time)
    VALUES (user,ora_dict_obj_name,ora_dict_obj_type,systimestamp);
    -- commit; --> drop이 성공하면 커밋이 발생하기 때문에 사용 안함
END;
/

user                세션의 유저아이디
ora_dict_obj_name   drop 작업을 하고 있는 테이블명
ora_dict_obj_type   drop 작업을 하고 있는 객체의 타입

SELECT * FROM dba_triggers WHERE trigger_name = 'DROP_OBJ_TRIGGER';
Name                 Value             
-------------------- -------------- 
OWNER                SYS
TRIGGER_NAME         SYS
TRIGGER_TYPE         BEFORE EVENT
TRIGGERING_EVENT     DROP 
TABLE_OWNER          SYS
BASE_OBJECT_TYPE     DATABASE        
TABLE_NAME           
COLUMN_NAME          
REFERENCING_NAMES    REFERENCING NEW AS NEW OLD AS OLD
WHEN_CLAUSE           
STATUS               ENABLED
DESCRIPTION          "drop_obj_trigger BEFORE DROP ON DATABASE"
ACTION_TYPE          PL/SQL     
TRIGGER_BODY         "BEGIN ~ END;"
...
<< HR SESSION >>
  CREATE TABLE hr.emp_10
AS SELECT * FROM hr.employees WHERE department_id = 10;

SELECT * FROM hr.emp_10;

DROP TABLE hr.emp_10 purge;


CREATE VIEW emp_30
AS SELECT * FROM hr.employees WHERE department_id = 30;

SELECT * FROM hr.emp_30;

DROP VIEW hr.emp_30;


CREATE SEQUENCE id_seq;

DROP SEQUENCE id_seq;
<< SYS SESSION >>
  CREATE USER ora10 IDENTIFIED BY oracle;

DROP USER ora10;

SELECT * FROM drop_obj_log;
OBJ_USER   OBJ_NAME        OBJ_TYPE        DROP_TIME
---------- --------------- --------------- ------------------------------
SYS        ORA10           USER            27-DEC-23 04.10.46.722926 PM
HR         EMP_10          TABLE           27-DEC-23 04.05.44.877522 PM
HR         EMP_30          VIEW            27-DEC-23 04.08.14.266720 PM
HR         ID_SEQ          SEQUENCE        27-DEC-23 04.09.26.603069 PM


# 트리거 삭제
DROP TRIGGER drop_obj_trigger;

 

<< SYS SESSION >>
  # DROP, TRUNCATE 수행 할 수 없게 만들기

CREATE OR REPLACE TRIGGER no_drop
BEFORE DROP OR TRUNCATE ON DATABASE
BEGIN
    RAISE_APPLICATION_ERROR(-20001,'DROP OR TRUNCATE 할 수 없습니다.');
END;
/
<< HR SESSION >>
  CREATE TABLE hr.emp_10
AS SELECT * FROM hr.employees WHERE department_id = 10;

SELECT * FROM hr.emp_10;

DROP TABLE hr.emp_10 PURGE;
ORA-20001: DROP OR TRUNCATE 할 수 없습니다.

TRUNCATE TABLE hr.emp_10;
ORA-20001: DROP OR TRUNCATE 할 수 없습니다.

CREATE SEQUENCE id_seq;

DROP SEQUENCE id_seq;
ORA-20001: DROP OR TRUNCATE 할 수 없습니다.
<< SYS SESSION >>
  DROP TRIGGER no_drop;

 

<< SYS SESSION >>
  # 특정한 유저 레벨에서 DROP, TRUNCATE 수행 할 수 없게 만들기

CREATE OR REPLACE TRIGGER no_drop
BEFORE DROP OR TRUNCATE ON hr.schema    -- 유저.schema
BEGIN
    RAISE_APPLICATION_ERROR(-20001,'DROP OR TRUNCATE 할 수 없습니다.');
END;
/
<< HR SESSION >>
  CREATE TABLE hr.emp_10
AS SELECT * FROM hr.employees WHERE department_id = 10;

DROP TABLE hr.emp_10 PURGE;
ORA-20001: DROP OR TRUNCATE 할 수 없습니다.
<< INSA01 SESSION >>
  CREATE TABLE test(id number);

DROP TABLE test PURGE;
<< SYS SESSION >>
  RUNCATE TABLE hr.emp_10;

DROP TABLE hr.emp_10 PURGE;

 



dba_users
dba_ts_quotas
dba_sys_privs 
dba_tables 
user_constraints 
user_tab_privs 
dba_tab_privs 
user_sys_privs
v$sgainfo;
dba_triggers 

sys.aud$
dba_stmt_audit_opts 
dba_audit_object
dba_obj_audit_opts
dba_audit_session
sys.fga_log$;
dba_audit_policies
dba_audit_policy_columns
dba_fga_audit_trail

저작자표시

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

57 ORACLE Constraints, Spool, SQL Loader  (1) 2024.01.03
56-2 ORACLE 종속성  (0) 2023.12.28
54-2 ORACLE Audit  (1) 2023.12.26
53 ORACLE Profile, Resource  (1) 2023.12.22
52 ORACLE Role, Execute, Profile  (1) 2023.12.21
'Oracle 11g/Oracle DB'의 다른글
  • 현재글55 ORACLE Audit, Trigger

티스토리툴바