사용자 관리, 권한 제어: GRANT, REVOKE, 스키마, 롤

1. 데이터 베이스 보안을 위한 권한

(1) 시스템 권한 : 사용자 생성, 제거, DB 접근 및 각종 객체를 생성할 수 있는 권한. (DBA가 부여)

1) 데이터 베이스 관리자가 가지고 있는 시스템 권한

CREATE USER : 사용자를 새롭게 생성하는 권한

DROP UWER : 사용자를 삭제하는 권한

DROP ANY TABLE : 임의 테이블을 삭제할 수 있는 권한

QUERY REWRITE : 함수 기반 인덱스를 생성하는 권한

BACKUP ANY TABLE : 임의 테이블을 백업할 수 있는 권한

2) 시스템 관리자가 사용자에게 부여하는 권한

CREATE SESSION : 데이터 베이스에 접속할 수 있는 권한

CREATE TABLE : 사용자 스키마에서 테이블을 생성할 수 있는 권한

CREATE VIEW : 사용자 스키마에서 뷰를 생성할 수 있는 권한

CREATE SEQUENCE : 사용자 스키마에서 시퀀스를 생성할 수 있는 권한

CREATE PROCEDURE : 사용자 스키마에서 함수를 생성할 수 있는 권한

※ 스키마(SCHEMA) : 객체를 소유한 사용자명

(2) 객체 권한 : 객체를 조작할 수 있는 권한. ex) 테이블, 뷰, 시퀀스, 인덱스, 동의어 등

2. 사용자 생성하기 CREATE USER

[형식]

CREATE USER user_name

IDENTIFIED BY password;

3. 권한을 부여하는 GRANT

[형식]

GRANT {privilege_name|role}

TO user_name

[WITH ADMIN OPTION]; 

※ user_name 대신에 PUBLIC이라 쓰면 모든 사용자에게 해당 시스템 권한이 부여된다.

※ role(롤) : 사용자에게 보다 효율적으로 권한을 부여할 수 있도록 여러 개의 권한을 묶어놓은 권한의 집합

GRANT CREATE SESSION TO USER01 -- USER01 사용자에게 CREATE SESSION 권한을 부여한다.

WITH ADMIN OPTION; -- 자신이 부여받은 권한을 다른 사용자에게 부여할 수 있는 권한을 줄 때 붙인다.

4. 객체 권한

1) 객체와 권한 설정

객체의 소유자는 객체에 대한 모든 권한을 가진다. 객체 권한은 테이블, 뷰, 시퀀스, 함수 같은 객체별로 DML문(SELECT, INSERT, DELETE)을 사용할 수 있는 권한을 설정하는 것이다.

[형식]

GRANT privilege_name[(column_name)]|ALL -- 줄 권한

ON object_name | role_name | PUBLIC -- 객체

TO user_name; -- 권한을 줄 사용자

GRANT SELECT ON EMP TO USER01;

-- EMP 테이블을 조회(SELECT)할 수 있는 권한을 USER01에게 준다.

2) 스키마 (SCHEMA)

스키마(SCHEMA) : 객체를 소유한 사용자명. 객체명 앞에 소속 사용자명을 기술한다.

SELECT * FROM EMP;

객체를 기술할 때에는 위와 같이 기술하지만 실은 스키마가 생략되어 있다.

스키마를 생략하지 않으면 원래는 다음과 같이 써야 한다.

SELECT * FROM SCOTT.EMP; -- SCOTT은 테이블 객체를 소유한 사용자명

3) 사용자에게 부여된 권한 조회

USER_TAB_PRIVS_MADE 데이터 딕셔너리 : 현재 사용자가 다른 사용자에게 부여한 권한 정보

USER_TAB_PRIVS_RECD 데이터 딕셔너리 : 자신에게 부여된 사용자 권한 정보

SELECT * FROM USER_TAB_PRIVS_RECD;

4) 사용자 권한을 빼앗는 REVOKE 명령어

[형식]

REVOKE {privilege_name|ALL} -- 철회할 객체 권한

ON object_name -- 해당 테이블명

FROM {user_name | role_name | public}; -- 사용자명

5) WITH GRANT OPTION

사용자에게 객체 권한을 WITH GRANT OPTION과 함께 부여하면 사용자는 그 객체에 접근할 권한을 부여함과 동시에 그 권한을 다른 사용자에게 부여할 수 있는 권한도 함께 부여받는다.

GRANT SELECT ON SCOTT.EMP TO USER02

WITH GRANT OPTION;

-- USER02에게 SCOOT이 보유한 EMP 테이블을 열람할 권한을 줌과 동시에 이 권한을 다른 사용자에게 줄 수도 있게 한다.

5. 롤의 정의와 종류

1) 롤(ROLE) : 사용자에게 보다 효율적으로 권한을 부여할 수 있도록 여러 개의 권한을 묶어놓은 것.

- CONNECT 롤 : 사용자가 데이터베이스에 접속할 수 있는 기본 시스템 권한 8가지

ALTER SESSION, CREATE CURSOR, CREATE DATABASE LINK, CREATE SEQUENCE, CREATE SESSION, CREATE SYNONYM, CREATE TABLE, CREATE VIEW 

- RESOURCE 롤 : 사용자가 객체(테이블, 뷰, 인덱스)를 생성할 수 있는 시스템 권한

CREATE CLUSTER, CREATE PROCEDURE, CREATE SEQUENCE, CREATE TABLE, CREATE TRIGGER

- DBA 롤 : 사용자들이 소유한 데이터베이스 객체를 관리하고 사용자들을 작성, 변경, 제거할 수 있는 모든 권한.

GRANT CONNECT, RESOURCE TO USER04;

-- CONNECT롤과 RESOURCE롤의 권한을 USER04에게 부여한다. 해당 롤에 속한 모든 권한들이 부여된다.

2) 롤 관련 데이터 딕셔너리

ROLE_SYS_PRIVS : 롤에 부여된 시스템 권한 정보

ROLE_TAB_PRIVS : 롤에 부여된 테이블 권한 정보

USER_ROLE_PRIVS : 접근 가능한 롤 정보

USER_TAB_PRIVS_MADE : 해당 사용자 소유의 오브젝트에 대한 오브젝트 권한 정보

USER_TAB_PRIVS_RECD : 사용자에게 부여된 오브젝트 권한 정보

USER_COL_PRIVS_MADE : 사용자 소유의 오브젝트 중 컬럼에 부여된 오브젝트 권한 정보

USER_COL_PRIVS_REDC : 사용자에게 부여된 특정 컬럼에 대한 오브젝트 권한 정보

3) 사용자 롤 정의

[ 형식 ]

CREATE ROLE role_name;

GRANT privilege_name TO role_name;

4) 롤 회수

REVOKE ROLE role_name FROM user_name;

5) 롤 제거

DROP ROLE role_name;