231228 Linux_감사 정책, 종속성 관계, 컴파일

#) 정책 생성

BEGIN
  DBMS_FGA.ADD_POLICY(
    object_schema     => 'hr',
    object_name       => 'employees',
    policy_name       => 'fga_emp_select',
    audit_condition   => 'employee_id = 100',
    audit_column     => 'salary',
    audit_column_opts => dbms_fga.any_columns , --기본값
    enable            => TRUE, --정책은 추가하되, 돌아가지 않는다.
    statement_types   => 'SELECT'
  );
END;
/

#) 정책 생성 확인

SELECT object_schema, object_name, policy_name, policy_text, policy_column, sel, ins, upd, del, policy_column_options, audit_trail
FROM dba_audit_policies;

# 정책 비활성화

- 활성화된 정책 확인 먼저 하기

SELECT object_schema, object_name, policy_name, policy_text, policy_column, sel, ins, upd, del, policy_column_options, audit_trail, enabled
FROM dba_audit_policies;

 

#) 비활성화 실행 : disable_policy

begin 
    dbms_fga.disable_policy (
    object_schema     => 'hr',
    object_name       => 'employees',
    policy_name       => 'fga_emp_select' );
end;
/

# 정책 활성화

- 비활성화된 정책 확인.

SELECT object_schema, object_name, policy_name, policy_text, policy_column, sel, ins, upd, del, policy_column_options, audit_trail, enabled
FROM dba_audit_policies;

 

#) 정책 활성화 하기 : enable_policy

begin 
    dbms_fga.enable_policy (
    object_schema     => 'hr',
    object_name       => 'employees',
    policy_name       => 'fga_emp_select' );
end;
/

 

#) 정책 활성화 확인

SQL>

SELECT object_schema, object_name, policy_name, policy_text, policy_column, sel, ins, upd, del, policy_column_options, audit_trail, enabled
FROM dba_audit_policies;

 

# test 용으로 생성

 

#1) <SYS SESSION> or <HR SESSION>

- table 생성

create table hr.fga_log (
    user_name varchar2(30),
    time_stamp timestamp ,
    user_sql varchar2(100)
);

 

#2) 프로시저 생성

CREATE OR replace PROCEDURE hr.fga_proc (
    object_schema  VARCHAR2,
    object_name    VARCHAR2,
    policy_name    VARCHAR2
) -- 필수
IS 
    pragma autonomous_transaction; 
    -- 독립적 + 다른 fga에 영향을 주지 않음
BEGIN
    INSERT INTO hr.fga_log(user_name, time_stamp, user_sql)
    VALUES (sys_context('userenv', 'session_user'), systimestamp, sys_context('userenv', 'current_sql'));
    COMMIT;
end;
/

 

#2-1) sys_context 확인

<happy session>

select sys_context('userenv', 'session_user') from dual;

select sys_context('userenv', 'current_sql') from dual;

#3) 에러유무 확인

show error

#4) 소스 확인

SELECT * FROM dba_source WHERE owner = 'HR' AND name = 'FGA_PROC';

#5) <SYS SESSION>
-- 프로시저 수행하기 위함

BEGIN
  DBMS_FGA.ADD_POLICY(
    object_schema     => 'hr', 
    object_name       => 'employees',
    policy_name       => 'fga_emp_log',
    audit_condition   => 'employee_id = 100',
    audit_column     => 'salary',
    audit_column_opts => dbms_fga.any_columns ,
    enable            => TRUE ,
    statement_types   => 'SELECT' ,
    handler_schema    => 'hr',
    handler_module    => 'fga_proc' --프로그램을 돌아가기 위해서는 프로시저 생성해주는것이 best!
  );
END;
/

#5-1) 정책 확인

SELECT * FROM dba_audit_policies;

#6) test 해보기

<happy session>

select * from hr.employees;

#7) select문 확인되는지check!

<sys session>

select to_char(timestamp,'yyyy-mm-dd hh24:mi:ss') timestamp, db_user, policy_name, sql_text, sql_bind
from dba_fga_audit_trail;

<HR session>
■ 종속성 관계
- 내가 참조하는 객체가 구조가 변경되었다는지 컬럼의 타입이 변경되었으면 나는 실행 불가능한 상태로 된다.

#) 함수생성
- 종속관계 : reset_comm -> validate_comm -> employees(commission_pct)
- employees(commission_pct)에서 컬럼이 타입이나 사이즈가 변경이 된다면, validate_comm, reset_comm 은 차례대로 실행 불가

CREATE OR REPLACE FUNCTION hr.validate_comm ( v_c IN NUMBER ) 
RETURN BOOLEAN 
IS
    v_max_comm NUMBER;
BEGIN
    SELECT MAX(commission_pct)
    INTO v_max_comm
    FROM hr.employees; --종속 관계

    IF v_c > v_max_comm THEN
        return(false);
    ELSE
        return(true);
    END IF;

END validate_comm;
/

#) 함수 소스 확인

select text from user_source where name = 'VALIDATE_COMM' order by line;

#)프로시저 생성 
- reset_comm 은 validate_comm 와 종속관계

CREATE OR REPLACE PROCEDURE hr.reset_comm( v_comm IN NUMBER )
IS 
    g_comm NUMBER :=0.1;
BEGIN 
    IF validate_comm(v_comm) then
        dbms_output.put_line('OLD : '||g_comm);
        g_comm := v_comm;
        dbms_output.put_line('NEW :'||g_comm);
    ELSE
        raise_application_error(-20200, 'Invalid commission');
    END IF;
END reset_comm;
/

#) 프로시저 소스 확인

select text from user_source where name = 'RESET_COMM' order by line;

#) test 해보기

<hr session>

execute reset_comm(0.2);

execute reset_comm(0.5);

<SYS SESSION>

