[MSSQL]DB 복원후 사용자와 스키마 분리 및 변경

 

 

 

SQL Server 2000에서 SA(system administor) 권한을 가진 사용자가 SQL Server에 object를 만들면 [서버명].[데이터베이스명].[dbo].[object] 와 같은 형식으로 만들어집니다. 그럼 objeect 생성권한을 가지고 있는 다른 사용자가 object를 만들면? 예를들어 YEON이라는 사용자가 존재를 하고 그 YEON이라는 사용자가 YEON_TABLE라는 테이블을 만들게 되면 [서버명].[데이터베이스명].[YEON].[YEON_TABLE] 이렇게 object가 생성이됩니다. 그렇죠? 가물가물 하신가요?

많은 SQL Server 책에서 설명하기를 YEON이라는 사용자가 퇴사를 하고 LEE라는 사원이 새로이 입사를 하게 되었다는 가정와 함께 YEON이라는 권한을 LEE에게 넘겨야 하겠는데 어떻게 하냐? 깝깝하시죠?

근데 보통 정말 특이한 회사나 특이한 JOB을 가지고 있는 사람빼고는 위와 같이 사용자를 구분하여 권한을 달리하는 경우는 극히 드물것 같아요. 그래서 WEB이나 기타 응용프로그램에서 사용하는 DB는 모두 DBO권한을 갖게 하죠.

위에서 설명했듯이 SQL Server는 object를 접근할때 기본적으로 Four depth 접근법을 사용합니다. Four depth 접근법이란? [서버명].[데이터베이스명].[사용자명].[객체명] 이렇게 접근한다는 것입니다. 하지만 이 접근 방법은 어디까지나 SQL Server 2000일때 이야기 입니다. 그럼 SQL Server 2005에서는 Four depth 접근이 어떻게 달라졌느냐? SQL Server 2005에서는 [서버명].[데이터베이스명].[스키마].[객체명] 이렇게 바뀌었습니다.

그럼 왜 저렇게 스키마라는 개념이 SQL Server 2005에서 새로이 추가가 되어 사람들을 혼란시키냐?? 라는 것을 조금만 고민해 보면 아하! 정말 필요했던 것이였구나! 라고 느끼실수 있습니다. 물론 이렇게 쓰는 회사나 개발 프로젝트는 극히 드물다고 생각합니다만, 왜 스키마라는 것이 필요한지 예를 들어보겠습니다.

앞서 설명했던 [YEON].[YEON_TABLE] 와 [LEE].[LEE_TABLE]가 있고, 여기서 [YEON].[YEON_TABLE]가 퇴사를 했고, 로그인유저 [YEON]을 삭제를 하고 모든것을 [LEE]에게 주어야 한다고 가정을 해봅시다. 아.. ~~ !! 생각만 해도 깝깝합니다. -0-;; 그래서 이런한 문제점들을 해결하고자 나온것이 SCHEMA라는 것입니다. 즉, 2000에서는 object 소유를 사용자가 직접하였지만, 2005부터는 object 소유를 스키마가 하고 그 스키마를 사용자가 소유하므로써 권한이행을 자유롭게 할 수 있게 되었다 라는 것 입니다.

위와 관련된 예제는 많은 책과 인터넷에서 심심치 않게 찾아보실 수 있습니다.
그리하여 이번 "사용자와 스키마 분리" Module에서는 위와 같은 예제를 다루지 않고, 2000에서 2005로 마이크레이션 또는 복원을 했을때 문제가 되는 것을 간략하게 보여드리겠습니다.

 TESTDB.BAK

해당 백업 파일은 SQL Server 2000에서 생성하여 백업한 파일로 [test]라는 유저가 [test].[tbl_TEST]라는 테이블을 만들고 백업을 한 파일입니다. 즉, 위 파일을 2005에서 그대로 그냥 복원을 하면 아래와 같은 데이터베이스 트리구조를 보게 되실겁니다.

그럼 여기에서 [TESTDB]에 접근할수 있게 [TEST] 로그인 계저을 만들어 보겠습니다.

view sourceprint?

1.--======================================================

2.-- test 로그인 유저 생성하기

3.--======================================================

4.CREATE LOGIN test WITH PASSWORD = '1111'

그리고 TESTDB에 유저를 추가해봅시다.

view sourceprint?

1.--======================================================

2.-- test 유저 추가하기

3.--======================================================

4.USE [TESTDB]

5.GO

6.CREATE USER [test] FOR LOGIN [test]

7.GO

로그인 생성까지는 무리없이 잘 되었는데, TESTDB에 [test]라는 유저를 추가하려고 하니 다음과 같은 에러메시지를 출력해주십니다.

