DCL

DCL(Data Control Language, 데이터 제어어)은 사용자에게 권한을 주고(GRANT) 거둡니다(REVOKE). 여러 권한을 역할(ROLE)로 묶어 한꺼번에 줄 수도 있습니다.

데이터베이스는 여러 사용자가 함께 씁니다. EMP를 만든 SQLD는 EMP의 주인이고, 다른 사용자는 주인이 권한을 주기 전에는 EMP를 읽지도 고치지도 못합니다. 그림에는 관리자와 SQLD, 접속 권한만 가진 새 사용자 KIM·LEE가 나옵니다.

KIMSELECT COUNT(*)FROM SQLD.EMP;→ ORA-00942

SQLDGRANT SELECT ON EMP TO KIM;

SQLDREVOKE SELECT ON EMP FROM KIM;

SQLDGRANT SELECT ON EMP  TO KIM WITH GRANT OPTION;KIMGRANT SELECT ON SQLD.EMP  TO LEE;

SQLDREVOKE SELECT ON EMP FROM KIM;

관리자GRANT CREATE TABLE  TO KIM WITH ADMIN OPTION;KIMGRANT CREATE TABLE TO LEE;

관리자REVOKE CREATE TABLE FROM KIM;

관리자CREATE ROLE EMP_READER;SQLDGRANT SELECT ON EMP  TO EMP_READER;관리자GRANT EMP_READER TO KIM, LEE;

KIMSELECT COUNT(*)FROM SQLD.EMP;→ ORA-00942

1객체 권한 주고 거두기

남의 테이블은 SQLD.EMP처럼 주인 이름을 붙여 쓰고, 권한이 없으면 Oracle은 테이블이 없다고(ORA-00942) 답합니다. EMP 같은 객체 하나에 대한 권한을 객체 권한(object privilege)이라 하고, GRANT SELECT ON EMP TO KIM⁠처럼 ON 뒤에 객체를 씁니다. 거둘 때는 REVOKE SELECT ON EMP FROM KIM⁠처럼 TO 대신 FROM을 씁니다. 권한은 쉼표로 여럿을 주고, 일부만 거둘 수도 있습니다.

2WITH GRANT OPTION과 회수

객체 권한을 줄 때 WITH GRANT OPTION을 붙이면 받은 사람도 그 권한을 나눠 줄 수 있습니다. 이 권한을 거두면, 받은 사용자가 남에게 준 권한까지 줄줄이 거둬집니다. 다만 같은 권한을 두 사람에게서 받으면 준 사람(GRANTOR)마다 따로 기록되어, 한 사람이 준 것이 거둬져도 다른 사람이 준 것은 남습니다. SQL Server는 WITH GRANT OPTION으로 준 권한을 거둘 때 CASCADE를 붙여야 하고, 붙이지 않으면 REVOKE가 실패합니다.

3시스템 권한과 WITH ADMIN OPTION

CREATE TABLE처럼 데이터베이스에서 어떤 일을 할 수 있는 권한을 시스템 권한(system privilege)이라 하고, GRANT CREATE TABLE TO KIM⁠처럼 ON 없이 씁니다. 새 사용자는 권한이 하나도 없어서 접속하는 시스템 권한 CREATE SESSION부터 받아야 합니다(없으면 ORA-01045). SQL Server는 서버에 접속하는 로그인(login)과 데이터베이스 안의 사용자(user)를 따로 만들어 잇습니다.

옵션을 바꿔 쓰면 오류입니다(ORA-01939 · ORA-00993).

4역할

역할(ROLE)은 권한을 담는 상자입니다. CREATE ROLE로 만들고, 역할에 권한을 준 뒤 역할을 사용자에게 줍니다. 역할의 권한을 더하거나 거두면 그 역할을 받은 사용자 모두에게 반영되고, 역할을 다시 주게 하는 옵션은 WITH ADMIN OPTION입니다. Oracle에는 CONNECT(접속)·RESOURCE(객체 생성) 같은 역할이 미리 있습니다.

예제

예제는 사용자를 만들어서 관리자(SYS)로 돌립니다. 끝의 DROP USER … CASCADE는 만든 사용자를 그 객체와 함께 지웁니다. CONNECT는 다른 사용자로 다시 접속하는 SQL*Plus 명령으로, 역할 CONNECT와 이름만 같습니다. FREEPDB1은 실습 데이터베이스 이름이라 시험과 상관없습니다.

SELECT·INSERT를 주고 INSERT만 거둔 뒤 객체 권한 목록 DBA_TAB_PRIVS를 봅니다.

CREATE USER KIM
  IDENTIFIED BY kim1234;
