DCL

DCL(Data Control Language)은 데이터베이스 내에서 유저와 권한을 관리하는 명령어들을 포함한다.

DCL은 유저를 생성하고 이들에게 데이터베이스 오브젝트에 대한 접근 권한을 부여하는 명령어들이다. DCL 명령어를 통해 데이터베이스의 보안을 강화하고 데이터의 손실을 예방할 수 있다.

유저와 권한

데이터베이스를 공유할 필요가 있을 때 새로운 유저를 생성하고, 이 유저에게 필요한 권한만 부여하여 데이터 손실을 방지할 수 있다. 예를 들어, 회원제 웹사이트에서는 유저가 가입하고 로그인하여 서비스를 이용하며, 특정 컨텐츠에 접근하기 위해서는 추가적인 권한이 필요하다.

Oracle에서 제공하는 유저들

Oracle 데이터 베이스는 몇 가지 기본 유저를 제공한다.

• SCOTT: Oracle 테스트용 샘플 유저 (Default 패스워드: TIGER)
• SYS: DBA(데이터베이스 관리자) ROLE을 부여받은 유저
• SYSTEM: 데이터베이스의 모든 시스템 권한을 부여받은 DBA 유저 (설치 시 패스워드 설정 필요)

Oracle과 SQL Server의 사용자 아키텍처는 다르다. Oracle에서는 유저를 통해 데이터베이스에 접속하며, 해당 유저에게 스키마와 오브젝트 생성 권한을 부여합니다. 반면에 SQL Server에서는 인스턴스에 접속하기 위해 로그인 계정을 생성하고, 로그인과 유저를 매핑하여 작업을 수행한다.

SQL Server의 로그인 방식

SQL Server 로그인 방식은 두 가지이다.

1. Windows 인증 방식
   • Windows 계정 정보를 이용하여 SQL Server에 접속한다. 이 방식은 운영 체제의 보안 주체 토큰을 사용하여 유효성을 확인하며, SQL Server 인증보다 더 안전하다. Windows 인증은 Kerberos 보안 프로토콜을 사용하고, 암호 정책을 적용하며, 계정 잠금 및 암호 만료를 지원한다.
2. 혼합 모드 (Windows 인증 또는 SQL 인증)
   • 기본적으로 Windows 인증을 사용하며, 사용자 아이디와 비밀번호를 통한 SQL 인증도 가능하다. SQL 인증 시에는 강력한 암호를 사용하는 것이 중요하다.

예시로, SQL Server에서는 SCOTT이라는 LOGIN 이름으로 인스턴스 INST1에 접속하여, SCOTT이라는 유저를 통해 PRODUCT 스키마의 ITEM 테이블에 접근할 수 있다.

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

데이터베이스에서 유저를 생성하고 권한을 부여하는 과정은 데이터 보안과 관리에 있어서 중요한 부분이다. 유저가 데이터베이스에 접속하여 다양한 작업을 수행하려면 필요한 권한이 부여되어야 한다.

1. 유저 생성과 권한 부여 개요

• 유저 생성과 권한 부여
  • 유저 생성: 데이터베이스에 새로운 유저를 추가하는 것
  • 시스템 권한 부여: 유저에게 테이블 생성, 뷰 생성, 세션 생성 등 다양한 작업을 수행할 수 있는 권한을 부여하는 것

유저가 시스템 권한을 가지지 않으면, DDL 문장(CREATE, ALTER, DROP, RENAME 등)을 실행할 수 없다. 이러한 권한은 약 100개 이상 있으며, 일반적으로 롤(ROLE)을 이용해 관리한다.

2. Oracle에서 유저 생성 및 권한 부여

Oracle에서 유저를 생성하고 권한을 부여하는 과정은 다음과 같다.

1) SCOTT 유저로 새로운 유저 생성 시도

CREATE USER PJS IDENTIFIED BY KOREA7;
-- 오류 발생: ERROR: 권한이 불충분하다
-- 이유: SCOTT 유저에게 유저 생성 권한(CREATE USER)이 부여되지 않았기 때문이다.

2) SYSTEM 유저로 SCOTT 유저에게 CREATE USER 권한 부여

