[SQLD] SQL 활용 2-7. DCL (Data Control Language)

7절. DCL

1. DCL 개요

:유저를 생성하고 권한을 제어하는 명령어

[DDL, DML, TCL]

 

2. 유저와 권한

: 공유할 테이블이나 기타 오브젝트에 대한 접근 권한만을 부여하는 방법

 

Oracle은 유저를 통해 데이터베이스에 접속

인스턴스에 접속을 하고 그에 해당하는 스키마에 오브젝트 생성 등의 권한을 부여

 

SQL Server는 인스턴스에 접속하기 위해 로그인이라는 것을 생성

인스턴스 내에 존재하는 다수의 데이터베이스에 연결하여 작업하기 위해 유저를 생성한 후 로그인과 유저를 매핑

 

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

:유저를 생성하고 데이터베이스에 접속

-> 원하는 작업에 해당하는 시스템 권한이 있어야 문장 실행 가능

 

유저 생성 권한

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

CONN SCOTT/TIGER 연결되었다. CREATE USER PJS IDENTIFIED BY KOREA7; CREATE USER PJS IDENTIFIED BY KOREA7; * 1행에 오류: ERROR: 권한이 불충분하다

현재 SCOTT 유저는 유저를 생성할 권한을 부여받지 못했기 때문에 권한이 불충분하다는 오류가 발생

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

 

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

GRANT CREATE USER TO SCOTT; 권한이 부여되었다. CONN SCOTT/TIGER 연결되었다. CREATE USER PJS IDENTIFIED BY KOREA7; 사용자가 생성되었다.

 

(3) 생성된 PJS 유저로 로그인

CONN PJS/KOREA7; 오류: ERROR: 사용자 PJS는 CREATE SESSION 권한을 가지고 있지 않음; 로그온이 거절되었다.

 

(4) PJS 유저가 로그인할 수 있도록 CREATE SESSION 권한을 부여

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

 

(5) PJS 유저로 테이블을 생성

