MSSQL 로그인 및 보안 관리 가이드

보안 아키텍처 개요

MSSQL의 보안 시스템은 계층적 구조로 이루어져 있으며, 크게 서버 수준과 데이터베이스 수준으로 구분됩니다.

보안 계층 구조

MSSQL은 3단계 보안 구조(Login → User → Schema)를 통해 세밀한 접근 제어를 제공합니다. 이는 Oracle의 1단계 구조(User = Schema = Login)와는 다른 방식입니다.

서버 수준 (Server Level)

로그인(Login): SQL Server 인스턴스에 접속할 수 있는 자격 증명
서버 역할(Server Role): 서버 전체에 대한 권한 그룹

데이터베이스 수준 (Database Level)

사용자(User): 특정 데이터베이스에 접근할 수 있는 계정
데이터베이스 역할(Database Role): 데이터베이스 내 권한 그룹
스키마(Schema): 데이터베이스 객체를 그룹화하는 네임스페이스

객체 수준 (Object Level)

테이블, 뷰, 프로시저 등 개별 객체에 대한 권한

완전한 객체 참조 형식은 [Server].[Database].[Schema].[Object]입니다.

인증 모드

MSSQL은 두 가지 인증 방식을 제공합니다.

Windows 인증 모드

Windows 운영체제의 계정 정보를 그대로 사용하는 방식입니다. SQL Server는 암호를 요청하지 않고 Windows에서 이미 인증된 사용자 ID만 확인합니다.

장점

Kerberos 또는 NTLM 보안 프로토콜 사용으로 높은 보안성
Windows의 암호 정책(복잡성, 만료, 계정 잠금)을 자동 적용
도메인 수준에서 Windows 그룹을 생성하여 권한 관리 간소화
별도의 암호 입력 불필요 (Single Sign-On)
감사 로그 생성이 용이

단점

Windows 계정이 필요하므로 관리 포인트가 증가
웹 기반 애플리케이션에서는 구현이 복잡할 수 있음

연결 문자열 예시

Data Source=[서버주소];Initial Catalog=[DB명];Integrated Security=SSPI;

SQL Server 인증 모드 (혼합 모드)

SQL Server 자체에서 계정과 암호를 관리하는 방식입니다. 연결할 때마다 자격 증명(로그인, 암호)을 제공해야 합니다.

장점

Windows 계정 없이도 접근 가능
알 수 없거나 신뢰할 수 없는 도메인에서 연결 가능
웹 기반 애플리케이션에서 구현이 용이
개발 단계에서 편리함

단점

Windows 인증보다 보안성이 낮음
암호가 네트워크를 통해 전달됨
Kerberos 프로토콜 사용 불가
별도의 암호 관리 필요

연결 문자열 예시

Data Source=[서버주소];Initial Catalog=[DB명];User ID=[아이디];Password=[비밀번호];

인증 모드 변경

SSMS를 통한 변경

SQL Server 인스턴스에 접속
서버 이름을 마우스 우클릭 → 속성
보안 페이지에서 "SQL Server 및 Windows 인증 모드" 선택
확인 후 SQL Server 서비스 재시작

T-SQL을 통한 변경

USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', 
    N'Software\Microsoft\MSSQLServer\MSSQLServer',
    N'LoginMode', REG_DWORD, 2
GO
-- 1 = Windows 인증, 2 = 혼합 모드
-- 변경 후 SQL Server 재시작 필요

로그인(Login) 관리

로그인은 SQL Server 인스턴스에 접속할 수 있는 서버 수준의 보안 주체입니다. 로그인만으로는 데이터베이스에 접근할 수 없으며, 데이터베이스 사용자와 매핑되어야 합니다.

로그인 생성

SQL Server 인증 방식 로그인 생성

-- 기본 로그인 생성
CREATE LOGIN [login_name]
WITH PASSWORD = N'password';
GO

-- 상세 옵션을 포함한 로그인 생성
CREATE LOGIN [dev_user]
WITH PASSWORD = N'StrongP@ssw0rd!',
     DEFAULT_DATABASE = [TestDB],
     DEFAULT_LANGUAGE = korean,
     CHECK_EXPIRATION = OFF,    -- 암호 만료 정책 비활성화
     CHECK_POLICY = ON;          -- 암호 정책 활성화
