[SQL] DCL - GRANT, REVOKE

DCL (DATA CONTROL LANGUAGE)

데이터베이스 접근, 객체사용 등에 대한
권한을 부여하거나, 회수하는 명령어

 

GRANT (권한부여)

- 시스템권한: 특정 DB에 접근하는 권한, 객체 생성권한
- 객체 접근권한 :특정객체 접근하여 조작할 수 있는 권한 (SELECT, INSERT, DELETE, UPDATE)

GRANT 권한1, 권한2,.... TO 계정명;

권한: 
- CREATE SESSION: 계정에 접속할 수 있는 권한
- CREATE TABLE: 테이블 생성권한
- CREATE VIEW:VIEW 생성권한
- CREATE SEQUENCE : 시퀀스 생성권한
- CREATE USER: 계정생성권한
......

-- SAMPLE 계정 생성

CREATE USER SAMPLE IDENTIFIED BY SAMPLE;

-- SAMPLE 계정에 접속하기 위한 CREATE SESSION권한 부여
GRANT CREATE SESSION TO SAMPLE;
--SAMPLE 계정에 계정 생성권한 부여
GRANT CREATE TABLE TO SAMPLE;
-- SAMPLE 계정에 테이블스페이스 할당(SYSTEM계정 변경)

ALTER USER SAMPLE QUOTA 2M ON SYSTEM;

-- SAMPLE 계정에 VEIW를 생성할 수 있는 CREATE VIEW권한 부여
GRANT CREATE VIEW TO SAMPLE;

<객체권한>

*객체권한의 종류
SELECT: TABLE, VIEW, SEQUENCE
INSERT: TABLE, VIEW
DELETE : TABLE, VIEW

GRANT 권한종류 ON 특정객체 TO 계정명;

--SAMPLE 계정에 CC.EMPLOYEE테이블 조회권한 부여

GRANT SELECT ON CC.EMPLOYEE TO SAMPLE;

-- SAMPLE계정에 CC.DEPART테이블 행 삽입 권한 부여
GRANT INSERT ON CC.DEPARTMENT TO SAMPLE;

권한회수(REVOKE)

권한 회수시 사용하는 명령어 

REVOKE 권한1, 권한2.... FROM 사용자이름;

--SAMPLE계정에서 테이블 생성할 수 있는 권한 회수
REVOKE CREATE TABLE FROM SAMPLE;

<ROLE>
특정 권한들을 하나의 집합으로 모아놓은 것
CONNECT: CREATE SESSION(데이터베이스에 접속할 수 있는 권한)
RESOURCE : CREATE TABLE, CREATE VIEW (특정 객체들을 생성 및 관리하는 권한);

GRANT CONNECT, RESOURCE TO 계정:
객체에 최소한의 권한을 부여하고자 할 때 CONNECT, RESOURCE만 부여하면 됨

SELECT *
FROM ROLE_SYS_PRIVS
WHERE ROLE IN('CONNECT', 'RESOURCE');

< 권한이 없을 때 발생하는 에러들>

-- 실습문제
---사용자에게 부여할 권한: CONNECT, RESOURCE
--- 권한부여받을 사용자: HAHA
-- 권한 부여 후 USER 객체 삭제

CREATE USER HAHA IDENTIFIED BY HAHA;
GRANT CONNECT, RESOURCE TO HAHA;
DROP USER HAHA;

---- 테이블 만들기
CREATE TABLE TEST(
    TEST_ID NUMBER 
);

/*
ORA-01031: insufficient privileges
01031. 00000 -  "insufficient privileges"
*Cause:    An attempt was made to perform a database operation without
           the necessary privileges.
*Action:   Ask your database administrator or designated security
           administrator to grant you the necessary privileges

-->SAMPLE계정에 테이블 생성권한이 없어 에러 발생 

*/ 
---CREATE TABLE 권한 받은 후 
CREATE TABLE TEST(
    TEST_ID NUMBER 
);

-->SAMPLE계정에 테이블스페이스 사용권한이 없어 에러 발생 
( INSERT데이터들은 하드디스크 물리적 저장장치에 파일로 저장됨)

/*
ORA-01950: no privileges on tablespace 'SYSTEM'
01950. 00000 -  "no privileges on tablespace '%s'"
*Cause:    User does not have privileges to allocate an extent in the
           specified tablespace.
*Action:   Grant the user the appropriate system privileges or grant the user
           space resource on the tablespace.
           
          



ALTER USER SAMPLE QUOTA 2M ON SYSTEM;
*/
---TABLE SPACE 할당받은 후
CREATE TABLE TEST(
    TEST_ID NUMBER 
);
--TABLE 생성권한을 부여받으면 
--해당 계정이 소유하고 있는 테이블 조작 가능 

SELECT *
FROM TEST;

INSERT INTO TEST VALUES(1);

--- 뷰 만들어보기 
CREATE VIEW V_TEST        
AS SELECT * FROM TEST;
/*
*Cause:    An attempt was made to perform a database operation without
           the necessary privileges.

---CREATE VIEW 권한이 없어서 오류 발생
*/

CREATE VIEW V_TEST
AS SELECT * FROM TEST;

-- SAMPLE 계정에서 CC계정의 TABLE에 접근하여 조회해보기
SELECT  *
FROM CC.EMPLOYEE ;
/**ORA-00942: table or view does not exist 
KH계정의 테이블에 접근해서 조회할 수 있는 권한이 없기 때문에 오류 발생


--SAMPLE 계정에서 CC.EMPLOYEE테이블 조회권한 부여 후
*/
SELECT  *
FROM CC.EMPLOYEE ;

SELECT * FROM CC.DEPARTMENT;


---SAMPLE 계정에서 KH계정의 테이블에 접근하여 행 삽입해보기
INSERT INTO CC.DEPARTMENT VALUES('D0', '회계부', 'L2');

/*
SQL 오류: ORA-00942: table or view does not exist
권한이 없어서 오류

-- SAMPLE계정에 CC.DEPART테이블 행 삽입 권한 부여
*/
INSERT INTO CC.DEPARTMENT VALUES('D0', '회계부', 'L2');

ROLLBACK;
--테이블 권한 회수 후
CREATE TABLE TEST2(
    TEST_ID NUMBER
);
/*01031. 00000 -  "insufficient privileges" */