SQL Server에서 고아 사용자 관리: 종합 가이드

SQL Server에서 고아 사용자 관리는 데이터베이스 보안 및 관리에서 매우 중요합니다. 고아 사용자는 데이터베이스 사용자와 마스터 데이터베이스에 해당하는 로그인이 존재하지 않을 때 발생합니다. 이 불일치는 접근을 방해하고 잠재적인 보안 취약점으로 이어질 수 있습니다. 이 기사에서는 고아 사용자가 무엇인지, 이를 감지하는 방법, 그리고 이를 해결하는 방법을 탐구할 것입니다. 여기에는 업데이트된 예제와 시나리오가 포함됩니다.

배경

SQL Server 데이터베이스에 연결하기 위해, 사용자는 마스터 데이터베이스에 유효한 로그인이 있어야 합니다. 이 로그인은 사용자를 인증하고 권한 부여를 위해 데이터베이스 사용자에 매핑됩니다. 이 매핑은 보안 식별자(SID)를 사용하여 유지되며, 데이터베이스 사용자와 서버 로그인이 올바르게 연결되어 있음을 보장합니다.

로그인-사용자 매핑 규칙의 예외는 다음과 같습니다:

1. 포함된 데이터베이스 사용자: 데이터베이스 수준에서 인증되며, 서버 로그인의 의존성을 제거합니다. 이러한 사용자는 데이터베이스의 이동성을 향상시키지만 각 데이터베이스에 대해 별도로 재생성해야 합니다.
2. 게스트 계정: 기본적으로 활성화되어 있으며, 명시적인 매핑 없이 사용자의 접근을 허용하지만 보안상의 이유로 일반적으로 권장되지 않습니다.
3. Microsoft Windows 그룹 구성원: Windows 그룹의 구성원이 데이터베이스에 접근할 수 있으며, 그룹이 사용자로 추가된 경우 가능합니다.

데이터베이스 사용자가 고아가 되는 경우는 다음과 같습니다:

• 해당 로그인이 삭제됩니다.
• 데이터베이스가 관련 로그인을 포함하지 않고 마이그레이션되거나 복원됩니다.
• 사용자와 로그인 간의 SID 매핑이 잘못되었습니다.

고아 사용자란 무엇인가요?

고아 사용자는 다음과 같은 경우에 발생합니다:

• 데이터베이스 사용자가 존재하지만, 마스터 데이터베이스에 해당하는 SQL Server 로그인이 존재하지 않습니다.
• 데이터베이스 사용자 SID와 로그인 SID가 불일치합니다.

이러한 시나리오는 인증 및 권한 부여를 방해하여 오류나 제한된 접근을 초래합니다.

고아 사용자 감지

SQL Server의 경우

다음 쿼리를 사용하여 고아 사용자를 식별합니다:

SELECT dp.type_desc, dp.sid, dp.name AS user_name  

FROM sys.database_principals AS dp  

LEFT JOIN sys.server_principals AS sp  

    ON dp.sid = sp.sid  

WHERE sp.sid IS NULL  

    AND dp.authentication_type_desc = 'INSTANCE';

Azure SQL 데이터베이스 또는 Synapse Analytics의 경우

1. 마스터 데이터베이스에서 로그인의 SID를 검색합니다:

SELECT sid 

FROM sys.sql_logins 

WHERE type = 'S'; 

2. 대상 데이터베이스에서 사용자의 SID를 검색합니다:

SELECT name, sid, principal_id

FROM sys.database_principals 

WHERE type = 'S' 

  AND name NOT IN ('guest', 'INFORMATION_SCHEMA', 'sys')

  AND authentication_type_desc = 'INSTANCE';

3. 목록을 비교하여 불일치를 식별합니다.

고아 사용자 해결

1. SID로 누락된 로그인 재생성 

로그인이 삭제된 경우:

CREATE LOGIN <login_name>   

WITH PASSWORD = '<use_a_strong_password_here>',  

SID = <SID>;

2. 고아 사용자를 기존 로그인에 매핑 

로그인이 존재하지만 매핑되지 않은 경우:

ALTER USER <user_name> WITH LOGIN = <login_name>;

3. 사용자 비밀번호 변경 

로그인을 재생성한 후, 비밀번호를 업데이트합니다:

ALTER LOGIN <login_name> WITH PASSWORD = '<enterStrongPasswordHere>';

sp_FindOrphanedUser 사용

저장 프로시저 sp_FindOrphanedUser는 고아 사용자를 감지하고 해결하는 강력한 도구입니다. 이는 다음을 제공합니다:

1. 고아 사용자 목록.
2. 수리를 위한 자동 생성 스크립트.
3. 소유자가 없는 사용자의 스키마에 대한 세부 정보.

sp_FindOrphanedUser에서 패키지 코드를 가져옵니다.

프로시저 실행

기본 실행:

EXEC sp_FindOrphanedUser;

특정 데이터베이스의 경우:

EXEC sp_FindOrphanedUser @DatabaseName = '<DatabaseName>';

소유자가 없는 사용자 시나리오 예시

예시 1: 로그인 없는 사용자

시나리오

로그인이 삭제되어 데이터베이스 사용자가 소유자 없이 남게 됩니다.

• 고아 사용자 감지:
  MS SQL

   

  EXEC sp_FindOrphanedUser;

• 로그인 재생성:
  MS SQL

   

  생성 로그인 [User1] 비밀번호 = 'SecurePassword123';

  사용하기 [ExampleDB];

  수정 사용자 [User1] 로그인 = [User1];

예시 2: SID 불일치

시나리오

로그인 SID가 사용자 SID와 일치하지 않습니다.

• 불일치를 감지하십시오:
  MS SQL

   

  EXEC sp_FindOrphanedUser @DatabaseName = 'ExampleDB';

• MS SQL

   

  USE [ExampleDB];

  EXEC sp_change_users_login 'UPDATE_ONE', 'User2', 'User2';

예시 3: 스키마를 소유한 고아 사용자

시나리오

고아 사용자로 인해 스키마 삭제가 방지됩니다.

• 스키마 소유권 감지:
  MS SQL

   

  EXEC sp_FindOrphanedUser @DatabaseName = 'ExampleDB';

• 스키마 소유권 재지정:
  MS SQL

   

  USE [ExampleDB];

  ALTER AUTHORIZATION ON SCHEMA::[User3Schema] TO [dbo];

  DROP USER [User3];

모범 사례

• 제한된 데이터베이스 사용자 사용: 서버 로그인에 대한 의존성을 피합니다.
• 로그인 동기화: 데이터베이스 마이그레이션 중 항상 로그인을 마이그레이션하거나 복원합니다.
• 정기 감사: 보안을 유지하기 위해 주기적으로 고아 사용자 확인을 수행합니다.

결론

고아 사용자 관리는 데이터베이스 보안 및 운영 효율성을 보장합니다. sp_FindOrphanedUser와 같은 쿼리 및 도구를 사용하여 문제를 신속하게 식별하고 해결하여 합법적인 사용자에 대한 원활한 액세스를 유지할 수 있습니다. 제한된 사용자 및 정기 감사와 같은 모범 사례를 채택하면 향후 이러한 문제를 예방할 수 있습니다.