[SQL] Oracle 권한(Privilege) 정리 - GRANT / REVOKE / ADMIN OPTION / GRANT OPTION

1. 권한(Privilege)이란?

• 특정 SQL 문장을 실행하기 위한 권리
• 시스템권한(DB 접근 권한)과 객체권한(DB내 객체 데이터 조작)이 있음

 

1) 데이터베이스 관리자(DBA)란?

• 데이터베이스와 그 객체에 대한 액세스를 사용자에게 부여하는 능력을 갖춘 상급 사용자

 

2. 시스템 권한

• 데이터베이스에 접근하기 위한 권한
• 주로 데이터베이스 관리자(DBA)에 의해 제공됨
•  

1) 시스템 권한 종류

분류	권한	설명
세션	CREATE SESSION	DB 접속 권한
테이블	CREATE TABLE	자신의 스키마에 테이블 생성
	CREATE ANY TABLE	모든 스키마에 테이블 생성
	DROP ANY TABLE	모든 스키마의 테이블 삭제
	ALTER ANY TABLE	모든 스키마의 테이블 변경
뷰	CREATE VIEW	뷰 생성
	CREATE ANY VIEW	모든 스키마에 뷰 생성
인덱스	CREATE ANY INDEX	모든 스키마에 인덱스 생성
프로시저	CREATE PROCEDURE	프로시저/함수/패키지 생성
	CREATE ANY PROCEDURE	모든 스키마에 생성
	EXECUTE ANY PROCEDURE	모든 프로시저 실행
시퀀스	CREATE SEQUENCE	시퀀스 생성
사용자	CREATE USER	사용자 생성
	DROP USER	사용자 삭제
	ALTER USER	사용자 수정
시스템	SYSDBA	최고 관리자 권한
	SYSOPER	운영 관리자 권한
	CREATE TABLESPACE	테이블스페이스 생성

⚠️ 사용자를 생성해도 CREATE SESSION 권한이 없으면 로그인 자체가 불가능!

 

⚠️ 테이블스페이스 (TABLESPACE)란?
- 데이터베이스에서 데이터를 저장하는 논리적인 저장 공간 단위
- 테이블, 인덱스 등의 객체는 실제로 테이블스페이스에 저장됨
- 물리적 파일(Datafile)과 연결되어 실제 디스크에 저장됨
- 하나의 테이블스페이스는 여러 개의 데이터파일을 가질 수 있음
- 사용자는 테이블 생성 시 특정 테이블스페이스에 데이터를 저장하게 됨

⚠️ QUOTA (테이블스페이스 사용 권한)란?
- 사용자가 특정 테이블스페이스에서 쓸 수 있는 용량 한도.
- 객체 권한(INSERT/UPDATE/DELETE)이 있어도 QUOTA가 없으면 데이터 저장 불가

 

2) 시스템 권한 부여 : GRANT

GRANT 권한명1 [, 권한명2, ...]
TO 사용자명1[, 사용자명2, ...] 
[WITH ADMIN OPTION]

 

3) WITH ADMIN OPTION

• 권한을 받은 사용자가 다른 사용자에게 동일한 권한을 재부여할 수 있는 옵션

-- DBA(system)로 접속
CONN system/1234

-- USER1에게 CREATE SESSION, CREATE TABLE + ADMIN OPTION 부여
GRANT CREATE SESSION, CREATE TABLE TO user1 WITH ADMIN OPTION;

-- USER1으로 접속
CONN user1/1234

-- USER1이 USER2에게 CREATE TABLE 부여 (ADMIN OPTION 덕분에 가능)
GRANT CREATE TABLE TO user2;

 

4) 시스템 권환 회수 : REVOKE

• 중간 사용자의 권한을 회수해도 중간 사용자가 다른 사용자에게 부여한 권한은 회수 안됨
• DBA나 직접 권한을 준 사용자(GRANTOR)가 REVOKE 해야 함

REVOKE 권한명1 [, 권한명2, ...]
FROM 사용자명1[, 사용자명2, ...] 

-- DBA(system)로 접속
CONN system/1234

