[Oracle(SQL)] SYNONYM(동의어), 권한과 ROLE

동의어(SYNONYM)
- 다른 데이터베이스가 가진 객체에 대한 별명 혹은 줄임말
- 여러 사용자가 테이블을 공유할 경우 다른 사용자가 테이블에 접근할 때

  '사용자명.테이블명'으로 표현하는데 동의어를 사용하면 간단하게 사용할 수 있다.

생성 방법

CREATE SYNONYM 줄임말 FOR 사용자명.객체명;
CREATE SYNONYM EMP FOR EMPLOYEE;

 

SELECT
       E.*
  FROM EMP E;

<시스템 계정으로 실행> (권한부여)

GRANT CREATE SYNONYM TO C##EMPLOYEE;

동의어의 구분

 

1. 비공개 동의어
- 객체에 대한 접근 권한을 부여 받은 사용자가 정의한 동의어

2. 공개 동의어
- 모든 권한을 주는 사용자가 정의한 동의어
- 모든 사용자가 사용할 수 있음(PUBLIC)

<시스템 계정으로 실행>

CREATE PUBLIC SYNONYM DEPT FOR C##EMPLOYEE.DEPARTMENT;

- 공개 동의어로 설정되어 시스템 계정에서도 조회 가능

SELECT
       D.*
  FROM DEPT D;

- 비공개 동의어로 설정되어 시스템 계정에서는 조회 불가

SELECT
       E.*
  FROM EMP E;

---

권한과 ROLE

<사용자 관리>
- 사용자의 계정과 암호설정, 권한 부여

보안을 위한 데이터베이스 관리자
- 사용자가 데이터베이스의 객체(테이블, 뷰 등)에 대해 특정 권한을 가질 수 있게 하는 권한이 있음
- 다수의 사용자가 공유하는 데이터베이스 정보에 대한 보안 설정
- 데이터베이스에 접근하는 사용자마다 서로 다른 권한과 롤을 부여함

내가 다른 사용자에게 부여한 객체 권한을 조회
<SYSTEM 계정으로 실행>

SELECT
       UTPR.*
  FROM USER_TAB_PRIVS_RECD UTPR;

나에게 부여된 객체 권한, 객체 이름을 조회

SELECT
       UTPM.*
  FROM USER_TAB_PRIVS_MADE UTPM;

1. 시스템 권한

- 데이터베이스 관리자가 가지고 있는 권한으로 오라클 접속, 테이블, 뷰, 인덱스 등의 생성 권한
- CREATE USER(사용자 계정 만들기)
- DROP USER(사용자 계정 삭제)
- DROP ANY TABLE(임의의 테이블 삭제)
- QUERY REWRITE(함수 기반 인덱스 생성 권한)
- BACKUP ANY TABLE(테이블 백업)

시스템 관리자가 사용자에게 부여하는 권한
- CREATE SESSION(데이터베이스에 접속)
- CREATE TABLE(테이블 생성)
- CREATE VIEW(뷰 생성)
- CREATE SEQUENCE(시퀀스 생성)

<SYSTEM 계정으로 실행>

CREATE USER C##SAMPLE IDENTIFIED BY SAMPLE;

=>생성한 SAMPLE 계정으로 접속 시도 시 접속 권한(CREATE SESSION)이 없어서 접속 불가

GRANT CREATE SESSION TO C##SAMPLE;

- C##SAMPLE 테이블 생성 구문 실행

GRANT CREATE TABLE TO C##SAMPLE;

=>테이블 생성 권한(CREATE TABLE)이 없어서 생성 불가

CREATE TABLE TEST_TABLE(
  COL1 VARCHAR2(20),
  COL2 NUMBER
);

WITH ADMIN OPTION
- 사용자에게 시스템 권한을 부여할 때 사용함
- 권한을 부여받은 사용자는 다른 사용자에게 권한을 지정할 수 있음

(시스템계정에서 실행)

GRANT CREATE SESSION TO C##SAMPLE
WITH ADMIN OPTION;

C##SAMPLE2 계정 생성하기 (SYSTEM 계정에서 실행)

CREATE USER C##SAMPLE2 IDENTIFIED BY SAMPLE2;

=>권한 부여 전이라 오류발생

 

=>C##SAMPLE 계정으로 C##SAMPLE2 계정에 접속 권한 부여하기

