오라클 사용자 권한 제어

사용자 권한 제어

시스템 권한

데이터베이스를 관리하는 권한이다. 먼저 DBA가 가지는 시스템 권한은 사용자 생성이나 삭제, 사용자 계정에서 객체의 생성 또는 수정, 데이터베이스 백업 관리 등이 있다.

 

시스템권한의 예

CREATE USER  : 사용자를 생성할수 있는 권한

DROP USER : 사용자를 삭제할 수 있는 권한

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

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

BACKUP ANY TABLE : 익스포트 유틸리티를 사용해서 임의의 테이블을 백업할 수 있는 권한.

 

일반 사용자가 가질수 있는 시스템 권한은 사용자가 생성한 객체를 관리할수있는 권한과 내장 프로시저를 관리할수 있는 권한이다.

 

CREATE SESSION : DB에 접숙할수 있는 권한

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

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

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

CREATE PROCEDURE : 사용자 스키마에서 프로시저, 함수, 패키지를 생성할 수 있는 권한.

시스템 권한 생성

사용법

GRANT {system_priv|role]

[, {system_priv|role} ]. . .

TO {user|role|PUBLIC}

[, {user|role|PUBLIC}]. . .

[WITH ADMIN OPTION]

 

PUBLIC : 모든 사용자에게 해당 시스템 권한 부여

WITH ADMIN OPTION : 해당 시스템 권한을 다른 사용자나 롤에 재부여 허용

 

query rwwrite 시스템 권한을 scott 사용자와 모든 사용자에게 부여하여라(qeury write 권한은 함수기반 인덱스를 생성하기위해 필요한 권한이다)

 

> CONNECT sysyem/manager   (관리자 접속)

 

> GRANT query rewrite TO scott;  (scott에게 부여)

> GRANT query rewrite TO PUBLIC;  (모든사용자에게 부여)

 

> SELECT * FROM user_sys_privs;  (사용자와 롤에 부여된 시스템 권한을 조회)

 

시스템 권한 철회

데이터베이스 관리자나 권한을 부여한 사용자는 다른 사용자에게 부여한 시스템 권한을 철회할 수 있다.

 

사용법

REVOKE { system_priv|role]

[, {system_priv|role} ]. . .

FROM {user|role|PUBLIC}

[, {user|role|PUBLIC}]. . .

 

scott 사용자에게 부여한 query write 시스템 권한을 철회하여라

SVRMGR> REVOKE query rewrite FROM scott;

 

객체 권한

( DB관리자로 들어가 tiger 사용자를 생성한다)

> sqlplus "/as sysdba"  

> CREATE USER tiger3 IDENTIFIED BY tiger123

DEFAULT TABLESPACE users

TEMPORARY TABLESPACE temp;

 

tiger사용자에게 데이터베이스에 대한 접속 객체 관리및 롤 사용권한을 부여

> GRANT connect, resource TO tiger3;

 

> sqlplus scott/tiger

 

scott이 tiger에게 자신이 갖고있는 dept테이블을 조회할수 있는 권한을 부여한다.

> GRANT SELECT ON scott.dept TO tiger3;

 

> sqlplus tiger3/tiger123

> SELECT * FROM scott.dept;

 

tiger사용자에게 scott이 소유한 dept 테이블의 DNAME을 수정할 수 있는 권한을 부여

 

> sqlplus scott/tiger

 

scott이 tiger에게 scott 소유의 dept테이블을 수정할수있는 권한 부여

> GRANT UPDATE (dname) ON dept TO tiger3;

 

> sqlplus tiger3/tiger123

 

tiger가 dept테이블 수정

>UPDATE scott.dept

 SET dname='SALES'

 WHERE DEPTNO=30;

 

tiger사용자에게 부여된 사용자 객체, 칼럼에 부여된 객체권한을 조회하여라

 

> sqlplus tiger3/tiger123

 

> SELECT * FROM user_tab_privs_recd; (tiger사용자에게 부여된 객체권한 객체이름 조회)

 

> SELECT * FROM user_col_privs_recd; (tiger사용자에게 부여된 칼럼에 대한 객체 권한과 칼럼 이름을 조회)

 

객체 권한의 철회

사용자와 롤에 부여된 객체 권한은 데이터베이스 관리자나 객체 소유자가 REVOKE 명령문을 사용하여 철회할 수 있다.(CASCADE CONSTRAINTS 옵션을 사용하면 REFERENCES나 ALL 권한 철회시 관련 참조 무결성 제약조건도 함께 삭제)

 

scott에 의해 tiger에 부여된 student 테이블에 대한 SELECT, UPDATE 권한을 철회하여라.

>sqlplus scott/tiger

>REVOKE UPDATE ON dept FROM tiger3;

>REVOKE SELECT ON dept FROM tiger3;

 

롤

롤이란 다수 사용자와 다양한 권한을 효과적으로 관리하기 위하여 서로 관련된 권한을 그룹화한 개념이다. (여러 권한을 그룹화)

롤을 수정하면 해당 롤을 부여받은 모든 사용자의 권한이 자동적으로 수정된다.

또한 롤은 활성화 또는 비활성화를 통한 일시적인 권한 부여와 철회가 가능하다.

롤에 암호를 부여할 수 도 있다.

 

사전 정의된 롤의 종류

