09_DCL(GRANT, REVOKE)_관리자계정, SAMPLE 계정
<DCL: DATA CONTROL LANGUAGE>
데이터 제어 언어
계정에게 시스템 권한 또는 객체 접근 권한을 부여(GRANT) 하거나 회수(REVOKE) 하는 언어
*권한 부여(GRANT)
- 시스템 권한: 특정 DB에 접근하는 권한, 객체들을 생성할 수 있는 권한
- 객체 접근 권한: 특정 객체들에 접근해서 조작할 수 있는 권한
1. 시스템 권한
특정 DB에 접근하는 권한, 객체들을 생성할 수 있는 권한
[ 표현법 ]
GRANT 권한1, 권한2, ..., TO 계정명;
- 시스템 권한의 종류
CREATE SESSION: 계정에 접속할 수 있는 권한
CREATE TABLE: 테이블을 생성할 수 있는 권한
CREATE VIEW: 뷰를 생성할 수 있는 권한
CREATE SEQUENCE: 시퀀스를 생성할 수 있는 권한
CREATE USER: 계정을 생성할 수 있는 권한
...
-- 1. SAMPLE 계정 생성
CREATE USER SAMPLE IDENTIFIED BY SAMPLE;
계정 생성만 하고 권한을 주지 않으면 테스트 실패 뜨고 접속 안 됨
-- 2. SAMPLE 계정에 접속하기 위한 CREATE SESSION 권한만 부여
GRANT CREATE SESSION TO SAMPLE;
권한을 준 뒤에는 테스트 성공했고, 접속도 가능함!
<관리자 계정, SAMPLE 계정 한 눈에 보기>
| 관리자계정 | SAMPLE계정 |
1. SAMPLE 계정 생성 CREATE USER SAMPLE IDENTIFIED BY SAMPLE; 2. SAMPLE 계정에 접속하기 위한 CREATE SESSION 권한만 부여 GRANT CREATE SESSION TO SAMPLE; 3_1. SAMPLE 계정에 테이블을 생성할 수 있는 CREATE TABLE 권한 부여 GRANT CREATE TABLE TO SAMPLE; 3_2. SAMPLE 계정에 테이블스페이스를 할당해 주기 (ALTER 구문으로) ALTER USER SAMPLE QUOTA 2M ON SYSTEM; QUOTA: 몫, 할당하다 2M: 2 MEGA BYTE SYSTEM: 오라클에서 제공하는 기본 테이블스페이스명 4. SAMPLE 계정에 뷰를 생성할 수 있는 CREATE VIEW 권한 부여 GRANT CREATE VIEW TO SAMPLE; |
CREATE TABLE 권한 부여받기 전 CREATE TABLE TEST ( TEST_ID NUMBER ); 3_1. SAMPLE 계정에 테이블을 생성할 수 있는 권한이 없기 때문에 오류 발생 ORA-01031: insufficient privileges --> 불충분한 권한들 CREATE TABLE 권한 부여받은 후 CREATE TABLE TEST ( TEST_ID NUMBER ); 3_2. TABLE SPACE(테이블들이 모여 있는 공간)가 할당되지 않음 ORA-01950: no privileges on tablespace 'SYSTEM' --> 테이블스페이스가 아직 사용 불가인 상태 TEST SPACE 할당받은 후 CREATE TABLE TEST ( TEST_ID NUMBER ); 테이블 생성 완료 위의 테이블 생성 권한(CREATE TABLE)을 부여받게 되면 기본적으로는 해당 계정이 소유하고 있는 테이블들을 조작하는 것도 가능해짐 SELECT * FROM TEST; INSERT INTO TEST VALUES(1); 뷰 만들어 보기 CREATE VIEW V_TEST AS (SELECT * FROM TEST); ORA-01031: insufficient privileges 4. 뷰 객체를 생성할 수 있는 CREATE VIEW 권한이 없기 때문에 오류 발생 CREATE VIEW 권한 부여받은 후 CREATE VIEW V_TEST AS (SELECT * FROM TEST); 뷰 생성 완료 SELECT * FROM V_TEST; |
*객체 접근 권한 (객체 권한)
특정 객체들을 조작(SELECT, INSERT, UPDATE, DALETE) 할 수 있는 권한
[ 표현법 ]
GRANT 권한종류 ON 특정객체 TO 계정명;
권한 종류 | 특정 객체
------------------------------------------------------------------------------------------------
SELECT | TABLE, VIEW, SEQUENCE
INSERT | TABLE, VIEW
UPDATE | TABLE, VIEW(안 되는경우가 더 많지만 되기는 됨)
DELETE | TABLE, VIEW(안 되는경우가 더 많지만 되기는 됨)
| 관리자계정 | SAMPLE계정 |
5. SAMPLE 계정에 KH.EMPLOYEE 테이블을 조회할 수 있는 권한 부여 GRANT SELECT ON KH.EMPLOYEE TO SAMPLE; 6. SAMPLE 계정에 KH.DEPARTMENT 테이블에 행을 삽입할 수 있는 권한 부여 GRANT INSERT ON KH.DEPARTMENT TO SAMPLE; 최소한의 권한을 부여하고자 할 때 CONNECT, RESOURCE만 부여 GRANT CONNECT, RESOURCE TO 계정명; |
SAMPLE 계정에서 KH 계정의 테이블에 접근해서 조회해 보기 SELECT * FROM KH.EMPLOYEE; 5. KH 계정의 테이블에 접근해서 조회할 수 있는 권한이 없기 때문에 오류 발생 ORA-00942: table or view does not exist SELECT ON 권한 부여받은 후 SELECT * FROM KH.EMPLOYEE; EMPLOYEE 테이블 조회 성공 SELECT * FROM KH.DEPARTMENT; KH 계정의 DEPARTMETN 테이블에 접근할 수있는 권한이 없기 때문에 오류 발생 SAMPLE 계정에서 KH 계정의 테이블에 접근해서 행 삽입해 보기 INSERT INTO KH.DEPARTMENT VALUES('D0', '회계부', 'L2'); 6. KH계정의 테이블에 접근해서 삽입할 수 있는 권한이 없기 때문에 오류 발생 SQL 오류: ORA-00942: table or view does not exist INSERT ON 권한 부여받은 후 INSERT INTO KH.DEPARTMENT VALUES ('D0', '회계부', 'L2'); KH.DEPARTMENT 테이블에 행 INSERT 성공 SELECT * FROM KH.DEPARTMENT; INSERT 권한만 주었을 뿐 SELECT 권한은 주지 않았기 때문에 조회는 불가함! 아직까지는 SAMPLE 계정에서 추가한 값이 반영되지 않음! INSERT 후 COMMIT까지 해야 KH 계정의 DEPARTMENT 테이블에 반영됨! SAMPLE계정에서 진행했으니 SAMPLE 계정에서 커밋 해 줘야 함! COMMIT; 커밋 후에는 삽입이 반영됨 |
<롤 ROLE>
특정 권한들을 하나의 집합으로 묶어 놓은 것
CONNECT: CREATE SESSION (데이터베이스에 접속할 수 있는 권한)
RESOURCE: CREATE TABLE, CREATE SEQUENCE, ... (특정 객체들을 생성 및 관리할 수 있는 권한)
-- 데이터 딕셔너리를 이용하여
-- CONNECT, RESOURCE라는 롤에 어떤 권한들이 묶여 있는지 확인해 보기
SELECT *
FROM ROLE_SYS_PRIVS
-- WHERE ROLE = 'CONNECT' OR ROLE = 'RESOURCE';
WHERE ROLE IN ('CONNECT', 'RESOURCE');
*권한 회수 (REVOKE)
권한을 회수할 때 사용하는 명령어
[ 표현법 ]
REVOKE 권한1, 권한2, 권한3, ... FROM 계정명;
| 관리자계정 | SAMPLE계정 |
| 7. SAMPLE 계정에서 테이블을 생성할 수 없도록 권한 회수 REVOKE CREATE TABLE FROM SAMPLE; |
테이블 만들어 보기 CREATE TABLE TEST2 ( TEST_ID NUMBER, TEST_TITLE VARCHAR2(50) ); 7. SAMPLE 계정에서 테이블을 생성할 수 없도록 권한을 회수했기 때문에 오류 발생 ORA-01031: insufficient privileges CREATE VIEW V_TEST2 AS (SELECT * FROM TEST WHERE 1 = 0); 테이블을 만드는 권한을 회수했을 뿐 뷰를 만드는 권한은 회수하지 않았기 때문에 뷰는 잘 만들어짐 |
----- 실습 문제 -----
-- 사용자 계정: MYTEST / MYTEST
CREATE USER MYTEST IDENTIFIED BY MYTEST;
-- 사용자에게 부여할 권한: CONNECT, RESOURCE, CREATE VIEW
GRANT CONNECT, RESOURCE, CREATE VIEW TO MYTEST;
-- 볼 일 보고(접속, 테이블 생성, 뷰 생성)
-- 사용자로부터 권한을 모두 회수
REVOKE CONNECT, RESOURCE, CREATE VIEW FROM MYTEST;
-- 사용자 계정 삭제
DROP USER MYTEST CASCADE;
-- 계정에 자료가 있기 때문에 그냥 삭제가 안 됨! CASCADE로 종속된 모든 것을 삭제하겠다고 해 줘야 함!