#) 상태 확인

select object_name, object_type, status
from dba_objects
where owner = 'HR';

 

#) 종속관계 있는 상태 확인

select object_name, object_type, status
from dba_objects
where owner = 'HR'
AND object_name in ('EMPLOYEES', 'RESET_COMM', 'VALIDATE_COMM');

 

- status의 값이 VALID : 실행가능 // INVALID : 실행 불가능

#) INVALID 인 상태 체크

select *
from dba_objects
where status = 'INVALID';

#)종속관계 확인
참조를 당하는 객체 : referenced_name
참조를 하는 객체 : name

SELECT * 
FROM dba_dependencies
WHERE referenced_name IN ('EMPLOYEES', 'RESET_COMM', 'VALIDATE_COMM');

#1) type 변경 

- NUMBER(3,2) 로 변경했을 경우, 종속관계에 있는 프로그램들은 실행 불가능(INVALID)

desc hr.employees;

#) TYPE 변경

ALTER TABLE hr.employees MODIFY commission_pct NUMBER(3,2);

#) 상태 확인

select object_name, object_type, status
from dba_objects
where owner = 'HR'
AND object_name in ('EMPLOYEES', 'RESET_COMM', 'VALIDATE_COMM');

<hr session>

execute reset_comm(0.3);

--INVALID 로 다시 컴파일을 해야 한다.
-- 서버 프로세스가 상태 확인 -> INVALID 확인 -> 내부적으로 재컴파일. -> 프로시저 완료 -> 출력 

<sys session>
#) type 변경 후  상태 확인

SELECT * 
FROM dba_dependencies
WHERE referenced_name IN ('EMPLOYEES', 'RESET_COMM', 'VALIDATE_COMM');

#) 수정

ALTER TABLE hr.employees MODIFY commission_pct NUMBER(4,2);

#) 수정 후 확인

SELECT object_name, object_type, status
FROM dba_objects
WHERE OWNER = 'HR'
AND object_name IN ('EMPLOYEES', 'RESET_COMM', 'VALIDATE_COMM');

#) 컴파일 방법
#1) 함수 컴파일 -> 생성 확인

ALTER function hr.validate_comm compile;

 

SELECT object_name, object_type, status
FROM dba_objects
WHERE OWNER = 'HR'
AND object_name IN ('EMPLOYEES', 'RESET_COMM', 'VALIDATE_COMM');

#2) 프로시져 컴파일 -> 생성 확인

ALTER PROCEDURE hr.reset_comm COMPILE;

SELECT object_name, object_type, status
FROM dba_objects
WHERE OWNER = 'HR'
AND object_name IN ('EMPLOYEES', 'RESET_COMM', 'VALIDATE_COMM');

#)type 변경

ALTER TABLE hr.employees MODIFY commission_pct NUMBER(5,2);

#) INVALID 확인

SELECT * 
FROM dba_objects
WHERE status = 'INVALID';

#) INVALID 변경해주기 

SELECT 'ALTER ' || object_type ||' '|| owner||'.'||object_name||' COMPILE;'
FROM dba_objects
WHERE object_type in ('PROCEDURE', 'FUNCTION', 'TRIGGER')
AND status = 'INVALID';

 

-- 변경할 TYPE 한번에 실행
ALTER TRIGGER HR.SECURE_EMPLOYEES COMPILE;
ALTER PROCEDURE HR.INSERT_EMP COMPILE;
ALTER PROCEDURE HR.INSERT_EMP1 COMPILE;
ALTER PROCEDURE HR.INSERT_EMP2 COMPILE;
ALTER FUNCTION HR.VALIDATE_COMM COMPILE;
ALTER PROCEDURE HR.RESET_COMM COMPILE;

SELECT 'ALTER ' || object_type ||' '|| owner||'.'||object_name||' COMPILE;'
FROM dba_objects
WHERE object_type in ('PROCEDURE', 'FUNCTION', 'TRIGGER')
AND status = 'INVALID';

 

SELECT 'ALTER ' || object_type ||' '|| owner||'.'||object_name||' COMPILE;'
FROM dba_objects
WHERE object_type = 'PACKAGE'
AND status = 'INVALID';

- 없다면 pass 해도 된다.

 

SELECT 'ALTER PACKAGE ' || owner||'.'||object_name||' COMPILE BODY;'
FROM dba_objects
WHERE object_type = 'PACKAGE BODY'
AND status = 'INVALID';

- 없다면 pass 해도 된다.

#) 특정하게 하나만 지정해서 조회하면 종속된 다른 referenced_name 은 확인이 되지 않는다. ('영향도 평가')

SELECT * 
FROM dba_dependencies
WHERE referenced_name = 'EMPLOYEES';

특정한 referenced_name  하나만 지정해서 조회했을 경우, 종속 관계에 있는 부분도 보여주기 위해 어떻게 해야 하나?
#) ★ 꼭 local(putty) 창에서 실행!!
SQL> @$ORACLE_HOME/rdbms/admin/utldtree.sql : 스크립트 수행해야 할 때

- 위의 문장을 실행하는 이유는? 객체 간의 의존성을 계층적으로 표시할 수 있다.

#1) utldtree.sql 

1)  DB 관리 작업 및 성능 최적화를 위해 필요한 메타 데이터를 쉽게 얻기 위해 실행

2) Oracle DB 에서 data 딕셔너리 뷰를 생성하는데 사용되는 스크립트

SQLPLUS 에서 실행되는

[oracle1@oracle ~]$ vi $ORACLE_HOME/rdbms/admin/utldtree.sql : 편집기를 통해 소스 확인

 

desc sys.deptree_fill

#) EXECUTE 하기

execute sys.deptree_fill('table', 'hr', 'employees')

#) 확인해보기

select * from sys.deptree;