모듈 서명(데이터베이스 엔진)

데이터베이스 응용 프로그램의 요청에 따라 초급 단계 프로시저나 뷰를 통해 응용 프로그램 스키마 내의 기본 테이블과 개체에 대한 액세스를 조정해야 하는 경우가 있습니다. 이는 최종 사용자에게 초급 단계 개체에 대한 액세스 권한을 부여하기 위한 것으로, 이렇게 하면 초급 단계 개체가 사용자를 대신해서 기본 개체에 액세스합니다. 그러므로 최종 사용자에게 응용 프로그램 스키마의 모든 개체에 대한 액세스 권한을 부여하지 않아도 됩니다. 이 방법은 다음 두 가지 용도로 사용됩니다.

• 응용 프로그램 스키마의 모든 개체 대신 작은 하위 집합의 개체에 대한 사용 권한만 관리하면 되기 때문에 사용 권한 관리가 간단해집니다.

• 진입점만 노출되므로 기본 스키마 레이아웃을 최종 사용자로부터 숨길 수 있습니다.

Microsoft SQL Server에는 소유권 체인, EXECUTE AS 문 사용 등 이러한 시나리오를 구성하는 데 도움이 되는 많은 기능이 있습니다. SQL Server 2005부터 SQL Server에서는 데이터베이스 내의 모듈 서명 기능도 도입했습니다. 모듈 서명은 유사한 기능을 제공하지만 실행 컨텍스트를 변경하지 않습니다. 이 컨텍스트의 모듈은 저장 프로시저, 함수, 트리거 또는 어셈블리를 참조합니다. 자세한 내용은 CREATE ROLE(Transact-SQL) 및 EXECUTE AS를 사용하여 사용자 지정 권한 집합 만들기를 참조하십시오.

모듈 서명

SQL Server 2005에서는 저장 프로시저, 함수, 트리거 또는 어셈블리와 같은 데이터베이스 내의 모듈 서명 기능을 도입했습니다. DDL(데이터 정의 언어) 트리거는 서명할 수 없습니다. 디지털 서명은 서명자의 개인 키로 암호화된 데이터 다이제스트입니다. 개인 키는 디지털 서명이 전달자 또는 소유자에 대해 고유한지 확인합니다.

데이터에 서명하기 위해 서명자는 데이터를 다이제스트하고 개인 키로 암호화한 다음 암호화된 다이제스트 값을 데이터에 연결합니다. 서명을 확인하기 위해 확인자는 서명자의 공개 키를 사용하여 데이터에 연결된 암호화된 다이제스트 값의 암호를 해독합니다. 그런 다음 암호가 해독된 다이제스트 값을 수반된 데이터에서 계산된 다이제스트 값과 비교합니다. 서명자와 확인자가 같은 해시 함수를 사용하여 데이터를 다이제스트하는 것이 중요합니다.

주의
모듈 서명은 사용 권한을 부여하기 위한 용도로만 사용해야 하며 사용 권한을 거부하거나 취소하는 데는 사용하지 마십시오.

시나리오

sys.sysprocesses 뷰에 대한 액세스를 usp_sysprocesses 저장 프로시저로 조정해야 한다고 가정합니다. 사용자는 usp_sysprocesses 프로시저를 통해서만 sys.sysprocesses 정보에 액세스할 수 있습니다. usp_sysprocesses 및 sys.sysprocesses 개체는 소유권이 다르기 때문에 소유권 체인이 적용되지 않습니다.

먼저 서버에서 CREATE CERTIFICATE 문을 사용하여 키 쌍으로부터 인증서를 만들어야 합니다. 그런 다음 sys.sysprocesses 테이블에서 선택할 수 있는 사용 권한을 인증서에 부여합니다. 그러나 SQL Server는 보안 주체에만 사용 권한을 부여하기 때문에 먼저 CREATE LOGIN 문을 사용하여 인증서로부터 로그인을 만들어야 합니다. 이 로그인은 사용 권한 자리 표시자일 뿐이며 서버 인스턴스에 연결할 때 사용되지 않으므로 서버에 대한 연결 권한이 필요하지 않습니다. 그런 다음 GRANT VIEW SERVER STATE TO 문을 사용하여 이 인증서 매핑 로그인에 sys.sysprocesses 테이블에 대한 SELECT 권한을 부여할 수 있습니다. usp_sysprocesses 저장 프로시저를 만든 다음 ADD SIGNATURE 문을 사용하여 인증서(실제로는 이 인증서에 해당하는 개인 키)로 저장 프로시저를 서명할 수 있습니다. 새 역할이 생성되고 usp_sysprocesses 저장 프로시저에 대한 실행 권한이 부여됩니다. 이 역할의 멤버인 모든 사용자는 usp_sysprocesses 저장 프로시저를 실행하고 sys.sysprocess 뷰에서 선택할 수 있는 권한이 있습니다. 서명된 모듈을 실행하면 서명 인증서에 연결된 보안 주체에 GRANT 문을 사용하여 부여한 사용 권한이 호출 기간 동안 런타임 보안 토큰에 일시적으로 결합됩니다. 이러한 사용 권한은 실행 제어가 반환되는 즉시 보안 토큰에서 제거됩니다. 따라서 모듈 실행 기간 동안에만 추가 사용 권한 집합을 효과적으로 가질 수 있습니다. 이 프로시저에 대한 EXECUTE 권한이 부여된 다른 모든 사용자나 역할도 같은 기능을 가집니다.

예

