[ORACLE] 시노님(SYNONYM) 조회/생성/삭제/이름 변경 SQL

시노 님(SYNONYM)이란?

오라클(Oracle) 객체(테이블, 뷰, 시퀀스, 프로시저)에 대한 대체 이름(=별칭 즉, Alias)을 의미한다.

실질적으로 그 자체가 객체가 아닌 객체에 대한 직접적인 참조이다.

대체로 다른 유저(= 스키마)의 객체를 참조할 때 많이 사용.

 

예제)

EMPLOYEE 테이블을 EMP라는 별칭을 주면 「SELECT * FROM EMP;」와 같이 다른 이름으로 접근할 수 있다.

다른 이름을 사용한 SQL은 테이블 본체의 이름이 바뀌어도 영향을 받지 않는다.

즉, EMPLOYEE라는 테이블명을 EMPLOYEES라고 변경을 하더라도 「SELECT * FROM EMP;」는 정상적으로 실행이 된다.

 

시노님을 사용하는 이유

①데이터베이스의 투명성을 제공하기 위해 사용한다. 

②실무에서 다른 유저의(스키마)의 객체를 참조할 경우가 있을 때, 시노 님을 생성한다면

추후에 참조하고 있는 객체가 이름이 변경되거나 이동될 경우 객체를 사용하는 SQL문을 모두 다시 고칠 필요 없이,

시노님만 재정의하면 되기 때문에 관리하기에 매우 편리하다.

③ 객체의 긴 이름을 사용하기 편한 짧은 이름으로 별칭을 지어, SQL문을 단순화시킬 수 있다.

④ 또한, 객체를 참조하는 스키마의 객체를 숨길 수 있기 때문에 이에 대한 보안을 유지할 수 있다.

시노님을 사용하는(= 시노님 권한이 있는) 유저는 참조하고 있는 객체에 대한 소유자, 이름, 서버 이름을 모르더라도 시노님 이름만 안다면 사용이 가능하다.

 

즉, 원래 객체 이름을 숨기기 위한 목적이거나

다른 스키마 객체의 경우 객체 이름 앞에 스키마 이름을 붙여야 하는데, 이것을 생략해서 사용하는 등에 시노님을 쓴다.

 

시노님을 사용하는 경우는 아래와 같다.

객체의 실제 이름과 소유자, 위치를 숨김으로써 데이터베이스의 보안을 개선하는 데 사용한다.

객체의 Public Access를 제공한다.

Remote 데이터베이스의 Table, View, Program unit을 위해 투명성을 제공한다.

데이터베이스의 사용자를 위해 SQL문을 단순화할 수 있다.

 

시노님의 종류 2가지

・Private Synonym : 전용 시노님은 특정 유저(스키마)만 사용할 수 있다.

・Public Synonym : 공용 시노님은 공용 사용자 그룹이 소유하며 그 데이터베이스에 있는 모든 사용자가 공유한다.

※ default로 private임!

---

1. 시노님 조회

・DBA_SYNONYMS : 데이터베이스의 모든 시노님 조회

DB의 모든 시노님의 정보는 DBA_SYNONYMS에서 얻을 수 있다.

SELECT * FROM DBA_SYNONYMS;

해당 SQL문을 실행하면 아래의 표에 기술한 컬럼이 출력되며 각 컬럼의 의미는 아래와 같다.

열이름	의미
OWNER	시노님 소유자
SYNONYM_NAME	시노님 이름
TABLE_OWNER	시노님에 의해 참조되고 있는 객체의 소유자 퍼블릭 시노님에서는 참조하는 시노님의 작성자
TABLE_NAME	시노님이 참조하는 객체의 이름
DB_LINK	데이터베이스 링크 이름(참조되는 DB링크가 있는 경우에만)

・ ALL_SYNONYMS : 현재 사용자가 액세스할 수 있는 시노님

현재 사용자가 액세스 할 수 있는 시노님의 정보는 ALL_SYNONYMS에서 얻을 수 있다.

SELECT * FROM ALL_SYSNONYMS;

- 해당 SQL문을 실행하면 DBA_SYNONYMS의 컬럼 결과와 동일

 

・USER_SYNONYMS : 현재 유저의 시노님

현재 사용자가 소유한 시노님의 정보는 USER_SYNONYMS에서 얻을 수 있다.

SELECT * FROM USER_SYNONYM;

- 해당 SQL문을 실행하면 DBA_SYNONYMS의 컬럼 결과와 동일하나, OWNER 컬럼은 출력하지 않는다.

---

2. 시노님 생성 및 삭제

시노님 생성과 삭제를 하려면 아래 4가지의 권한이 필요하다.

※ 권한 부여 방법은 다른 게시글 참조

- CREATE ANY SYNONYM

- PUBLIC SYNONYM

- DROP ANY SYNONYM

- DROP PUBLIC SYNONYM 

※ 즉, DBA 권한이 있는 sys 계정으로 진행해야 하므로, oracle 로그인 시 sys로 로그인하기!

 

・시노님 생성 문법

시노님 생성은 CREATE SYNONYM 문법을 사용한다.

※ 디비 링크(DB LINK)가 걸려있는 경우에는 생성 문법이 조금 다름!

CREATE [OR REPLACE] [PUBLIC] SYNONYM 시노님명 FOR 스키마명.오브젝트명;

- 시노님을 덮어쓰는 경우 OR REPLACE를 지정한다.

- 이미 존재하는 Synonym이름으로 생성을 한다면 자동으로 재작성으로 처리가 되어 덮어쓰기가 된다.

- public : 모든 사용자가 접근 가능한 시노님을 생성한다. ※ Default : Private

 

아래의 SQL에서는 별칭을 "emp"로 지정하고 USER1의 employee 오브젝트를 참조한다.

CREATE SYNONYM emp FOR user1.employee;

-- 공용 시노님으로 사용하려면
CREATE PUBLIC SYNONYM emp FOR user1.employee;

 

・시노님 삭제 문법

DROP [PUBLIC/PRIVATE] SYNONYM 시노님명;
-- PUBLIC 시노님이면 PUBLIC 명시적으로 지정해주어야한다.

---

3. 시노님 이름 변경 

・시노님 이름 변경

RENAME 기존시노님명 TO 변경할시노님명;

아래의 SQL문은 emp라는 시노님명을 emp1로 변경한다.

RENAME emp TO emp1;

---

<정리>

- 시노님에 대한 정보는 DBA_SYNNYMS, ALL_SYNNYMS,USER_SYNNYMS에서 확인할 수 있다.
- 시노님 작성 및 삭제를 위해서는  CREATE ANY SYNONYM, PUBLIC SYNONYM, DROP SYNONYM, DROP PUBLIC SYNONYM   권한이 있어야한다.

- 시노님 작성은 CREATE SYNONYM, 변경은 RENAME, 삭제는 DROP 문으로 실행하면 된다.

- 시노님을 공유하려면 PUBLIC을 지정하여 공용 시노님으로 생성하기.

- 시노님을 사용하여 오브젝트를 사용할 때는 시노님이 아닌 오브젝트에 대한 오브젝트 권한이 필요하다.