[ORACLE] 객체 참조를 위한 SYNONYM 생성 및 사용 방법

Oracle 유저별로 관리하고 있는 테이블이나 뷰 등 객체를 다른 유저에서 참조해야 하는 경우가 있다.


나의 경우는 인터페이스를 담당하는 쪽에서 우리 데이터를 봐야한다고 해서 계정을 공유해달라고 한 일이 있었다.

어차피 같은 회사에서 요청왔으니 계정 정보를 공유해도 되겠지만.. 두어개 테이블 보여주자고 계정정보를 주면 어디에 접근할지도 모르고 문제 생기는 경우 발뺌하고 모른척하는 아찔한 경험을 할 수도 있다.

여튼 그런 위험을 감수하고 싶지 않아서 DBA 통해서 계정부터 분리했다.



이렇게 분리한 계정에 GRANT 명령어를 통해 접근을 요청한 특정 테이블과 뷰에 조회 및 업데이트를 위한 권한을 부여했다.
계정명.객체명 의 형태로 작성하여 접근하는 방식을 사용하고 있었는데, 이렇게 되는 경우 계정명과 테이블명이 모두 노출되어 보안에 좋지 않다고 한다.

계정 분리해서 나름 보안을 신경썼다고 생각했는데 아직 보안에 안 좋다니 ^_ㅠ
이걸 꽁꽁 더 숨겨야 하나보다.. 나중에 유지보수가 더 힘들어질 것 같은 슬픈 예감이 들지만



기왕 하는거 좀 더 나아가서 Synonym을 사용해보기로 했다. 오늘도 미래의 나에게 도움이 되기 위해 자세히 써야지 ^0^

---

Synonym의 개념이 조금 생소했는데 쿼리에서 사용하는 Alias와 비슷한 개념이라고 한다.

Alias는 해당 쿼리에서 필드나 객체를 부르기 쉽게 만들어 일시적으로 사용하는 별칭이라면 Synonym은 특정 계정별로 혹은 공용으로 등록해서 사용하는 객체를 참조하는 별칭이라고 생각하면 편하다.

A라는 계정에서 생성하여 사용하고 있는 TBL_USER, TBL_AUTH 테이블에 대해 B라는 계정이 이 객체들을 사용하고자 하는 경우를 가정하고 아래와 같이 진행하자.

1. 기존 권한 정보 확인

권한 확인을 원하는 계정으로 접속해서 아래의 쿼리를 날려본다. 시스템 권한 조회는 굳이 안해도 되지만 조사했으니까 한 줄 더 써봤다.

-- 시스템 권한 조회
SELECT * FROM USER_SYS_PRIVS;

-- 객체 권한 조회
SELECT * FROM USER_TAB_PRIVS;

-- ROLE 조회 (기본적으로 CONNECT, RESOURCE 두 개 존재함)
SELECT * FROM USER_ROLE_PRIVS;

A 계정으로 접속해서 객체 권한을 조회하면 해당 계정이 보유한 객체에 대해 권한을 부여한 대상들을 볼 수 있음
만약 B에게 TBL_AUTH, TBL_USER 테이블을 볼 수 있게 권한을 부여했다면 아래와 같은 결과를 볼 수 있다.



GRNATEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRANTABLE HIERARCHY COMMON TYPE INHERITED

반응형

2. 조회/수정/입력/삭제 등 원하는 권한 부여가 안되어 있다면 권한 부여

만약 원하는 권한이 없거나 불필요한 권한이 있다면 GRANT/REVOKE 명령어를 통해 권한을 변경하자.

권한 변경은 객체의 OWNER 계정에서 진행해야 한다. 현재는 A 계정의 객체들에 대해 B에 부여하고자 하는 것이므로 A로 접속해서 진행해야 한다.

-- 권한부여 예시
-- GRANT 부여하고자 하는 권한 명시 ON 객체명 TO 권한을 부여할 계정명
GRANT SELECT, INSERT, UPDATE ON TBL_USER TO B;