GO

Windows 인증 방식 로그인 생성

-- Windows 사용자 계정
CREATE LOGIN [DOMAIN\UserName]
FROM WINDOWS
WITH DEFAULT_DATABASE = [TestDB];
GO

-- Windows 그룹
CREATE LOGIN [DOMAIN\GroupName]
FROM WINDOWS;
GO

로그인 수정

암호 변경

-- 현재 암호를 알고 있는 경우
ALTER LOGIN [login_name]
WITH PASSWORD = 'NewPassword'
     OLD_PASSWORD = 'OldPassword';
GO

-- 관리자 권한으로 암호 변경 (OLD_PASSWORD 불필요)
ALTER LOGIN [login_name]
WITH PASSWORD = 'NewPassword';
GO

-- 다음 로그인 시 암호 변경 강제
ALTER LOGIN [login_name]
WITH PASSWORD = 'NewPassword'
MUST_CHANGE;
GO

기본 데이터베이스 변경

ALTER LOGIN [login_name]
WITH DEFAULT_DATABASE = [NewDatabase];
GO

로그인 활성화/비활성화

-- 로그인 비활성화
ALTER LOGIN [login_name] DISABLE;
GO

-- 로그인 활성화
ALTER LOGIN [login_name] ENABLE;
GO

로그인 이름 변경

ALTER LOGIN [old_name]
WITH NAME = [new_name];
GO

로그인 삭제

-- 로그인 삭제
DROP LOGIN [login_name];
GO

-- 로그인 삭제 시 주의사항:
-- 1. 해당 로그인이 현재 연결 중이면 삭제 불가
-- 2. 데이터베이스 사용자가 매핑되어 있어도 삭제 가능
-- 3. 삭제 후 해당 로그인의 사용자는 고아 사용자(Orphaned User)가 됨

로그인 조회

-- 모든 로그인 조회
SELECT 
    name AS LoginName,
    type_desc AS LoginType,
    is_disabled AS IsDisabled,
    create_date AS CreateDate,
    modify_date AS ModifyDate,
    default_database_name AS DefaultDatabase
FROM sys.server_principals
WHERE type IN ('S', 'U', 'G')  -- S: SQL Login, U: Windows User, G: Windows Group
ORDER BY name;
GO

-- 특정 로그인의 서버 역할 확인
EXEC sp_helpsrvrolemember 'login_name';
GO

-- 로그인 상태 확인
SELECT 
    name,
    is_disabled,
    is_policy_checked,
    is_expiration_checked
FROM sys.sql_logins
WHERE name = 'login_name';
GO

사용자(User) 관리

사용자는 데이터베이스 수준의 보안 주체로, 로그인이 특정 데이터베이스에 접근하기 위해서는 해당 데이터베이스에 사용자로 매핑되어야 합니다.

사용자 생성

로그인과 매핑된 사용자 생성

USE [DatabaseName];
GO

-- 기본 사용자 생성
CREATE USER [user_name]
FOR LOGIN [login_name];
GO

-- 기본 스키마를 지정한 사용자 생성
CREATE USER [user_name]
FOR LOGIN [login_name]
WITH DEFAULT_SCHEMA = [schema_name];
GO

포함된 데이터베이스 사용자 (Contained Database User)

-- 로그인 없이 데이터베이스 사용자 직접 생성
USE [DatabaseName];
GO

CREATE USER [contained_user]
WITH PASSWORD = 'StrongP@ssw0rd!';
GO

사용자 수정

기본 스키마 변경

USE [DatabaseName];
GO

ALTER USER [user_name]
WITH DEFAULT_SCHEMA = [new_schema];
GO

사용자 이름 변경

USE [DatabaseName];
GO

ALTER USER [old_name]
WITH NAME = [new_name];
GO

사용자 삭제

USE [DatabaseName];
GO

DROP USER [user_name];
GO

-- 사용자 삭제 시 주의사항:
-- 1. 사용자가 스키마를 소유하고 있으면 삭제 불가
-- 2. 사용자가 객체를 소유하고 있으면 먼저 소유권 이전 필요

