메타데이터 표시 문제 해결

이 항목을 사용하여 메타데이터 표시 문제를 해결할 수 있습니다.

사용자는 소유하고 있거나 일부 사용 권한이 부여된 메타데이터만 볼 수 있습니다. 이 정책으로 인해 최소 권한을 가진 사용자는 SQL Server 인스턴스의 모든 개체에 대한 메타데이터를 볼 수 없습니다. 메타데이터 표시에 대한 자세한 내용은 메타데이터 표시 유형 구성을 참조하십시오.

사용자가 메타데이터를 볼 수 있도록 하려면

최소 권한을 가진 사용자가 모든 메타데이터를 볼 수 있도록 하려면 다음 문 중 하나를 실행합니다.

• GRANT VIEW ANY DEFINITION TO public;

  이 문은 인스턴스 수준에서 메타데이터 표시 제한을 무시합니다. 해당 인스턴스의 모든 메타데이터는 public 수준으로 공개됩니다.

• GRANT VIEW DEFINITION TO public;

  이 문은 데이터베이스 수준에서 메타데이터 표시 제한을 무시합니다. 해당 데이터베이스의 모든 메타데이터는 public 수준으로 공개됩니다.

• GRANT VIEW DEFINITION ON SCHEMA :: <schema_name> TO public;

  이 문은 스키마 수준에서 메타데이터 표시 제한을 무시합니다. 해당 스키마의 모든 메타데이터는 public 수준으로 공개됩니다.

• GRANT VIEW DEFINITION ON OBJECT :: <object_name> TO public;

  이 문은 개체 수준에서 메타데이터 표시 제한을 무시합니다. 해당 개체의 모든 메타데이터는 public 수준으로 공개됩니다. 개체가 테이블인 경우 해당 테이블의 모든 열, 인덱스, 통계 및 제약 조건이 public 수준으로 공개됩니다. 이 동작은 GRANT VIEW DEFINITION ON ASSEMBLY 및 다른 유사한 GRANT 문에도 적용됩니다.

최소 권한을 가진 특정한 사용자나 역할이 모든 메타데이터를 볼 수 있도록 하려면 public 대신 특정 사용자 이름이나 역할 이름을 피부여자로 사용합니다.

사용자가 서로를 볼 수 있도록 하려면

기본적으로 최소 권한을 가진 사용자는 sys.database_principals 및 sys.server_principals 카탈로그 뷰에 있는 다른 사용자를 볼 수 없습니다. 즉, 최소 권한을 가진 사용자는 자신이 소유한 테이블에 대한 권한을 다른 사용자에게 부여하려 해도 다른 사용자를 볼 수 없습니다. 최소 권한을 가진 사용자인 user_X가 다른 사용자 user_Y를 볼 수 있도록 하려면 다음 GRANT 문을 실행합니다.

• GRANT VIEW DEFINITION ON USER :: <user_Y> TO <user_X>

각 사용자에 대해 이 문을 실행해야 합니다. 다음과 유사한 DDL 트리거를 만들어 이 프로세스를 자동화할 수 있습니다.

CREATE TRIGGER grant_view_definition_on_principal ON DATABASE
FOR CREATE_USER, CREATE_ROLE
AS
    DECLARE @event_type sysname, @principal_name sysname, @sql nvarchar(max);
    SELECT @event_type     = eventdata().value('(/EVENT_INSTANCE/EventType) [1]','sysname');
    SELECT @principal_name = eventdata().value('(/EVENT_INSTANCE/ObjectName)[1]','sysname');
    IF (@event_type = 'CREATE_USER')
        SELECT @sql = 'GRANT VIEW DEFINITION ON USER :: ' + @principal_name + ' TO PUBLIC ' ;
    ELSE
        SELECT @sql = 'GRANT VIEW DEFINITION ON ROLE :: ' + @principal_name + ' TO PUBLIC ' ;
    EXEC (@sql) ;
GO

응용 프로그램 역할이 서버 수준 메타데이터를 볼 수 있도록 하려면

응용 프로그램 역할은 서버 수준의 보안 주체와 연결되어 있지 않으므로 해당 데이터베이스 외부의 메타데이터에 액세스할 수 없습니다. 응용 프로그램 역할이 서버 수준 메타데이터를 볼 수 있도록 하려면 다음 방법을 사용할 수 있습니다.

추적 플래그 설정

응용 프로그램 역할이 서버 수준 메타데이터에 액세스할 수 있도록 하려면 전역 플래그 4616을 설정합니다. 추적 플래그 설정에 대한 자세한 내용은 DBCC TRACEON(Transact-SQL)을 참조하십시오. 추적 플래그 4616에 대한 자세한 내용은 추적 플래그(Transact-SQL)를 참조하십시오.

인증서 서명된 저장 프로시저 사용

인증서 서명된 프로시저를 사용하여 서버 수준 시스템 테이블에 액세스하는 것이 좋습니다. 인증서 서명된 프로시저의 이점은 다음과 같습니다.

• 추적 플래그를 사용할 필요가 없습니다.

• 공개될 수 있는 서버 수준 정보가 더 적습니다. 응용 프로그램 역할 기반 응용 프로그램에서 일반 쿼리 대신 저장 프로시저를 사용해야 합니다. 저장 프로시저는 응용 프로그램에 필요한 특정 데이터만 반환합니다.