메시지 15023, 수준 16, 상태 1, 줄 1
현재 데이터베이스에 사용자, 그룹 또는 역할 'test'이(가) 이미 있습니다.

우린 추가해준 기억이 없는데 이미 있다네요. 왜 있을까요 ??
그렇죠 앞에서 우리는 복원을 했기 때문에 기존 사용자가 그대로 복원이 된 것입니다. 그럼 어떻게 할까요?? 저 사용자를 지울까요?? 한번 지워볼까요??

view sourceprint?

1.--======================================================

2.-- test 유저 해제하기

3.--======================================================

4.USE [TESTDB]

5.GO

6.DROP USER [test]

7.GO

이렇게 실행을 해보겠습니다.
하지만, 우리의 MSSQL님께서는 다음과 같은 에러 메시지를 출력해주십니다.

메시지 15138, 수준 16, 상태 1, 줄 1
데이터베이스 보안 주체는 데이터베이스의 스키마을(를) 소유하며 삭제할 수 없습니다.

갈수록 태산입니다. 그럼 SCHEMA를 삭제 해볼까요??

view sourceprint?

1.--======================================================

2.-- test schema 스키마 삭제하기

3.--======================================================

4.USE [TESTDB]

5.GO

6.DROP SCHEMA test

7.GO

위와 같이 또 실행을 해봅시다. 그러나 역시 우리의 MSSQL님께서는 다음과 같은 에러메시지를 또 뿌리십니다. 

메시지 3729, 수준 16, 상태 1, 줄 1
'test'은(는) 개체 'tbl_TEST'에서 참조하고 있으므로 drop schema할 수 없습니다.

아 깝깝합니다. 그럼 테이블을 지울까요?? 그 테이블에 중요한 내용이 있다면?? 그럼 다른 테이블을 만들어서 해당 내용을 이동했다가 지우고 다시 동일한 이름의 테이블을 만들고 데이터를 이동 시킬까요?? 아.. 왠지 삽질 하는것 같고 없어보이고 작업도 짜증날것 같습니다.

그래서 이런 경우에 사용하라고 MSSQL 2000에서는 

sp_change_users_login

 즉, 유저를 매핑하는 명령어 입니다. 이후 MSSQL 2005에서는 같은 기능을 한 다른 명령어를 제공했습니다. ALTER USER 입니다.

view sourceprint?

01.--======================================================

02.-- 유저 매핑하기

03.--======================================================

04.USE [TESTDB]

05.GO

06.EXEC sp_change_users_login 'update_one', 'test', 'test';

07.GO

08.

09.USE [TESTDB]

10.GO

11.ALTER USER test WITH LOGIN = test

12.GO

이렇게 하면 기존에 백업하면서 생성되었던 [test]와 우리가 만들어주었던 [test]가 매핑이 되면서 사용을 할 수 있게 됩니다. 그런데 아직 테이블은 [test].[tbl_TEST] 입니다. 특별한 사유가 없다면 [dbo]로 만들어서 관리하는 것이 효율적일것 같습니다. 아래 쿼리를 실행시켜 출력되는 query를 실행시키면 [test].[tbl_TEST]가 [dbo].[tbl_TEST]로 변경된 것을 보실 수 있습니다.

view sourceprint?

01.--======================================================

02.-- 스키마 변경하기

03.-- 해당 스키마 www.sqlworld.pe.kr의 이장래 선생님

04.-- 께서 작성하신 쿼리입니다.

05.--======================================================

06.USE [TESTDB]

07.GO

08.SELECT 'ALTER SCHEMA dbo TRANSFER test.' + name FROM sys.tables

09.WHERE schema_id = SCHEMA_ID('test')

10.GO

해당 포스트에서 사용자가 무엇이며, 스키마가 무엇이고 스키마 정의는 어떻게 하고 스키마를 이용하여 권한이행을 어떻게 하면 등등의 스키마에 대한 자세한 설명을 다루지 않았습니다. 이 부분은 책이나 인터넷을 통하여 쉽게 접근할 수 있는 내용이라고 생각하여 정리를 하지 않았습니다. 하지만, 위와 같은 문제는 빈번하게 발생하며 또한, 꾸준히 질문이 올라오고 있습니다. 해당 포스트를 읽고 문제 해결에 조금이나마 도움이 되었으면 하는 바램에 적어보았습니다.

 

 

출처 : http://m.cafe.daum.net/ilovevb/5Ckl/31?listURI=%2Filovevb%2F_rec%3Fpage%3D8