CONNECT롤 : 사용자가 데이터베이스에 접속하여 세션을 생성하거나 테이블 또는 뷰와 같은 객체를 생성할 수 있는 권한을 그룹화한 롤이다. 이 롤을 부여받은 사용자는 테이블, 뷰 , 시퀀스, 동의어, 세션 생성 권한과 다른 데이터베이스에 접속할 수 있는 권한을 사용할 수 있다.

 

RESOURCE롤

사용자에게 자신의 테이블, 시퀀스, 프로시져, 트리거와 같은 객체를 생성할 수 있는 권한을 부여한 롤이다. 일반적으로 디비 관리자는 사용자 생성시 CONNECT롤과 RESORCE롤을 부여한다.

 

DBA롤

시스템 자원의 무제한적인 사용이나 시스템 관리에 필요한 모든권한 그리고 DBA권한을 다른 사용자에게 부여할 수 있는 강력한 권한을 보유한 롤이다.

또한 DBA롤은 모든 사용자 소유의 CONNECT, RESOURCE, DBA 권한을 포함한 어떠한 권한도 부여하거나 철회할 수 있다. 따라서 디비의 철저한 보안관리를 위해 DBA롤의 부여는 엄격히 통제할 필요가 있다.

 

롤 생성

롤 자체에 대한 보안을 위해 롤 생성시 암호를 부여할 수 있다. 롤 이름은 사용자나 다른 롤과 중복될수없다.

 

사용법

CREATE ROLE rolr [NOT IDENTIFIED|IDENTIFIED{ BY password | EXTERNALLY}]

 

NOT IDENTIFIED : 롤 활성화시 암호에 의한 검증 과정 생략

IDENTIFIED : 롤 활성화시 암호에 의한 검증과정 필요

BY password : 롤 활성화시 사용되는 암호 지정

EXTERNALLY : 롤 활성화시 운영체제 인증을 통한 사용자 검증

 

암호를 지정한 롤과 지정하지 않은 롤 생성하기

> sqlplus "/as sysdba"

> CREATE ROLE hr_clerk;

 

>CREATE ROLE hr_mgr

 IDENTIFIED BY manager;

 

롤에 권한 부여

롤에 시스템 권한이나 객체 권한 또는 다른 롤을 부여할 수 있다. 사용자에게 권한을 부여하는 것처럼 GRANT 명령문을 사용하여 부여할수있다.

 

롤에 시스템권한 부여

hr_mgr롤에 CREATE SESSION 시스템 권한을 부여

 

SVRMGR>GRANT create session TO hr_mgr;

 

롤에 객체 권한 부여

사용자가 롤에 객체 권한을 부여할 수 있는 경우는 2종류가 있다. 하나는 사용자가 객체의 소유자인 경우와 WITH GRANT OPTION옵션과 함께 객체 권한을 부여받은 경우이다.

 

scott 사용자의 dept 테이블을 모든 칼럼에 대한 SELECT, INSERT, DELETE 객체 권한을 hr_clerk롤에 부여하여라.

> sqlplus scott/tiger

> GRANT select, insert, delete ON dept TO hr_clerk;

 

롤 부여

롤은 사용자 또는 다른 롤에게 부여될 수 있다. WITH ADMIN OPTION을 부여받은 롤은 사용자나 다른 롤에게 해당 롤을 재부여할 수 있다.

 

hr_clerk롤을 hr_mgr롤과 tiger사용자에게 부여하여라

> GRANT hr_clerk TO hr_mgr;

> GRANT hr_clerk TO tiger;

 

데이터 딕셔너리를 이용하여 롤 정의를 조회

 

롤에 부여한 시스템 권한 조회

SELECT * FROM role_sys_privs;

 

롤에 부여한 객체권한 조회

SELECT * FROM role_tab_privs;

 

사용자가 부여받은 롤 조회

SELECT * FROM user_role_privs;

 

동의어

학생테이블의 소유자가 scott이고 홍길동이 scott로부터 접근 권한을 부여받아 학생 테이블을 조회하려면 scott.student와 같이 소유자 아이디를 테이블 이름앞에 지정해야 한다. 매번 사용하려면 번거로운 일이다. 동의어(synonym)은 하나의 객체에 대해 다른 이름을 정의하는 방법이다. 예를들어 scott.student 테이블에 대해 my_boy라는 동의어를 정의할 경우 질의어에서 scott.student라는 긴 이름 대신 my_boy라는 간단한 이름으로 scott.student테이블을 조회할 수 있다.

 

동의어 생성방법

동의어의 종류는 개별 사용자를 대상으로 한 전용동의어(private synonym)와 전체 사용자를 대상으로 한 공용 동의어(public synonum)가 있다. 전용 동의어는 객체에 대한 접근 권한을 부여받은 사용자가 정의한 동의어로 해당 사용자만 사용할 수 있다. 공용 동의어는 권한을 주는 사용자가 정의한 동의어로 누구나 사용할 수 있다. 공용 동의어는 DBA권한을 가진 사용자만 생성할 수 있다.

 

사용법

CREATE [ PUBLIC] SYNONYM [ schema.]synonym

FOR [ schema.] object;

 

PUBLIC : 모든 사용자가 사용 가능

기본값은 전용 동의어

 

동의어 삭제

 

사용법

DROP SYNONYM synonym;

 

my_project와 pub_project 동의어를 삭제하여라

 

my_project동의어 삭제.

 

DROP SYNONYM my_project;