[SQLD] 27.DCL(DATA CONTROL LANGUAGE)

제 7절 DCL(DATA CONTROL LANGUAGE)

1. DCL 개요

- DDL : 테이블 생성과 조작에 관련된 명령어

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

- TCL : 트랜잭션을 제어하기 위한 명령어 (COMMIT, ROLLBACK, SAVEPOINT 등)

- DCL: 유저를 생성하고 권한을 제어할 수 있는 명령어

2. 유저와 권한

  데이터베이스 시스템을 운영할 때 새로운 유저를 생성하고, 생성한 유저에게 공유할 테이블이나 기타 오브젝트에 대한 접근 권한만을 부여한다면 문제는 쉽게 해결할 수 있다. 대부분의 데이터베이스는 데이터 보호와 보안을 위해서 유저와 권한을 관리하고 있는데, 오라클에서는 기본적으로 제공되는 유저들인 SYS, SYSTEM, SCOTT 유저에 대해서 간단하게 알아보고자 한다. 

오라클에서의 데이터베이스 접속 방식

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

SQL Server에서의 데이터베이스 접속 방식

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

SQL Server 로그인

1. Window 인증 방식 - 윈도우에 로그인한 정보를 가지고 SQL Server에 접속

2. 혼합모드 인증 방식 - 윈도우 인증 또는 SQL 인증 방식으로 기본적으로 윈도우 인증으로도 SQL Server 접속 가능하며, Oracle의 인증과 같은 방식으로 사용자 아이디와 비밀번호로 서버에 접속하는 방식이다. 

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

  유저를 생성하고 데이터베이스에 접속을 하게 되면, 테이블, 뷰, 인덱스 등과 같은 오브젝트를 바로 생성할 수는 없다. 사용자가 실행하는 모든 DDL문장(CREATE, ALTER, DROP, RENAME 등)은 그에 해당하는 적절한 권한이 있어야 문장을 실행할 수 있고, 이러한 권한을 시스템 권한이라고 한다. 이러한 시스템 권한은 유저에게 일일히 설정하는 것은 너무 복잡하기 때문에 ROLE 이라는 것을 이용하여 권한을 부여하게 된다. 

Oracle의 경우

데이터베이스 연결

CONN SCOTT/TIGER

권한 부여

GRANT CREATE USER TO SCOTT;

유저 생성

CREATE USER PSH IDENTIFIED BY POSSWORDDD;

SQL Server의 경우

로그인 생성 

- 유저를 생성하기 전 먼저 로그인을 생성해야 함, 로그인 생성 권한을 가진 로그인은 기본적으로 sa

- sa로 로그인을 한 후, SQL 인증을 사용하는 PSH라는 로그인을 생성

- 최초로 접속할 데이터베이스는 AdventureWorks로 설정

- SQL Sever에서의유저는 데이터베이스마다 존재하기 때문에 접속할 데이터베이스를 설정

CREATE LOGIN PSH WITH PASSWORD='KOREA7', DEFAULT_DATABASE = AdventureWorks;

USE ADVENTUREWORKS;

GO

CREATE USER PSH FOR LOGIN PSH WITH DEFAULT_SCHEMA = dbo;

유저가 생성되었지만, 아무런 권한을 부여받지 못했기 때문에 로그인을 하면 CREATE SESSION 권한이 없다는 오류가 발생한다. 그렇기 때문에 권한을 부여하고 나서 로그인을 수행해야한다.

오라클

CONN SCOTT/TIGER;

GRANT CREATE SESSION TO PSH;

CONN PSH/KOREA7;

테이블 권한 부여

CONN SYSTEM/MANAGER;

GRANT CREATE TABLE TO PSH;

CONN PSH/KOREA7;

CREATE TABLE MENU (

MENU_SEQ NUMBER NOT NULL,

TITLE VARCHER2(10) );

SQL Server

GRANT CREATE TABLE TO PSH;

GRANT Control ON SCHEMA::dbo TO PJS;

CREATE TABLE MENU (

MENU_SEQ NUMBER NOT NULL,

TITLE VARCHER2(10) );

나. OBJECT에 대한 권한 부여

  특정 유저가 소유한 객체(OBJECT) 권한에 대해 알아보자. 오브젝트 권한은 특정 오브젝트인 테이블, 뷰 등에 대한 SELECT, INSERT, DELETE, UPDATE 작업 명령어를 의미한다. 다음은 오브젝트 권한과 오브젝트와의 관계를 나타내는 표이다.

  모든 유저는 각각 자신이 생성한 테이블 외에 다른 유저의 테이블에 접근하려면 해당 테이블에 대한 오브젝트 권한을 소유자로부터 부여받아야 한다. SQL Server도 같은 방식으로 동작한다. 한가지 다른점은 위에서 언급했듯이 유저는 단지 스키마에 대한 권한만을 가진다. 즉, 테이블과 같은 오브젝트는 유저가 소유하는 것이 아니고 스키마가 소유를 하게 되며 유저는 스키마에 대해 특정한 권한을 가지는것이다.

테이블에 접근할 수 있는 권한을 유저에게 주기

GRANT SELECT ON MENU TO SCOTT;

3. Role을 이용한 권한 부여

  유저를 생성하면 기본적으로 CREATE SESSION, CREATE TABLE, CREATE PROCEDURE 등 많은 권한을 부여해야한다. 데이터베이스 관리자는 유저가 생성될 떄마다 각가그이 권한들을 유저에게 부여하는 작업을 수행해야 하며, 간혹 권한을 빠뜨릴 수도 있으므로 각 유저별로 어떤 권한이 부여되었는지를 관리해야한다. 

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

  Role에는 시스템 권한과 오브젝트 권한을 모두 부여할 수 있으며, Role은 유저에게 직접 부여될 수도 있고, 다른 Role에 포함되어 유저에게 부여될 수도 있다.

부여했었던 권한 취소 (오라클)

REVOKE CREATE SESSION, CREATE TABLE FROM PSH;

부여했었던 권한 취소 (SQL Server)

REVOKE CREATE TABLE FROM PSH;

Role 만들기(LOGIN_TABLE 이라는 롤 만들기)

CONN SYSTEM/MANAGER;

CREATE ROLE LOGIN_TABLE;

GRANT CREATE SESSION, CREATE TABLE TO LOGIN_TABLE;

GRANT LOGIN_TABLE TO PSH;

CONN PSH/KOREA7;

CREATE TABLE MENU2(

MENU_SEQ NUMBER NOT NULL,

TITLE VARCHER2(10));

오라클에서는 몇 가지 롤을 제공하고 있는데, 제일 많이 쓰이는 롤이 아래와 같이 CONNECT와 RESOURCE이다. 

CONNECT : CREATE SESSION 처럼 로그인 권한이 포함

RESOURCE : CREATE TABLE과 같은 오브젝트의 생성 권한이 포함

유저 삭제 명령

DROP USER PSH CASCADE; // 유저가 만든 테이블도 같이 삭제됨

사용자 생성 명령

CREATE USER PSH IDENTIFIED BY KOREA7;

롤을 이용한 권한 부여

GRANT CONNECT, RESOURCE TO PSH;

SQL Server에서는 롤을 생성하여 사용하기 보다는 기본적으로 제공되는 롤에 멤버로 참여하는 방식으로 사용된다. 하지만 오라클과 같이 롤을 자주 사용하지는 않는다. 

- 인스턴스 수준의 작업이 필요한 경우 : 서버 수준 역할명을 이용하여 로그인 및 사용자 권한을 제어

- 데이터베이스 수준의 작업이 필요한 경우 : 데이터베이스 수준 역할을 이용하여 부여