• 다음 예에서는 인증서 서명된 저장 프로시저를 만들고 응용 프로그램 역할에서 이 프로시저를 사용하여 서버 수준 메타데이터를 볼 수 있는 방법을 보여 줍니다.

USE master;
GO 
CREATE DATABASE approle_db; 
GO 
CREATE LOGIN some_login WITH PASSWORD = '<enterStrongPasswordHere>'; 
GO 
USE approle_db; 
GO 
CREATE USER some_user FOR LOGIN some_login; 
GO
EXEC sp_addapprole 'an_approle', '<enterStrongPasswordHere>'; 
GO
--------------------------------------------------------------------- 
-- This section shows how to use a certificate to authenticate 
-- a signed procedure.
--------------------------------------------------------------------- 
CREATE LOGIN execute_as_login WITH PASSWORD = '<enterStrongPasswordHere>'; 
GO 
USE master; 
GO 
GRANT VIEW ANY DEFINITION TO execute_as_login; 
GRANT VIEW SERVER STATE TO execute_as_login; 
GO 
USE approle_db;
GO 
CREATE USER execute_as_user FOR LOGIN execute_as_login; 
GO 
--
-- You must use EXECUTE AS 'authenticator' here because the application role 
-- does not have a server identity. Therefore, the application role cannot use 
-- the certificate permissions on the server. Therefore, you 
-- need a new execution context to which you can grant 
-- the needed VIEW* permissions. 
-- 
CREATE PROC access_server_system_tables 
    WITH EXECUTE AS 'execute_as_user' 
    AS 
    SELECT sid, status, name, dbname, hasaccess, loginname 
        FROM master.dbo.syslogins; 
    SELECT spid, kpid, lastwaittype, waitresource, dbid 
        FROM master.dbo.sysprocesses; 
GO 
GRANT EXECUTE ON access_server_system_tables TO an_approle; 
GO 
CREATE CERTIFICATE signing_cert 
    ENCRYPTION BY PASSWORD = '<enterStrongPasswordHere>' 
    WITH SUBJECT = 'Signing Cert'; 
GO 
BACKUP CERTIFICATE signing_cert TO FILE = 'signing_cert.cer'; 
GO 
ADD SIGNATURE TO access_server_system_tables
    BY CERTIFICATE signing_cert WITH PASSWORD = '<enterStrongPasswordHere>';
GO
--------------------------------------------------------------------- 
-- Create a copy of the signing certificate in the target 
-- database. In this case, the target database is the master database. 
-- This copy of the signing certificate vouches for the execution context
-- that enters this database from the signed procedure. 
--------------------------------------------------------------------- 
USE master; 
GO 
CREATE CERTIFICATE signing_cert FROM FILE = 'signing_cert.cer'; 
GO 
--------------------------------------------------------------------- 
-- Because the VIEW permissions in question are server-level permissions,
-- we need to grant AUTHENTICATE SERVER permission on a login-mapped certificate. 
--------------------------------------------------------------------- 

CREATE LOGIN signing_cert_login FROM CERTIFICATE signing_cert; 
GO 
GRANT AUTHENTICATE SERVER TO signing_cert_login 
GO 
--------------------------------------------------------------------- 
-- Now you can open a new connection as "some_login" and 
-- set the application role. Then, call the "access_server_system_tables"
-- procedure, and obtain verification that you can access server-level information 
-- when the application role-based application runs. 
-- For an example, see the Demo usage.sql code below.
--------------------------------------------------------------------- 

--------------------------------------------------------------------- 
-- Clean up. 
-- The following statements remove the objects created above.
--------------------------------------------------------------------- 
USE master 
GO 
DROP DATABASE approle_db; 

DROP LOGIN some_login; 
GO 
DROP LOGIN execute_as_login; 
GO 
DROP LOGIN signing_cert_login; 
GO 
DROP CERTIFICATE signing_cert; 
GO 
-- 
-- Delete the certificate file. 
-- 
EXEC sp_configure 'show advanced options', 1; 
GO 
RECONFIGURE; 
GO 
EXEC sp_configure 'xp_cmdshell', 1; 
GO 
RECONFIGURE; 
GO 
EXEC xp_cmdshell 'del "C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\signing_cert.cer"'; 
GO 
EXEC sp_configure 'xp_cmdshell', 0; 
GO 
RECONFIGURE; 
GO 

-- ============================================================================
-- - Application role access to server information - Demo usage.sql
--
--  This code is companion code that shows an example of application role access
--  to server information by using a certificate-signed procedure.
--
-- ============================================================================
--  -------------------------------------------------- 
-- Connect as some_login first.
-- ------------------------------------------------ 
USE approle_db;
GO
EXEC sp_setapprole 'an_approle', '<enterStrongPasswordHere>';
GO
-- Display the server-level information the application role can currently view. 
SELECT sid, status, name, dbname, hasaccess, loginname 
FROM master.dbo.syslogins; 
SELECT spid, kpid, lastwaittype, waitresource, dbid 
FROM master.dbo.sysprocesses; 
GO 
-- Display the server-level information the application role
-- can view by running the certificate-signed stored procedure.
EXEC access_server_system_tables;
GO

참고 항목

태스크

분산형 분할 뷰의 메타데이터 표시 문제 해결

개념

메타데이터 표시 유형 구성

개체 정의 보기

VIEW DEFINITION 권한

VIEW ANY DATABASE 권한