[DB/Oracle] 권한 관리

권한 관리

 

• 데이터베이스에 보관 및 관리되는 데이터는 대부분 데이터를 소유한 특정 단체 또는 기업에게 재산 이상 가치를 가지는 경우가 많다.
• 따라서 데이터를 안전히 보관하고 특정 데이터에 대해 관련된 사용자만 데이터를 사용 및 관리할 수 있는 보안 장치가 필요하다.
• 접속 사용자에 따라 접근할 수 있는 데이터 영역과 권한을 지정해 줄 수 있다.
• 오라클에서는 권한을 시스템 권한(system privilege), 객체 권한(object privilege)으로 분류한다.

[시스템 권한이란]

 

오라클 데이터베이스의 시스템 권한은 사용자 생성과 정보 수정 및 삭제, 데이터베이스 접근, 오라클 데이터베이스의 여러 자원과 객체 생성 및 관리 등의 권한을 포함한다. 이러한 내용은 데이터베이스 관리 권한이 있는 사용자가 부여할 수 있는 권한이다.

 

-  시스템 권한의 일부(ANY 키워드가 들어 있는 권한은 소유자에 상관없이 사용 가능한 권한을 의미)

시스템 권한 분류	시스템 권한	설명
USER(사용자)	CREATE USER	사용자 생성 권한
	ALTER USER	생성된 사용자의 정보 수정 권한
	DROP USER	생성된 사용자의 삭제 권한
SESSION(접속)	CREATE SESSION	데이터베이스 접속 권한
	ALTER SESSION	데이터베이스 접속 상태에서 환경 값 변경 권한
TABLE(테이블)	CREATE TABLE	자신의 테이블 생성 권한
	CREATE ANY TABLE	임의의 스키마 소유 테이블 생성 권한
	ALTER ANY TABLE	임의의 스키마 소유 테이블 수정 권한
	DROP ANY TABLE	임의의 스키마 소유 테이블 삭제 권한
	INSERT ANY TABLE	임의의 스키마 소유 테이블 데이터 삽입 권한
	UPDATE ANY TABLE	임의의 스키마 소유 테이블 데이터 수정 권한
	DELETE ANY TABLE	임의의 스키마 소유 테이블 데이터 삭제 권한
	SELECT ANY TABLE	임의의 스키마 소유 테이블 데이터 조회 권한
	CREATE ANY TABLE	임의의 스키마 소유 테이블 삭제 권한
INDEX(인덱스)	CREATE ANY INDEX	임의의 스키마 소유 테이블의 인덱스 생성 권한
	ALTER ANY INDEX	임의의 스키마 소유 테이블의 인덱스 수정 권한
	DROP ANY INDEX	임의의 스키마 소유 테이블의 인덱스 삭제 권한
VIEW(뷰)	(생략)	뷰와 관련된 여러 권한
SEQUENCE(시퀀스)	(생략)	시퀀스와 관련된 여러 권한
SYNONYM(동의어)	(생략)	동의어와 관련된 여러 권한
PROFILE(프로파일)	(생략)	사용자 접속 조건 지정과 관련된 여러 권한
ROLE(롤)	(생략)	권한을 묶은 그룹과 관련된 여러 권한

---

[시스템 권한 부여]

 

GRANT CREATE SESSION TO ORCLSTUDY;

GRANT [시스템 권한] TO [사용자 이름/롤(Role)이름/PUBLIC]
[WITH ADMIN OPTION];

>> ORCLSTUDY 사용자에게 CREATE SESSION 권한을 부여하겠다는 뜻

 

1. 시스템 권한: 오라클 데이터베이스에서 제공하는 시스템 권한을 지정한다. 한 번에 여러 종류의 권한을 부여하려면 쉼표로 구분하여 권한 이름을 여러 개 명시해 주면 된다(필수).

2. 사용자 이름/롤(Role)이름/PUBLIC: 권한을 부여하려는 대상을 지정한다. 사용자 이름을 지정해 줄 수도 있고, 롤을 지정할 수도 있다. 여러 사용자 또는 롤에 적용할 경우 쉼표로 구분한다. PUBLIC은 현재 오라클 데이터베이스의 모든 사용자에게 권한을 부여하겠다는 의미이다(필수).

3. WITH ADMIN OPTION은 현재 GRANT문을 통해 부여받은 권한을 다른 사용자에게 부여할 수 있는 권한도 함께 부여 받는다. 현재 사용자가 권한이 사라져도 권한을 재부여한 다른 사용자의 권한은 유지된다(선택).

 

>> SYSTEM 계정으로 접속하여 사용자 생성하고 권한 부여하기

 

>> ORCLSTUDY 소유 테이블을 생성했으므로 INSERT, SELECT문을 사용할 수 있음

---

[GRANT에 사용된 RESOURCE 키워드]

 

오라클 데이터베이스에서 제공하는 롤(role) 중 하나이다. 롤은 여러 권한을 하나의 이름으로 묶어권한 부여 관련 작업을 간편하게 하려고 사용한다.

 

만약 GRANT문에 RESOURCE를 지정하지 않는다면, ORCLSTUDY 사용자에게 테이블 생성 권한을 부여해도 CREATE문으로 테이블을 생성할 수 없거나 테이블이 생성되더라도 INSERT문에서 오류 메시지를 출력하며 동작하지 않는다.

ORA-01950: 테이블 스페이스 USERS 권한이 없습니다.

