pajama

SQL Server 2005부터 각 개체는 데이터베이스 스키마에 속해 있습니다. 데이터베이스 스키마는 데이터베이스 사용자와 다른 고유 네임스페이스입니다. 스키마를 개체 컨테이너로 간주할 수 있습니다. 데이터베이스에서 스키마를 만들고 변경할 수 있으며 사용자에게 스키마에 대한 액세스 권한을 부여할 수 있습니다. 사용자가 스키마를 소유할 수 있으며 스키마 소유권은 양도할 수 있습니다.

참고:
데이터베이스 스키마는 XML 스키마와 다릅니다. XML 스키마에 대한 자세한 내용은 서버에서 XML 스키마 컬렉션 관리를 참조하십시오.

데이터베이스 개체 스키마를 만드는 방법은 CREATE SCHEMA(Transact-SQL)를 참조하십시오.

새 동작

---

이전 버전의 SQL Server에서는 데이터베이스 사용자와 스키마가 개념상 동일한 개체였습니다. SQL Server 2005부터 사용자와 스키마는 서로 다르며 스키마가 개체 컨테이너 역할을 합니다.

스키마에서 소유권을 분리하면 중요한 이점이 있습니다. 다음과 같은 방식으로 데이터베이스 스키마를 통해 보다 강력하게 데이터베이스 개체의 보안을 제어할 수 있습니다.

• 스키마 및 스키마에 포함된 보안 개체에 대한 사용 권한을 이전 버전보다 더욱 세부적으로 관리할 수 있습니다. 자세한 내용은 GRANT 스키마 권한(Transact-SQL) 및 GRANT 개체 권한(Transact-SQL)을 참조하십시오.
  
• 스키마와 스키마 범위 보안 개체에 대한 소유권을 양도할 수 있습니다. 자세한 내용은 ALTER AUTHORIZATION(Transact-SQL)을 참조하십시오.
  
• 스키마 간에 개체를 이동할 수 있습니다. 자세한 내용은 ALTER SCHEMA(Transact-SQL)를 참조하십시오.
  
• 하나의 스키마에 여러 데이터베이스 사용자가 소유하는 개체가 포함될 수 있습니다.
  
• 여러 데이터베이스 사용자가 하나의 기본 스키마를 공유할 수 있습니다.
  
• 역할, 응용 프로그램 역할 등의 데이터베이스 보안 주체가 스키마를 소유할 수 있습니다.
  
• 해당되는 스키마에서 개체를 삭제하지 않고 데이터베이스 사용자를 삭제할 수 있습니다.
  

데이터베이스 스키마를 통해 이전 버전에서 변경된 기타 중요한 보안 관련 사항은 다음과 같습니다.

• 코드가 스키마는 데이터베이스 사용자와 동일하다고 가정하는 경우 이전 버전의 SQL Server에서 작성된 코드가 잘못된 결과를 반환할 수 있습니다.
  
• 이전 버전의 SQL Server용으로 제작된 sysobjects 등의 카탈로그 뷰는 잘못된 결과를 반환할 수 있습니다.
  
• 사용자가 여러 개의 스키마를 소유할 수 있으므로 이제 소유권 체인과 사용자 컨텍스트 전환이 다르게 동작할 수 있습니다. 소유권 체인에 대한 자세한 내용은 소유권 체인 및 사용 권한 계층을 참조하십시오. 컨텍스트 전환에 대한 자세한 내용은 컨텍스트 전환을 참조하십시오.
  
• SQL Server 2000에서는 사용자가 데이터베이스 개체를 소유했습니다. SQL Server 2000에서 네 부분으로 구성된 데이터베이스 개체 참조는 [DatabaseServer].[DatabaseName].[ObjectOwner].[DatabaseObject]였습니다. SQL Server 2005부터 네 부분으로 구성된 데이터베이스 개체 참조는 [DatabaseServer].[DatabaseName].[DatabaseSchema].[DatabaseObject]입니다.
  

개체 소유권 변경 내용

