SQLD 2과목 관리구문 - DCL

<목차> DML TCL DDL DCL

※ 자료출처: 데이터 산업 진흥원(K-DATA)

SQL 문장의 종류

 

명령어의 종류	명령어	설명
데이터 조작어(DML: Data Manipulation Language)	SELECT	데이터베이스에 들어 있는 데이터를 조회하거나 검색하기 위한 명령어 (RETRIEVE)
	INSERT UPDATE DELETE	- 데이터베이스의 테이블에 들어 있는 데이터에 변형을 가하는 종류의 명령어 - 데이터를 테이블에 새로운 행에 집어넣거나, 원하지 않는 데이터를 삭제하거나 수정하는 것들의 명령어
데이터 정의어(DDL: Data Definition Language)	CREATE ALTER DROP RENAME	- 테이블과 같은 데이터 구조를 정의하는데 사용되는 명령어 - 구조를 생성하거나 변경하거나 삭제하거나 이름을 바꾸는 데이터 구조와 관련된 명령어
데이터 제어어(DCL: Data Control Language)	GRANT REVOKE	데이터베이스에 접근하고 객체들을 사용하도록 권한을 주고 회수하는 명령어
트랜잭션 제어어(TCL: Transactuon Control Language)	COMMIT ROLLBACK	논리적인 작업의 단위를 묶어서 DML에 의해 조작된 결과를 작업단위(트랜잭션) 별로 제어하는 명령어

 

4. DCL (Data Control Language)

A.DCL 개요

 

• 유저를 생성하고 권한을 제어할 수 있는 명령어
• 다른 부서 또는 다른 회사 간에 데이터를 공유하기 위해 데이터베이스를 오픈해야 하는 경우, 새로운 유저를 생성하고 생성한 유저에게 공유할 테이블이나 기타 오브젝트에 대한 접근 권한만을 부여하여 데이터베이스를 오픈함으로써 데이터의 손실이 커지는 것을 방지.
• Oracle: 유저를 통해 데이터베이스 접속하는 형태. (아이디와 비밀번호 방식으로 인스턴스에 접속)
• SQL Server: 인스턴스에 접속하기 위해 로그인이라는 것을 생성 후, 인스턴스 내에 존재하는 다수의 데이터베이스에 연결하여 작업하기 위해 유저를 생성 후 로그인과 유저를 매핑. (특정 유저는 특정 데이터베이스 내의 특정 스키마에 대한 권한 부여 가능)
• SQL Server 로그인 방식: 1) Windows 인증방식  2) 혼합모드 (Windows 인증 또는 SQL 인증)

■ Oracle 에서 제공하는 유저들

유저	역할
SCOTT	Oracle 테스트용 샘플 유저 Default  패스워드: TIGER
SYS	DBA Role 을 부여받은 유저
SYSTEM	데이터베이스의 모든 시스템 권한을 부여받은 DBA 유저 Oracle 설치 완료 시에 패스워드 설정

 

 

SQL Sever 접속 경로(출처: 데이터 산업 진흥원)

 

---

B. 유저와 권한

 

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

• 시스템 권한: 모든 DDL 문장 (CREATE, ALTER, DROP, RENAME 등)을 실행하는 권한
• 100개 이상의 종류
• 복잡하고, 유저로부터 권한을 관리하기가 어렵기 때문에 일일이 유저에게 부여되지 않음. 
• ROLE을 이용하여 간편하고 쉽게 권한 부여
• 유저를 생성하기 위해 유저 생성 권한 (CREATE USER)이 있어야 함.

[예제]

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

<Oracle> CONN SCOTT/TIGER 연결되었다. CREATE USER PJS IDENTIFIED BY KOREA7; * 1행에 오류: ERROR: 권한이 불충분하다.

 

▶ SCOTT 유저는 유저를 생성할 권한을 부여받지 못했기 때문에 권한이 불충분하다는 오류 발생.

 

[예제]

SCOTT 유저에게 유저생성 권한(CREATE USER)을 부여한 후 다시 PJS 유저를 생성

