SYNOYM

SYNOYM - 객체

동의어
Oracle의 객체의 하나로 객체에 대해서 영속적으로 별명을 지어주는 것입니다.
테이블 ALIAS와는 다르게 영속적으로 별명을 부여하는 것이 Synonym입니다.

우리는 이미 Synonym을 사용하고 있습니다.

DUAL이라는 임시 테이블은 SYNONYM입니다.
DUAL테이블은 모든 계정에서 스키마 없이 DUAL이라는 명칭으로 사용되고 있습니다.
DUAL도 임시적으로 사용되는 테이블인데 왜 스키마 없이 DUAL이라는 명칭으로 사용이 가능했을까요?
DUAL 테이블은 어느 계정에 소유된 테이블일까요?

DUAL 테이블은 사실 SYS 계정이 소유하고 있는 테이블입니다.
Public 권한으로 동의어를 생성하여 모든 계정에서는 DUAL이라는 Synonym을 사용하는 것이었습니다.

SYSDATE

[사용법]

SELECT SYSDATE FROM DUAL;

Dual 이라는 테이블은 오라클이 설치될때 만들어지는 테이블이며, 산술,날짜처리,계산 등을 하는 기본 임시 테이블입니다.
간단한 날짜 산술도 가능합니다.

SELECT SYSDATE-1 FROM DUAL; (1일전의 날짜/시간을 반환)
SELECT SYSDATE+1 FROM DUAL; (1일후의 날짜/시간을 반환)
결과 포멧을 유저가 원하는 대로 바꿀수도 있습니다.

SELECT TO_CHAR(SYSDATE, 'HH24:MI:SS') FROM DUAL; (시간만 반환 (24시간포멧), ex> 16:53:43);

SELECT TO_CHAR(SYSDATE, 'HH:MI:SS') FROM DUAL; (시간만 반환, ex> 04:53:43);

SELECT TO_CHAR(SYSDATE, 'YYYYMMDD') FROM DUAL; (날짜만 반환, ex> 20140416);

TO_CHAR 함수는 임의의 데이터를 문자형 데이터타입으로 변경하는 함수입니다.

반환된 날짜 포멧을 유저가 원하는 스타일로 바꿔서 가져올 수 있습니다.

그럼 SYSDATE 함수가 쓰이는 예제를 하나 해 보겠습니다.

TABLE_SHOP 테이블에서 유통기한(EXP_DATE char(8))이 오늘부터 3일 이내인 제품의

이름(PRODUCT_NAME)을 SELECT해 보겠습니다.
SELECT PRODUCT_NAME ,
EXP_DATE
FROM TABLE_SHOP
WHERE EXP_DATE < (SELECT TO_CHAR(SYSDATE, 'yyyy-MM-dd')+3 FROM DUAL);

SYNOYM 왜 사용하나..?

1. 테이블 소유 보안

DUAL 테이블 처럼 긴이름을 짧게 혹은 짧은 이름을 길게 사용하기 위해 Synonym을 이용하기도 하지만, 실제로는 보안의 목적으로 많이 사용되고 있습니다.

위 이미지처럼 사용자가 EMP라는 테이블에 대한 정보가 필요할때 테이블이 어떤 스키마에 있는지 노출시켜서는 안됩니다.

그래서 EMP라는 SYNONYM을 생성하여 해당 SYNONYM에 대한 권한을 사용자에게 부여하여 보안을 강화할 수 있습니다

2. Database 위치 투명성

Database에 접근하기 위해서는 Protocol, IP, Listener Port, SID, 계정 정보를 들고 Database Link를 통해 결과를 받아옵니다.

DEPT라는 테이블은 USA에 있는 정보이고 EMP 테이블은 한국에 있는 정보라고 가정해보겠습니다.

SELECT * FROM EMP;
현재 접속한 유저의 권한으로 EMP 테이블을 읽으면 LOCAL에 있는 Database에 Schema를 읽겠다는 것을 의미합니다.

그런데 USA에 있는 DEPT테이블을 읽어야하는 상황이라면

SELECT * FROM DEPT@USA;
USA라는 DB LINK를 생성하여 @USA로 DB를 조회해야합니다.

사용자 측에서 @USA DB LINK 정보를 같이 사용하도록 데이터베이스에 대한 정보를 제공한다면 DEPT 테이블은 USA에 존재하고

EMP 테이블은 한국에 존재한다는 것을 알게 됩니다.

위 처럼 Database의 위치 투명성에 위반되는 문제를 해결하기 위해 Synonym을 생성해줄 때

DB Link에 대한 정보를 넣어 생성하고 Synonym에 대한 정보만 제공한다면 사용자로부터 Database의 위치를 가려줄 수 있습니다.

3. 업무적 편의

업무적 편의를 위해 사용되기도 합니다.

HR SCHEMA에서 사원(EMP)에 대한 정보를 관리하고 있었습니다.

그런데 회사 정책상 PROD SCHEMA로 사원에 대한 관리 업무가 넘어갔습니다.
만약 기존 시스템에서 개발 시 Synonym으로 DB 정보가 제공되지 않고 테이블에 대한 SCHEMA가 제공되었다면, 쿼리를 HR.EMP로 데이터를 조회 했을겁니다.

PROD SCHEMA로 테이블 정보가 넘어가면 기존에 개발되었던 프로그램에서 HR.EMP 로 구성했던 쿼리를 PROD.EMP로 모두 변경해야하는 번거로움이 생깁니다.

만약 EMP라는 Synonym을 제공했다면, 번거로운 프로그램 변경없이 Synonym에 대한 정보만 수정하면 해결 가능합니다.

운영되고 있는 시스템이라면 더더욱 Synonym의 활용이 유용할 것 같습니다

SYNOYM 생성

GRANT CREATE PUBLIC SYNONYM TO [계정];
SYNONYM 생성에 대한 권한이 없다면 DBA 권한의 계정에서 권한을 부여해줍니다.

CREATE 생략가능[PUBLIC] SYNONYM Synonym명 FOR 객체(계정/테이블);

CREATE PUBLIC SYNONYM EMP FOR EMPLOYEES;
PUBLIC 옵션을 사용해주면 Synonym을 생성한 계정 뿐만 아니라 다른 계정에서도 사용할 수 있습니다.

CREATE PUBLIC SYNONYM DEPT FOR DEPARTMENTS@USA;
DB LINK를 사용해준다면 위와 같이 생성해주면 됩니다.