MS SQL SQLAgentRole

 MS SQL Server의 `msdb` 데이터베이스에 존재하는 `SQLAgentOperatorRole`, `SQLAgentReaderRole`, `SQLAgentUserRole`에 대해 비교 설명하는 글입니다.

---

MS SQL Server의 msdb에서 SQL Server Agent 역할 비교: SQLAgentOperatorRole, SQLAgentReaderRole, SQLAgentUserRole

MS SQL Server에서 SQL Server Agent와 관련된 작업을 관리하기 위해 `msdb` 데이터베이스에 정의된 세 가지 고정된 데이터베이스 역할(`SQLAgentOperatorRole`, `SQLAgentReaderRole`, `SQLAgentUserRole`)에 대해 알아보겠습니다. 이 역할들은 SQL Server Agent의 작업(Job), 스케줄(Schedule), 프록시(Proxy) 등에 대한 접근 권한을 세밀하게 제어할 수 있도록 설계되었습니다. 각 역할의 특징과 차이점을 비교하며, 어떤 상황에서 어떤 역할을 사용해야 하는지 살펴보겠습니다.

SQL Server Agent와 msdb 데이터베이스

SQL Server Agent는 SQL Server에서 작업 자동화 및 스케줄링을 담당하는 핵심 구성 요소입니다. 이와 관련된 모든 메타데이터는 `msdb`라는 시스템 데이터베이스에 저장됩니다. `msdb`에는 SQL Server Agent의 보안 관리를 위해 세 가지 고정된 데이터베이스 역할이 정의되어 있습니다. 이 역할들은 권한 수준에 따라 계층적으로 구성되어 있으며, 사용자가 필요에 따라 적절한 수준의 접근 권한을 부여받을 수 있도록 설계되었습니다. 이제 각 역할의 세부 사항을 알아보겠습니다.

 1. SQLAgentUserRole: 최소 권한 역할

특징

- 권한 범위: `SQLAgentUserRole`은 SQL Server Agent의 고정된 데이터베이스 역할 중 가장 낮은 권한을 가진 역할입니다.
-*주요 권한:
  - 사용자가 **소유한 로컬 작업(Local Job)**과 **작업 스케줄(Job Schedule)**에 대한 관리 권한.
  - 작업 생성, 수정, 실행, 삭제 가능 (단, 자신이 소유한 작업에 한함).
  - 프록시(Proxy) 목록은 작업 단계 속성(Job Step Properties)에서만 확인 가능.
- 제한점:
  - 다른 사용자가 소유한 작업에는 접근 불가.
  - 멀티서버 작업(Multiserver Job)은 사용할 수 없음.
  - 작업 소유권 변경 불가.
- SSMS에서의 가시성: SQL Server Management Studio(SSMS)에서 SQL Server Agent 노드가 표시되며, `Jobs` 노드만 볼 수 있습니다.

사용 사례

- 예시: 개발자나 특정 팀원이 자신의 작업만 생성하고 관리해야 하는 경우.
- 장점: 최소 권한 원칙(Principle of Least Privilege)을 따르며, 보안성을 유지하면서도 기본적인 작업 관리가 가능합니다.

2. SQLAgentReaderRole: 읽기 중심 역할

 특징

- 권한 범위: `SQLAgentUserRole`의 모든 권한을 포함하며, 추가로 읽기 권한이 확장된 역할입니다.
- 주요 권한:
  - 자신이 소유한 작업에 대한 관리 권한(`SQLAgentUserRole`과 동일).
  - 모든 로컬 작업과 스케줄의 목록, 속성, 실행 이력을 볼 수 있음 (소유 여부와 관계없이).
  - 멀티서버 작업(Multiserver Job)의 목록, 속성, 이력 조회 가능.
- 제한점:
  - 자신이 소유하지 않은 작업의 실행, 수정, 삭제는 불가.
  - 작업 소유권 변경 불가.
- SSMS에서의 가시성: `Jobs` 노드만 표시되며, 자신이 소유하지 않은 작업도 볼 수 있습니다.

 

