SQL Server의 보안 매커니즘(권한부여)에 따른 문제사례 및 해결방법

로그인(login)정보를 잃어버린 DB 사용자(User)에 대한 이해와 처리방법

 

 

DBA의 중요한 업무 중 하나가 주기적으로 테스트 서버의 DB를 운영서버와 동기화(Refresh(?))해주는 작업입니다. 이때 동기화 방법은 몇 가지가 있지만 테스트서버라는 특성상 쉽게 할 수 있는 방법이 운영서버의 백업을 테스트 서버로 복구하는 방법입니다.

몇 년 전 일입니다. 주기적으로 하는 단순 업무라 이제 막 SQL Server에 입문한 후배에게 이 일을 맡기고 다 완료됐다고 해서 개발자들한테 사용해도 된다고 통보를 했는데요. 개발자들한테서 ‘특정 계정이 운영서버에서는 이상이 없는데 테스트 서버에서는 ‘Permission denied’에러가 나며 DB로 접근이 안된다’고 자꾸 연락이 오는 겁니다. 

부랴부랴 확인을 해보니 SQL Server login과 database user간의 계정 매핑이 안되어있어서 발생하는 문제였습니다.

아뿔사….후배한테 계정 매핑하는 방법을 안가르쳤네요..^^;;;

 

[문제]

SQL Server의 DB를 다른 instance로 복구하거나 또는 DB detach(분리)/attach(연결) 방법으로 옮겼을 경우, DB에 이전에 생성/구성된 login이 접근이 안 되는 현상이 발생하는 경우가 있습니다. 이경우 가장 일반적인 증상은 어플리케이션에서 ‘login failed’ error가 발생하거나, DBA가 해당 DB에 login을 추가하려고 할 때 ‘현재 database에 이미 user가 존재합니다’라는 메시지를 보게 됩니다. 

Msg 229, Level 14, State 1
%s permission denied on object %.*s, database %.*s, owner %.*s 
Microsoft SQL-DMO (ODBC SQLState: 42000) Error 15023: User or role '%s' already exists in the current database

그렇다면, 다른 Instance로 DB복구나 연결 작업 후에 발생하는 이러한 문제를 어떻게 해결할 수 있을까요?

 

[원인]

SQL Server의 특정 DB가 ‘A서버’에서 ‘B서버’로 옮겨졌을 경우 옮겨진 user database의 매핑 테이블에 저장된 SID가 ‘B서버’의 master 데이터베이스에 저장되어있는 login id(SID)와 맞지 않기 때문에 발생하는 문제입니다.

참고로, login(로그인)은 master시스템 데이터베이스에 저장되어있으며, SQL Server에 로그온할 때 사용합니다. login은 서버에 접속할 수 있는 자격만을 제시하는 것이지 서버 안에 있는 DB에 접근할 수 있는 기능은 아니기 때문에 DB를 사용하려면 이 login 계정과 매핑이 되는 user 계정이개별 DB 안에 만들어져 있어야 접근이 가능합니다. 

SQL Server에 대한 사전지식이 없을 경우, 위의 에러들을 보게 된다면 아마도 Database에서 user를 삭제하고 재생성하는 단순한 방법으로 문제를 해결하려고 할지도 모릅니다. (제 후배도 그랬습니다. ^^;;) 하지만 그럴 경우 DB내의 User에 이미 구성되어있는 모든 권한들을 잃게 되는 문제가 발생하기 때문에 이러한 권한정보들을 유지하기 위해서는 다른 방법을 찾아야 합니다. 해결책을 알려드리기 전에(^^) 잠시 SQL Server의 내부를 살펴보도록 하겠습니다.

 

[Quick Overview] SQL Server login(로그인)계정과 Database user(사용자) 계정의 관계

아래의 [그림1]처럼 Master DB에 저장된 SQL Server login은 각 개별 database의 user에 맵핑되어 있습니다. 일부 특수한 경우를 제외하고 SQL Server login은 미리 login에 사용 가능하도록 매핑된 각 database user를 이용하여 각 database에 접근하게 됩니다. login들은 시스템 카탈로그 뷰인 sys.server_principals와 sys.syslogins 뷰를 통해 볼 수 있고, 각 DB user에 매핑된 정보는 sys.sysusers라는 시스템 뷰를 통해 볼 수 있습니다. (실제 저장은 별도 시스템 테이블에 있으나 뷰를 통해서만 접근이 가능합니다.) 이 시스템 테이블에는 database user의 이름과 여기에 대응되는 SQL Server login의 SID의 매핑정보 들어있는데, 이 database user의 권한은 DB에 대한 인증(authorization)을 목적으로 사용됩니다.

 