<Oracle> GRANT CREATE USER TO SCOTT; 권한이 부여되었다. CONN SCOTT/TIGER 연결되었다. CREATE USER PJS IDENTIFIED BY KOREA7; 사용자가 생성되었다.

 

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

 

[예제]

sa로 로그인을 한 후 SQL 인증을 사용하는 PJS라는 로그인(패스워드: KOREA7)을 생성.

로그인 후 최초로 접속할 데이터베이스는 AdventureWorks 데이터베이스로 설정.

<SQL Server> CREATE LOGIN PJS WITH PASSWORD='KOREA7', DEFAULT_DATABASE=AdventureWorks

 

SQL Server에서의 유저는 데이터베이스마다 존재.

유저를 생성하기 위해서는 생성하고자 하는 유저가 속할 데이터베이스로 이동을 한 후 처리.

 

<SQL Server> USE AdventureWorks; GO CREATE USER PJS FOR LOGIN PJS WITH DEFAULT_SCHEMA = dbo;

 

생성된 PJS 유저로 로그인 <Oracle> CONN PJS/KOREA7; 오류: ERROR: 사용자 PJS는 CREATE SESSION 권한을 가지고 있지 않음; 로그온이 거절되었다.

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

 

PJS 유저가 로그인할 수 있도록 CREATE SESSION 권한 부여 <Oracle> CONN SCOTT/TIGER 연결되었다. GRANT CREATE SESSION TO PJS; 권한이 부여되었다. CONN PJS/KOREA7 연결되었다.

 

