[Oracle/SQL] 14. 시스템 권한 | 객체 권한 | GRANT | REVOKE | ROLE

목차

1. 권한
   1-1. 권한 부여(GRANT)
   1-2. 권한 조회(데이터 딕셔너리)
   1-3. 권한 철회(REVOKE)
2. 시스템 권한
   2-1. 관리자가 가지는 권한
   2-2. 사용자에게 부여하는 권한
   2-3. WITH ADMIN OPTION
3. 객체 권한
   3-1. WITH GRANT OPTION
4. 롤(ROLE)
   4-1. 사전 정의된 롤
   4-2. 사용자가 정의하는 롤

---

 

1. 권한

1-1. 권한 부여(GRANT)

-- 계정 생성
CREATE USER C##EMPLOYEE IDENTIFIED BY EMPLOYEE;
-- 권한 부여
GRANT CONNECT, RESOURCE TO C##EMPLOYEE;

• 사용자 관리란, 사용자 계정 및 암호 설정(CREATE)부터 권한 부여(GRANT)까지를 아우르는 개념이다.
• 보안을 위한 데이터베이스 관리자는 사용자로 하여금 테이블, 뷰(VIEW) 등 데이터베이스 객체에 대해 특정 권한을 가지도록 부여할 수 있는 권한을 지녔다.
• 즉 데이터베이스에 접근하는 사용자마다 서로 다른 권한 및 롤(ROLE)을 부여할 수 있다.
• 나아가 다수의 사용자가 공유하는 데이터베이스 정보에 대한 보안을 설정할 수 있다.

❗ 이때 사용자의 권한은 가져야 할 최소한으로 맞추는 것이 데이터 보안 관리 측면에 있어 필요한 방향이다.

 

---

 

1-2. 권한 조회(데이터 딕셔너리)

-- 자신이 다른 사용자에게 부여한 객체 권한 조회 -- SYSTEM 계정
✅ SELECT UTPR.*
　   FROM USER_TAB_PRIVS_RECR UTPR;

-- 자신에게 부여된 객체 권한 및 이름을 조회 -- EMPLOYEE 계정
✅ SELECT UTPM.*
　   FROM USER_TAB_PRIVS_MADE UTPM;

 

 

---

 

1-3. 권한 철회(REVOKE)

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

• REVOKE~FROM 키워드를 사용한다.

 

---

 

2. 시스템 권한

2-1. 관리자가 가지는 권한

-- 데이터베이스 관리자가 가지고 있는 권한
✅ CREATE USER -- 사용자 계정 생성
✅ DROP USER -- 사용자 계정 삭제
✅ DROP ANY TABLE -- 임의의 테이블 삭제
✅ QUERY REWRITE -- 함수 기반 인덱스 생성
✅ BACKUP ANY TABLE -- 테이블 백업

• 시스템 권한은 데이터베이스 관리자가 가지고 있는 권한을 말한다.
• 오라클 접속부터 테이블, 뷰(VIEW), 인덱스(INDEX) 등 객체 생성 권한을 아우른다.

 

---

 

2-2. 사용자에게 부여하는 권한

• 시스템 관리자가 사용자에게 부여하는 권한의 일부 예시는 다음과 같다.

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

CREATE USER C##SAMPLE IDENTIFIED BY SAMPLE;

• ① 계정명과 암호를 설정할 수 있는 계정 생성 구문이다. 이 상태에서는 CREATE SESSION 권한이 없어 접속이 이루어지지 않는다.

GRANT CREATE SESSION TO C##SAMPLE;

• ② 데이터베이스 접속 권한을 부여한다.

GRANT CREATE TABLE TO C##SAMPLE;

• ③ 테이블 생성 권한을 부여한다. 권한 없이 구문을 실행하면 '권한이 불충분합니다'라는 오류가 발생한다.

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

• 권한이 생기자 CREATE TABLE 구문이 비로소 정상 실행되는 모습을 확인할 수 있다.

 

GRANT CONNECT, RESOURCE TO C##EMPLOYEE;

❗ CONNECT, RESOURCE와 CREATE 객체 비교
앞서 다룬 권한 부여 예시에서 CONNECT는 CREATE SESSION을 의미한다.
RESOURCE는 CREATE TABLE 및 CREATE SEQUENCE를 가지지만, VIEW 또는 SYNONYM 작성 권한은 포함돼 있지 않아 별도로 CREATE VIEW, CREATE SYNONYM 해주어야 했다.
이렇듯 CONNECT, RESOURCE는 포괄적인 개념이며, 이를 보다 구체적으로 CREATE 객체처럼 작성할 수도 있다.

 

---

 

2-3. WITH ADMIN OPTION

• 사용자에게 시스템 권한을 부여할 때 쓰인다.
• 권한을 부여 받은 사용자는 다른 사용자에게 권한을 지정할 수 있다.

-- SYSTEM 계정에서 실행
GRANT CREATE SESSION TO C##SAMPLE
WITH ADMIN OPTION;

• 데이터베이스 접속 권한 부여 시에 WITH ADMIN OPTION을 명시한다.

-- 권한 부여 받은 SAMPLE 계정에서 실행
GRANT CREATE SESSION TO C##SAMPLE2;

• 사용자 SAMPLE2에게 접속 권한 GRANT가 정상 실행된 모습을 확인할 수 있다.

 

