56-2 ORACLE 종속성

# 감사 로그 프로그램

<< SYS SESSION >>
	CREATE TABLE hr.fga_log (     user_name VARCHAR2(30),     time_stamp TIMESTAMP,     user_sql VARCHAR2(100)     ); CREATE OR REPLACE PROCEDURE hr.fga_proc (     object_schema VARCHAR2,     object_name VARCHAR2,     policy_name VARCHAR2     )        -- 필수 변수 IS     PRAGMA autonomous_transaction;        -- 독립 트랜잭션 BEGIN     INSERT INTO hr.fga_log (user_name,time_stamp,user_sql)     VALUES (sys_context('userenv','session_user'),  -- 현재 정책에 걸린 유저ID             systimestamp,             sys_context('userenv','current_sql'));  -- 현재 정책에 걸린 유저 sql문     COMMIT; END; / SELECT text FROM dba_source WHERE owner = 'HR' AND name = 'FGA_PROC'; >> 소스보기 # 현재 정책에 걸린 유저ID SELECT sys_context('userenv','session_user') FROM dual; SYS_CONTEXT('USERENV','SESSION_USER') ------------------------------------- SYS 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');  -- 프로시저명 END; / SELECT * FROM dba_audit_policies; COLUMNS               VALUE --------------------- -------------------- OBJECT_SCHEMA         HR OBJECT_NAME           EMPLOYEES POLICY_OWNER          SYS POLICY_NAME           FGA_EMP_LOG POLICY_TEXT           employee_id = 100 POLICY_COLUMN         SALARY PF_SCHEMA             HR PF_FUNCTION           FGA_PROC ENABLED               YES SEL                   YES INS                   NO UPD                   NO DEL                   NO AUDIT_TRAIL           DB+EXTENDED POLICY_COLUMN_OPTIONS ANY_COLUMNS
<< HAPPY SESSION >>
	SELECT * FROM hr.employees WHERE employee_id = 100;
<< SYS SESSION >>
	SELECT * FROM hr.fga_log; USER_NAME  TIME_STAMP            USER_SQL ---------- --------------------- -------------------------------------------------- HAPPY      28-DEC-23 01.59.19 PM SELECT * FROM hr.employees WHERE employee_id = 100 HAPPY      28-DEC-23 02.04.02 PM SELECT * FROM hr.employees SELECT to_char(timestamp,'yyyy-mm-dd hh24:mi:ss') timestamp,         db_user, policy_name, sql_bind, sql_text FROM dba_fga_audit_trail; TIMESTAMP           DB_USER POLICY_NAME SQL_BIND   SQL_TEXT ------------------- ------- ----------- ---------- --------------------------------------- 2023-12-28 15:03:12 HAPPY   FGA_EMP_LOG            SELECT * FROM hr.employees where empl.. 2023-12-28 15:03:20 HAPPY   FGA_EMP_LOG            SELECT * FROM hr.employees

 

---

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

FUNCTION hr.validate_comm -> hr.employees의 commission_pct을 항상 참조
PROCEDURE reset_comm -> validate_comm()을 항상 참조

commission_pct 칼럼의 MODIFY 작업-> 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;

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(-2000,'Invalid commission');
    END IF;
END reset_comm;
/

SELECT text FROM user_source where name = 'RESET_COMM' ORDER BY line;


execute reset_comm(0.2)

SELECT object_name, object_type, status
FROM dba_objects
WHERE owner = 'HR'
AND object_name IN ('EMPLOYEES','RESET_COMM','VALIDATE_COMM');
OBJECT_NAME     OBJECT_TYPE         STATUS
--------------- ------------------- -------
EMPLOYEES       TABLE               VALID
RESET_COMM      PROCEDURE           VALID
VALIDATE_COMM   FUNCTION            VALID



# 객체관리
- 데일리 체크 대상

SELECT object_name, object_type, status
FROM dba_objects
WHERE status = 'INVALID';
>> 실행불가 상태

SELECT * FROM dba_dependencies
WHERE referenced_name IN ('EMPLOYEES','RESET_COMM','VALIDATE_COMM');
OWNER NAME               TYPE      REFERENCED                                 DEPENDENCY
                                   _OWNER _NAME         _TYPE     _LINK_NAME  _TYPE
----- ------------------ --------- ------ ------------- --------- ----------- ----------
HR    SECURE_EMPLOYEES   TRIGGER   HR     EMPLOYEES     TABLE                 HARD
HR    EMP_DETAILS_VIEW   VIEW      HR     EMPLOYEES     TABLE                 HARD
INSA  EMP_VIEW           VIEW      HR     EMPLOYEES     TABLE                 HARD
HR    AUDIT_EMP_VALUES   TRIGGER   HR     EMPLOYEES     TABLE                 HARD
HR    VALIDATE_COMM      FUNCTION  HR     EMPLOYEES     TABLE                 HARD
HR    UPDATE_JOB_HISTORY TRIGGER   HR     EMPLOYEES     TABLE                 HARD
OE    EMPLOYEES          SYNONYM   HR     EMPLOYEES     TABLE                 HARD
HR    RESET_COMM         PROCEDURE HR     VALIDATE_COMM FUNCTION              HARD

