2024-07-03(수) 2
1. CASE
이어서 DB를 이관하는 과정에서 한 가지 문제사항이 생겼다.
전체 계정을 관리하는 최상위 계정인 A계정, 이관 전의 계정을 B계정 그리고 이관 후의 계정을 C계정이라고 하겠다.
이관하는 계정인 C계정은 아예 새로 만들어야 되는 건 아니었고, 기존에 사용했던 적이 있던 계정이었다.
문제는 이관한 C계정에서 특정 테이블(*USER1.TABLENM)이 조회되지 않았고, 원인은 USER1이 아닌 USER2를 참조하고 있었다. 즉, 다른 데이터베이의 객체를 참조하고 있는 것이었다.
대신에 조회하는 테이블명은 동일했기 때문에 B계정과 C계정의 데이터를 비교해 본 결과, 데이터도 동일하다고 판단했다.
결국에는 C계정에서 USER2로 참조하고 있었던 걸 모두 USER1으로 바꿔야 했는데 수십 개의 패키지와 프로시저를 하나하나씩 바꾸기에는 너무 노가다성이 짙었다.
그래서 찾은 방법이 시노님(SYNONYM)과 트리거(TRIGGER)를 쓰는 거였다.
(*보안상 임의의 유저와 테이블명을 사용함)
2. Used Code (보안상 코드를 각색함)
-- 1) A계정의 모든 테이블에 대한 시노님을 작성한다.
BEGIN
FOR t IN(SELECT table_name FROM all_tables WHERE owner = 'A계정')
LOOP
EXECUTE IMMEDIATE 'CREATE SYNONYM USER2.' || t.table_name || ' FOR A계정.' || t.table_name;
END LOOP;
END;
-- USER2 유저 생성
CREATE USER USER2 IDENTIFIED BY USER2;
-- USER2 유저에 A계정의 모든 테이블에 대한 SELECT 권한 부여
GRANT SELECT ON A계정 TO USER2;
-- 2) A계정에 새로운 테이블이 생성될 시, 자동으로 트리거를 생성한다.
CREATE OR REPLACE TRIGGER create_synonym_on_new_table
AFTER CREATE ON SCHEMA
DECLARE
v_table_owner VARCHAR2(30);
v_table_name VARCHAR2(30);
BEGIN
-- 시스템 이벤트 속성에서 테이블의 소유자와 이름을 가져옴
SELECT ora_dict_obj_owner, ora_dict_obj_name
INTO v_table_owner, v_table_name
FROM dual;
-- 테이블 소유자가 A계정일 경우에만 Synonym을 생성함
IF v_table_owner = 'A계정' THEN
EXECUTE IMMEDIATE 'CREATE SYNONYM USER2.' || v_table_name || 'FOR A계정.' || v_table_owner || '.' || v_table_name;
END IF;
END;
3. How to Use
1️⃣시노님 (SYNONYM)
*참고 ) https://bangu4.tistory.com/319
1) 시노님이란?
시노님(동의어)은 데이터베이스에서 테이블, 뷰, 시퀀스 또는 기타 스키마 개체에 대한 별칭 또는 대체 이름이다. 이는 주로 사용자가 다른 사용자가 소유한 데이터베이스 개체에 액세스 하는 것을 만드는 데 사용된다.
2) 사용 이유
- 오브젝트명(테이블명)을 짧게 하여 쿼리를 단순화시킬 수 있음
- 스키마, 오브젝트명이 변경되어도 시노님만 재생성하면 쿼리문은 변경하지 않아도 됨
- 오브젝트를 외부에 제공할 경우 스키마, 오브젝트명을 숨길 수 있어서 보안에 도움이 됨
- 테이블뿐만 아니라 View, Procedure, Function, Sequence 등의 오라클 오브젝트에서도 사용이 가능함
- 다른 데이터베이스와의 링크 기술자도 시노님을 사용해 줄일 수 있음
이처럼 단순성과 보안성, 융통성 등의 이유로 시노님을 많이 사용한다.
3) 사용 방법
시노님은 접근 제한자(PUBLIC / PRIVATE)가 존재한다.
- PUBLIC : 해당 테이블에 권한을 가지고 있는 모든 유저가 시노님을 사용할 수 있음
- PRIVATE : 시노님을 선언한 해당 유저만 시노님을 사용할 수 있음
① 해당 계정으로 로그인하여, 조회할 수 있는 계정에 권한을 부여한다.
-- 모든 권한을 주고 싶을 때
GRANT ALL ON USER1.TABLEINFO TO USER2;
-- 일부 권한만 주고 싶을 때
GRANT SELECT ON USER1.TABLEINFO TO USER2;
GRANT SELECT, INSERT, DELETE, UPDATE ON USER1.TABLEINFO TO USER2;
② 조회
SELECT * FROM ALL_SYNONYMS;
③ 생성 / 변경
CREATE OR REPLACE [PUBLIC / PRIVATE] SYNONYM [시노님명]
FOR [스키마명.테이블명]
④ 삭제
DROP SYNONYM employees;
DROP PUBLIC SYNONYM employees;
*PUBLIC 시노님은 꼭 PUBLIC를 붙여서 삭제해야 한다.
(나는 최상위 계정의 모든 테이블에 대한 시노님을 작성해야 했기 때문에, LOOP 문을 추가하여 사용했다.)
4) 뷰와 시노님
- 뷰
: 쿼리의 결과에 기반한 가상 테이블이다.
: 복잡한 쿼리를 캡슐화하고, 데이터를 단순화된 방식으로 표현할 수 있다.
- 시노님
: 데이터베이스 객체(테이블, 뷰, 시퀀스 또는 다른 동의어)의 별칭이다.
: 다른 이름을 사용하여 객체를 참조할 수 있다.
2️⃣트리거 (Trigger)
*참고 ) https://pongshowng.tistory.com/26
1) 트리거란?
트리거는 데이터베이스에서 하나의 구문에 값이 입력될 때 자동으로 다른 테이블의 값을 변경하는 등의 업무를 할 수 있도록 하는 SQL Server의 데이터베이스 객체이다. 데이터 작업 제한, 작업 기록, 변경 작업 감사 등을 할 수 있다. 테이블에 어떤 이벤트가 발생했을 때, 자동으로 사용자가 정의한 PL/SQL 명령을 실행할 수 있는 구문이다.
2) 사용 방법
CREATE [OR REPLACE] TRIGGER 트리거명 [BEFORE | AFTER]
triggering-event[Insert, Delete, Update] ON 테이블명
[Referencing OLD AS {변경 전 값을 참조하는 변수명} NEW AS {변경 후 값을 참조하는 변수명}]
[FOR EACH ROW] -- 이 옵션이 있으면 행 트리거가 됨
[WHEN (condition)] -- 데이터 처리 유형 이외의 조건을 삽입할 경우에 사용함
DECLARE
( 변수 선언 )
BEGIN
( 트리거 PL/SQL 명령 작성 )
EXCEPTION
END;
*FOR EACH ROW
- 행 수준 트리거(Row-Level Triggers) : 트랜잭션 내의 각 행에 대해 한 번만 수행한다. (컬럼의 각각의 행에 변화가 생길 때마다 실행되며, 각 데이터 행의 값을 제어할 수 있음)
- 문장 수준 트리거(Statement-Level Triggers) : 트랜잭션 내에서 한 번만 수행되며, 컬럼의 각 데이터 행을 제어할 수 없다.
① 활성화 / 비활성화
ALTER TRIGGER [schema.]trigger ENABLE; ===> 활성화
ALTER TRIGGER [schema.]trigger DISABLE; ===> 비활성화
② 조회
SELECT table_name, status FROM user_triggers;
③ 삭제
DROP TRIGGER [schema.]trigger;
3) 장단점
- 장점
: 데이터의 무결성 강화 (참조 무결성)
: 업무처리 자동화
- 단점
: 과도하게 사용하면 복잡한 상호 의존성을 야기
4. Reviewww
DB를 이관하기 위해 많은 시행착오가 있었다. 패키지나 테이블, 뷰 등 큰 부분보다 테이블 안의 데이터나 쿼리문과 같은 작은 부분에서 생각해야 할 부분이 많았기에 이번 일을 통해 아는 만큼 보인다는 걸 깨달았다.