[SQL] DCL (DATE CONTROL LANGUAGE)

DCL

유저를 생성하고 권한을 제어할 수 있는 명령어

 

유저와 권한

다른 부서 간, 다른 회사 간 데이터를 공유하기 위해 DB를 오픈해야 하는 경우, 새로운 유저를 생성하고 생성한 유저에게 공유할 테이블이나 기타 오브젝트에 대한 접근 권한만을 부여하면 된다. 

대부분 데이터베이스에서는 데이터 보호와 보안을 위해 유저와 권한을 관리하고 있다. 

오라클에서 기본적으로 제공되는 유저는 SCOTT, SYS, SYSTEM 이 있다. 

• SCOTT : 오라클 테스트용 샘플 유저. 디폴트 패스워드: TIGER
• SYS : DBA 롤을 부여받은 유저
• SYSTEM : 데이터베이스 모든 시스템 권한을 부여받은 DBA 유저. 오라클 설치 롸욜 시에 패스워드를 설정한다. 

오라클과 sql server는 사용자에 대한 아키텍처가 많이 다르다. 

오라클은 유저를 통해 데이터베이스에 접속하는 방식. (아이디와 비미럽ㄴ호 방식으로 인스턴스에 접속하고 그에 해당하는 스키마에 오브젝트 생성 등의 권한을 부여받게 된다)

sql server 는 인스턴스에 접속하기 위해 로그인이라는 것을 생성하게 되며, 인스턴스 내에 존재하는 다수의 데이터베이스에 연결하여 작업하기 위해 유저를 생성한 후 로그인과 유저를 매핑해주어야 한다. 더 나아가 특정 유저는 특정 데이터베이스 내의 특정 스키마에 대해 권한을 부여받을 수 있다. 

 

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

사용자가 실행하는 DDL 문장 (create, alter, drop, rename)은 권한이 있어야만 실행할 수 있다. 

쉽게 권한을 부여하기 위해, ROLE(롤)을 만들어 권한을 부여게 된다. 

 

유저 생성 권한이 있어야 유저를 만들 수 있으므로 스캇 유저에게 유저 생성 권한을 부여한 후 pjs 유저를 생성해보자.

-- 오라클 방식
GRANT CREATE USER TO SCOTT ;
-- 스캇에게 유저 생성 권한을 부여했다.

CONN SCOTT/TIGER
-- 스캇 유저, 비번 tiger로 연결했다.

CREATE USER PJS IDENTITFIED BY KOREA7;
-- PJS 유저를 생성했다. 

 

sql server 의 경우 sa로 로그인을 한 후 sql 인증을 사용하는 pjs라는 로그인(패스워드 korea7) 을 생성한다. 로그인 후 최초로 접속할 데이터베이스는 adventureworks 데이터베이스다. 

CREATE LOGIN PJS WITH PASSWORD='KOREA7',
DEFAULT_DATABASE = AdventureWorks ;

-- sql server는 유저가 데이터베이스마다 존재한다.
-- 따라서 유저를 생성하기 위해서는 해당 데이터베이스로 이동한 후 처리해야 한다. 

USE ADVENTUREWORKS;
GO 
CREATE USER PJS FOR LOGIN WITH DEAFAULT_SCHEMA = dbo ; 

 

 

생성된 pjs 유저로 로그인할 수 있도록 CREATE SESSION 권한 부여하기

CONN SCOTT/TIGER ; 

GRANT CREATE SESSION TO PJS ;

CONN PJS/KOREA7 ;

 

 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) ;
       

-- sql server
GRANT CREATE TABLE TO PJS ;

GRANT CONTROL ON SCHEMA :: dbo TO PJS ;

CREATE TABLE MENU (
       MENU_SEQ INT NOT NULL
       TITLE    VARCHAR(10) ;

 

OBJECT 에 대한 권한 부여

특정 유저가 소유한 객체(OBJECT) 권한을 알아보자.

오브젝트 권한은 특정 오브젝트인 테이블, 뷰 등에 대한 SELECT, DELETE, UPDATE 작업 명량어를 의미한다. 

앞서 pjs 유저가 생성한 menu 테이블을 scott 유저를 통해 조회하려면 어떻게 해야할까? 오브젝트 권한 소유자로부터 권한을 부여받아야 한다. 

 

pjs로 접속해서 csott 에게 menu 테이블을 select 할 수 있는 권한을 부여하자.

-- 오라클
CONN PJS/KOREA7 ; -- pjs로 연결

UNSERT INTO MENU VALUES (1, '화이팅'); -- 테이블에 행 추가
COMMIT ; -- 커밋

GRANT SELECT ON MENU TO SCOTT ; -- 스캇에게 메뉴 테이블 셀렉트 권한 주기

-- sql server

-- pjs 로 로그인 한다.

INSERT INTO MENU VALUES (1, '화이팅'); -- 메뉴 테이블에 행 추가

GRANT SELECT ON MENU TO SCOTT ; -- 스캇에게 메뉴 테이블 셀렉트 권한 주기

 

ROLE을 이용한 권한 부여

유저를 생성하려면 기본적으로 CREATE SESSION, CREATE TABLE, CREATE PROCEDURE 등 많은 권한을 부여해야 한다. 

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

관리자는 롤을 생성, 권한 부여할 수 있다. 롤을 이용해서 빠르고 정확하게 권한을 부여할 수 있다. 

pjs 유저에게  CREATE SESSION, CREATE TABLE 권한을 가진 롤을 생성한 후 권한을 부여해보자. 

권한을 취고할 때는 REVOKE를 사용한다. 

-- 오라클
CONN SYSTEM/MANAGER ; -- 접속

CREATE ROLE LOGIN_TABLE ;  -- LOGIN_TABLE 라는 롤 생성

GRANT CREATE SESSION, CREATE TABLE TO LOGIN_TABLE ; -- 롤에다가 권한 부여

GRANT LOGIN_TABLE TO PJS ; -- pjs에게 롤 부여 

CONN PJS/KOREA7 ;

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

오라클에서 기본적으로 제공하는 롤 중에서 가장 많이 쓰이는 CONNECT, RESOURCE.

 

 

sql server에서는 role을 생성하여 사용하기도바는 기본적으로 제공되는 role에 멤버로 참여하는 방식으로 사용한다. 

특정 로그인이 멤버로 참여할 수 있는 서버 수준 역할은 다음과 같다. 

데이터베이스에 존재하는 유저에 대해서는 다음과 같은 역할 멤버로 참여할 수 있다. 

sql server 에서는 오라클과 같이 롤을 자주 사용하지 않는다. 

대신 서버 수준 역할 및 데이터 베이스 수준 역할을 이용하여 로그인 및 사용자 권한을 제어한다. 

인스턴스 수준을 요구하는 로그인에는 서버 수준 역할을, 데이터베이스 수준을 요구하는 사용자에게는 데이터베이스 수준 역할을 부여한다. 

 

 

 

 

kdata 한국데이터진흥원에서 출간한 SQL 전문가 가이드 2013 Edition을 요약했습니다.