NAME                 : 참조를 하는 객체
REFERENCED_NAME      : 참조를 당하는 객체      


desc hr.employees
Name           Null     Type          
-------------- -------- ------------ 
COMMISSION_PCT          NUMBER(2,2)
...

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

desc hr.employees
Name           Null     Type          
-------------- -------- ------------ 
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');
OBJECT_NAME     OBJECT_TYPE         STATUS
--------------- ------------------- -------
EMPLOYEES       TABLE               VALID
RESET_COMM      PROCEDURE           INVALID 
VALIDATE_COMM   FUNCTION            INVALID 

execute reset_comm(0.3)
PLS-00905: object SYS.RESET_COMM is invalid
PL/SQL: Statement ignored

INVALID 상태 : 컴파일해야 진행된다.
>> 오라클이 내부 컴파일 진행

SELECT object_name, object_type, status
FROM dba_objects
WHERE owner = 'HR'
AND object_name IN ('EMPLOYEES','RESET_COMM','VALIDATE_COMM');
OBJECT_NAME     OBJECT_TYPE         STATUS
--------------- ------------------- -------
EMPLOYEES       TABLE               VALID
RESET_COMM      PROCEDURE           VALID 
VALIDATE_COMM   FUNCTION            VALID 


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');
OBJECT_NAME     OBJECT_TYPE         STATUS
--------------- ------------------- -------
EMPLOYEES       TABLE               VALID
RESET_COMM      PROCEDURE           INVALID 
VALIDATE_COMM   FUNCTION            INVALID 

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');
OBJECT_NAME     OBJECT_TYPE         STATUS
--------------- ------------------- -------
EMPLOYEES       TABLE               VALID
RESET_COMM      PROCEDURE           INVALID 
VALIDATE_COMM   FUNCTION            VALID 


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');
OBJECT_NAME     OBJECT_TYPE         STATUS
--------------- ------------------- -------
EMPLOYEES       TABLE               VALID
RESET_COMM      PROCEDURE           VALID 
VALIDATE_COMM   FUNCTION            VALID 


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

SELECT OWNER,OBJECT_NAME,OBJECT_TYPE,STATUS
FROM dba_objects WHERE status = 'INVALID';
OWNER   OBJECT_NAM     OBJECT_TYPE  STATUS
------- -------------- ------------ -------
HR      VALIDATE_COMM  FUNCTION     INVALID
HR      RESET_COMM     PROCEDURE    INVALID
...


# INVALID 상태 -> COLPILE 작업대상 일괄 생성
SELECT * FROM dba_objects 
WHERE object_type IN ('PROCEDURE','FUNCION','TRIGGER')
AND status = 'INVALID';

SELECT 'ALTER '||object_type||' '||owner||'.'||object_name||' COMFILE;'
FROM DBA_OBJECTS
WHERE object_type IN ('PROCEDURE','FUNCION','TRIGGER')
AND status = 'INVALID';
↓ 
ALTER TRIGGER HR.SECURE_EMPLOYEES COMFILE;
ALTER PROCEDURE HR.INSERT_EMP COMFILE;
ALTER PROCEDURE SYS.RESET_COMM COMFILE;
...

---

# 영향도 평가

<< SYS SESSION >> SQL> @$ORACLE_HOME/rdbms/admin/utldtree.sql desc sys.deptree_fill  Argument Name                  Type                    In/Out Default  ------------------------------ ----------------------- ------ --------  TYPE                           CHAR                    IN  SCHEMA                         CHAR                    IN  NAME                           CHAR                    IN EXECUTE sys.deptree_fill('table','hr','employees') 'table'     : TYPE    객체(TABLE, VIEW, PROCEDURE, TRIGGER, ...) 'hr'        : SCHEMA  소유자 'employees' : NAME    객체명 SELECT * FROM sys.deptree ORDER BY seq#; NESTED_LEVEL TYPE            SCHEMA     NAME                       SEQ# ------------ --------------- ---------- -------------------- ----------            0 TABLE           HR         EMPLOYEES                     0            1 VIEW            HR         EMP_DETAILS_VIEW              9            1 TRIGGER         HR         SECURE_EMPLOYEES             10            1 TRIGGER         HR         UPDATE_JOB_HISTORY           11            1 SYNONYM         OE         EMPLOYEES                    12            1 VIEW            INSA       EMP_VIEW                     13            1 TRIGGER         HR         AUDIT_EMP_VALUES             14            1 FUNCTION        HR         VALIDATE_COMM                15            2 PROCEDURE       HR         RESET_COMM                   16 NESTED_LEVEL : 1 = 0번을 참조한다. NESTED_LEVEL : 2 = 1번을 참조한다. >> 트리구조