[SQL] DCL
개요
DCL(Data Control Language)는 유저를 생성하고 권한을 제어할 수 있는 언어이다. DCL에는 어떤 것들이 있고 어떻게 활용하는지 살펴보자.
유저와 권한
다른 부서 간에 또는 다른 회사 간에 데이터를 공유하기 위해 DB를 공개해야 하는 경우가 가끔 발생한다. 물론 DB를 공개하는 것 자체가 문제가 될 수 있다. 즉, 운영 시스템에서 사용하던 유저를 공개하면 데이터의 손실 우려가 너무 커진다. 이런 경우에 새로운 유저를 생성하고, 생성한 유저에게 공유할 테이블이나 기타 오브젝트에 대한 접근 권한만을 부여한다면 문제를 쉽게 해결할 수 있다.
일반적으로 회원제 웹사이트를 방문해 서비스를 이용하려면 먼저 회원 가입을 해야 한다. 유저 아이디, 패스워드, 기타 개인정보를 입력하고 약관에 동의하면 회원 가입이 된다. 그리고 유저 아이디와 패스워드로 로그인하면 웹사이트의 서비스를 이용할 수 있다. 그러나 영화나 유료 게임과 같은 특정 콘텐츠를 이용하려 하면 ‘권한이 없다’ 라는 메시지가 나온다. 유저 아이디와 패스워드를 유저라 할 수 있고, 유료 서비스에 대한 결재 여부를 권한이라 할 수 있다.
대부분의 DB는 데이터 보호와 보안을 위해서 유저와 권한을 관리하고 있다. 예를 들어 오라클을 설치하면 기본적으로 제공되는 유저들인 SYS, SYSTEM, SCOTT 유저에 대해 살펴보자.
- SCOTT
오라클 테스트용 샘플 계정이며, DEFAULT 패스워드는 TIGER이다.
- SYS
백업 및 복구 등 DB상의 모든 관리 기능을 수행할 수 있는 최상위 관리자 계정이다.
- SYSTEM
백업, 복구 등 일부 관리 기능을 제외한 모든 시스템 권한을 부여받은 DBA 계정이다. 오라클 설치 시에 패스워드를 설정해야 한다.
Oracle과 SQL Server의 사용자에 대한 아키텍처는 다른 면이 많다. Oracle은 유저를 통해 DB에 접속하는 형태다. 즉 아이디와 비밀번호 방식으로 인스턴스에 접속을 하고 그에 해당하는 스키마에 오브젝트 생성 등의 권한을 부여받게 된다.
SQL Server는 인스턴스에 접속하기 위해 로그인이라는 것을 생성하게 되며, 인스턴스 내에 존재하는 다수의 DB에 연결해 작업하기 위해 유저를 생성한 후 로그인과 유저를 매핑해 주어야 한다. 더 나아가 특정 유저는 특정 DB 내의 특정 스키마에 대해 권한을 부여받을 수 있다. SQL Server 로그인은 두가지 방식으로 가능하다.
- 마이크로소프트 윈도우 OS 인증 방식으로 윈도우에 로그인한 정보를 가지고 SQL Server에 접속
- 혼합모드(Windows 인증 또는 SQL 인증)방식으로 기본적으로 Window 인증으로도 SQL Server에 접속 가능하며, 오라클의 인증과 같은 방식으로 사용자 아이디와 패스워드로 SQL Server에 접속
유저 생성과 시스템 및 오브젝트 권한 부여/취소
유저를 생성하고 DB에 접속한다. 하지만 DB에 접속했더라도 테이블, 뷰, 인덱스 등과 같은 오브젝트는 생성할 수 없다. 사용자가 실행하는 모든 DDL 문장(CREATE, ALTER, DROP, RENAME 등)은 그에 해당하는 적절한 권한이 있어야만 문장을 실행할 수 있다.
이러한 권한을 시스템 권한이라고 하며 약 100개 이상의 종류가 있다. 일반적으로 시스템 권한은 일일이 유저에게 부여되지 않는다. 100개 이상의 시스템 권한을 일일이 사용자에게 설정하는 것은 너무 복잡하고, 특히 유저로부터 권한을 관리하기가 어렵기 때문이다. 그래서 롤을 이용해 간편하고 쉽게 권한을 부여하게 된다. 롤에 대한 자세한 설명은 차후에 하도록 하고 먼저 권한을 부여하는 방법을 살펴보자. 쿼리는 다음과 같다.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 특정 유저가 로그인할 수 있도록 권한 부여
GRANT CREATE SESSION TO 유저명;
-- 특정 유저가 로그인할 수 없도록 권한 취소
REVOKE CREATE SESSION FROM 유저명;
-- 특정 유저에게 테이블 생성 권한 부여
GRANT CREATE TABLE TO 유저명;
-- 특정 유저의 테이블 생성 권한 취소
REVOKE CREATE TABLE FROM 유저명;
-- 특정 유저에게 CRUD 권한 부여
GRANT 'CRUD명령어' ON 테이블명 TO 유저명;
알고가기
오브젝트는 종류별로 가지는 권한이 다르다. 내용은 다음과 같다.
Role을 이용한 권한 부여
유저를 생성하면 기본적으로 CREATE SESSION, CREATE TABLE, CREATE PROCEDURE 등 많은 권한을 부여해야 한다. DB관리자는 유저가 생성될 때마다 각각의 권한들을 유저에게 부여ㅑ하는 작업을 수행해야 하며, 간혹 권한을 빠뜨릴 수도 있으므로 유저별로 어떤 권한이 부여됐는지를 관리해야 한다. 하지만 관리해야 할 유저가 점차 늘어나고 자주 변경되는 상황에서는 매우 번거로운 작업이 될 것이다. 이와 같은 문제를 줄이기 위해 많은 DB에서 유저들과 권한들 사이에서 궁개 역할을 하는 ROLE을 제공한다.
DB관리자는 ROLE을 생성하고 ROLE에 각종 권한들을 부여한 후, ROLE을 다른 ROLE이나 유저에게 부여할 수 있다. 또한 ROLE에 포함된 권한들이 필요한 유저에게는 해당 ROLE만을 부여함으로써 빠르고 정확하게 필요한 권한을 부여할 수 있다.
아래 그림은 유저들과 권한들 사이 간 ROLE의 역할을 보여 주고 있다. 왼쪽 그림은 권한을 직접 유저에게 할당할 때를 나타내는 것이며, 오른쪽 그림은 ROLE에 권한을 부여한 후 ROLE을 유저들에게 부여하는 것을 나타내고 있다.
ROLE에는 시스템 권한과 오브젝트 권한을 모두 부여할 수 있다. ROLE은 유저에게 직접 부여될 수도 있고, 다른 ROLE에 포함해 유저에게 부여될 수도 있다.
ROLE을 다루는 방법을 예제를 통해 살펴보자. 내용은 다음과 같다.
1
2
3
4
5
6
7
8
-- ROLE 생성
CREATE ROLE 롤명;
-- 특정 유저에 ROLE 부여
GRANT 롤명 TO 유저명;
-- 특정 ROLE에 로그인 및 테이블 생성 권한 부여
GRANT CREATE SESSION, CREATE TABLE TO 롤명;
위와 같이 ROLE을 만들어 사용하는 것이 권한을 직접 부여하는 것보다 빠르고 안전하게 유저를 관리할 수 있는 방법이다. 오라클에서는 기본적으로 몇 가지 ROLE을 제공한다. 그 중 많이 사용하는 ROLE은 CONNECT와 RESOURCE다. 아래 이미지는 CONNECT와 RESOURCE 그리고 DBA에 부여된 권한 목록을 정리한 것이다.
읽어주셔서 감사합니다. 😊
Reference
SQL 전문가 가이드 - Kdata 한국데이터산업진흥원
오라클 유저와 권한 및 롤 - Tistory