사용 사례

- 예시: DBA 팀에서 작업 상태를 모니터링해야 하지만, 작업을 직접 수정하거나 실행할 필요는 없는 경우.
- 장점: 작업 전체를 모니터링할 수 있는 권한을 제공하면서도 관리 권한은 제한하여 보안과 관리를 균형 있게 유지합니다.

3. SQLAgentOperatorRole: 최고 권한 역할

특징

- 권한 범위: SQL Server Agent의 고정된 데이터베이스 역할 중 가장 높은 권한을 가집니다. `SQLAgentUserRole`과 `SQLAgentReaderRole`의 모든 권한을 상속받습니다.
- 주요 권한:
  - 모든 로컬 작업의 실행, 중지, 시작 가능 (소유 여부와 관계없이).
  - 모든 로컬 작업의 이력 삭제 가능.
  - 모든 로컬 작업과 스케줄의 활성화/비활성화 가능 (`sp_update_job`, `sp_update_schedule` 저장 프로시저 사용).
  - 운영자(Operator)와 프록시(Proxy)의 속성 조회 및 사용 가능.
  - 서버에서 사용 가능한 프록시와 경고(Alert) 목록 조회 가능.
- 제한점:
  - SQL Server Agent 자체 설정 변경은 불가 (이는 `sysadmin` 역할만 가능).
- SSMS에서의 가시성: `Jobs` 노드 외에도 더 많은 세부 정보를 볼 수 있으며, 광범위한 작업 관리가 가능합니다.

 

사용 사례

- 예시: 여러 팀원이 생성한 작업을 중앙에서 관리해야 하는 DBA나 운영 담당자.
- 장점: 작업에 대한 거의 모든 제어 권한을 제공하므로, 복잡한 환경에서의 작업 관리를 효율적으로 수행할 수 있습니다.

역할 비교 표

 

권한의 계층적 구조

이 세 역할은 계층적(Concentric)으로 설계되었습니다. 즉:
- `SQLAgentReaderRole`은 `SQLAgentUserRole`의 모든 권한을 포함.
- `SQLAgentOperatorRole`은 `SQLAgentReaderRole`과 `SQLAgentUserRole`의 모든 권한을 포함.

예를 들어, `SQLAgentUserRole`에 특정 프록시 접근 권한이 부여되면, `SQLAgentReaderRole`과 `SQLAgentOperatorRole`의 멤버도 자동으로 그 프록시에 접근할 수 있습니다. 이는 보안 설계 시 주의해야 할 점입니다.

역할 부여 방법

다음은 각 역할에 사용자를 추가하는 간단한 T-SQL 예제입니다:

USE msdb;
GO

-- SQLAgentUserRole에 사용자 추가
ALTER ROLE SQLAgentUserRole ADD MEMBER [YourUser];
GO

-- SQLAgentReaderRole에 사용자 추가
ALTER ROLE SQLAgentReaderRole ADD MEMBER [YourUser];
GO

-- SQLAgentOperatorRole에 사용자 추가
ALTER ROLE SQLAgentOperatorRole ADD MEMBER [YourUser];
GO

`YourUser`는 실제 사용자 이름으로 대체해야 합니다. 사용자가 `msdb`에 이미 매핑되어 있어야 하며, 그렇지 않다면 먼저 `CREATE USER`로 사용자를 추가해야 합니다.

 

결론

- `SQLAgentUserRole`: 작업을 생성하고 관리해야 하는 개별 사용자에게 적합.
- `SQLAgentReaderRole`: 모든 작업을 모니터링해야 하지만 수정 권한은 필요 없는 사용자에게 적합.
- `SQLAgentOperatorRole`: 작업에 대한 광범위한 제어 권한이 필요한 관리자에게 적합.

이 역할들을 적절히 활용하면 SQL Server Agent의 보안을 강화하면서도 필요한 기능을 효율적으로 제공할 수 있습니다.