[SQLD] #027 SQL 활용 - DCL

목차

1. DCL 개요

2. 유저와 권한

3. ROLE을 이용한 권한 부여

 

 

---

 

 

 

1. DCL 개요

지금까지 정리한 SQL 문장은 다음과 같다. 

 

DDL : 테이블 생성과 조작에 관한 명령어

DML : 데이터를 조작하기 위한 명령어

TCL : 트랜잭션을 제어하기 위한 명령어

 

추가로 이런 명령어들 외에도 유저를 생성하고 권한을 제어할 수 있는 DCL(DATA CONTROL LANGUAGE) 명령어가 있다.

 

 

 

 

 

 

 

 

2. 유저와 권한

대부분의 데이터베이스는 데이터 보호와 보안을 위해서 유저와 권한을 관리하고 있는데, 예를 들어 Oracle에서 제공하는 유저들은 다음과 같다. 

 

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

 

 

하지만 SQL Server 로그인은 두 가지 방식으로 가능하다. 

 

- Windows 인증 방식으로 Windows에 로그인한 정보를 가지고 SQL Server에 접속하는 방식

Microsoft Windows 사용자 계정을 통해 연결되면 SQL Server 는 운영체제의 Windows 보안 주체 토큰을 사용하여 계정 이름과 암호가 유효한지 확인한다. 즉, Windows 에서 사용자 ID 를 확인한다. SQL Server 는 암호를 요청하지 않으며 ID 의 유효성을 검사하지 않는다. Windows 인증는 기본 인증 모드이며 SQL Server 인증보다 훨씬 더 안전하다.

Windows 인증은 Kerberos 보안 프로토콜을 사용하고, 암호 정책을 적용하여 강력한 암호에 대해 적합한 복잡성 수준을 유지하도록 하며, 계정 잠금 및 암호 만료를 지원한다. SQL Server 가 Windows 에서 제공하는 자격 증명을 신뢰하므로 Windows 인증을 사용한 연결을 트러스트 된 연결이라고도 한다.

 

 

- 혼합 모드(Windows 인증 또는 SQL 인증) 방식으로 접속

기본적으로 Windows 인증으로도 SQL Server에 접속 가능하며, Oracle 의 인증처럼 사용자 아이디와 비밀번호로 SQL Server에 접속하는 방식이다. SQL 인증을 사용할 때는 강력한 암호 (숫자+문자+특수문자 등을 혼합) 를 사용해야 한다.

예를 들어 아래 그림을 보면 SCOTT 이라는 LOGIN 이름으로 인스턴스 INST1 에 접속하여 미리 매핑되어 있는 SCOTT 이라는 유저를 통해 PRODUCT 라는 스키마에 속해있는 ITEM 이라는 테이블의 데이터에 엑세스 하고있다.

 

 

 

 

 

 

 

 

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

사용자가 실행하는 모든 DDL 문장 (CREATE, ALTER, DROP, RENAME 등) 은 그에 해당하는 적절한 권한이 있어야만 문장을 실행할 수 있다. 일반적으로 이러한 시스템 권한은 일일이 유저에게 부여되지 않는다.

 

우선 새로운 유저를 생성하려면 유저 생성 권한이 있어야 한다. 

 

 

[Oracle]

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

CONN SCOTT/TIGER
-- 연결되었다.

CREATE USER PJS IDENTIFIED BY KOREA7;
-- ERROR: 권한이 불충분하다

현재 SCOTT 유저는 유저를 생성할 권한을 부여받지 못했기 때문에 에러가 난다. 

 

 

예제) SCOTT 유저에게 유저생성권한을 부여한 후 다시 PJS 유저를 생성한다. 

CONN SYSTEM/MANAGER
-- 연결되었다.

GRANT USER TO SCOTT;
-- 권한이 부여되었다.

CONN SCOTT/TIGER
-- 연결되었다.

CREATE USER PJS IDENTIFIED BY KOREA7;
-- 사용자가 생성되었다.

 

예제) 생성된 PJS 유저로 로그인한다. 

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

유저가 로그인을 하려면 CREATE SESSION 권한을 부여받아야 한다. 

 