❗ WITH ADMIN OPTION은 권한 철회(REVOKE) 시 해당 사용자의 권한만을 회수한다.
해당 사용자가 다른 사용자들에게 부여한 권한은 일괄 회수 대상이 아니다.
이는 아래 서술한 객체 권한의 WITH GRANT OPTION과의 차이점이기도 하다.

 

---

 

3. 객체 권한

✅ GRANT 권한종류 | [(컬럼명)] | ALL -- 어떤 권한을
　  ON 객체명 | ROLE 이름 | PUBLIC -- 무엇에 대해
　  TO 사용자이름 -- 누구에게 줄 것인지 명시

• 객체 권한이란, 테이블, 뷰(VIEW), 시퀀스(SEQUENCE), 함수 등 사용자가 특정 객체를 조작하거나 접근할 수 있도록 허용하는 권한이다.

 

3-1. WITH GRANT OPTION

• 사용자가 특정 객체를 조작 또는 접근할 수 있는 권한을 부여 받으면서, 그 권한을 다시 다른 사용자에게 부여할 수 있는 권한 옵션을 말한다.

-- SYSTEM 계정에서 실행
GRANT SELECT ON C##EMPLOYEE.EMPLOYEE TO C##SAMPLE
WITH GRANT OPTION;

• 예시에서는 직원관리계정의 EMPLOYEE 테이블을 SELECT 할 수 있는 권한을 SAMPLE 계정에게 부여한다.

-- 권한 부여 받은 SAMPLE 계정에서 실행
SELECT EE.*
 FROM C##EMPLOYEE.EMPLOYEE EE;

• 권한 부여 마친 뒤 SAMPLE 계정에서 SELECT 시도하면 정상 조회됨을 알 수 있다.

-- 권한 부여 받은 SAMPLE 계정에서 SAMPLE2에게 권한 부여
GRANT SELECT ON C##EMPLOYEE.EMPLOYEE TO C##SAMPLE2;

• 권한을 갖게 된 SAMPLE 계정은 해당 권한을 다시금 다른 계정에게 부여하는 작업 또한 가능하다.

 

❗ WITH GRANT OPTION은 권한 철회(REVOKE) 시 해당 사용자는 물론 그로부터 다른 사용자에게 부여된 권한까지 일괄 회수한다.
이는 앞서 서술한 사용자 권한의 WITH ADMIN OPTION과의 차이점이기도 하다.

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

• SAMPLE로부터 권한을 회수하였으므로, SAMPLE → SAMPLE2에게 부여되었던 권한 역시 일괄 회수되었음을 알 수 있다.

 

---

 

4. 롤(ROLE)

❗ 데이터베이스 롤(ROLE)은 권한 제어를 일컫는다. 사용자마다 권한을 일일이 부여하는 것은 번거롭기에, 권한 부여 과정을 간편하게 할 수 있는 방법으로써 ROLE을 제공한다.

• 사용자에게 보다 간편하게 부여할 수 있도록 여러 개의 권한을 묶어놓은 것이다.

 

• 사용자에게 부여한 권한을 수정하고자 할 때도 ROLE만 변경하면, 해당 권한을 부여 받은 사용자들에게 자동 반영된다.
• ROLE을 활성화 또는 비활성화해 일시적으로 권한을 부여하거나 철회할 수 있다.

 

---

 

4-1. 사전 정의된 롤

• 오라클 설치 시 시스템에서 기본적으로 제공되는 롤을 말한다.
• 예) CONNECT ROLE, RESOURCE ROLE

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

• RESOURCE로 묶인 8개의 권한이 조회된다: CREATE TABLE, CREATE PROCEDURE, CREATE SEQUENCE 등

WHERE GRANTEE = 'CONNECT';

• CONNECT로 묶인 2개 권한이 조회된다: CREATE SESSION, SET CONTAINER

 

---

 

4-2. 사용자가 정의하는 롤

-- ①. 롤 생성
CREATE ROLE 롤이름;

-- ②. 생성된 롤에 권한 추가
GRANT 권한종류 TO 롤이름;

-- ③. 사용자에게 롤 부여
GRANT 롤이름 TO 사용자이름;

• CREATE ROLE 명령 통해 생성된 롤을 말한다.
• 롤 생성은 반드시 DBA 권한이 있는 사용자(=SYSTEM 계정)만 할 수 있다.

 

CREATE ROLE C##MYROLE;

• MYROLE이라는 이름의 ROLE 생성한다.

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

• VIEW 및 SEQUENCE 생성 권한을 추가한다.

GRANT C##MYROLE TO C##SAMPLE;

• SAMPLE 사용자에게 MYROLE 권한을 부여한다.

 

-- 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 계정이 C##ROLE을 가지고 있음을 조회할 수 있다.

 

-- SAMPLE 계정에서 확인
CREATE SEQUENCE SEQ_TEST; -- 시퀀스 생성 OK
DROP SEQUENCE SEQ_TEST; -- 시퀀스 삭제 OK

CREATE OR REPLACE FORCE VIEW VIEW_TEST
AS
SELECT
　　　　E.A
 FROM EEE E; -- 존재하지 않는 테이블에 대고 VIEW 생성 OK

• 끝으로 SAMPLE 계정에서 CREATE VIEW, CREATE SEQUENCE 권한이 정상 실행됨을 확인할 수 있다.