서버 및 데이터베이스 수준 DML 권한 확인하기
이번에 처음으로 IT감사에 대응하면서 DBMS에서 직접 DML권한을 가지는 계정 목록을 제출해 달라는 요구를 받았다. 해당 요구사항에 맞게 쿼리를 작성하며 서버 수준의 게정과 데이터베이스 수준의 계정, 그리고 각각의 매핑 관계에 대해 자세히 알아보는 계기가 되었다. 자료조사를 하면서 DML 원한이 있는 유저를 조회하고 결과를 PIVOT 테이블 형태로 변환하는 쿼리를 작성하여 자료로 제출했으나 다시 보니 간과한 점이 다소 보인다. 권한 조회 쿼리를 작성하면서 접한 개념과 해당 쿼리를 작성할 때 고려한 것과 개선점을 정리하고자 한다. **SQL SERVER 19 버전을 기준으로 작성되었다.
사용자 권한의 계층구조
처음 요청자료를 접했을 때는 database_principals테이블에서 database_permissions이 'INSERT', 'UPDATE', 'DELETE'인 계정만 조회하면 된다고 생각했다. 그러나 이렇게 조회해보니 소수의 계정만 조회되었다.
SQL Server의 보안 구조는 계층적이며, 계정(Account)이 서버 수준과 데이터베이스 수준으로 나뉘고, 각 계층에서 역할(Role)과 권한(Permission)이 다르게 적용된다. 따라서 데이터베이스 수준의 database_permissions만으로는 DML권한을 가진 모든 계정을 조회할 수 없었다.
서버 수준의 보안주체과 데이터베이스 수준의 보안주체
| 구분 | 서버 수준 보안 주체 (Server-Level Principal) |
데이터베이스 수준 보안 주체 (Database-Level Principal) |
| 범위(Scope) | SQL Server 인스턴스 전체 | 특정 데이터베이스 내에서만 유효 |
| 역할(Role) | sysadmin, serveradmin 등 서버 수준 역할 | db_owner, db_datawriter, db_reader 등 데이터베이스 역할 |
| 권한 관리 (Permission Management) |
서버 연결 및 데이터베이스 접근 권한 제공 | 데이터베이스 개체(테이블, 뷰 등)에 대한 세부적인 권한 제공 |
| 생성 방법(Creation Method) | CREATE LOGIN 명령어를 통해 생성 | CREATE USER 명령어를 통해 데이터베이스 내에서 생성 |
| 서버와의 관계 (Server Relationship) |
서버 수준 보안 주체를 통해 데이터베이스에 접근 가능 | 서버 수준 보안 주체와 매핑되거나 독립적인 데이터베이스 사용자로 작동 가능 |
계정(Account)과 보안주체(Principal)의 관계
계정은 SQL Server에 접근하기 위한 인증 정보로 SQL Server 인증, Windows 인증으로 자격증명을 필요로 한다. 보안 주체(Principal)는 SQL Server에서 권한(Permission)을 부여받아 작업을 수행할 수 있는 모든 개체를 의미한다. 보안주체는 서버 수준의 보안주체와 데이터베이스 수준의 보안주체라는 계층으로 나눠진다. 보안 주체는 계정(Account)뿐만 아니라 역할(Role)이나 애플리케이션(Application)도 포함된다. 요약하자면 계정(Account)은 서버 수준의 보안주체의 한 종류이다.
여기서 각 개념에 대해 소개하기보다는 결국 이런 계층 구조를 통해 어떻게 DML권한을 가진 계정을 조회하는지에 초점을 맞추고자 한다.
서버 수준의 DML 권한 확인
- sysadmin 역할은 모든 데이터베이스에서 DML 권한을 포함한 모든 권한을 가진다.
- sys.server_principals를 조회하여 sysadmin 역할에 속한 사용자를 식별한다.
서버 수준에서의 권한 확인 쿼리
SELECT name AS LoginName, 'sysadmin' AS Role
FROM sys.server_principals
WHERE IS_SRVROLEMEMBER('sysadmin', name) = 1;
데이터베이스 수준 DML 권한 확인
- db_owner 역할은 명시적인 DML 권한이 없어도 모든 테이블에서 DML 작업이 가능하다.
- db_datawriter 역항은 모든 테이블에 대해 INSERT, UPDATE, DELETE 권한이 있다.
- 명시적인 권한(GRANT INSERT, GRANT UPDATE, GRANT DELETE): 특정 사용자나 역할이 테이블 또는 스키마에 대해 부여받은 권한을 부여받을 수 있다.
데이터베이스 수준에서의 권한 확인 쿼리
USE [Database]; -- 데이터베이스 수준을 조회할 때는 반드시 특정 데이터베이스를 지정해야한다.
SELECT u.name AS UserName, r.name AS RoleName
FROM sys.database_principals u
LEFT JOIN sys.database_role_members rm
ON u.principal_id = rm.member_principal_id
LEFT JOIN sys.database_principals r
ON rm.role_principal_id = r.principal_id
WHERE r.name IN ('db_owner', 'db_datawriter')
OR EXISTS (
SELECT 1
FROM sys.database_permissions p
WHERE p.grantee_principal_id = u.principal_id
AND p.permission_name IN ('INSERT', 'UPDATE', 'DELETE')
AND p.state_desc = 'GRANT'
);
서버와 데이터베이스 수준에서 DML 권한 조회 종합
종합 권한 확인 쿼리
-- 임시 테이블 생성: 각 로그인 계정, 각 데이터베이스, DML 권한 여부
CREATE TABLE #DMLPermissions (
LoginName NVARCHAR(128),
DBName NVARCHAR(128),
HasDMLPermission CHAR(1) DEFAULT ''
);
-- 모든 로그인 계정 가져오기 (시스템 계정 포함)
DECLARE @UserName NVARCHAR(128);
DECLARE user_cursor CURSOR FOR
SELECT name FROM sys.server_principals
WHERE type IN ('S', 'U', 'G') -- S: SQL 로그인, U: Windows 로그인, G: Windows 그룹
-- 데이터베이스 및 사용자별 DML 권한 검사
DECLARE @DBName NVARCHAR(128);
DECLARE @SQL NVARCHAR(MAX);
OPEN user_cursor;
FETCH NEXT FROM user_cursor INTO @UserName;
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE db_cursor CURSOR FOR
SELECT name FROM sys.databases
WHERE name NOT IN ('master', 'msdb', 'model', 'tempdb'); -- 시스템 데이터베이스 제외
OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @DBName;
WHILE @@FETCH_STATUS = 0
BEGIN
-- 시스템 계정은 DML 권한이 없다고 판단하여 빈 값으로 기록
IF @UserName LIKE '##%'
BEGIN
INSERT INTO #DMLPermissions (LoginName, DBName, HasDMLPermission)
VALUES (@UserName, @DBName, '');
END
ELSE
BEGIN
BEGIN TRY
-- 동적 SQL 생성 및 권한 확인
SET @SQL = '
USE ' + QUOTENAME(@DBName) + ';
DECLARE @HasDMLPermission CHAR(1) = '' '';
-- sysadmin 또는 db_owner, db_datawriter 확인
IF IS_SRVROLEMEMBER(''sysadmin'', ''' + @UserName + ''') = 1
BEGIN
SET @HasDMLPermission = ''O'';
END
ELSE
BEGIN
EXECUTE AS USER = ''' + @UserName + ''';
IF EXISTS (
SELECT 1
FROM sys.database_role_members AS drm
INNER JOIN sys.database_principals AS dp ON drm.role_principal_id = dp.principal_id
WHERE dp.name IN (''db_owner'', ''db_datawriter'')
AND drm.member_principal_id = DATABASE_PRINCIPAL_ID(''' + @UserName + ''')
)
OR EXISTS (
SELECT 1
FROM fn_my_permissions(NULL, ''DATABASE'')
WHERE permission_name IN (''INSERT'', ''DELETE'', ''UPDATE'')
)
BEGIN
SET @HasDMLPermission = ''O'';
END
REVERT;
END
-- 결과 삽입
INSERT INTO #DMLPermissions (LoginName, DBName, HasDMLPermission)
VALUES (''' + @UserName + ''', ''' + @DBName + ''', @HasDMLPermission);';
EXEC sp_executesql @SQL;
END TRY
BEGIN CATCH
-- 접근 불가할 때 빈 값으로 기록
INSERT INTO #DMLPermissions (LoginName, DBName, HasDMLPermission)
VALUES (@UserName, @DBName, '');
END CATCH;
END
FETCH NEXT FROM db_cursor INTO @DBName;
END
CLOSE db_cursor;
DEALLOCATE db_cursor;
FETCH NEXT FROM user_cursor INTO @UserName;
END
CLOSE user_cursor;
DEALLOCATE user_cursor;
-- 최종 결과 조회
SELECT * FROM #DMLPermissions;
-- 임시 테이블 삭제
-- DROP TABLE #DMLPermissions;- 각 데이터베이스를 USE 키워드로 지정하기 위해 동적 쿼리 사용
- 서버 수준에서 sysadmin 역할인 계정은 전체 데이터베이스에 대해 DML권한을 가지고 있으므로 sysadmin일 경우, 다른 조건으로 조회하지 않고 DML권한을 가지고 있다고 명시
- 데이터베이스 수준에서 db_owner, db_writer의 경우도 database_permissions을 조회하지 않고 DML권한이 있다고 명시
- 위의 조건에 해당하지 않으면서 database_permissions을 'INSERT', 'UPDATE', 'DELETE' 모두 가진 계정을 DML 권한이 있다고 명시
PIVOT 테이블로 변환
-- 데이터베이스 목록을 열로 사용하기 위한 동적 피벗 생성
DECLARE @PivotColumns NVARCHAR(MAX);
DECLARE @FinalSQL NVARCHAR(MAX);
-- 데이터베이스 이름을 열로 생성
SELECT @PivotColumns = STRING_AGG(QUOTENAME(DBName), ', ')
FROM (SELECT DISTINCT DBName FROM #DMLPermissions) AS DBNames;
-- 피벗 테이블 생성 쿼리
SET @FinalSQL = '
SELECT LoginName, ' + @PivotColumns + '
FROM #DMLPermissions
PIVOT (
MAX(HasDMLPermission)
FOR DBName IN (' + @PivotColumns + ')
) AS PivotTable
ORDER BY LoginName;';
-- 최종 쿼리 실행
EXEC sp_executesql @FinalSQL;
-- 임시 테이블 삭제
DROP TABLE #DMLPermissions;
최종 출력 형태
최종적으로 이렇게 엑셀로 제출하였으나 다시 보면 간과한 점이 많다.
개선점
1. DENY 권한 추가 확인
sys.database_permissions에서 state_desc에서 GRANT와 DENY 상태 확인이 필요하다. INSERT, UPDATE, DELETE에 대해 DENY가 먼저 적용되는데 이를 간과했다.
2. dbcreator와 bulkadmin 역할 반영 필요
sysadmin 뿐만 아니라 서버 수준의 역할에서 bulkadmin와 dbcreator 역할도 명시적으로 조회가 필요하다.
dbcreator가 생성한 데이터베이스에서 db_owner 권한을 가지는지도 확인해야 한다. bulkadmin의 BULK INSERT 작업이 가능하므로 역시 조회해야 한다.
3. 접근 제한 처리 개선
데이터베이스에 접근할 수 없는 경우 명확히 구분하도록 EXECUTE AS USER가 불가할 때는 HasDMLPermission 컬럼에 "Restricted"로 표시한다.
4. 시스템 계정 권한 평가
현재 쿼리는 시스템 계정(##로 시작하는 계정)의 권한을 무조건적으로 제외한다. 시스템 계정이라고 해서 반드시 DML권한이 없는 것은 아니기에 이에 대한 확인이 필요하다. 시스템 계정도 특정 시나리오에서 DML 권한을 가질 수 있다.
5. 커서로 인한 성능 이슈
다시 작성한다면 sp_MSforeachdb를 사용하거나 데이터베이스 목록을 추출하고 STRING_AGG를 이용해 쿼리를 조합해 커서 사용할 것 같다.
로그인 계정 및 데이터베이스 사용자 계정 확인
1. 로그인 계정이란?SQL Server에 로그온할 때 사용하는 계정을 말합니다.서버에 로그온할 자격만을 제시할 뿐, 데이터베이스에 접근할 수 있는것은 아닙니다.Type_Desc 열을 보시면 SQL_LOGIN 으로 되있
zihyee.tistory.com
[MSSQL] SQL Server 점검 시 유용한 쿼리 모음
DBA라고 하기 부끄러운 수준이지만, 그래도 다른 사람보다 DB를 조금 더 자주 접하면서 MSSQL에서 가지고 있으면 유용한 쿼리를 아래에 정리해 본다. 모두 sysadmin의 Server-level role을 가지고 수행하였
co-no.tistory.com
'SQL' 카테고리의 다른 글
| [MSSQL][SQL SERVER] SQL 성능 최적화: 캐싱과 동적 평가(Dynamic Evaluation)의 함정 (1) | 2025.01.05 |
|---|---|
| [SQL]SQL: SQL 정의, 특성, 기능, 예시, ACID (1) | 2024.11.24 |
| [SQL조인][조인 순서] 왜 조인을 할 때 ON절에서 조인 대상 테이블의 alias가 먼저 올까 (0) | 2024.10.25 |
| [Oracle]SQLPlus에서 'set autotrace on' 설정 시 에러 조치 (0) | 2024.06.09 |
| [SQL][#1][강의]기초 데이터 분석을 위한 핵심 SQL (0) | 2024.03.24 |
댓글