[DB] MSSQL 계정 생성 및 권한부여

안녕하세요. 바른 호랑이입니다.

이번 게시글에서는 대표적인 RDB중 하나인 MSSQL의 계정생성 방법과 권한부여 방법에 대해 알아볼 예정입니다.

MSSQL은 Oracle과 달리 Instance 하나에 여러개의 DB를 공유할 수 있고, 로그인 계정과 DB 접근 계정을 따로 관리한다는 특징을 가지고 있습니다. 이로 인해 MSSQL은 로그인 계정 생성 후 서버 권한을 부여하고 이후에 DB를 생성한 이후 각 DB에 대해 계정생성과 권한부여를 하는 과정이 필요합니다. 해당 부분을 알아보기 위해 로컬 서버를 구성 후 시나리오에 따라 진행해보았으며, 시나리오는 아래와 같습니다.

 

※ 시나리오

- Windows Authentication(또는 생성되어 있는 관리자 계정)으로 접속 후 USER_01, USER_02, USER_03 생성

ㆍUSER_01 : GLOBAL_USER / 모든 DB에 접근 가능하고 DB내의 객체는 수정가능하나 DB 생성 및 계정 수정은 불가능한 유저

ㆍUSER_02 : ADMIN / 모든 권한을 가진 관리자 

ㆍUSER_03 : LOCAL_USER / 특정 DB에만 접근 가능한 유저

- DB는 TESTDB01, TESTDB02로 총 2개를 생성

 

위의 시나리오를 만족시키기 위한 구체적인 DB 계정 생성 방법에 대한 세부내용은 아래와 같습니다.

1. LOCALDB또는 Server에 Windows Authentication 또는 초기 관리자 계정으로 접속합니다.(만약 로그인 방식이 Windows Authentication으로만 설정되어 있으면, login 계정을 생성해도 로그인이 안되니 먼저 설정을 확인 후 변경하고 재부팅 후 작업을 진행해야합니다.)

2. 접속 후  CODE 또는 UI 를 이용하여 Server에 접속가능한 계정 3개를 생성하고 유저 권한에 맞게 계정 역할을 설정합니다.

- USER_01, USER_03 : public(USER_03은 USER_02 계정으로 생성진행) / USER_02 : public, sysadmin

역 할	설 명
sysadmin	서버에서 모든 작업 수행이 가능한 역할
serveradmin	서버 전체 구성 옵션을 변경하고 서버를 종료할 수 있는 역할
securityadmin	로그인 및 해당 속성을 관리할 수 있는 역할. sysadmin과 같은 수준으로 관리해야 함.
processadmin	인스턴스에서 실행되는 프로세스를 관리할 수 있는 역할
setupadmin	T-SQL문을 사용하여 연결된 서버를 추가하고 제거할 수 있는 역할
bulkadmin	BULK INSERT문을 실행할 수 있는 역할.
diskadmin	디스크 파일을 관리할 수 있는 역할
dbcreator	DB 생성, 변경, 삭제, 복원할 수 있는 역할
public	공용 서버 역할로 가장 기본적인 역할

※ 참고 사이트 

Database-Level Roles - SQL Server

3. DB생성 후 각 DB에 등록해야하는 유저를 등록하고 적절한 권한을 가진 역할을 할당해준 후 테이블 생성, 삽입, 조회, 삭제 쿼리를 실행 여부를 테스트 합니다.

- USER_01은 TESTDB01, TESTDB02에 모두 등록 후 역할 할당(dbreader, dbwriter, ddladmin)

- USER_03은 TESTDB01에만 등록 후 역할 할당 (dbreader, dbwriter, ddladmin)

- USER_02의 경우 sysadmin으로 이미 모든 권한을 가지고 있어 따로 등록 불필요.

역 할	설 명
db_owner	DB에서 모든 구성 및 작업을 수행할 수 있는 역할
db_securitiyadmin	역할 멤버의 자격을 수정하고 권한을 관리할 수 있는 역할
db_accessadmin	Windows 로그인, Windows 그룹 및 SQL Server로그인에 대한  DB 액세스 추가 및 제거가 가능한 역할
db_backupoperator	DB를 백업할 수 있는 역할
db_ddladmin	모든 DDL(데이터 정의 언어)명령을 실행할 수 있는 역할
db_datawriter	모든 사용자 테이블에서 데이터 추가, 삭제, 변경이 가능한 역할
db_data_reader	모든 사용자 테이블에서 데이터 조회가 가능한 역할
db_denydatawriter	모든 사용자 테이블에서 데이터 추가, 삭제, 변경이 불가능한 역할
db_denydatareader	모든 사용자 테이블에서 데이터 조회가 불가능한 역할

※ 참고 사이트

Server-level roles - SQL Server

USE [db_name]; -- 해당 데이터베이스 이름으로 변경
GO

-- 특정 역할의 역할 그룹 멤버십 확인
SELECT r.name AS 'ROLENAME', rm.name AS 'ROLEGROUPNAME'
FROM sys.database_role_members drm
INNER JOIN sys.database_principals r ON drm.role_principal_id = r.principal_id
INNER JOIN sys.database_principals rm ON drm.member_principal_id = rm.principal_id
WHERE rm.name = 'db_ddladmin'; -- 확인하려는 역할 이름으로 변경
GO

USE [db_name]; -- 해당 데이터베이스 이름으로 변경
GO

-- 특정 역할에 할당된 역할 확인
SELECT r.name AS 'ROLENAME', rm.name AS 'ROLEGROUPNAME'
FROM sys.database_role_members drm
INNER JOIN sys.database_principals r ON drm.role_principal_id = r.principal_id
INNER JOIN sys.database_principals rm ON drm.member_principal_id = rm.principal_id
WHERE rm.name = 'db_ddladmin'; -- 확인하려는 역할 이름으로 변경
GO

이상으로 MSSQL 계정 생성 및 권한 설정 방법에 대해 알아보았습니다.

 

P.S 더 나은 개발자가 되기위해 공부중입니다. 잘못된 부분을 댓글로 남겨주시면 학습하는데 큰 도움이 될 거 같습니다.