>> 오류 메시지

 

오류 메시지에서 테이블 스페이스는 테이블이 저장되는 공간을 의미하며 따로 지정하지 않으면 기본 테이블 스페이스 USERS가 할당된다. 위 오류는 이 테이블 스페이스의 사용 영역을 정하지 않아 발생하는 오류이다. RESOURCE롤에는 사용자를 생성할 떄 사용 테이블 스페이스의 영역을 무제한 사용 가능하게 해 주는 권한이 포함되어 있기 때문에, RESOURCE롤을 GRANT문에 추가하면 별 문제 없이 사용자가 테이블을 새엇ㅇ하고 신규 데이터를 저장할 수 있다.

하지만 테이블 스페이스의 영역 사용에 한계를 두지 않는 UNLIMITED TABLESPACE권한은 엄밀한 관리가 필요한 경우에 적절하지 않으므로 사용자를 생성 및 수정할 때 QUOTA절로 사용 영역에 제한을 두기도 한다.

ATLER USER ORCLSTUDY QUOTA 2M ON USERS;

(이러한 이슈 때문에 오라클 데이터베이스 12C버전에서는 RESOURCE 롤에 UNLIMITED TABLESPACE 권한을 부여하지 않음)

---

[시스템 권한 취소]

 

GRANT 명령어로 부여한 권한의 취소는 REVOKE 명령어를 사용한다.

REVOKE [시스템 권한] FROM [사용자 이름/롤(Role)이름/PUBLIC];

---

[객체 권한이란]

 

특정 사용자가 생성한 테이블, 인덱스, 뷰, 시퀀스 등과 관련된 권한이다.

 

예를 들어 SCOTT 소유 테이블에 ORCLSTUDY 사용자가 SELECT나 INSERT 등의 작업이 가능하도록 허용할 수 있다.

 

-  주로 사용하는 객체 권한 중 일부

객체 권한 분류	객체 권한	설명
TABLE(테이블)	ALTER	테이블 변경 권한
	DELETE	테이블 데이터 삭제 권한
	INDEX	테이블 인덱스 생성 권한
	INSERT	테이블 데이터 삽입 권한
	REFERENCES	참조 데이터 생성 권한
	SELECT	테이블 조회 권한
	UPDATE	테이블 데이터 수정 권한
VIEW(뷰)	DELETE	뷰 데이터 삭제 권한
	INSERT	뷰 데이터 삽입 권한
	REFERENCES	참조 데이터 생성 권한
	SELECT	뷰 조회 권한
	UPDATE	뷰 데이터 수정 권한
SEQUENCE(시퀀스)	ALTER	시퀀스 수정 권한
	SELECT	시퀀스의 CURRVAL과 NEXTVAL 사용 권한
PROCEDURE(프로시저)	(생략)	프로시저 관련 권한
FUNCTION(함수)	(생략)	함수 관련 권한
PACKAGE(패키지)	(생략)	패키지 관련 권한

---

[객체 권한 부여]

 

GRANT [객체 권한/ALL PRIVILEGES]
ON [스키마.객체 이름]
TO [사용자 이름/롤(Role)이름/PUBLIC]
[WITH GRANT OPTION];

1. 객체 권한/ALL PRIVILEGES: 오라클 데이터베이스에서 제공하는 객체 권한을 지정한다. 한 번에 여러 종류의 권한을 부여하려면 쉼표로 구분하여 권한을 여러 개 명시해 준다. ALL PRIVILEGES는 객체의 모든 권한을 부여함을 의미한다(필수).

2. 스키마.객체 이름: 권한을 부여할 대상 객체를 명시한다(필수).

3. 사용자 이름/롤 이름/PUBLIC: 권한을 부여하려는 대상을 지정한다. 사용자 이름을 지정해 줄 수도 있고 롤을 지정할 수도 있다. 여러 사용자 또는 롤에 적용할 경우 쉼표로 구분한다. PUBLIC은 현재 오라클 데이터베이스의 모든 사용자에게 권한을 부여하겠다는 의미이다(필수).

4. WITH GRANT OPTION: 현재 GRANT문을 통해 부여받은 권한을 다른 사용자에게 부여할 수 있는 권한도 함께 부여받는다. 현재 권한을 부여받은 사용자의 권한이 사라지면, 다른 사용자에게 재부여된 권한도 함께 사라진다(선택).

 

>> SCOTT 계정으로 접속하여 새로운 테이블을 생성한 후 ORCLSTUDY 사용자에게 SELECT, INSERT 권한 부여하기

GRANT SELECT, INSERT ON TEMP TO ORCLSTUDY;

>> 쉼표 사용해서 한 번에 권한을 부여하기도 가능

 

>> ORCLSTUDY로 사용 권한 부여받은 TEMP 테이블 사용하기

>> ORCLSTUDY 사용자의 소유는 아니지만 SCOTT 계정의 TEMP 테이블을 조회하고 INSERT도 가능해짐

---

[객체 권한 취소]

 

시스템 권한과 마찬가지로 REVOKE문 사용

REVOKE [객체 권한/ALL PRIVILEGES]
ON [스키마.객체 이름]
FROM [사용자 이름/롤 이름/PUBLIC]
[CASCADE CONSTRAINTS/FORCE];

>> ORCLSTUDY에 부여된 TEMP 테이블 사용 권한 취소하기

>> ORCLSTUDY로 권한 철회된 TEMP 테이블 조회하기(실패)