[SQLD 학습 자료 요약] SQL 기본 및 활용 2.7. DCL

본 문서의 내용은 한국데이터산업진흥원에서 펴낸 SQL 전문가 가이드를 기반으로 자격증 취득에 도움이 될 개념을 정리한 것입니다.

SQL 전문가 가이드 국내도서 저자 : 한국데이터산업진흥원 출판 : 한국데이터산업진흥원 2020.05.29 상세보기

 

7. DCL

1. DCL 개요

유저를 생성하고 권한을 제어할 수 있는 DCL (Data Control Language)

 

---

2. 유저와 권한

Oracle에서 제공하는 유저들

• SCOTT / 패스워드: TIGER = 테스트용 샘플 유저
• SYS = DBA ROLE을 부여받은 유저
• SYSTEM = 모든 시스템 권한을 부여받은 DBA 유저, Oracle 설치 완료 시 패스워드 설정

 

Oracle 과 SQL Server 의 사용자에 대한 아키텍처는 다른 면이 많다. Oracle 은 유저를 통해 데이터베이스에 접속을 하는 형태이다. 즉, 아이디와 비밀번호 방식으로 인스턴스에 접속을 하고 그에 해당하는 스키마에 오브젝트 생성 등의 권한을 부여받게 된다.

 

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

 

SQL Server 로그인 방식

1. Windows 인증 방식으로 Windows 에 로그인한 정보를 가지고 SQL Server 에 접속하는 방식이다. Microsoft Windows 사용자 계정을 통해 연결되면 SQL Server 는 운영 체제의 Windows 보안 주체 토큰을 사용하여 계정 이름과 암호가 유효한지 확인한다. 즉, Windows 에서 사용자 ID 를 확인한다. SQL Server 는 암호를 요청하지 않으며 ID 의 유효성 검사를 수행하지 않는다. Windows 인증은 기본 인증 모드이며 SQL Server 인증보다 훨씬 더 안전하다.
2. 혼합 모드(Windows 인증 또는 SQL 인증) 방식으로 기본적으로 Windows 인증으로도 SQL Server 에 접속 가능하며, Oracle 의 인증과 같은 방식으로 사용자 아이디와 비밀번호로 SQL Server 에 접속하는 방식이다.

 

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

사용자가 실행하는 모든 DDL 문장 (CREATE, ALTER, DROP, RENAME 등)은 그에 해당하는 적절한 권한이 있어야만 문장을 실행할 수 있다.

 

Oracle 유저 생성 및 권한 부여 과정

-- Oracle SQL Plus 사용
CONN SCOTT/TIGER;

CREATE USER PJS IDENTIFIED BY KOREA7; -- ERROR 발생

CONN SYSTEM/비밀번호;
GRANT CREATE USER TO SCOTT; -- 유저 생성 권한 부여

CONN SCOTT/TIGER;
CREATE USER PJS IDENTIFIED BY KOREA7;  -- 정상 동작

CONN PJS/KOREA7;  -- ERROR 발생

CONN SYSTEM/비밀번호;
GRANT CREATE SESSION TO PJS;  -- 로그인 권한 부여

CONN PJS/KOREA7;  -- 정상 동작

CREATE TABLE MENU (MENU_SEQ NUMBER NOT NULL, TITLE VARCHAR2(10));  -- ERROR 발생

CONN SYSTEM/비밀번호;
GRANT CREATE TABLE TO PJS;  -- 테이블 생성 권한 부여

CONN PJS/KOREA7;
CREATE TABLE MENU (MENU_SEQ NUMBER NOT NULL, TITLE VARCHAR2(10));  -- 정상 동작

SQL PLUS를 활용하여 오라클 유저에게 권한 부여 및 테이블 생성

참고로 오라클 18c XE 버전을 사용하였으며, SYSTEM으로 연결하여 유저를 생성했습니다.

최근에는 유저명 앞에 C##을 붙여주어야 정상적으로 생성이 되니 참고해주시기 바랍니다.

 

SQL Server 유저 생성 및 권한 부여 과정

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

USE ADVENTUREWORKS;
GO CREATE USER PJS FOR LOGIN PJS WITH DEFAULT_SCHEMA = dbo;
-- 데이터베이스로 이동하여 유저 생성

CREATE TABLE MENU (MENU_SEQ INT NOT NULL, TITLE VARCHAR(10));  -- ERROR 발생

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, INSERT, DELETE, UPDATE 작업 명령어를 의미한다.

오브젝트 권한과 오브젝트와의 관계 (출처: SQL 전문가 가이드)

 

모든 유저는 각각 자신이 생성한 테이블 외에 다른 유저의 테이블에 접근하려면 해당 테이블에 대한 오브젝트 권한을 소유자로부터 부여받아야 한다. SQL Server 도 같은 방식으로 동작한다.

 

