DB- DCL

유저를 생성하고 권한을 제어할 수 있는 DCL(DATA CONTROL LANGUAGE) 명령어가 있다.

운영 시스템에서 사용하던 유저를 오픈하면 데이터의 손실 우려가 너무 커지게 되는 것이다. 이런 경우에 새로운 유저를 생성하고, 생성한 유저에게 공유할 테이블이나 기타 오브젝트에 대한 접근 권한만을 부여한다면 문제는 쉽게 해결할 수 있다

 

Oracle은 유저를 통해 데이터베이스에 접속을 하는 형태이다. 즉, 아이디와 비밀번호 방식 으로 인스턴스에 접속을 하고 그에 해당하는 스키마에 오브젝트 생성 등의 권한을 부여받게 된다

 

유저 생성과 시스템 권한 부여

유저를 생성하고 데이터베이스에 접속한다. 하지만 데이터베이스에 접속했다고 해서 테이블, 뷰, 인덱스 등과 같은 오브젝트(OBJECT)를 생성할 수는 없다. 사용자가 실행하는 모든 DDL 문장(CREATE, ALTER, DROP, RENAME 등)은 그에 해당하는 적절한 권한이 있어야만 문장을 실행할 수 있다. 이러한 권한을 시 스템 권한이라고 하며 약 100개 이상의 종류가 있다. 일반적으로 시스템 권한은 일일이 유저에게 부여되지 않는다. 100개 이상의 시스템 권한을 일일이 사용 자에게 설정하는 것은 너무 복잡하고, 특히 유저로부터 권한을 관리하기가 어렵기 때문이다. 그래서 롤(ROLE)을 이용하여 간편하고 쉽게 권한을 부여하게 된다.

SCOTT 유저로 접속한 다음 PJS 유저(패스워드: KOREA7)를 생성해 본다.


CONN SCOTT/TIGER
CREATE USER PJS IDENTIFIED BY KOREA7;

 

일단 유저 생성 권한(CREATE USER)이 있어야 한다.

Oracle의 DBA 권한을 가지고 있는 SYSTEM 유저 로 접속하면 유저 생성 권한(CREATE USER)을 다른 유저에게 부여할 수 있다.

SCOTT 유저에게 유저생성 권한(CREATE USER)을 부여한 후 다시 PJS 유저를 생성한다.

GRANT CREATE USER TO SCOTT;
CONN SCOTT/TIGER
CREATE USER PJS IDENTIFIED BY KOREA7;

 

PJS 유저가 생성됐지만 아무런 권한도 부여받지 못했기 때문에 로그인을 하면 CREATE SESSION 권한이 없다는 오류가 발생한다. 유저가 로그인을 하려면 CREATE SESSION 권한을 부여받아야 한다.

CONN SCOTT/TIGER 
연결되었다. 
GRANT CREATE SESSION TO PJS;
권한이 부여되었다. 
CONN PJS/KOREA7 
연결되었다.


PJS 유저로 테이블을 생성한다.

SELECT * FROM TAB; 
선택된 레코드가 없다. 

CREATE TABLE MENU ( 
MENU_SEQ NUMBER NOT NULL, 
TITLE VARCHAR2(10) 
);

PJS 유저는 로그인 권한만 부여되었기 때문에 테이블을 생성하려면 테이블 생성 권한(CREATE TABLE)이 불충분하다는 오류가 발생한다

SYSTEM 유저를 통하여 PJS 유저에게 CREATE TABLE 권한을 부여한 후 다시 테이블을 생성한다.

CONN SYSTEM/MANAGER
GRANT CREATE TABLE TO PJS;
CONN PJS/KOREA7
CREA TE TABLE MENU ( 
MENU_SEQ NUMBER NOT NULL, 
TITLE VARCHAR2(10) );

 

OBJECT에 대한 권한 부여

특정 유저가 소유한 객체(OBJECT) 권한에 대해 알아본다. 오브젝트 권한은 특정 오브젝트인 테이블, 뷰 등에 대한 SELECT, INSERT, DELETE, UPDATE 작업 명령어를 의미한다