GRANT CREATE SESSION TO C##SAMPLE2;

2. 객체 권한

- 사용자가 특정 객체(테이블, 뷰, 시퀀스, 함수)를 조작하거나 접근할 수 있는 권한
- DML(SELECT/INSERT/UPDATE/DELETE)
- GRANT 권한종류 [(컬럼명)] | ALL
- ON 객체명 | ROLE 이름 | PUBLIC
- TO 사용자 이름

WITH GRANT OPTION
- 사용자가 특정 객체를 조작하거나 접근할 수 있는 권한을 부여받으면서

  그 권한을 다른 사용자에게 다시 부여할 수 있는 권한 옵션

GRANT SELECT ON C##EMPLOYEE.EMPLOYEE TO C##SAMPLE
WITH GRANT OPTION;

 (직원관리계정의 EMPLOYEE 테이블에 대해서 SELECT할 수 있는 권한을 샘플계정에게 다시 부여함)

- C##SAMPLE 계정에서 C##EMPLOYEE.EMPLOYEE 조회

SELECT
       EE.*
  FROM C##EMPLOYEE.EMPLOYEE EE;

- C##SAMPLE 계정이 C##SAMPLE2 계정에게 권한 부여 가능

GRANT SELECT ON C##EMPLOYEE.EMPLOYEE TO C##SAMPLE2;

권한 철회(REVOKE) 

(시스템계정에서)

REVOKE SELECT ON C##EMPLOYEE.EMPLOYEE FROM C##SAMPLE;

참고
- WITH GRANT OPTION은 REVOKE시 다른 사용자에게도 부여한 권한을 같이 회수
- WITH ADMIN OPTION은 특정 사용자의 권한만 회수가 되고 나머지 다른 사용자에게 부여한 권한은 회수가 되지 않음

데이터베이스 ROLE(권한 제어)
- 사용자마다 일일히 권한을 부여하는 것은 번거롭기 때문에

  간편하게 권한을 부여할 수 있는 방법으로 ROLE을 제공한다.

롤(ROLE)
- 사용자에게 보다 간편하게 부여할 수 있도록 여러 개의 권한을 묶어놓는 것
- 사용자에게 부여한 권한을 수정하고자 할 때도 롤만 수정하면

  그 롤에 대한 권한을 부여 받은 사용자들의 권한이 자동으로 수정된다.
- 롤을 활성화 하거나 비활성화 해서 일시적으로 권한을 부여하고 철회할 수 있다.

SELECT
       GRANTEE
     , PRIVILEGE
  FROM DBA_SYS_PRIVS
 -- WHERE GRANTEE = 'RESOURCE';
 WHERE GRANTEE = 'CONNECT';

롤의 종류

 

1. 사전 정의된 롤

- 오라클 설치 시 시스템에서 기본적으로 제공 됨
   EX) CONNECT ROLE, RESOURCE ROLE

2. 사용자가 정의하는 롤
- CREATE ROLE 명령으로 롤 생성
- 롤 생성은 반드시 DBA 권한이 있는 사용자만 할 수 있음
- 1. 롤 생성 

CREATE ROLE 롤이름;

- 2. 생성된 롤에 권한 추가

GRANT 권한종류 TO 롤이름;

- 3. 사용자에게 롤 부여

GRANT 롤이름 TO 사용자이름;

 

CREATE ROLE C##MYROLE;
GRANT CREATE VIEW, CREATE SEQUENCE TO C##MYROLE;
GRANT C##MYROLE TO C##SAMPLE;

 

 

- MYROLE 권한 확인

SELECT
       GRANTEE
     , PRIVILEGE
  FROM DBA_SYS_PRIVS
 WHERE GRANTEE = 'C##MYROLE';

- SAMPLE 계정의 ROLE 확인

SELECT
       DRP.*
  FROM DBA_ROLE_PRIVS DRP
 WHERE GRANTEE = 'C##SAMPLE';

<SAMPLE 계정으로 확인하기>

CREATE SEQUENCE SEQ_TEST;
DROP SEQUENCE SEQ_TEST;

- 컴파일 오류와 함께 뷰가 생성되었단 문구가 뜬다.

CREATE OR REPLACE FORCE VIEW VIEW_TEST
AS
SELECT
       E.A
  FROM EEE E;