예제) PJS 유저가 로그인할 수 있도록 권한을 부여한다. 

CONN SCOTT/TIGER
-- 연결되었다.

GRANT CREATE SESSION TO PJS;
-- 권한이 부여되었다.

CONN PJS/KOREA7
-- 연결되었다.

 

 

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

CREATE TABLE MENU
( MENU_SEQ  NUMBER       NOT NULL,
  TITLE     VARCHAR2(10)
);
-- ERROR: 권한이 불충분하다.

PJS 유저는 로그인 권한만 부여됐기 때문에 테이블을 생성하려면 테이블 생성 권한이 불충분하다는 에러가 생긴다. 

 

 

예제) 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)
);
-- 테이블이 생성되었다.

 

 

 

 

 

[SQL Server]

SQL Server 는 유저를 생성하기 전 먼저 로그인을 생성해야 한다. 로그인을 생성할 수 있는 권한을 가진 로그인은 기본적으로 sa 이다. 

 

예제) sa 로 로그인 한 후 SQL 인증을 사용하는 PJS 로그인(패스워드: KOREA7) 을 생성한다. 로그인 후 최초 접속할 데이터베이스는 AdventureWorks 데이터베이스로 설정한다.

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

 

 

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

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

 

예제) 생성된 PJS 유저로 테이블을 생성한다.

CREATE TABLE MENU
( MENU_SEQ  INT         NOT NULL,
  TITLE     VARCHAR(10)
);
-- ERROR: 데이터베이스 'AdventureWorks' 에서 CREATE TABLE 사용 권한이 거부되었다.

PJS 유저는 로그인 권한만 부여됐기 때문에 테이블을 생성하려면 테이블 생성 권한이 불충분하다는 에러가 생긴다. 

 

 

 

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

GRANT CREATE TABLE TO PJS;
-- 권한이 부여되었다.

GRANT Control ON SCHEMA::dbo TO PJS
-- 스키마에 권한이 부여되었다.

-- PJS 로그인

CREATE TABLE MENU
( MENU_SEQ  INT         NOT NULL,
  TITLE     VARCHAR(10)
);
-- 테이블이 생성되었다.

 

 

 

 

 

 

2. OBJECT에 대한 권한 부여

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

 

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

다른 유저가 소유한 객체에 접근하기 위해서는 객체 앞에 객체를 소유한 유저의 이름을 붙여서 접근해야 한다. SQL Server는 객체 앞에 소유한 유저의 이름을 붙이는 것이 아니고, 객체가 속한 스키마 이름을 붙여야 한다.  

 

 

 

예제) SCOTT 유저로 접속해서 PJS.MENU 테이블을 조회한다.

/* Oracle */

CONN SCOTT/TIGER
-- 연결되었다.

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


/* SQL Server */

-- SCOTT 로그인

SELECT * FROM dbo.MENU;
-- ERROR: 개체이름 'dbo.MENU' 이(가) 잘못되었다.  

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

 

 

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

/* Oracle */

CONN PJS/KOREA7
-- 연결되었다.

INSERT INTO MENU VALUES (1, '화이팅');
-- 1개 행이 만들어졌다.

COMMIT;
-- 커밋이 완료되었다.

GRANT SELECT ON MENU TO SCOTT;
-- 권한이 부여되었다.



/* SQL Server */

-- PJS 로그인

INSERT INTO MENU VALUES (1, '화이팅');
-- 1개 행이 만들어졌다.

GRANT SELECT ON MENU TO SCOTT;
-- 권한이 부여되었다.

 

예제) PJS.MENU 테이블에 UPDATE를 시도한다.

/* SQL Server */

SELECT * FROM PJS.MENU;

UPDATE PJS.MENU SET TITLE = '코리아' WHERE MENU_SEQ = 1;
-- ERROR: 개체 'MENU', 데이터베이스 'AdventureWorks', 스키마 'dbo' 에 대한 UPDATE 권한이 거부되었다.

PJS 유저에게 UPDATE 권한을 부여한 후 다시 시도하면 업데이트가 가능하다. 

 

 

 

 

 

 

 

 

 

3. ROLE 을 이용한 권한 부여

