[MSSQL] 사용자 계정(SQL Server Authentication) 추가 및 권한 부여 / Server Roles, Database Role Membership

본 포스팅에서는 SQL Server 에서 [사용자 계정] 을 추가하는 구체적인 방법과 추가할 때 설정해야 하는 여러 옵션들에 대해 알아보고자 한다!

---

1. 사용자 계정 추가하기

사용자 계정 추가 권한이 'sa' 계정밖에 없다고 가정한다!

 

① 'sa' 계정으로 데이터베이스에 로그인하기

② [Object Explorer] - [Security] - [Logins] - [New Login...] 클릭

새로운 사용자 계정 생성하는 창 띄우기

---

2. [General] 탭 설정 확인

[New Login] 창 - [General] 탭

① [Login name] 을 우선 지정해주어야 한다. 이 값은 사용자 계정 이름이자, 로그인할 때 사용하는 ID에 해당된다.

② 본 포스팅에서는 AD(Active Directory) 계정에 권한을 주는 방식이 아닌, SQL Server Authentication 방식으로 사용자 계정을 추가하는 방법을 설명하므로, [SQL Server Authentication] 항목을 체크하고, [Password] 를 입력해준다. 
당연하게도, 로그인할 때 사용하는 PW에 해당한다.

③ 'User must change password at next login' 항목의 체크를 해제한다! 이 항목은 다음에 로그인할 때 비밀번호를 바꿔야하는 귀찮음을 덜기 위해서다.

---

3. [Server Roles] 탭 설정 확인

[New Login] 창 - [Server Roles] 탭

 여기서 말하는 'Server Role' 이란, 서버를 관리하는 권한의 집합이라고 보면 된다. 유저가 이 MSSQL 서버에 대해서 어떠한 권한을 가질 수 있는 지에 대해서 정의가 되어 있는 '집합'을 말한다. 이는 SQL 서버 상에서 로그인 계정(사용자 계정)들을 그룹화하고 정해진 권한을 편리하게 부여하기 위해 사용되는 개념이다.

 아래 표는 MSSQL에서 제공하는 SQL Server 2019 및 이전 버전에서의 '고정 서버 역할(Fixed server-level role)'을 나타낸다. (MSSQL에서 기본으로 제공하는 Server Role을 말하는 것임. SQL Server 2022 버전에서는 10가지를 제공하며, SQL Server 2012 버전 이상부터는 사용자가 정의한 Server Role을 사용할 수 있음.)

 admin 수준의 권한이 필요하지 않은 일반 계정이라면 그냥 편하게 'public' 만 선택하자!

고정 서버 수준 역할 이름	설명
sysadmin	해당 SQL 서버에서 모든 작업을 수행할 수 있다. 서버 수준 사용 권한을 최종적으로 관리하는 역할이다.sa 계정 및 OS의 administrator 계정이 해당된다.
serveradmin	서버 차원의 configuration 설정 및 서버를 셧다운시킬 수 있다.
securityadmin	로그인 및 로그인 관련 속성을 관리할 수 있다. 서버 수준 사용 권한에 대하여 GRANT, DENY, REVOKE가 가능하다. 데이터베이스 수준 사용 권한 역시 GRANT, DENY, REVOKE 가 가능하다.  또한 SQL Server 의 로그인 암호를 다시 설정할 수 있다. 사실상 sysadmin 과 동일한 급으로 봐도 될 정도로 중요하게 관리되어야 한다.
processadmin	SQL Server 의 인스턴스에서 실행 중인 프로세스를 종료시킬 수 있다.
setupadmin	T-SQL 문을 사용하여 연결된 서버를 추가하고 제거할 수 있다. (SSMS를 이용할 때에는 sysadmin 멤버 자격이 필요하다.)
bulkadmin	BULK INSERT 문을 실행할 수 있다. 리눅스에 설치된 SQL Server에서는 지원되지 않는 Role이며, 리눅스에서는 sysadmin 만이 bulkadmin의 역할을 수행할 수 있다.
diskadmin	디스크 파일을 관리하는 데에 사용된다.
dbcreator	데이터베이스를 생성, 변경, 삭제, 복원할 수 있다.
public	모든 SQL Server 로그인 계정은 public 서버 역할에 속한다. 별다른 서버 수준 역할을 지정하지 않으면 기본으로 지정되는 서버 수준 역할이다.