사용자 조회

-- 현재 데이터베이스의 모든 사용자 조회
USE [DatabaseName];
GO

SELECT 
    name AS UserName,
    type_desc AS UserType,
    authentication_type_desc AS AuthType,
    default_schema_name AS DefaultSchema,
    create_date AS CreateDate
FROM sys.database_principals
WHERE type IN ('S', 'U', 'G')
ORDER BY name;
GO

-- 사용자와 로그인 매핑 정보 조회
SELECT 
    dp.name AS UserName,
    sp.name AS LoginName,
    dp.default_schema_name AS DefaultSchema
FROM sys.database_principals dp
LEFT JOIN sys.server_principals sp ON dp.sid = sp.sid
WHERE dp.type IN ('S', 'U', 'G')
ORDER BY dp.name;
GO

역할(Role) 관리

역할은 권한의 집합으로, 사용자나 로그인을 그룹화하여 권한 관리를 효율적으로 할 수 있습니다.

고정 서버 역할

고정 서버 역할은 SQL Server가 제공하는 서버 수준의 역할로, 수정이나 삭제가 불가능합니다.

역할	설명
sysadmin	서버에서 모든 작업 수행 가능. 모든 권한 보유
serveradmin	서버 구성 옵션 변경 및 서버 종료 가능
securityadmin	로그인 및 속성 관리, 서버/데이터베이스 수준 권한 부여 가능
processadmin	SQL Server 인스턴스에서 실행 중인 프로세스 종료 가능
setupadmin	연결된 서버 추가/제거 가능
bulkadmin	BULK INSERT 문 실행 가능
diskadmin	디스크 파일 관리 가능
dbcreator	데이터베이스 생성, 변경, 삭제, 복원 가능
public	모든 로그인이 기본적으로 속하는 역할. 최소한의 권한

서버 역할에 로그인 추가/제거

-- 서버 역할에 로그인 추가
ALTER SERVER ROLE [sysadmin]
ADD MEMBER [login_name];
GO

-- 또는 저장 프로시저 사용
EXEC sp_addsrvrolemember 'login_name', 'sysadmin';
GO

-- 서버 역할에서 로그인 제거
ALTER SERVER ROLE [sysadmin]
DROP MEMBER [login_name];
GO

-- 또는 저장 프로시저 사용
EXEC sp_dropsrvrolemember 'login_name', 'sysadmin';
GO

-- 서버 역할 멤버 조회
EXEC sp_helpsrvrolemember;
GO

-- 특정 역할의 멤버 조회
EXEC sp_helpsrvrolemember 'sysadmin';
GO

고정 데이터베이스 역할

고정 데이터베이스 역할은 각 데이터베이스에 미리 정의된 역할입니다.

역할	설명
db_owner	데이터베이스의 모든 구성 및 유지 관리 작업 수행, 데이터베이스 삭제 가능
db_securityadmin	역할 멤버 자격 수정 및 권한 관리 가능
db_accessadmin	Windows 로그인, 그룹, SQL Server 로그인의 데이터베이스 액세스 추가/제거 가능
db_backupoperator	데이터베이스 백업 가능
db_ddladmin	모든 DDL(데이터 정의 언어) 명령 실행 가능
db_datawriter	모든 사용자 테이블에서 데이터 추가, 삭제, 변경 가능
db_datareader	모든 사용자 테이블의 모든 데이터 읽기 가능
db_denydatawriter	데이터베이스 내 사용자 테이블의 데이터 추가, 수정, 삭제 불가
db_denydatareader	데이터베이스 내 사용자 테이블의 데이터 읽기 불가

데이터베이스 역할에 사용자 추가/제거

USE [DatabaseName];
GO

-- 역할에 사용자 추가
ALTER ROLE [db_datareader]
ADD MEMBER [user_name];
GO

-- 또는 저장 프로시저 사용
EXEC sp_addrolemember 'db_datareader', 'user_name';
GO

-- 역할에서 사용자 제거
ALTER ROLE [db_datareader]
DROP MEMBER [user_name];
GO

-- 또는 저장 프로시저 사용
EXEC sp_droprolemember 'db_datareader', 'user_name';
GO

