SQLD - SQL 활용 [DCL]

1.   DCL 개요

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

2.   유저와 권한

DBMS	사용자에 대한 차이점
Oracle	유저를통해 데이터베이스에 접속하는 형태이다. 즉, 아이디와 비밀번호 방식으로 인스턴스에 접속을 하고 그에 해당하는 스키마에 오브젝트 생성 등의 권한을 부여받게 된다.
SQL Server	인스턴스에 접속하기 위해 로그인이라는 것을 생성하게 되며, 인스턴스 내에 조재하는 다수의 데이터베이스에 연결하여 작업하기 위해 유저를 생성한후 로그인과 유저를 매핑해 주어야 한다. 더 나아가 특정 유저는 특정 데이터베이스 내의 특정 스키마에 대해 권한을 부여 받을 수 있다.

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

n  유저를 생성하고 데이터베이스에 접속한다. 하지만 데이터베이스에 접속했다고 해서 테이블, 뷰, 인덱스 등과 같은 오브젝트(OBJECT)를 생성할 수는 없다. 사용자가 실행하는 모든 DDL 문장(CREATE, ALTER, DROP, RENAME 등)은 그에 해당하는 적절한 권한이 있어야만 문장을 실행할 수 있다.

나.  OBJECT에 대한 권한 부여

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

객체 권한	테이블	VIEWS	SEQUENCE	PROCEDURE
ALTER	O		O
DELETE	O	O	(SQL Server 만 O)
EXECUTE				O
INDEX	O
INSERT	O	O
REFERENCES	O
SELECT	O	O	O
UPDATE	O	O

 

3.   Role을 이용한 권한 부여

-       유저를 생성하면 기본적으로 CREATE SESSION, CREATE TABLE, CREATE PROCEDURE 등 많은 구너한을 부여해야 한다.

-       데이터베이스 관리자는 유저가 생성될 때마다 각각의 권한들을 유저에게 부여하는 작업을 수행해야 하며 간혹 권한을 빠뜨릴 수도 있으므로 각 유저별로 어떤 권한이 부여되었는지를 관리해야 한다.

-       데이터베이스 관리자는 ROLE을 생성하고, ROLE에 각종 권한들을 부여한 후 ROLE을 다른 ROLE이나 유저에게 부여할 수 있다.

-       ROLE에 포함되어 있는 권한들이 필요한 유저에게는 해당 ROLE만을 부여함으로써 빠르고 정확하게 필요한 권한을 부여할 수 있게 된다.

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

CONNECT ROLE과 RESOURCE ROLE에 포함된 권한 목록(Oracle 사례)
CONNECT	RESOURCE
ALTER SESSION	CREATE CLUSTER
CREATE CLUSTER	CREATE INDEXTYPE
CREATE DATABASE LINK	CREATE OPERATOR
CREATE MENU_SEQUECE	CREATE PROCEDURE
CREATE SESSION	CREATE MENU_SEQUENCE
CREATE SYNONYM	CREATE TABLE
CREATE TABLE	CREATE TRIGGER
CREATE VIEW	CREATE

 

-       SQL Server에서는 위와 같은 ROLE을 생성하여 사용하기 보다는 기본적으로 제공되는 ROLE에 멤버로 참여하는 방식으로 사용한다.

서버 수준 역할명	설명
public	모든 SQL Server 로그인은 PUBLIC 서버 역할에 속한다. 서버 보안 주체에게 보안 객체에 대한 특정 사용 권한이 부여되지 않았거나 거부된 경우 사용자는 해당 개체에 대해 PUBLIC으로 부여된 사용 권한을 상속 받는다. 모든 사용자가 개체를 사용할 수 있도록 하려는 경우에만 개체에 PUBLIC권한을 할당해야 한다.
bulkadmin	BULK INSERT 문을 수행할 수 있다.
dbcreator	데이터베이스를 생성, 변경, 삭제 및 복원할 수 있다.
diskadmin	디스크 파일을 관리하는데 사용된다.
processadmin	SQL Server의 인스턴스에서 실행중인 프로세스를 종료할 수 있다.
securityadmin	로그인 및 해당 속성을 관리한다. 서버 및 데이터베이스 수준의 사용 권한을 부여(GRANT), 거부(DENY), 취소(REVOKE)할 수 있다. 또한, 로그인의 암호를 다시 설정할 수 있다.
serveramin	서버 차원의 구성 옵션을 변경하고 서버를 종료할 수 있다.
setupadmin	연결된 서버를 추가하거나 제거할 수 있다.
sysadmin	서버에서 모든 작업을 수행할 수 있다. 기본적으로 Window BUILTIN\Administrators 그룹의 멤버인 로컬 관리자 그룹은 sysadmin 고정 서버 역할의 멤버이다.

 

-       데이터베이스에 존재하는 유저에 대해서는 아래와 같은 데이터베이스 역할의 멤버로 참여할 수 있다.

데이터베이스 수준 역할명	설명
db_accessadmin	Windows 로그인, Windows 그룹 및 SQL Server 로그인의 데이터베이스에 대한 액세스를 추가하거나 제거할 수 있다.
db_backupoperator	데이터베이스를 백업할 수 있다.
db_datareader	모든 사용자 테이블의 모든 데이터를 읽을 수 있다.
db_datawriter	모든 사용자 테이블에서 데이터를 추가, 삭제, 변경할 수 있다.
db_ddlamin	데이터베이스에서 모든 DDL 명령을 수행할 수 있다.
db_denydatareader	데이터베이스 내에 있는 사용자 테이블의 데이터를 읽을 수 있다.
db_denydatawriter	데이터베이스 내의 모든 사용자 테이블에 있는 데이터를 추가, 삭제, 변경할 수 있다.
db_owner	데이터베이스 내에 있는 모든 구성 및 유지관리 작업을 수행할 수 있고 데이터베이스를 삭제할 수 있다.
db_securityadmin	역할 멤버 자격을 수정하고 사용 권한 관리를 할 수 있다. 이 역할에 보안 주체를 추가하면 원하지 않는 권한 상승이 설정될 수 있다.

 

-       SQL Server에서는 Oracle과 같이 ROLE을 자주 사용하지 않는다. 대신 위에서 언급한 서버 수준 역할 및 데이터베이스 수준 역할을 이용하여 로그인 및 사용자 권한을 제어한다.

-       인스턴스 수준의 작업이 필요한 경우 서버 수준 역할을 부여하고 그보다 작은 개념인 데이터베이스 수준의 권한이 필요한 경우 데이터베이스 수준의 역할을 부여하면 된다.

-       즉, 인스턴스 수준을 요구하는 로그인에는 서버 수준 역할을, 데이터베이스 수준을 요구하는 사용자에게는 데이터베이스 수준 역할을 부여한다.