다음 개체의 소유자 속성은 사용자가 아니라 스키마를 참조합니다.

• CREATE TABLE
  
• ALTER TABLE
  
• CREATE VIEW
  
• ALTER VIEW
  
• CREATE INDEX
  
• ALTER INDEX
  
• CREATE FUNCTION
  
• ALTER FUNCTION
  
• DROP FUNCTION
  
• VIEW_TABLE_USAGE
  
• VIEW_COLUMN_USAGE
  
• TABLE_CONSTRAINTS
  
• REFERENTIAL_CONSTRAINTS
  
• KEY_COLUMN_USAGE
  
• CONSTRAINT_TABLE_USAGE
  
• CONSTRAINT_COLUMN_USAGE
  
• CHECK_CONSTRAINTS
  
• COLUMN_DOMAIN_USAGE
  
• COLUMNS
  
• DOMAIN_CONSTRAINTS
  
• ROUTINE_COLUMNS
  

사용자 메타데이터 및 스키마 메타데이터 비교를 반환하는 열에 대한 자세한 내용은 아래의 "스키마 카탈로그 뷰 및 함수" 섹션을 참조하십시오.

카탈로그 뷰 및 함수로 대체된 시스템 테이블

SQL Server 2005에서는 250개 이상의 새 카탈로그 뷰를 제공하며, 일부는 SQL Server 2000 시스템 테이블을 대체하는 데이터베이스 사용자 및 스키마 개체를 다룹니다. 메타데이터에 액세스하려면 카탈로그 뷰를 사용하는 것이 좋습니다. 자세한 내용은 카탈로그 뷰(Transact-SQL)를 참조하십시오.

다음 표에서는 SQL Server 2000 시스템 테이블 및 이와 동등한 SQL Server 2005 카탈로그 뷰 간의 매핑을 보여 줍니다.

SQL Server 2000 시스템 테이블	SQL Server 2005 카탈로그 뷰
Sysusers	sys.database_principals(Transact-SQL) sys.schemas(Transact-SQL)
Syslogins	sys.server_principals(Transact-SQL)

기본 스키마

정규화되지 않은 보안 개체의 이름을 확인하기 위해 SQL Server 2000에서는 이름 확인을 사용하여 호출하는 데이터베이스 사용자가 소유한 스키마 및 dbo가 소유한 스키마를 확인했습니다.

SQL Server 2005의 각 사용자에게는 기본 스키마가 할당될 수 있습니다. CREATE USER 또는 ALTER USER의 DEFAULT_SCHEMA 옵션을 사용하여 기본 스키마를 설정하고 변경할 수 있습니다. DEFAULT_SCHEMA가 정의되어 있지 않으면 SQL Server 2005에서는 dbo 스키마를 기본 스키마로 가정합니다.

참고:
Windows 인증 그룹을 통해 연결하는 사용자에게는 기본 스키마 연결이 없습니다. 이러한 사용자가 스키마를 사용하여 정규화되지 않은 개체를 만드는 경우 새 스키마가 생성되고 해당 이름이 현재 사용자 이름으로 설정되며 사용자 이름이 지정된 새 네임스페이스에 테이블 개체가 생성됩니다.

새로운 DDL(데이터 정의 언어) 문은 sysobjects와 같은 이전 시스템 테이블에 정확하게 반영되어 있지 않은 시스템 메타데이터에 대한 복잡한 메타데이터를 제공할 수 있습니다. 이 예에서는 sysobjects에서 반환한 사용자 ID 및 스키마 이름이 동기화되어 있지 않으며 SQL Server 2005에서 제공하는 스키마와 사용자 간 구분을 반영합니다.

USE tempdb
GO
CREATE LOGIN u1 WITH PASSWORD = 'Mdfjd$sakj943857l7sdfh##30'
CREATE USER u1 WITH DEFAULT_SCHEMA = u1
GO
GRANT CREATE TABLE TO u1 
GO
CREATE SCHEMA sch1
GO
CREATE SCHEMA u1 AUTHORIZATION u1
GO
EXECUTE AS USER = 'u1'
GO
CREATE TABLE t1(c1 int)
GO
REVERT
GO
SELECT user_name(uid) , * FROM sysobjects WHERE name = 't1'
GO