-- 데이터베이스 역할 멤버 조회
SELECT 
    r.name AS RoleName,
    m.name AS MemberName,
    m.type_desc AS MemberType
FROM sys.database_role_members rm
INNER JOIN sys.database_principals r ON rm.role_principal_id = r.principal_id
INNER JOIN sys.database_principals m ON rm.member_principal_id = m.principal_id
ORDER BY r.name, m.name;
GO

사용자 정의 역할

사용자가 필요에 따라 생성하는 역할입니다.

서버 수준 사용자 정의 역할

-- 서버 역할 생성
CREATE SERVER ROLE [CustomServerRole];
GO

-- 서버 역할에 권한 부여
GRANT VIEW SERVER STATE TO [CustomServerRole];
GO

-- 서버 역할에 로그인 추가
ALTER SERVER ROLE [CustomServerRole]
ADD MEMBER [login_name];
GO

-- 서버 역할 삭제
DROP SERVER ROLE [CustomServerRole];
GO

데이터베이스 수준 사용자 정의 역할

USE [DatabaseName];
GO

-- 데이터베이스 역할 생성
CREATE ROLE [CustomDatabaseRole];
GO

-- 역할에 권한 부여
GRANT SELECT, INSERT ON SCHEMA::[dbo] TO [CustomDatabaseRole];
GO

-- 역할에 사용자 추가
ALTER ROLE [CustomDatabaseRole]
ADD MEMBER [user_name];
GO

-- 역할 삭제
DROP ROLE [CustomDatabaseRole];
GO

스키마(Schema) 관리

스키마는 데이터베이스 객체(테이블, 뷰, 프로시저 등)를 그룹화하는 네임스페이스입니다. SQL Server 2005부터 스키마와 사용자가 분리되어 더욱 유연한 권한 관리가 가능해졌습니다.

스키마의 역할

객체 그룹화: 논리적으로 관련된 객체를 함께 관리
권한 관리 효율화: 스키마에 권한을 부여하면 스키마 내 모든 객체에 자동 적용
소유권 분리: 스키마 소유자와 객체 소유자를 분리하여 관리 용이
네이밍 충돌 방지: 서로 다른 스키마에 같은 이름의 객체 존재 가능

스키마 생성

USE [DatabaseName];
GO

-- 기본 스키마 생성
CREATE SCHEMA [Sales];
GO

-- 소유자를 지정한 스키마 생성
CREATE SCHEMA [HR]
AUTHORIZATION [user_name];
GO

-- 스키마 생성과 동시에 테이블 생성
CREATE SCHEMA [Finance]
CREATE TABLE Budget (
    BudgetID INT PRIMARY KEY,
    Amount DECIMAL(18,2)
);
GO

스키마 수정

-- 스키마 소유자 변경
ALTER AUTHORIZATION ON SCHEMA::[Sales]
TO [new_owner];
GO

-- 객체를 다른 스키마로 이동
ALTER SCHEMA [NewSchema]
TRANSFER [OldSchema].[TableName];
GO

스키마 삭제

-- 스키마 삭제 (스키마 내 객체가 없어야 함)
DROP SCHEMA [SchemaName];
GO

특별한 스키마

dbo 스키마

모든 데이터베이스에 기본으로 존재
기본 스키마가 지정되지 않은 사용자의 기본 스키마
sysadmin 역할 멤버는 항상 dbo 스키마에 매핑되며 변경 불가
가장 일반적으로 사용되는 스키마

sys 스키마

시스템 객체(시스템 뷰, 시스템 함수 등)를 포함
수정 불가

INFORMATION_SCHEMA 스키마

ANSI SQL 표준에 정의된 메타데이터 뷰 포함
수정 불가

스키마 조회

-- 모든 스키마 조회
SELECT 
    s.name AS SchemaName,
    u.name AS SchemaOwner,
    s.schema_id AS SchemaID
FROM sys.schemas s
INNER JOIN sys.database_principals u ON s.principal_id = u.principal_id
ORDER BY s.name;
GO

-- 특정 스키마의 객체 조회
SELECT 
    SCHEMA_NAME(schema_id) AS SchemaName,
    name AS ObjectName,
    type_desc AS ObjectType