-- 해당 객체에 대해 모든 권한을 부여하고 싶은 경우 ALL PRIVILEGES를 사용한다
GRANT ALL PRIVILEGES ON TBL_AUTH TO B;

-- 권한회수 예시
-- REVOKE 회수하고자 하는 권한 명시 ON 객체명 FROM 권한을 회수할 계정명
REVOKE INSERT ON TBL_USER FROM B;

3. 계정명.객체명 으로 호출하던 내용을 SYNONYM으로 대체하기

권한을 모두 부여했다면 아래와 같이 조회했을때 이제 테이블의 내용이 조회가 가능해진다.

SELECT * FROM A.TBL_USER;
SELECT * FROM A.TBL_AUTH;


사실 이렇게만 둬도 부여한 권한에 대해 업무를 수행하는데 전혀 문제가 없다. 보안에 문제가 있다고 하는데 그 마저도 넘길 수 있지만 한 가지 이슈가 있을 수 있는 부분이 있어서 개선하고자 한다.

바로 개발 DB서버와 운영 DB서버간의 계정명이 다른 경우 프로그램에서 쿼리를 작성후 반영할 때 문제가 될 수 있다.



[CASE] 개발 DB의 계정명이 A_DEV이고 운영 DB의 계정명이 A_PRD일 때, A_DEV.USER 의 형태로 참조하던 객체를 A_PRD.USER로 바꿔야 하는 문제 발생

1. 프로그램 단에서 접속 환경에 따라 분기처리
2. 개발환경 반영용 파일과 운영환경 반영용 파일을 따로 관리함
3. 개발/운영 관계 없이 동일한 명칭으로 호출할 수 있도록 Synonym을 사용함

위와 같은 해결방법이 있다고 했을 때 Synonym을 사용하는게 가장 깔끔한 방법이므로 이 방법을 선택하자.

-- Synonym 생성을 위한 명령어
-- CREATE OR REPLACE [PUBLIC] SYNONYM 시노님이름 FOR 해당 시노님으로 조회할 객체명;

CREATE OR REPLACE SYNONYM TBL_AUTH FOR A.TBL_AUTH;
CREATE OR REPLACE SYNONYM USERS FOR A.TBL_USER;

Synonym은 계정 구분없이 공용으로 사용할 수 있는 PUBLIC Synonym과 특정 계정에서만 사용하는 PRIVATE Synonym이 존재한다.
별도로 PUBLIC으로 지정하지 않은 경우, 접속 계정을 대상으로 PRIVATE synonym이 생성된다.

단, 보통 PUBLIC Synonym은 DBA 계정에서 생성하는 경우가 많아 생성 권한이 없을 수 있다.


또한 Synonym 명칭은 객체와 동일한 명칭으로 생성이 가능하다. 유지보수의 용이성을 생각하면 참조하고자 하는 원 객체와 동일한 명칭을 쓰는 것이 좋을테고, 외부에 노출되어야 하는 경우라면 변경하는 것이 보안에 도움이 될 것으로 생각된다.

이건 담당자 재량껏 판단하여 만들면 될 듯!





이제 생성된 정보를 아래의 명령어를 통해 확인할 수 있다.

SELECT * FROM ALL_SYNONYMS
WHERE TABLE_OWNER = 'A';

또한 생성된 정보를 SELECT * FROM TAB; 에서도 볼 수 있다.
인터페이스용으로 만든 계정이라 본인이 보유한 객체가 없었는데 Synonym 생성 이후 TABS 테이블에서 조회가 된다.


Oracle에서 다른 계정의 객체를 참조하여 사용해야 하는 경우 사용할 수 있는 Synonym에 대해 알아봤다.
유지보수 용이성을 위해서는 명시적으로 작성할 필요가 있으나 보안 측면이나 계정명 차이로 인한 배포 시 이슈 해결 등을 위해서는 좋은 선택으로 보인다.





참고
https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_7001.htm

CREATE SYNONYM