주의:
CREATE/ALTER/DROP SCHEMA; CREATE/ALTER/DROP USER; CREATE/ALTER/DROP ROLE; CREATE/ALTER/DROP APPROLE; ALTER AUTHORIZATION 등의 DDL 문이 사용된 데이터베이스에서 새 카탈로그 뷰를 사용해야 합니다.

스키마 카탈로그 뷰 및 함수

---

SQL Server 2005부터 스키마는 메타데이터에 반영되는 명시적 엔터티입니다. 따라서 각 스키마에 하나의 소유자만 있을 수 있지만 한 명의 사용자가 스키마를 하나 이상 소유할 수 있습니다. SQL Server 2000 시스템 테이블에는 이 복잡한 관계가 반영되지 않으므로 SQL Server 2005에서는 새 메타데이터를 정확하게 반영하는 새 카탈로그 뷰를 제공합니다.

다음 표에서는 SQL Server 2005의 스키마에 대한 카탈로그 뷰, 메타데이터 및 함수를 보여 줍니다.

내용	참고
일반 스키마 메타데이터	sys.schemas(Transact-SQL)
정보 스키마 뷰	정보 스키마 뷰(Transact-SQL)
INFORMATION_SCHEMA.SCHEMATA 뷰에서 반환된 열 정의	SCHEMATA(Transact-SQL)

예

---

1. 스키마 만들기 및 사용자에게 소유권 할당

다음 예에서는 Marjorie라는 SQL Server 로그인 및 사용자와 Auditing이라는 새 스키마를 AdventureWorks 데이터베이스에 추가합니다. Marjorie는 Auditing 스키마의 소유자로 할당됩니다.

CREATE LOGIN Marjorie
    WITH PASSWORD = '8fdKJl3$nlNv3049jsKK';
USE AdventureWorks;
CREATE USER Marjorie FOR LOGIN Marjorie
GO
CREATE SCHEMA Auditing AUTHORIZATION Marjorie;
GO

2. 다른 스키마에 사용자 권한 부여

다음 예에서는 Marjorie라는 사용자에게 AdventureWorks 데이터베이스의 Purchasing 스키마에 대한 SELECT 권한을 부여합니다.

USE AdventureWorks;
GO
GRANT SELECT on SCHEMA::Purchasing TO Marjorie;
GO

3. 스키마의 소유권 변경

다음 예에서는 Jon이라는 새 사용자가 AdventureWorks 데이터베이스에서 생성됩니다. Jon에게는 AdventureWorks 데이터베이스의 Auditing 스키마 소유권이 부여됩니다. 그런 다음 Marjorie라는 사용자가 AdventureWorks 데이터베이스에서 삭제됩니다.

USE AdventureWorks;
GO
/* Create a new user in the database */
CREATE LOGIN Jon
    WITH PASSWORD = '1fdKJl3$nlNv3049jsBB';
USE AdventureWorks;
CREATE USER Jon FOR LOGIN Jon
GO
ALTER AUTHORIZATION on SCHEMA::Auditing TO Jon;
GO
/* Removes the user from the system */
DROP LOGIN Marjorie;
GO
DROP USER Marjorie;
GO

4. 스키마의 소유권 표시

다음 예에서는 AdventureWorks 데이터베이스의 Auditing 스키마 소유자를 표시합니다.

USE AdventureWorks;
GO
/* This method uses the INFORMATION_SCHEMA views */
SELECT * 
FROM INFORMATION_SCHEMA.SCHEMATA
WHERE SCHEMA_NAME = 'Auditing';
GO
/* This method uses the sys.schemas catalog and links
   the names of the database users and server logins */
