[SQLD 시험 대비] 2과목. SQL 기본 및 활용 : 2장. SQL 활용 - 7. DCL(Data Control Language)

유저를 생성하고 권한을 제어할 수 있는 DCL(DATA CONTROL LANGUAGE) 명령어에 대해 알아보자.

유저와 권한

• 대부분의 데이터베이스는 데이터 보호와 보안을 위해서 유저와 권한을 관리하고 있다.
• Oracle을 설치하면 기본적으로 제공되는 유저들은 다음과 같다.
  
• Oracle은 유저를 통해 데이터베이스에 접속을 하는 형태이다.
  • 아이디와 비밀번호 방식으로 인스턴스에 접속을 하고 그에 해당하는 스키마에 오브젝트 생성 등의 권한을 부여받게 된다.
• SQL Server는 인스턴스에 접속하기 위해 로그인이라는 것을 생성하게 되며, 인스턴스 내에 존재하는 다수의 데이터베이스에 연결하여 작업하기 위해 유저를 생성한 후 로그인과 유저를 매핑해 주어야 한다.

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

• 사용자가 실행하는 모든 DDL 문장(CREATE, ALTER, DROP, RENAME 등)은 그에 해당하는 적절한 권한이 있어야만 문장을 실행할 수 있다.
• 유저를 생성하고 권한을 부여하는 방법에 대해 알아보자.

유저 생성하기

Oracle

• Oracle의 DBA 권한을 가지고 있는 SYSTEM 유저로 접속하면 유저 생성 권한(CREATE USER)을 다른 유저에게 부여할 수 있다.
• SCOTT 유저에게 유저생성 권한(CREATE USER)을 부여한 후 PJS 유저를 생성해보자.

GRANT CREATE USER TO SCOTT; 
CONN SCOTT/TIGER
CREATE USER PJS IDENTIFIED BY KOREA7;

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 SESSION 권한을 부여한다.
• Oracle

CONN SCOTT/TIGER
GRANT CREATE SESSION TO PJS;
CONN PJS/KOREA7

• PJS 유저에게 테이블 생성 권한을 부여한다.
• 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 

// 테이블 생성
CREATE TABLE MENU (
MENU_SEQ INT NOT NULL, TITLE VARCHAR(10) );

OBJECT에 대한 권한 부여

• 오브젝트 권한은 특정 오브젝트인 테이블, 뷰 등에 대한 SELECT, INSERT, DELETE, UPDATE 작업 명령어를 의미한다.
• 모든 유저는 각각 자신이 생성한 테이블 외에 다른 유저의 테이블에 접근하려면 해당 테이블에 대한 오브젝트 권한을 소유자로부터 부여받아야 한다.
• 아래 표는 오브젝트 권한과 오브젝트와의 관계를 보여준다.
  

Role을 이용한 권한 부여

• 유저를 생성하면 기본적으로 CREATE SESSION, CREATE TABLE, CREATE PROCEDURE 등 많은 권한을 부여해야 한다.
  • 관리해야 할 유저가 점점 늘어나고 자주 변경되는 상황에서는 매우 번거로운 작업이 될 것이다.
  • 많은 데이터베이스에서 유저들과 권한들 사이에서 중개 역할을 하는 ROLE을 제공한다.
• 데이터베이스 관리자는 ROLE을 생성하고, ROLE에 각종 권한들을 부여한 후 ROLE을 다른 ROLE이나 유저에게 부여할 수 있다.
  • ROLE에 포함되어 있는 권한들이 필요한 유저에게는 해당 ROLE만을 부여함으로써 빠르고 정확하게 필요한 권한을 부여할 수 있게 된다.
• ROLE에는 시스템 권한과 오브젝트 권한을 모두 부여할 수 있으며, ROLE은 유저에게 직접 부여될 수도 있고, 다른 ROLE에 포함하여 유저에게 부여될 수도 있다.

예제

• JISUNG 유저에게 CREATE SESSION과 CREATE TABLE 권한을 가진 ROLE을 생성한 후 ROLE을 이용하여 다시 권한을 할당한다.
• Oracle

CONN SYSTEM/MANAGER
// 롤 생성 및 롤에 권한 부여
CREATE ROLE LOGIN_TABLE;
GRANT CREATE SESSION, CREATE TABLE TO LOGIN_TABLE;

// JISUNG 유저에게 롤 부여
GRANT LOGIN_TABLE TO JISUNG;

// 로그인하고 테이블 생성해보기
CONN JISUNG/KOREA7
CREATE TABLE MENU ( 
  MENU_SEQ INT NOT NULL, 
  TITLE VARCHAR(10) );

• 권한을 취소할 때는 REVOKE를 사용 한다.

REVOKE CREATE SESSION, CREATE TABLE FROM JISUNG;

Oracle에서 제공하는 기본 Role

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

유저 삭제하기

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

예제

• 앞에서 MENU라는 테이블을 생성했기 때문에 CASCADE 옵션을 사용하여 JISUNG 유저를 삭제해보자.

CONN SYSTEM/MANAGER
DROP USER JISUNG CASCADE;
// 사용자와 함께 사용자가 만든 MENU 테이블도 함께 삭제된다.

SQL Server에서 역할명

• SQL Server에서는 위와 같이 ROLE을 생성하여 사용하기보다는 기본적으로 제공되는 ROLE에 멤버로 참여하는 방식으로 사용한다.
• 특정 로그인이 멤버로 참여할 수 있는 서버 수준 역할(ROLE)은 아래와 같다.
  
• 데이터베이스에 존재하는 유저에 대해서는 아래와 같은 데이터베이스 역할의 멤버로 참여할 수 있다.