앞에서 PJS 유저가 생성한 MENU 테이블을 SCOTT 유저를 통해서 조회하면 어떻게 될까? SCOTT, PJS 뿐만 아니라 모든 유저는 각각 자신이 생성한 테이블 외에 다른 유저의 테이블에 접근하려면 해당 테이블에 대한 오브젝트 권한을 소유자로부터 부여받아야 한다. 

유저는 단지 스키마에 대한 권 한만을 가진다. 다시 말하면 테이블과 같은 오브젝트는 유저가 소유하는 것이 아니고 스키마가 소유를 하게 되며 유저는 스키마에 대해 특정한 권한을 가지 는 것이다. 

 

먼저 SCOTT 유저로 접속하여 PJS.MENU 테이블을 조회한다. 다른 유저가 소유한 객체에 접근하기 위해서는 객체 앞에 객체를 소유한 유저의 이 름을 붙여서 접근해야 한다

CONN SCOTT/TIGER
SELECT * FROM PJS.MENU;
ERROR: 테이블 또는 뷰 가 존재하지 않는다.

>> 아직 권한을 안줘서 그럼

PJS 유저로 접속하여 SCOTT 유저에게 MENU 테이블을 SELECT 할 수 있는 권한을 부여한다.

CONN PJS/KOREA7
INSERT INTO MENU VALUES (1, '화이팅'); 
COMMIT; 
GRANT SELECT ON MENU TO SCOTT;

다시 한 번 SCOTT 유저로 접속하여 PJS.MENU 테이블을 조회한다. 이제 PJS.MENU 테이블을 SELECT하면 테이블 자료를 볼 수 있다. SCOTT 유저는 PJS.ME NU 테이블을 SELECT하는 권한만 부여 받았기 때문에 UPDATE, INSERT, DELETE와 같은 다른 작업을 할 수 없다. 오브젝트 권한은 SELECT, INSERT, DELET E, UPDATE 등의 권한을 따로따로 관리한다.

 

Role을 이용한 권한 부여

유저를 생성하면 기본적으로 CREATE SESSION, CREATE TABLE, CREATE PROCEDURE 등 많은 권한을 부여해야 한다. 데이터베이스 관리자는 유저 가 생성될 때마다 각각의 권한들을 유저에게 부여하는 작업을 수행해야 하며 간혹 권한을 빠뜨릴 수도 있으므로 각 유저별로 어떤 권한이 부여되었는 지를 관리해야 한다. 하지만 관리해야 할 유저가 점점 늘어나고 자주 변경되는 상황에서는 매우 번거로운 작업이 될 것이다.

 

데이터베이스에서 유저들과 권한들 사이에서 중개 역할을 하는 ROLE을 제공한다.

데이터베이스 관리자는 ROLE을 생성하고, ROLE에 각 종 권한들을 부여한 후 ROLE을 다른 ROLE이나 유저에게 부여할 수 있다. 

 

JISUNG 유저에게 CREATE SESSION과 CREATE TABLE 권한을 가진 ROLE을 생성한 후 ROLE을 이용하여 다시 권한을 할당한다. 권한을 취소 할 때는 REVOKE를 사용한다.
CONN SYSTEM/MANAGER
REVOKE CREATE SESSION, CREATE TABLE FROM JISUNG;
CONN JISUNG/KOREA7 

이러면 당연히 JISUNG은 세션을 만들 권한을 가지지 않기에 로그인 불가능.

이제 LOGIN_TABLE이라는 ROLE을 만들고, 이 ROLE을 이용하여 JISUNG 유저에게 권한을 부여한다.
CONN SYSTEM/MANAGER
CREATE ROLE LOGIN_TABLE;
GRANT CREATE SESSION, CREAT E TABLE TO LOGIN_TABLE; (ROLL에 권한 부여)
GRANT LOGIN_TABLE TO JISUNG; (ROLL을 통해서 한번에 유저에게 권한부여)
CONN JISUNG/KOREA7 (세션 만들 권한 있으니 로그인 가능)
CREATE TABLE MENU2( MENU_SEQ NUMBER NOT NULL, TITLE VARCHAR2(10));

Oracle에서는 기본적으로 몇 가지 ROLE을 제공하고 있다. 그 중 가장 많이 사용하는 ROLE은 CONNECT와 RESOURCE이다.