GRANT CREATE USER TO SCOTT;
-- 권한 부여 후 SCOTT 유저로 다시 접속하여 유저 생성
CREATE USER PJS IDENTIFIED BY KOREA7;
-- 성공: 사용자가 생성되었다

3) PJS 유저로 로그인 시도

CONN PJS/KOREA7;
-- 오류 발생: ERROR: 사용자 PJS는 CREATE SESSION 권한을 가지고 있지 않음
-- 이유: 유저가 로그인하려면 CREATE SESSION 권한이 필요하다.

4) SCOTT 유저로 PJS 유저에게 CREATE SESSION 권한 부여

GRANT CREATE SESSION TO PJS;
-- 권한 부여 후 PJS 유저로 다시 접속:
CONN PJS/KOREA7;
-- 성공: PJS 유저로 로그인 가능

5) PJS 유저로 테이블 생성 시도

CREATE TABLE MENU ( MENU_SEQ NUMBER NOT NULL, TITLE VARCHAR2(10) );
-- 오류 발생: ERROR: 권한이 불충분하다.
-- 이유: 테이블 생성 권한(CREATE TABLE)이 부족하기 때문이다.

6) SYSTEM 유저로 PJS 유저에게 CREATE TABLE 권한 부여

GRANT CREATE TABLE TO PJS;
-- 권한 부여 후 PJS 유저로 다시 접속하여 테이블 생성
CREATE TABLE MENU ( MENU_SEQ NUMBER NOT NULL, TITLE VARCHAR2(10) );
-- 성공: 테이블이 생성되었다.

3. SQL Server에서 유저 생성 및 권한 부여

SQL Server에서는 유저를 생성하기 전 로그인을 생성해야 한다.

1) sa 유저로 로그인 생성

CREATE LOGIN PJS WITH PASSWORD='KOREA7', DEFAULT_DATABASE=AdventureWorks;
-- 성공: 로그인이 생성되었다

2) AdventureWorks 데이터베이스로 이동하여 유저 생성

USE AdventureWorks;
GO
CREATE USER PJS FOR LOGIN PJS WITH DEFAULT_SCHEMA = dbo;
-- 성공: 유저가 생성되었다.

3) PJS 유저로 로그인 시도

-- Oracle과 동일하게 CREATE SESSION 권한이 필요합니다.

4) 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 작업 명령어를 의미한다.

1. 객체 권한 부여 과정

1) SCOTT 유저로 PJS.MENU 테이블 조회 시도

-- Oracle
CONN SCOTT/TIGER
SELECT * FROM PJS.MENU;

-- SQL Server
SCOTT로 로그인
SELECT * FROM dbo.MENU;

-- 오류 발생: 권한이 부족하여 테이블 조회 불가.

2) PJS 유저로 SCOTT 유저에게 SELECT 권한 부여

-- Oracle
CONN PJS/KOREA7
INSERT 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;

-- 성공: SCOTT 유저에게 MENU 테이블 SELECT 권한 부여됨.

3) SCOTT 유저로 PJS.MENU 테이블 조회

-- Oracle
CONN SCOTT/TIGER
SELECT * FROM PJS.MENU;

-- SQL Server
SCOTT로 로그인
SELECT * FROM dbo.MENU;

-- 성공: MENU 테이블 조회 가능.

4) SCOTT 유저로 PJS.MENU 테이블 UPDATE 시도

-- Oracle
CONN SCOTT/TIGER
UPDATE PJS.MENU SET TITLE = '코리아' WHERE MENU_SEQ = 1;

-- SQL Server
SCOTT로 로그인
UPDATE dbo.MENU SET TITLE = '코리아' WHERE MENU_SEQ = 1;

-- 오류 발생: 권한 부족으로 UPDATE 불가.

5) PJS 유저로 SCOTT 유저에게 UPDATE 권한 부여

-- Oracle
CONN PJS/KOREA7
GRANT UPDATE ON MENU TO SCOTT;

-- SQL Server
PJS로 로그인
GRANT UPDATE ON dbo.MENU TO SCOTT;

-- 성공: SCOTT 유저에게 MENU 테이블 UPDATE 권한 부여됨.

6) SCOTT 유저로 PJS.MENU 테이블 UPDATE 다시 시도

-- Oracle
CONN SCOTT/TIGER
UPDATE PJS.MENU SET TITLE = '코리아' WHERE MENU_SEQ = 1;