FROM sys.objects
WHERE SCHEMA_NAME(schema_id) = 'dbo'
ORDER BY type_desc, name;
GO

권한(Permission) 관리

MSSQL의 권한 시스템은 GRANT(부여), DENY(거부), REVOKE(회수)의 세 가지 명령으로 구성됩니다.

권한 명령어의 차이

GRANT: 특정 권한을 명시적으로 허용
DENY: 특정 권한을 명시적으로 거부 (GRANT보다 우선순위 높음)
REVOKE: 부여되거나 거부된 권한을 제거하여 중립 상태로 변경

DENY와 REVOKE의 차이

-- 시나리오 1: GRANT 후 REVOKE
GRANT SELECT ON SCHEMA::[Sales] TO [user_name];
REVOKE SELECT ON SCHEMA::[Sales] FROM [user_name];
-- 결과: 권한 없음 (중립 상태)

-- 시나리오 2: GRANT 후 DENY
GRANT SELECT ON SCHEMA::[Sales] TO [user_name];
DENY SELECT ON dbo.Orders TO [user_name];
-- 결과: 스키마 전체는 조회 가능하나 특정 테이블은 차단
-- DENY가 우선순위가 높아 상위 GRANT를 무시함

서버 수준 권한

-- 서버 수준 권한 부여
GRANT VIEW SERVER STATE TO [login_name];
GO

GRANT ALTER ANY LOGIN TO [login_name];
GO

-- 서버 수준 권한 거부
DENY SHUTDOWN TO [login_name];
GO

-- 서버 수준 권한 회수
REVOKE VIEW SERVER STATE FROM [login_name];
GO

주요 서버 수준 권한

VIEW SERVER STATE: 서버 상태 정보 조회
ALTER ANY LOGIN: 로그인 생성, 수정, 삭제
ALTER ANY DATABASE: 데이터베이스 생성, 수정, 삭제
VIEW ANY DATABASE: 모든 데이터베이스 목록 조회
SHUTDOWN: 서버 종료

데이터베이스 수준 권한

USE [DatabaseName];
GO

-- 데이터베이스 수준 권한 부여
GRANT CREATE TABLE TO [user_name];
GO

GRANT ALTER ANY SCHEMA TO [user_name];
GO

-- 데이터베이스 수준 권한 거부
DENY CREATE PROCEDURE TO [user_name];
GO

-- 데이터베이스 수준 권한 회수
REVOKE CREATE TABLE FROM [user_name];
GO

주요 데이터베이스 수준 권한

CREATE TABLE: 테이블 생성
CREATE VIEW: 뷰 생성
CREATE PROCEDURE: 저장 프로시저 생성
ALTER ANY SCHEMA: 스키마 생성, 수정, 삭제
BACKUP DATABASE: 데이터베이스 백업

스키마 수준 권한

USE [DatabaseName];
GO

-- 스키마 전체에 대한 권한 부여
GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::[Sales] TO [user_name];
GO

-- 스키마 권한 거부
DENY DELETE ON SCHEMA::[Sales] TO [user_name];
GO

-- 스키마 권한 회수
REVOKE SELECT ON SCHEMA::[Sales] FROM [user_name];
GO

객체 수준 권한

테이블 권한

USE [DatabaseName];
GO

-- 테이블 전체에 대한 권한
GRANT SELECT, INSERT, UPDATE, DELETE ON [dbo].[TableName] TO [user_name];
GO

-- 특정 컬럼에 대한 권한
GRANT SELECT ON [dbo].[Employees] ([EmployeeID], [FirstName], [LastName]) TO [user_name];
GO

GRANT UPDATE ON [dbo].[Employees] ([Salary]) TO [manager_user];
GO

-- 테이블 권한 거부
DENY DELETE ON [dbo].[CriticalTable] TO [user_name];
GO

뷰 권한

-- 뷰에 대한 권한 부여
GRANT SELECT ON [dbo].[ViewName] TO [user_name];
GO

저장 프로시저 권한

-- 프로시저 실행 권한 부여
GRANT EXECUTE ON [dbo].[ProcedureName] TO [user_name];
GO