유저를 생성하면 기본적으로 CREATE SESSION, CREATE TABLE, CREATE PROCEDURE 등 많은 권한을 부여해야한다. 유저의 수가 많아지고 자주 변경해야 하는 상황에서는 이것이 아주 번거로운 작업이 될 것이다. 

 

이와 같은 문제를 줄이기 위해 많은 데이터베이스에서 유저들과 권한들 사이에서 중개 역할을 하는 ROLE을 제공한다. 데이터베이스 관리자는 ROLE을 생성하고, ROLE에 각종 권한을 부여한 후, ROLE을 다른 ROLE이나 유저에게 부여할 수 있다. 

왼쪽 그림은 권한을 직접 유저에게 할당할 때를 나타내는 것이고, 오른쪽 그림은 ROLE에 권한을 부여한 후 ROLE을 유저에게 부여하는 것을 나타내고 있다. 

 

 

 

예제) LOGIN_TABLE 이라는 ROLE을 만들고, 이 ROLE을 이용하여 JISUNG 유저에게 권한을 부여한다. 권한을 취소할 때는 REVOKE를 사용한다. 

/* Oracle */

CONN SYSTEM/MANAGER
-- 연결되었다.

CREATE ROLE LOGIN_TABLE;
-- 롤이 생성되었다.

GRANT CREATE SESSION, CREATE TABLE TO LOGIN_TABLE;
-- 권한이 부여되었다.

GRANT LOGIN_TABLE TO PJS;
-- 권한이 부여되었다.

CONN JISUNG/KOREA7
-- 연결되었다.

CREATE TABLE MENU2 ( MENU_SEQ NUMBER NOT NULL, TITLE VARCHAR2(10)); 
-- 테이블이 생성되었다.

 

 

 Oracle에서는 기본적으로 몇 가지 ROLE을 제공한다. 그 중 가장 많이 사용하는 ROLE은 CONNECT와 RESOURCE 이다. CONNECT 는 CREATE SESSION과 같은 로그인 권한이 포함되어 있고, RESOURCE는 CREATE TABLE과 같은 오브젝트의 생성 권한이 포함되어있다. 일반적으로 유저를 생성할 때 CONNECT와 RESOURCE ROLE을 사용하여 기본 권한을 부여한다. 

 

유저를 삭제하는 명령어는 DROP USER이고, CASCADE 옵션을 주면 해당 유저가 생성한 오브젝트를 먼저 삭제한 후 유저를 삭제한다. 

 

예제) 앞에서 MENU라는 테이블을 생성했기 때문에 CASCADE 옵션을 사용하여 JISUNG 유저를 삭제한 후, 유저 재생성 및 기본적인 ROLE을 부여한다. 

CONN SYSTEM/MANAGER
-- 연결되었다.

DROP USER JISUNG CASCADE;
-- JISUNG 유저와, JISUNG이 만든 테이블도 같이 삭제되었다. 

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)
);
-- 테이블이 생성되었다.

 

 

SQL Server에서는 위와 같이 ROLE을 생성하여 사용하기보다는 기본적으로 제공되는 ROLE에 멤버로 참여하는 방식으로 사용한다. 특정 로그인이 멤버로 참여할 수 있는 서버 수준 역할(ROLE)은 다음과 같다. 

 

 

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

 

SQL Server 에서는 Oracle과 같이 Role을 자주 사용하지 않는다. 대신 위 표와 같은 서버 수준 역할 및 데이터베이스 수준 역할을 이용하여 로그인과 사용자 권한을 제어한다.

인스턴스 수준의 작업이 필요한 경우 서버 수준 역할을 부여하고 그보다 작은 개념인 데이터베이스 수준의 권한이 필요한 경우 데이터베이스 수준의 역할을 부여하면 된다. 즉, 인스턴스 수준을 요구하는 로그인에는 서버 수준 역할을, 데이터베이스 수준을 요구하는 사용자에게는 데이터베이스 수준 역할을 부여한다. 

 

 

 

 

 

 

 

 

 

---

출처
이 글의 내용은 모두 한국데이터베이스진흥원이 출판한 SQL 전문가 가이드 2013 Edition을 기본으로 한다.