SELECT s.name AS 'Schema Name'
, db.name AS 'Database User Name'
, svr.name AS 'SQL Server Login Name'
FROM sys.schemas s
/* Obtains the name of the database user */
   INNER JOIN sys.database_principals db
      on s.principal_id = db.principal_id
/* Obtains the name of the server login */
      INNER JOIN sys.server_principals svr
         on db.sid = svr.sid
WHERE s.name = 'Auditing'
ORDER BY s.name

http://msdn.microsoft.com/ko-kr/library/ms190387(v=SQL.90).aspx

Could not come up with a better title for this post. After reading some of our blog posts, one of our readers asked the question – “Why do you always make use of a “SELECT 1″ rather than a “SELECT *” when using an EXISTS or a NOT EXISTS clause”.

The answer is that during the query plan stages, the * is expanded to bind with the list of columns. And then since the semantics of the query is such (EXISTS or NOT EXISTS), since it does not need any of those columns, it will remove all of them. Using a SELECT 1, avoids having to look at any of the meta-data that is not even needed for that table during query compilation time.

Please do NOTE that at runtime BOTH of them will have the same plans. We can quickly check that with an example (using SQL Server Syntax):

CREATE TABLE TBL1 (COL1 INT IDENTITY PRIMARY KEY, COL2 INT)
GO
CREATE TABLE TBL2 (COL3 INT IDENTITY PRIMARY KEY, COL4 INT, COL5 INT, COL6 INT, COL7 INT, COL8 INT, COL9 INT, COL10 INT, CONSTRAINT FK_TBL2_TO_TBL1 FOREIGN KEY (COL4) REFERENCES TBL1(COL1))
GO

CREATE INDEX TBL2_IND_1 on TBL2 (COL4)
GO

SET NOCOUNT on
GO
DECLARE @I INT, @J INT
SELECT @I = 1, @J = 1000
WHILE (@I <= @J)
BEGIN
INSERT INTO TBL1 (COL2) VALUES (@I)

IF @I%7 = 0
INSERT INTO TBL2 (COL4, COL5) VALUES (@I, @I + 10)

SET @I = @I + 1
END
GO

SET SHOWPLAN_TEXT OFF
GO

SELECT * FROM TBL1 AS A
WHERE EXISTS (SELECT * FROM TBL2 WHERE COL4 = A.COL1)

———————————————————————————————————————————————————————–
|–Merge Join(Left Semi Join, MERGE:([A].[COL1])=([master].[dbo].[TBL2].[COL4]), RESIDUAL:([master].[dbo].[TBL2].[COL4]=[master].[dbo].[TBL1].[COL1] as [A].[COL1]))
|–Clustered Index Scan(OBJECT:([master].[dbo].[TBL1].[PK__TBL1__17E28260] AS [A]), ORDERED FORWARD)
|–Index Scan(OBJECT:([master].[dbo].[TBL2].[TBL2_IND_1]), ORDERED FORWARD)

SET SHOWPLAN_TEXT OFF
GO

DBCC FREEPROCCACHE
GO

SET SHOWPLAN_TEXT on
GO

SELECT * FROM TBL1 AS A
WHERE EXISTS (SELECT 1 FROM TBL2 WHERE COL4 = A.COL1)

StmtText
———————————————————————————————————————————————————————–
|–Merge Join(Left Semi Join, MERGE:([A].[COL1])=([master].[dbo].[TBL2].[COL4]), RESIDUAL:([master].[dbo].[TBL2].[COL4]=[master].[dbo].[TBL1].[COL1] as [A].[COL1]))
|–Clustered Index Scan(OBJECT:([master].[dbo].[TBL1].[PK__TBL1__17E28260] AS [A]), ORDERED FORWARD)
|–Index Scan(OBJECT:([master].[dbo].[TBL2].[TBL2_IND_1]), ORDERED FORWARD)

As you can see from above, the execution plan at runtime are exactly the same regardless of whether a “SELECT 1″ is used or a “SELECT *” is used. We use SELECT 1 because of the reason mentioned in the start of this post.