231227 Linux_DBA 감사, TRIGGER

■ DBA 감사

select * from sys.fga_log$; -- 조회
truncate table sys.fga_log$; --삭제

SQL> show parameter audit
NAME                        TYPE        VALUE
------------------------------ -------------- ----------------------------------
audit_file_dest            string      /u01/app/oracle/admin/ora11g/adump
audit_sys_operations  boolean   FALSE : 감사 이력정보 남기지 않겠다 (TRUE 면 이력정보 남기겠다.)

-- VALUE = TRUE 로 변경하려면 DB 내려야 함.

select * from v$parameter where name = 'audit_sys_operations';

#) audit_sys_operations 변경
SQL>

alter system set audit_sys_operations=true scope=spfile;

shutdown immediate
startup

#) 변경된 값 확인

SQL> show parameter audit

SQL>

select * from v$sgainfo;

SQL>

alter system set shared_pool_size = 113246208;

SQL> !
[oracle1@oracle ~]$ cd /u01/app/oracle/admin/ora11g/adump
[oracle1@oracle adump]$ pwd
/u01/app/oracle/admin/ora11g/adump
[oracle1@oracle adump]$ ls -alt

[oracle1@oracle adump]$ vi ora11g_ora_27632_20231227150258950154143795.aud

여기서 sys 계정으로 로그 정보가 확인된다.

 

#) audit_sys_operations=false 로 변경. (계속적으로 정보 저장되면서 용량 문제 생김)

SQL> alter system set shared_pool_size = 113246208;
SQL> alter system set audit_sys_operations=false scope=spfile;
SQL> shutdown immediate
SQL> startup

# trigger 용 table

CREATE TABLE drop_obj_log (
    obj_user   VARCHAR2(50),
    obj_name   VARCHAR2(30),
    obj_type   VARCHAR2(30),
    drop_time  TIMESTAMP
)
TABLESPACE audit_tbs;

 

※ DDL trigger : drop 쿼리문을 수행하고 있는 session에서 수행된다

CREATE OR REPLACE TRIGGER drop_obj_trigger
BEFORE DROP ON DATABASE -- 데이터베이스 레벨
BEGIN
    INSERT INTO drop_obj_log(obj_user, obj_name, obj_type, drop_time)
    VALUES(USER, -- 계정명
           ora_dict_obj_name, -- drop 하고 있는 현재 session
           ora_dict_obj_type, -- 현재 삭제하고 있는 객체 타입
           systimestamp -- drop 하고 있는 시간
           );
END;
/

tip) 데이터베이스 레벨이란?

- 데이터베이스 전체에 적용되는 트리거. 특히, 어떤 스키마나 특정 객체 유행에 제한되지 않고 데이터베이스 전체에서 발생하는 'DROP' 이벤트에 대응함.

# 트리거 확인

select * from dba_triggers where trigger_name = 'DROP_OBJ_TRIGGER';

 

# test해보자

<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;

<sys session>

- DROP 문을 실행 했을때, 해당 테이블을 확인 된다.

select * from drop_obj_log;

<hr session>

create sequence id_seq;
drop sequence id_seq;

<sys session>

select * from drop_obj_log;

#) USER 생성 후 DROP 실행. 로그정보 확인

CREATE USER ora10 IDENTIFIED BY ORACLE;
DROP USER ora10;
SELECT * FROM drop_obj_log;

#) 트리거 삭제

drop trigger drop_obj_trigger;

※ drop, truncate 를 수행 할 수 없게 트리거 생성

- DB에 있는 모든 user 에게 해당

CREATE OR REPLACE TRIGGER no_drop
BEFORE DROP OR TRUNCATE ON DATABASE
BEGIN
    raise_application_error(-20001, 'DROP OR TRUNCATE 할 수 없습니다.');
END;
/

#) 트리거 생성 확인

select * from dba_triggers where trigger_name = 'NO_DROP';

<hr session>

- 오류를 유발시키는 trigger 생성해 다른 session에서 오류발생 확인 가능.

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

<sys session>

drop trigger no_drop;

※ 특정한 유저 레벨에서 drop, truncate 를 수행 할 수 없게 트리거 생성

- DB에 있는 특정한 계정인 hr 에게 해당

CREATE OR REPLACE TRIGGER hr_drop
BEFORE DROP OR TRUNCATE ON hr.SCHEMA 
BEGIN
    raise_application_error(-20001, 'DROP OR TRUNCATE 할 수 없습니다.');
END;
/

#)트리거 확인

select * from dba_triggers where trigger_name = 'HR_DROP';

<insa01 session>

- hr 계정이 아니므로, drop+truncate 가능하다.

CREATE TABLE test(id number);
DROP TABLE test;

<hr session>

- hr 계정에서는 drop+truncate 불가능하며, 오류발생 확인된다.

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

SELECT * FROM hr.emp_30;

DROP TABLE hr.emp_30;

<sys session>

- 트리거만 삭제.

DROP TRIGGER hr_drop;

- 해당 table로 drop, truncate 문 실행한 로그 정보 확인 가능하다.

select * from drop_obj_log;