한 가지 다른 점은 위에서 언급했듯이 유저는 단지 스키마에 대한 권한만을 가진다. 다시 말하면 테이블과 같은 오브젝트는 유저가 소유하는 것이 아니고 스키마가 소유를 하게 되며 유저는 스키마에 대해 특정한 권한을 가지는 것이다. 먼저 SCOTT 유저로 접속하여 PJS.MENU 테이블을 조회한다. 다른 유저가 소유한 객체에 접근하기 위해서는 객체 앞에 객체를 소유한 유저의 이름을 붙여서 접근해야 한다.

-- 예시
GRANT SELECT, UPDATE ON A_User.TB_A TO B_User;

 

---

 

3. Role 을 이용한 권한 부여

데이터베이스 관리자는 ROLE 을 생성하고, ROLE 에 각종 권한들을 부여한 후 ROLE 을 다른 ROLE 이나 유저에게 부여할 수 있다. 또한 ROLE 에 포함되어 있는 권한들이 필요한 유저에게는 해당 ROLE 만을 부여함으로써 빠르고 정확하게 필요한 권한을 부여할 수 있게 된다.

 

ROLE 생성 및 ROLE 을 이용한 권한 부여

CONN SYSTEM/비밀번호;

CREATE ROLE LOGIN_TABLE; -- ROLE 생성
GRANT CREATE SESSION, CREATE TABLE TO LOGIN_TABLE; -- 권한 부여
GRANT LOGIN_TABLE TO JISUNG;  -- JISUNG에게 권한 부여

 

 

ROLE 을 만들어 사용하는 것이 권한을 직접 부여하는 것보다 빠르고 안전하게 유저를 관리할 수 있는 방법이다. Oracle 에서는 기본적으로 몇 가지 ROLE 을 제공하고 있다. 그 중 가장 많이 사용하는 ROLE 은 CONNECT 와 RESOURCE 이다.

오라클에서 제공하는 CONNECT, RESOURCE ROLE (출처: SQL 전문가 가이드)

 

유저 삭제

CONN SYSTEM/비밀번호;

DROP USER JISUNG CASCADE; -- JISUNG이 생성한 오브젝트 삭제 후 유저 삭제

 

SQL Server에서 제공하는 ROLE 사례

SQL Server에서 제공하는 서버 수준 ROLE (출처: SQL 전문가 가이드)

SQL Server에서 제공하는 데이터베이스 수준 ROLE (출처: SQL 전문가 가이드)

 

SQL Server 에서는 Oracle 과 같이 Role 을 자주 사용하지 않는다. 대신 위에서 언급한 서버 수준 역할 및 데이터베이스 수준 역할을 이용하여 로그인 및 사용자 권한을 제어한다. 인스턴스 수준의 작업이 필요한 경우 서버 수준 역할을 부여하고 그보다 작은 개념인 데이터베이스 수준의 권한이 필요한 경우 데이터베이스 수준의 역할을 부여하면 된다. 즉, 인스턴스 수준을 요구하는 로그인에는 서버 수준 역할을, 데이터베이스 수준을 요구하는 사용자에게는 데이터베이스 수준 역할을 부여한다.

 

---

↓SQL 전문가 가이드 요약 목록

1장. 데이터 모델링의 이해

Part 1. 데이터 모델링의 이해

1.1. 데이터 모델링의 이해

1.2. 엔터티

1.3. 속성

1.4. 관계

1.5. 식별자

 

Part 2. 데이터 모델과 성능

2.1. 성능 데이터 모델링 개요

2.2. 정규화와 성능

2.3. 반정규화와 성능

2.4. 대량 데이터에 따른 성능

2.5. 데이터베이스 구조와 성능

2.6. 분산 데이터베이스와 성능

 

2장. SQL 기본 및 활용

Part 1. SQL 기본

1.1. 관계형 데이터베이스 개요

1.2. DDL

1.3. DML

1.4. TCL

1.5. WHERE 절

1.6. 함수 (FUNCTION)

1.7. GROUP BY, HAVING 절

1.8. ORDER BY 절

1.9. 조인 (JOIN)

 

Part 2. SQL 활용

2.1. 표준 조인

2.2. 집합 연산자

2.3. 계층형 질의와 셀프 조인

2.4. 서브쿼리

2.5. 그룹 함수

2.6. 윈도우 함수

2.7. DCL

2.8. 절차형 SQL

 

Part 3. SQL 최적화 기본원리

3.1. 옵티마이저와 실행계획

3.2. 인덱스 기본

3.3. 조인 수행 원리

 

따로 PDF 파일이 필요하신 분은 댓글을 통해 메일 주소 적어주시기 바랍니다.