---

4. [User Mapping] 탭 설정 확인

[New Login] 창 - [User Mapping] 탭

 [User Mapping] 탭에서는 생성하려는 로그인 계정이 어떤 DB까지 접근할 수 있게 할지, 그리고 해당 DB에 대해서 어떤 역할까지 가질 수 있게 할 지 지정한다.

 

① 'Users mapped to this login' : 지금 생성하는 계정이 어떤 데이터베이스까지 액세스할 수 있게 할 지 지정한다.

① 에 해당하는 GUI를 쿼리로 수행하려고 한다면, 다음과 같이 수행할 수 있다.

-- GUI에서 체크하는 DB name을 USE 해야 한다.
USE [DATABASE_NAME]
GO

CREATE USER [USER_NAME] FROM LOGIN [USER_NAME]
GO
ALTER USER [USER_NAME] WITH DEFAULT_SCHEMA=[dbo]
GO

 

② 'Database role membership' for ~ : ~에 해당하는 데이터베이스에 대해서 어떠한 Role까지 가질 수 있게 할 지 지정한다. ① 에서 선택한 데이터베이스에 따라 각각 모두 다르게 지정할 수 있다. 

② 에 해당하는 GUI를 쿼리로 수행하려고 한다면, 다음과 같이 수행할 수 있다.

USE [DATABASE_NAME]
GO

EXEC sp_addrolemember N'DATABASE_ROLE', N'USER_NAME'
GO

 

 아래 표는 SQL Server 에서 default로 제공하는 고정 데이터베이스 역할(Fixed-Database Role)을 나타낸다.

고정 데이터베이스 역할 이름	설명
db_owner	해당 데이터베이스의 모든 configuration 및 유지 관리 작업을 수행할 수 있고, SQL Server 상에서 데이터베이스를 DROP 할 수도 있다.
db_securityadmin	사용자 지정 고정 데이터베이스 역할에 대하여 수정 및 사용 권한을 관리할 수 있다. 이 Role은 잠재적으로 다른 계정에 대해 권한을 상승시킬 수 있는 권한이므로, 철저한 모니터링이 필요하다.
db_accessadmin	Winodws 로그인, Windows 그룹 및 SQL Server 로그인 상에서의 계정을 통한, 데이터베이스에 대한 엑세스 권한을 추가하거나 제거할 수 있는 권한이다.
db_backupoperator	해당 데이터베이스를 백업할 수 있는 Role 이다.
db_ddladmin	해당 데이터베이스 내에서 모든 DDL 명령을 실행할 수 있다. 데이터를 변경할 수 있는 권한을 가졌으므로, 철저한 모니터링이 필요한 Role 이다.
db_datawriter	모든 사용자 테이블에서 데이터를 추가, 삭제, 변경이 가능하다.
db_datareader	모든 사용자 테이블 및 뷰의 모든 데이터를 읽을 수 있다. 즉, sys 및 INFORMATION_SCHEMA를 제외한 모든 *스키마에서의 테이블 및 뷰를 읽을 수 있다는 말이다.
db_denydatawriter	모든 사용자 테이블에 있는 데이터를 추가, 수정, 삭제할 수 없는 Role 이다.
db_denydatareader	모든 사용자 테이블 및 뷰의 데이터를 읽을 수 없는 Role 이다.

데이터베이스 수준 역할 정리

 