-- 스키마 내 모든 프로시저 실행 권한
GRANT EXECUTE ON SCHEMA::[dbo] TO [user_name];
GO

함수 권한

-- 함수 실행 권한 부여
GRANT EXECUTE ON [dbo].[FunctionName] TO [user_name];
GO

WITH GRANT OPTION

권한을 받은 사용자가 다른 사용자에게 해당 권한을 부여할 수 있도록 합니다.

-- 권한 부여 권한 포함
GRANT SELECT ON [dbo].[TableName] TO [user_name]
WITH GRANT OPTION;
GO

-- CASCADE로 연쇄 권한 회수
REVOKE SELECT ON [dbo].[TableName] FROM [user_name]
CASCADE;
GO

권한 조회

-- 서버 수준 권한 조회
SELECT 
    pr.name AS PrincipalName,
    pr.type_desc AS PrincipalType,
    pe.permission_name AS PermissionName,
    pe.state_desc AS PermissionState
FROM sys.server_principals pr
INNER JOIN sys.server_permissions pe ON pr.principal_id = pe.grantee_principal_id
WHERE pr.name = 'login_name'
ORDER BY pe.permission_name;
GO

-- 데이터베이스 수준 권한 조회
USE [DatabaseName];
GO

SELECT 
    pr.name AS PrincipalName,
    pr.type_desc AS PrincipalType,
    pe.permission_name AS PermissionName,
    pe.state_desc AS PermissionState,
    OBJECT_NAME(pe.major_id) AS ObjectName
FROM sys.database_principals pr
INNER JOIN sys.database_permissions pe ON pr.principal_id = pe.grantee_principal_id
WHERE pr.name = 'user_name'
ORDER BY pe.permission_name;
GO

-- 특정 테이블에 대한 권한 조회
SELECT 
    pr.name AS UserName,
    pe.permission_name AS Permission,
    pe.state_desc AS State
FROM sys.database_permissions pe
INNER JOIN sys.database_principals pr ON pe.grantee_principal_id = pr.principal_id
INNER JOIN sys.objects o ON pe.major_id = o.object_id
WHERE o.name = 'TableName'
ORDER BY pr.name, pe.permission_name;
GO

-- 현재 사용자의 권한 확인
SELECT * FROM fn_my_permissions(NULL, 'SERVER');  -- 서버 수준
SELECT * FROM fn_my_permissions(NULL, 'DATABASE');  -- 데이터베이스 수준
SELECT * FROM fn_my_permissions('dbo.TableName', 'OBJECT');  -- 객체 수준
GO

특별 계정 관리

sa 계정

sa(System Administrator)는 SQL Server의 최고 관리자 계정으로 sysadmin 고정 서버 역할의 멤버입니다.

보안 고려사항

sa 계정은 잘 알려진 계정으로 공격 대상이 되기 쉬움
Windows 인증 모드 설치 시 자동으로 비활성화됨
가능한 sa 계정 사용을 피하고 Windows 인증 사용 권장
혼합 모드에서는 반드시 강력한 암호 설정 필요

-- sa 계정 활성화
ALTER LOGIN [sa] ENABLE;
GO

-- sa 계정 비밀번호 변경
ALTER LOGIN [sa]
WITH PASSWORD = 'VeryStrongP@ssw0rd!2024';
GO

-- sa 계정 비활성화 (보안 강화)
ALTER LOGIN [sa] DISABLE;
GO

-- sa 계정 상태 확인
SELECT 
    name,
    is_disabled,
    create_date,
    modify_date
FROM sys.sql_logins
WHERE name = 'sa';
GO

dbo 사용자

dbo(Database Owner)는 모든 데이터베이스에 자동으로 존재하는 특수 사용자입니다.

특징

sa 로그인과 sysadmin 역할 멤버가 데이터베이스에 접근할 때 자동으로 dbo에 매핑
데이터베이스의 모든 권한 보유
삭제하거나 수정할 수 없음
기본 스키마가 지정되지 않은 객체는 dbo 스키마에 생성됨

guest 사용자

guest는 로그인이 특정 데이터베이스에 사용자로 매핑되지 않았을 때 사용되는 계정입니다.