-- USER1의 CREATE TABLE 권한 회수
REVOKE CREATE TABLE FROM user1;

• USER1 시스템 권한 회수 후

-- USER1 → CREATE TABLE 권한 없음 ❌
CONN user1/1234
CREATE TABLE test(id NUMBER);  -- 오류 발생!

-- USER2 → CREATE TABLE 권한 여전히 있음 ✅
CONN user2/1234
CREATE TABLE test(id NUMBER);  -- 성공!

 

3. 객체 권한

• 데이터베이스에서 객체의 내용을 조작하기 위함 권한

⚠️ 오라클이 생성하는 것들은 전부 객체(Object)로 관리됨

주요 객체	설명
TABLE	데이터 저장
VIEW	가상 테이블
SEQUENCE	자동 번호 생성
INDEX	검색 속도 향상
SYNONYM	객체 별칭
PROCEDURE	절차형 SQL
FUNCTION	반환값 있는 절차형 SQL
TRIGGER	자동 실행 SQL
PACKAGE	프로시저/함수 묶음
TYPE	사용자 정의 타입

 

1) 객체 권한 종류

객체 유형	권한	설명
테이블/뷰	SELECT	데이터 조회
	INSERT	데이터 삽입
	UPDATE	데이터 수정
	DELETE	데이터 삭제
	ALTER	구조 변경
	INDEX	인덱스 생성
	REFERENCES	외래키 참조
시퀀스	SELECT	시퀀스 값 사용 (NEXTVAL, CURRVAL)
	ALTER	시퀀스 변경
프로시저/함수	EXECUTE	실행 권한
디렉토리	READ	읽기
	WRITE	쓰기

 

2) 객체 권한 부여

• 다른 사용자가 생성한 객체는 권한이 없으면 table or view does not exist처럼 에러남

GRANT 권한명1 [, 권한명2, ...]
ON 객체명
TO {사용자명1[, 사용자명2, ...] | PUBLIC}
[WITH GRANT OPTION]

-- DBA로 접속, user1/user2에 기본 권한 부여
CONN system/1234
GRANT CREATE SESSION, CREATE TABLE TO user1;
GRANT CREATE SESSION TO user2;

-- user1이 테이블 생성
CONN user1/1234
CREATE TABLE emp(id NUMBER, name VARCHAR2(10));

-- user2가 권한 없이 접근 시도
CONN user2/1234
SELECT * FROM user1.emp;  -- ❌ 오류! table or view does not exist

-- user1이 user2에게 객체 권한 부여
CONN user1/1234
GRANT SELECT, INSERT ON emp TO user2;

-- 이제 user2가 접근 가능
CONN user2/1234
SELECT * FROM user1.emp;                  -- ✅ 성공
INSERT INTO user1.emp VALUES(1, 'hong');  -- ✅ 성공
DELETE FROM user1.emp;                    -- ❌ DELETE 권한 없음

-- 모든 사용자에게 권한 부여
GRANT SELECT ON emp TO PUBLIC;     -- ✅ 전체 사용자 접근 가능

 

3) 객체 권한 회수

• 사용자 생성한 직후에는 아무 권한이 없음
• 로그인이라도 하려면 create session 권힌아 있어야함

REVOKE {권한명1 [, 권한명2, ...] | ALL}
ON 객체명
FROM {사용자명1[, 사용자명2, ...] | PUBLIC}

-- user1이 user2 권한 회수
CONN user1/1234

-- 특정 권한만 회수
REVOKE SELECT ON emp FROM user2;

-- 모든 권한 회수
REVOKE ALL ON emp FROM user2;

-- PUBLIC 권한 회수
REVOKE SELECT ON emp FROM PUBLIC;

• USER1 객체 권한 회수 후

-- USER2 → SELECT 권한 없음 ❌
CONN user2/1234
SELECT * FROM user1.emp;  -- 오류 발생!

 

시스템 권한 vs 객체 권한 회수 비교

구분	시스템 권한 (ADMIN OPTION)	객체 권한 (GRANT OPTION)
중간 사용자 회수 시	USER2 권한 유지 ✅	USER2 권한 연쇄 회수 ❌