SELECT * FROM TAB; 선택된 레코드가 없다. CREATE TABLE MENU ( MENU_SEQ NUMBER NOT NULL, TITLE VARCHAR2(10) ); CREATE TABLE MENU ( * 1행에 오류: ERROR: 권한이 불충분하다.

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

 

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

CONN SYSTEM/MANAGER 연결되었다. GRANT CREATE TABLE TO PJS; 권한이 부여되었다. CONN PJS/KOREA7 연결되었다. CREATE TABLE MENU ( MENU_SEQ NUMBER NOT NULL, TITLE VARCHAR2(10) ); 테이블이 생성되었다.

 

나. OBJECT에 대한 권한 부여

: 특정 유저가 소유한 객체(OBJECT) 권한

다른 유저의 테이블에 접근하려면 해당 테이블에 대한 오브젝트 권한을 소유자로부터 부여받는 작업이 필요하다.

유저는 단지 스키마에 대한 권한만 소유한다.

 

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

-> 다른 유저가 소유한 객체에 접근하기 위해서 객체 앞에 객체를 소유한 유저의 이름을 붙여서 접근한다.

 

CONN SCOTT/TIGER 연결되었다. SELECT * FROM PJS.MENU; SELECT * FROM PJS.MENU* 1행에 오류: ERROR: 테이블 또는 뷰가 존재하지 않는다.

SCOTT 유저는 PJS 유저로부터 MENU 테이블을 SELECT할 수 있는 권한을 부여받지 못 했기 때문에 MENU 테이블을 조회할 수 없다.

 

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

CONN PJS/KOREA7 연결되었다. INSERT INTO MENU VALUES (1, '화이팅'); 1개의 행이 만들어졌다. COMMIT; 커밋이 완료되었다. GRANT SELECT ON MENU TO SCOTT; 권한이 부여되었다.

SCOTT 유저는 PJS.MENU 테이블을 SELECT하는 권한만 부여 받았기 때문에 UPDATE, INSERT, DELETE와 같은 다른 작업은 할 수 없다.
오브젝트 권한은 SELECT, INSERT, DELETE, UPDATE 등의 권한을 각각 관리한다.

 

(8) PJS.MENU 테이블에 UPDATE를 시도

CONN SCOTT/TIGER 연결되었다. SELECT * FROM PJS.MENU; MENU_SEQ TITLE -------- -------- 1 파이팅 UPDATE PJS.MENU SET TITLE = '코리아' WHERE MENU_SEQ = 1; UPDATE PJS.MENU * 1행에 오류: ERROR: 권한이 불충분하다

권한이 부족하여 UPDATE를 할 수 없다는 오류가 나타난다.
PJS 유저에게 UPDATE 권한 을 부여한 후 다시 시도하면 업데이트가 가능

 

3. Role을 이용한 권한 부여

:많은 데이터베이스에서 유저들과 권한들 사이에서 중개 역할을 하는 ROLE을 제공해 빠르고 쉽게 권한을 관리한다.

-> ROLE에 포함되어 있는 권한들이 필요한 유저에게는 해당 ROLE만을 부여

 

 

시스템 권한과 오브젝트 권한을 모두 부여할 수 있으며, ROLE은 유저에게 직접 부여될 수도 있고, 다른 ROLE에 포함하여 유저에게 부여 가능

 

(9) JISUNG 유저에게 CREATE SESSION과 CREATE TABLE 권한을 가진 ROLE을 생성한 후 ROLE을 이용하여 다시 권한을 할당한다. 권한을 취소할 때는 REVOKE 이용

CONN SYSTEM/MANAGER 연결되었다. REVOKE CREATE SESSION, CREATE TABLE FROM JISUNG; 권한이 취소되었다. CONN JISUNG/KOREA7 ERROR: 사용자 JISUNG은 CREATE SESSION 권한을 가지고 있지 않음. 로그온이 거절 되었다.

 

(10) LOGIN_TABLE이라는 ROLE을 만들고, 이 ROLE을 이용하여 JISUNG 유저에게 권한을 부여

CONN SYSTEM/MANAGER 연결되었다. CREATE ROLE LOGIN_TABLE; 롤이 생성되었다. GRANT CREATE SESSION, CREATE TABLE TO LOGIN_TABLE; 권한이 부여되었다. GRANT LOGIN_TABLE TO JISUNG; 권한이 부여되었다. CONN JISUNG/KOREA7 연결되었다. CREATE TABLE MENU2( MENU_SEQ NUMBER NOT NULL, TITLE VARCHAR2(10)); 테이블이 생성되었다.

Oracle에서 자주 사용하는 ROLE

: CONNECT와 RESOURCE

 

CONNECT : CREATE SESSION과 같은 로그인 권한이 포함

RESOURCE : CREATE TABLE과 같은 오브젝트의 생성 권한이 포함

일반적으로 유저를 생성 할 때 CONNECT와 RESOURCE ROLE을 사용하여 기본 권한을 부여

 

유저를 삭제하는 명령어는 DROP USER이고,

CASCADE 옵션을 주면 해당 유저가 생성한 오브젝트를 먼저 삭제한 후 유저를 삭제

 

 

(11) MENU라는 테이블을 생성했기 때문에 CASCADE 옵션을 사용하여 JISUNG 유저를 삭제한 후,

유저 재생성 및 기본적인 ROLE을 부여

CONN SYSTEM/MANAGER 연결되었다. DROP USER JISUNG CASCADE; 사용자가 삭제되었다. ☞ JISUNG 유저가 만든 MENU 테이블도 같이 삭제되었다. CREATE USER JISUNG IDENTIFIED BY KOREA7; 사용자가 생성되었다. GRANT CONNECT, RESOURCE TO JISUNG; 권한이 부여되었다. CONN JISUNG/KOREA7 연결되었다. CREATE TABLE MENU ( MENU_SEQ NUMBER NOT NULL, TITLE VARCHAR2(10)); 테이블이 생성되었다.