* 스키마(Schema) : SQL Server 에서의 '스키마'란, 데이터베이스 개체에 대해서 어떠한 이름이 붙여진 컨테이너 개념인데, 이는 개체를 별도의 네임스페이스로 그룹화할 수 있는 역할을 한다. 예를 들자면, 'AdventureWorks' 라는 샘플 데이터베이스에는 Production, Sales, HumanResources 등에 대한 '스키마'가 들어 있다.

스키마는 다음과 같은 형식에 사용된다.

ex) ServerName.Database.DatabaseSchema.DatabaseObject

 

** dbo 스키마 : dbo 스키마는 모든 데이터베이스의 기본 스키마이다. CREATE USER T-SQL 명령을 사용하여 만들어진 사용자는 기본적으로 dbo를 기본 스키마로 가진다. dbo 스키마는 dbo 사용자 계정에서 소유하게 된다.

---

5. [Securables] 탭 설정 확인

[New Login] 창 - [Securables] 탭

 [Securables] 탭에서는 각 개체에 대해서 개별적으로 사용 권한(Explicit Permission)에 대하여 지정할 수 있다.
[Search...] 버튼을 클릭하여 여러 개체들을 불러올 수 있으며, 각 개체별로 사용 권한을 부여하거나(GRANT), 부여하면서 다른 개체에게도 권한을 부여할 수 있는 권한까지 부여하거나(WITH GRANT), 권한을 막을 수도(DENY) 있다.

 참고로 REVOKE의 경우, 부여한 권한 자체에 대하여 회수(권한을 부여받은 그룹 전체가 권한이 해제됨)하는 것이고, DENY의 경우, 부여한 권한들에서 특정 개체들을 예외로 지정할 때 사용한다고 생각하면 된다!

 아래 표는 사용 권한(Explicit Permission)에 대한 설명이다.

Explicit Permission 이름	설명
Alter	소유권(ownership)을 제외한 특정 Securable의 속성을 변경할 수 있는 권한을 부여한다. Scope에 부여하게 되면, 해당 Scope에 포함된 Securable을 변경, 생성, 삭제할 수 있는 기능도 부여한다.
Control	소유권(ownership)과 동일한 권한을 부여한다. 즉, 피부여자(grantee, 여기서는 새로 생성될 사용자 계정)는 Securable에 정의된 모든 사용 권한을 갖게 된다.
Impersonate	피부여자(grantee, 여기서는 새로 생성될 사용자 계정)가 로그인 또는 특정 사용자의 권한을 자기 권한처럼 사용할 수 있도록 한다.
View definition	피부여자(grantee, 여기서는 새로 생성될 사용자 계정)가 metadata에 접근할 수 있도록 한다.
REFERENCES	해당 테이블을 참조하는 FOREIGN KEY 제약 조건을 생성하려면, 테이블에 대한 REFERENCES 권한이 필요하다.  또한, 'WITH SCHEMABINDING' 과 함께 함수 또는 VIEW를 만드려면 REFERENCES 권한이 필요하다.

---

6. [Status] 탭 설정 확인

[New Login] 창 - [Status] 탭

데이터베이스 엔진에 로그인 가능한 사용자를 만들 목적이므로, 당연히 위 두 항목을 'Grant', 'Enabled'로 선택해준다.

---

참고

https://bebeya.tistory.com/entry/MSSQL-%EC%82%AC%EC%9A%A9%EC%9E%90-%EA%B3%84%EC%A0%95-%EC%B6%94%EA%B0%80-DB-%EA%B6%8C%ED%95%9C-%EB%B6%80%EC%97%AC

[MSSQL] 사용자 계정 추가 & DB 권한 부여

https://docs.microsoft.com/ko-kr/sql/relational-databases/security/authentication-access/server-level-roles?view=sql-server-ver16 

서버 수준 역할 - SQL Server

https://docs.microsoft.com/ko-kr/sql/relational-databases/security/authentication-access/database-level-roles?view=sql-server-ver16 

데이터베이스 수준 역할 - SQL Server