다음 Transact-SQL 스크립트는 위의 시나리오에 대한 예를 제공합니다. 키 쌍에서 인증서가 생성되어 새 로그인에 매핑됩니다. 먼저 .NET Framework SDK에 포함된 MakeCert 도구를 사용하여 테스트 키 쌍을 만들어야 합니다. 그런 다음 인증서에 연결된 로그인에 sys.sysproceses 뷰에 대한 SELECT 권한이 부여됩니다. 관리되는 저장 프로시저 usp_sysprocesses가 새 데이터베이스에 생성되어 인증서로 서명됩니다. SysProcRole 역할이 생성되고 해당 역할에 usp_sysprocesses 저장 프로시저에 대한 EXECUTE 권한이 부여됩니다. 테스트 사용자가 생성되어 SysProcRole 역할에 추가됩니다. 테스트 사용자는 sys.sysprocess에 대해 SELECT 문을 수행한 다음 비교를 위해 usp_sysprocesses 저장 프로시저를 실행합니다. 그런 다음 스크립트가 테스트 환경을 정리합니다.

use master
go

-- Create a test database.
CREATE DATABASE db_Demo
go

-- Create a certificate on the server. A test key pair can be created
-- using the MakeCert tool that ships with the .NET Framework SDK.
CREATE CERTIFICATE SysProcCert FROM FILE = 'e:\programming\testCert.cer'
go

-- Create a login and map it to the certificate.
CREATE LOGIN login_SysProcCert FROM CERTIFICATE SysProcCert
Go

-- Revoke the connect permission.
REVOKE CONNECT SQL FROM login_SysProcCert ;
go 
 
-- Grant the certificate, through the login, permission to select from sys.sysprocesses view.
GRANT VIEW SERVER STATE TO login_SysProcCert
go

-- Create a test login.
CREATE LOGIN bob WITH PASSWORD = '<enterStrongPasswordHere>'
go

-- Connect to the test database.
use db_Demo
go

-- Create the master key for the test database (used to protect 
-- private keys and certificates in the database).
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<enterStrongPasswordHere>' 

-- Create a certificate from a private key.
CREATE CERTIFICATE SysProcCert FROM FILE = 'e:\programming\testCert.cer'
WITH PRIVATE KEY
(FILE = 'e:\programming\testCert.pvk', 
 DECRYPTION BY PASSWORD= '<enterStrongPasswordHere>', 
 ENCRYPTION BY PASSWORD='<enterStrongPasswordHere>')
go 

-- Create the assembly on the server. The assembly DLL must be signed.
CREATE ASSEMBLY SysStoredProcedures
FROM 'E:\programming\SysStoredProcs.dll'
WITH PERMISSION_SET = SAFE
go 

-- Create the managed stored procedure on the server.
CREATE PROCEDURE usp_sysprocesses
AS EXTERNAL NAME SysStoredProcedures.StoredProcedures.usp_sysprocesses
go 

-- Add the signature to the stored procedure.
ADD SIGNATURE TO [dbo].[usp_sysprocesses] 
BY CERTIFICATE SysProcCert WITH PASSWORD = '<enterStrongPasswordHere>'
go 

-- Create a role.
CREATE ROLE SysProcRole
go

-- Create a test user
CREATE USER bob
go

-- Add the test user to the role.
EXEC sp_addrolemember 'SysProcRole', 'bob'
go

-- Grant execute permissions on the stored procedure to the new role.
GRANT EXECUTE ON [dbo].[usp_sysprocesses] TO SysProcRole
go
 
-- Connect as the test user.
EXECUTE AS LOGIN = 'bob'
use db_Demo
go
 
-- User only has permission to see their own processes.
SELECT * FROM sys.sysprocesses
go

-- Execute the stored procedure, which has been signed.
exec usp_sysprocesses
go

-- REVERT
REVERT
----------------------------------------------------------------------
-- Cleanup

use db_Demo
go

use master
go

DROP DATABASE db_Demo
go 

DROP login login_SysProcCert
DROP login bob
go

DROP CERTIFICATE SysProcCert
go

다음은 sys.sysprocesses 뷰에 대해 SELECT * 문을 수행하는 usp_sysprocesses 저장 프로시저의 원본 코드입니다. 작성 시 어셈블리를 서명해야 합니다.

C#

using System;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;

public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void usp_sysprocesses()
{
    using(SqlConnection connection = new SqlConnection("context connection=true"))
    {
        connection.Open();
        SqlCommand command = new SqlCommand("SELECT * FROM sys.sysprocesses", connection);
        SqlContext.Pipe.ExecuteAndSend(command);
    }
}
};

Visual Basic

Imports System
Imports System.Data.SqlClient
Imports Microsoft.SqlServer.Server

Partial Public Class StoredProcedures
<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Shared Sub  usp_sysprocesses ()
    Using connection As New SqlConnection("context connection=true")
        connection.Open()

        Dim command As New SqlCommand("SELECT * FROM sys.sysprocesses", connection)
        SqlContext.Pipe.ExecuteAndSend(command)
    End Using
End Sub
End Class

참고 항목

참조

CREATE CERTIFICATE(Transact-SQL)

CREATE LOGIN(Transact-SQL)

GRANT(Transact-SQL)

CREATE MASTER KEY(Transact-SQL)

ADD SIGNATURE(Transact-SQL)

CREATE ROLE(Transact-SQL)

개념

SQL Server 보안 설정

SQL Server 인증서 및 비대칭 키