그런데 만약 어떤 DB를 다른 인스턴스로 옮겼을 경우에 이 옮겨진 DB안에 저장된 매핑정보인login SID가 자동으로 바뀌게 될까요? SQL Server는 새로운 서버에 같은 login이 있는지 없는지, 혹은 있더라도 이것이 맞는 정보인지 알 수가 없기 때문에 절.대. 임의로 SID 정보를 맞추지 않습니다. 따라서, 옮겨진 DB에 있는 User의 SID와새로운 서버에 있는 master database에 있는 login의 SID정보와 맞지 않게 되므로 결국 [그림2]와 같이 부모(login)를 잃어버린 고아(orphan) 신세가 돼버린 user들이 생기게 됩니다.
 

 

이러한 문제들은 [그림3]과 같이 복구된 DB들의 user를 Instance2에 있는 기존 login들에 매핑(relink)하거나 기존에 없는 login들을 신규로 생성해서 매핑함으로써 해결할 수 있습니다.

 

 

 

위의 매커니즘을 다 이해하셨다면, 이제 이러한 문제들을 분석하고 해결할 수 있는 방법을 구체적으로 알아보겠습니다.

 

[해결방법]

1. 복구된 database에 얼마나 많은 고아 user(매핑이 끊어진 User)들이 있는지 분석
위의 세 user를 가진 TestDB database를 다른 인스턴스로 복구하고, 아래의 커맨드를 이용하여 복구된 database에 얼마나 많은 고아 user들이 있는지 분석합니다.

 

 

고아 user들을 확인했으면 이제 이 문제를 해결해보겠습니다. 문제를 해결하기 위해서는 master database에 있는 login들의 SID에 sysusers테이블에 있는 user들의 SID를 연결합니다.(물론 어떤 로그인과 연결해야하는지는 알고있어야겠죠? ^^) 아래 구문은 TestUser1 user를 TestUser1 login에 다시 맵핑하는 구문입니다.

2. User를 login에 매핑하기

2.1 1 user에 대하여 login 매핑하기

 

TestDB의 TestUser1 user SID가 Master DB의 TestUser1 Login SID로 맞춰져 있음을 확인할 수 있습니다. 2.2 Database의 User 명이 SQL Server login 이름과 동일할 경우 ‘Auto_Fix’ 사용 만약 database에 있는 고아 user 명이 SQL Server login 이름과 동일하다고 확신한다면 아래 커맨드를 이용해서 좀더 간단하게 매핑할 수 있습니다. 하지만, 만약 Server Login name과 database user의 이름이 서로 다를 경우에는 Auto_Fix를 사용하면 큰일납니다~

 

 

 

2.3 Login이 존재하지 않을 경우, 자동으로 login 생성 후 매핑
만약 login name이 존재하지 않는다면, 매핑하기 전에 먼저 해당 login을 생성한 후 매핑 작업을 진행해야 하는데, 아래 커맨드를 이용하면 이러한 작업을 빠르게 할 수 있습니다. 
 

지금까지 SQL Server의 login(로그인)과 user(사용자)의 매핑 관계로 인해 생길 수 있는 문제와 해결방법을 알아봤는데요. Login, user, Schema 간의 보안 매커니즘만 완벽히 잘 이해하고 있다면 이번에 소개해드린 문제가 아니더라도 권한부여 관련한 문제는 쉽게 해결할 수 있습니다. 그리고 위에 소개해드린 쿼리들은 데이터베이스를 옮기는 절차나 복구하는 절차서의 마지막에 반드시 포함해서 항상 체크하는 습관을 들인다면 이런 문제가 발생하는 것을 사전에 예방할 수 있다는 것을 기억해주세요~~ ^^

 

출처 :한국데이터베이스진흥원

제공 : DB포탈사이트 DBguide.net