[DB] MS-SQL2005 스키마(Schema) 관리

아래 내용의 출처는 http://www.dbguide.net/db.db?cmd=view&boardUid=13958&boardConfigUid=9&categoryUid=216&boardIdx=74&boardStep=1
입니다.
----------------------------------------------------------------------------------------------------

스키마는 테이블, 뷰, 함수, 프로시저 등을 포함하는 보안 개체입니다. 사용자는 직접 테이블 이나 뷰와 같은 데이터베이스 개체들을 직접 소유하는 대신 이러한 개체들이 포함되어 있는 스키마를 소유함으로써 각 개체들에 대한 권한을 얻게 됩니다. 사용자와 스키마가 분리 되어 있기 때문에, 사용자가 삭제 또는 변경이 되더라도 해당 스키마의 소유권을 다른 사용자에게로 변경하면 되기 때문에 데이터베이스 개체에는 영향을 미치지 않게 됩니다. 단일 사용자뿐만 아니라 데이터베이스 역할이나 Windows 그룹 계정도 스키마를 소유할 수 있기때문에 이를 이용하여 하나의 스키마를 여러 사용자가 공유할 수 있습니다. SQL Server 2000과의 호환성을 위하여 dbo 스키마가 디폴트로 존재하며, 시스템 카탈로그의 스키마는 sys입니다.

■ 스키마 생성하기
현재 데이터베이스에 스키마를 생성합니다. CREATE SCHEMA는 한 문장으로 새로운스키마를 생성하고, 그 스키마가 소유하는 테이블과 뷰를 생성할 수 있고, 이 오브젝트들에 대해 GRANT, DENY, REVOKE 권한 설정을 할 수 있습니다.

[따라하기] 스키마 생성하기

Person 스키마를 생성하면서 동시에 PersonInfo 테이블을 생성하는 예제입니다.
Person 스키마는 UserA 사용자가 소유하며 UserB 에게는 Select 권한이 있고, UserC 에게는 Select 권한을 거부합니다.

CREATE LOGIN userA WITH PASSWORD='testA'; CREATE LOGIN userB WITH PASSWORD='testB'; CREATE LOGIN userC WITH PASSWORD='testC'; GO USE Sample; GO CREATE USER userA; CREATE USER userB; CREATE USER userC; GO CREATE SCHEMA Person AUTHORIZATION userA CREATE TABLE PersonInfo (ID int, name varchar(50), Address varchar(100) GRANT SELECT TO userB DENY SELECT TO userC; GO

■ 스키마 변경하기

ALTER SCHEMA는 동일한 데이터베이스 내에서 스키마 간에 개체를 이동할 때만 사용할 수 있습니다. 스키마 내에서 개체를 변경하거나 삭제하려면 해당 개체와 관련된 ALTER 또는 DROP 문을 사용합니다

[따라하기] 테이블을 동일 데이터베이스 내의 다른 스키마로 이동하기

-- PersonInfo 테이블을 Person 스키마에서 HR 스키마로 이동 USE Sample; ALTER SCHEMA HR TRANSFER Person.PersonInfo; GO

■ 스키마 삭제하기
스키마를 삭제하려면, 스키마가 소유하고 있는 테이블을 삭제한 다음에 스키마를 삭제할 수 있습니다.

[따라하기] 스키마 삭제하기

DROP TABLE HR.PersonInfo; DROP SCHEMA HR; GO

■ 스키마 정보 확인하기
sys.schemas 카탈로그 뷰를 통하여 스키마 목록을 확인할 수 있습니다.

■ 엔터티 소유권 변경하기
ACompany라는 회사에 userA라는 관리자가 있고 userA라는 관리자가 UserA.Table1이라는 테이블과 UserA.Procedure1이라는 저장 프로시저를 만들었으며 프로그램이나 SQL 쿼리 등에서 이 개체들을 사용해 왔습니다. 그런데 userA 가 퇴사를 하게 되고, userB라는 관리자가 이 자리를 대체하는 경우를 가정합니다. 이 경우, HR Schema의 소유권을 userB로 이전해 주기만 하면, HR 스키마는 userB가 소유하게 되고, 응용 프로그램이나 SQL 쿼리 등은 아무런 변경 사항 없이 HR.Table1 이나 HR.Procedure1 등을 사용할 수 있습니다. 그리고 userA 사용자 계정은 삭제할 수 있습니다.

[따라하기] 소유권 이전하기

ALTER AUTHORIZATION ON SCHEMA::HR TO userB; GO