-- SQL Server
SCOTT로 로그인
UPDATE dbo.MENU SET TITLE = '코리아' WHERE MENU_SEQ = 1;

-- 성공: MENU 테이블 업데이트 가능.

3️⃣ Role을 이용한 권한 부여

데이터베이스에서 유저에게 필요한 권한을 효율적으로 관리하기 위해 Role을 사용하는 방법을 설명하겠다. Role을 통해 권한을 중앙에서 관리하고, 여러 유저에게 필요한 권한을 손쉽게 부여할 수 있다.

1. Role의 개념

Role은 여러 권한을 묶어서 관리할 수 있는 그룹이다. 데이터베이스 관리자는 Role을 생성하고, Role에 각종 권한을 부여한 후, Role을 유저에게 할당할 수 있다. 이를 통해 개별 유저에게 권한을 직접 부여하는 대신, Role을 통해 일괄적으로 권한을 부여하고 관리할 수 있다.

위 그림은 권한을 직접 유저에게 할당하는 것이며, 오른쪽 그림은 Role에 권한을 부여한 후 Role을 유저에게 부여하는 것을 나타낸다.

2. Oracle에서 Role 생성 및 권한 부여 예제

1) JISUNG 유저의 기존 권한 취소

CONN SYSTEM/MANAGER
REVOKE CREATE SESSION, CREATE TABLE FROM JISUNG;

-- 권한 취소 후 JISUNG 유저는 접속할 수 없습니다.

2) Role 생성 및 권한 부여

CONN SYSTEM/MANAGER
CREATE ROLE LOGIN_TABLE;
GRANT CREATE SESSION, CREATE TABLE TO LOGIN_TABLE;
GRANT LOGIN_TABLE TO JISUNG;

-- LOGIN_TABLE Role 생성
-- Role에 CREATE SESSION 및 CREATE TABLE 권한 부여
-- Role을 JISUNG 유저에게 부여

3) JISUNG 유저로 접속하여 테이블 생성

CONN JISUNG/KOREA7
CREATE TABLE MENU2 ( MENU_SEQ NUMBER NOT NULL, TITLE VARCHAR2(10) );

3. SQL Server에서의 Role 생성 및 권한 부여

1) PJS 유저의 기존 권한 취소

sa로 로그인
REVOKE CREATE TABLE FROM PJS;

-- 권한 취소 후 PJS 유저는 테이블을 생성할 수 없다.

2) Role 생성 및 권한 부여

sa로 로그인
CREATE ROLE LOGIN_TABLE;
GRANT CREATE TABLE TO LOGIN_TABLE;
GRANT LOGIN_TABLE TO PJS;

-- LOGIN_TABLE Role 생성
-- Role에 CREATE TABLE 권한 부여
-- Role을 PJS 유저에게 부여

3) PJS 유저로 접속하여 테이블 생성

-- PJS로 로그인
CREATE TABLE MENU2 ( MENU_SEQ INT NOT NULL, TITLE VARCHAR(10) );

4. Oracle에서 제공하는 기본 Role

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

• CONNECT Role: CREATE SESSION과 같은 로그인 권한이 포함됨
• RESOURCE Role: CREATE TABLE과 같은 오브젝트 생성 권한이 포함됨

5. Role을 통한 유저 관리의 장점

• 효율성: 유저마다 개별 권한을 부여할 필요 없이 Role을 통해 일괄적으로 권한을 부여할 수 있다.
• 관리 용이성: Role을 통해 권한을 중앙에서 관리하므로, 권한 변경 시 모든 관련 유저에게 쉽게 적용할 수 있다.
• 보안 강화: 권한 부여 및 취소를 체계적으로 관리하여 보안을 강화할 수 있다.

6. SQL Server에서의 권한 관리

SQL Server에서는 Role을 자주 사용하지 않고, 기본적으로 제공되는 서버 수준 역할과 데이터베이스 수준 역할을 사용한다.

SQL Server에서는 서버 수준 역할을 통해 인스턴스 수준의 작업 권한을, 데이터베이스 수준 역할을 통해 데이터베이스 내의 권한을 관리한다.

이와 같이 Role을 이용하여 유저와 권한을 효율적으로 관리할 수 있다.