PJS 유저로 테이블 생성 <Oracle> SELECT * FROM TAB; 선택된 레코드가 없다. CREATE TABLE MENU (    MENU_SEQ NUMBER NOT NULL,    TITLE VARCHAR2(10) ); CREATE TABLE MENU (* 1행에 오류: ERROR: 권한이 불충분하다.

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

 

SYSTEM 유저를 통하여 PJS 유저에게 CREATE TABLE 권한을 부여한 후 다시 테이블 생성

<Oracle> 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 권한이 부여되었다. PJS로 로그인한다. CREATE TABLE MENU (    MENU_SEQ INT NOT NULL    TITLE VARCHAR(10) ); 테이블이 생성되었다.

---

■ OBJECT에 대한 권한 부여

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

출처: 데이터 자격 진흥원

테이블과 같은 오브젝트는 유저가 소유하는 것이 아니고 스키마가 소유를 하게 되며 유저는 스키마에 대해 특정한 권한을 가지는 것. 다른 유저가 소유한 객체에 접근하기 위해서는 객체 앞에 객체를 소유한 유저의 이름을 붙여서 접근. SQL Server는 객체 앞에 소유한 유저의 이름을 붙이는 것이 아니고 객체가 속한 스키마 이름을 붙여야 함.

 

 

[예제]

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 N MENU TO SCOTT; 권한이 부여되었다.

 

[예제]

PJS.MENU 테이블에 UPDATE를 시도

<Oracle> CONN SCOTT/TIGER 연결되었다. SELECT * FROM PJS.MENU; MENU_SEQ TITLE ---------- ------------            1   파이팅 UPDATE PJS.MENU SET TITLE - '코리아' WHERE MENU_SEQ = 1; UPDATE PJS.MENU * 1행에 오류: ERROR: 권한이 불충분하다.

<SQL Server> SCOTT으로 로그인한다. SELECT * FROM PJS.MENU; MENU_SEQ TITLE ---------- ------------            1   파이팅 UPDATE PJS.MENU SET TITLE - '코리아' WHERE MENU_SEQ = 1; 개체 'MENU', 데이터베이스 'AdventureWorks', 스키마 'dbo'에 대한 UPDATE 권한이 거부되었다.

▶ 권한이 부족하여 UPDATE를 할 수 없다는 오류가 나타날 때 PJS 유저에게 UPDATE 권한을 부여한 후 다시 시도하면 업데이트 가능.

 

---

 

C. ROLE을 이용한 권한 부여

 

• 유저를 생성하려면 기본적으로 CREATE SESSION, CREATE TABLE, CREATE PROCEDURE 등 많은 권한을 부여해야 함.
• 데이터베이스 관리자는 유저가 생성될 때마다 각각의 권한들을 유저에게 부여하는 작업을 수행
• 각 유저별로 어떤 권한이 부여되었는지 역시 관리 필요.
• 관리해야 할 유저가 점점 늘어나고 자주 변경되는 상황에서는 매우 번거로운 작업이 되기 때문에 데이터베이스에서 유저들과 권한들 사이에서 중개 역할을 하는 ROLE 제공.
• 데이터베이스 관리자는 ROLE 생성 및 ROLE에 각종 권한 부여 후, ROLE을 다른 ROLE 이나 유저에게 부여
• ROLE 에 포함되어 있는 권한들이 필요한 유저에게는 해당 ROLE 만을 부여함으로써 빠르고 정확하게 필요한 권한 부여 가능.

ROLE의 개념 (왼쪽-유저에게 권한 직접 할당 / 오른쪽-ROLE에 권한 부여 후 유저들에게 ROLE 부여), 출처: 데이터 산업 진흥원

 

 

[예제]

JISUNG 유저에게 CREATE SESSION 과 CREATE TABLE 권한을 가진 ROLE을 생성 후 ROLE을 이용하여 다시 권한 할당. (권한 취소 시에는 REVOKE 사용)

<Oracle> CONN SYSTEM/MANAGER 연결되었다. REVOKE CREATE SESSION, CREATE TABLE FROM JISUNG; 권한이 취소되었다. CONN JISUNG/KOREA7 ERROR: 사용자 JISUNG은 CREATE SESSION 권한을 가지고 있지 않음. 로그온이 거절되었다.

<SQL Server> sa로 로그인한다. REVOKE CREATE TABLE FROM PJS; 권한이 취소되었다. PJS로 로그인한다. CREATE TABLE MENU (    MENU_SEQ INT NOT NULL    TITLE VARCHAR(10) ); 데이터베이스 'AdventureWorks'에서 CREATE TABLE 사용 권한이 거부되었다.

 

 

[예제]

LOGIN_TABLE 이라는 ROLE을 만들고, 이 ROLE을 이용하여 JISUNG 유저에게 권한 부여

<Oracle> CONN SYSTEM/MANAGER 연결되었다. CREATE ROLE LOGIN_TABLE; 롤이 생성되었다. GRANT CREATE SESSION, CREATE TABLE TO LOGIN_TABLE; 권한이 부여되었다. GRANT LOGIN_TABLE TO JISUNG; 권한이 부여되었다. 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 과 같은 오브젝트의 생성 권한 포함
• DROP USER: USER를 삭제
• CASCADE 옵션: 해당 유저가 생성한 오브젝트를 먼저 삭제 후 유저 삭제

출처: 데이터 산업 진흥원

 

[예제]

(MENU 라는 테이블 생성 된 상태) CASCADE 옵션을 사용하여 JISUNG 유저를 삭제한 후, 유저 재생성 및 기본적인 ROLE 부여

<Oracle> CONN SYSTEM/MANAGER 연결되었다. DROP USER JISUNG CASCADE; 사용자가 삭제되었다. (JISUNG 유저가 만든 MENU 테이블도 같이 삭제) 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 에 멤버로 참여하는 방식으로 사용.
• SQL Server 에서는 Oracle과 같이 ROLE을 자주 사용하지 않음.
• 대신 서버 수준 역할 및 데이터베이스 수준 역할을 이용하여 로그인 및 사용자 권한 제어.
• 인스턴스 수준의 작업이 필요한 경우 서버 수준 역할을 부여하고 그보다 작은 개념인 데이터베이스 수준의 권한이 필요한 경우 데이터베이스 수준의 역할 부여
• 인스턴스 수준을 요구하는 로그인 - 서버 수준 / 데이터베이스 수준을 요구하는 사용자 - 데이터베이스 수준

■ 특정 로그인이 멤버로 참여할 수 있는 서비스 수준 역할(ROLE) 

 

■ 데이터베이스에 존재하는 유저에 대한 데이터베이스 역할

  

이전글

DDL

 

다음글