GRANT CREATE SESSION TO KIM;
GRANT SELECT, INSERT
  ON SQLD.EMP
  TO KIM;
REVOKE INSERT ON SQLD.EMP
  FROM KIM;
SELECT GRANTEE,
  PRIVILEGE
FROM DBA_TAB_PRIVS
WHERE GRANTEE = 'KIM';
DROP USER KIM CASCADE;

그림의 두 옵션 장면입니다.

CREATE USER KIM
  IDENTIFIED BY kim1234;
CREATE USER LEE
  IDENTIFIED BY lee1234;
GRANT CREATE SESSION
  TO KIM, LEE;
GRANT SELECT ON SQLD.EMP
  TO KIM WITH GRANT OPTION;
GRANT CREATE TABLE
  TO KIM WITH ADMIN OPTION;
-- KIM 으로 접속해
-- LEE 에게 다시 준다
CONNECT KIM/kim1234@FREEPDB1
GRANT SELECT ON SQLD.EMP
  TO LEE;
GRANT CREATE TABLE TO LEE;
-- 관리자로 돌아와
-- KIM 에게서 거둔다
CONNECT / AS SYSDBA
ALTER SESSION
  SET CONTAINER = FREEPDB1;
REVOKE SELECT ON SQLD.EMP
  FROM KIM;
REVOKE CREATE TABLE FROM KIM;
-- KIM 과 LEE 에게 남은
-- SELECT 와 CREATE TABLE
SELECT GRANTEE,
  PRIVILEGE
FROM DBA_TAB_PRIVS
WHERE GRANTEE IN
    ('KIM', 'LEE')
UNION ALL
SELECT GRANTEE,
  PRIVILEGE
FROM DBA_SYS_PRIVS
WHERE GRANTEE IN
    ('KIM', 'LEE')
  AND PRIVILEGE =
    'CREATE TABLE';
DROP USER KIM CASCADE;
DROP USER LEE CASCADE;

LEE에게는 ADMIN OPTION 쪽인 CREATE TABLE만 남았습니다. 역할 하나에 접속 권한과 EMP의 SELECT를 담아 LEE에게 줍니다.

CREATE USER LEE
  IDENTIFIED BY lee1234;
CREATE ROLE EMP_READER;
GRANT CREATE SESSION
  TO EMP_READER;
GRANT SELECT ON SQLD.EMP
  TO EMP_READER;
GRANT EMP_READER TO LEE;
CONNECT LEE/lee1234@FREEPDB1
SELECT COUNT(*) AS CNT
FROM SQLD.EMP;
CONNECT / AS SYSDBA
ALTER SESSION
  SET CONTAINER = FREEPDB1;
DROP USER LEE CASCADE;
DROP ROLE EMP_READER;

시험 포인트

• 새 사용자는 권한이 없어 CREATE SESSION이 있어야 접속합니다(ORA-01045).
• 객체 권한은 ON 뒤에 객체를 쓰고, 거둘 때는 TO 대신 FROM을 씁니다.
• WITH GRANT OPTION은 객체 권한에, WITH ADMIN OPTION은 시스템 권한과 역할에 붙입니다. 회수가 이어지는 것은 GRANT OPTION 쪽뿐입니다.
• 같은 권한도 준 사람마다 따로 기록되어, 한 사람이 준 것을 거둬도 다른 사람이 준 것은 남습니다.
• 역할에 준 권한을 바꾸면 그 역할을 받은 사용자 모두에게 반영됩니다.
• 객체를 가진 사용자는 DROP USER 사용자 CASCADE⁠로 지웁니다.

확인 문제

1. 다음을 차례로 실행한 뒤 EMP를 읽을 수 있는 사람은 누구일까요? -- 줄은 실행한 사용자입니다.

-- SQLD
GRANT SELECT ON EMP
  TO KIM WITH GRANT OPTION;
-- KIM
GRANT SELECT ON SQLD.EMP
  TO LEE WITH GRANT OPTION;
-- LEE
GRANT SELECT ON SQLD.EMP
  TO PARK;
-- SQLD
REVOKE SELECT ON EMP
  FROM KIM;

2. 관리자가 새 사용자 PARK을 만들고 다음 한 문장만 실행했습니다. PARK으로 접속해 EMP를 읽을 수 있을까요?

GRANT SELECT ON SQLD.EMP
  TO PARK;

3. KIM이 두 권한을 남에게 다시 줄 수 있게 하려고 다음 두 문장을 실행했습니다. Oracle에서 오류가 나는 문장은 어느 것이고, 어떻게 고칠까요?

GRANT CREATE TABLE
  TO KIM WITH GRANT OPTION;
GRANT SELECT ON SQLD.EMP
  TO KIM WITH GRANT OPTION;