다음을 통해 공유

sp_refresh_parameter_encryption (Transact-SQL)

  • 아티클

적용 대상: SQL Server 2016(13.x) 이상 Azure SQL 데이터베이스 Azure SQL Managed Instance

현재 데이터베이스에서 지정된 스키마 바인딩되지 않은 저장 프로시저, 사용자 정의 함수, 뷰, DML 트리거, 데이터베이스 수준 DDL 트리거 또는 서버 수준 DDL 트리거의 매개 변수에 대한 Always Encrypted 메타데이터를 업데이트합니다.

Transact-SQL 구문 표기 규칙

구문

sys.sp_refresh_parameter_encryption [ @name = ] 'module_name' 
    [ , [ @namespace = ] '<class>' ]
[ ; ]

<class> ::=
{ DATABASE_DDL_TRIGGER | SERVER_DDL_TRIGGER }

인수

[ @name = ] 'module_name'은(는) 저장 프로시저, 사용자 정의 함수, 뷰, DML 트리거, 데이터베이스 수준 DDL 트리거 또는 서버 수준 DDL 트리거의 이름입니다. module_name은 공용 언어 런타임(CLR) 저장 프로시저 또는 CLR 함수가 될 수 없습니다. module_name은 스키마 바인딩이 불가능합니다. module_name은 nvarchar이며, 기본값이 없습니다. module_name은 여러 부분으로 구성된 식별자가 될 수 있지만 현재 데이터베이스의 객체만 참조할 수 있습니다.

[ @namespace = ] ' < class > '은(는) 지정된 모듈의 클래스입니다. module_name이 DDL 트리거인 경우 <class>은(는) 필수입니다. <class>은 nvarchar(20)입니다. 잘못된 입력은 DATABASE_DDL_TRIGGER 및 SERVER_DDL_TRIGGER입니다.

반환 코드 값

0(성공) 또는 0이 아닌 숫자(실패)

설명

다음과 같은 경우 모듈의 매개 변수에 대한 암호화 메타데이터가 오래된 것일 수 있습니다.

  • 모듈이 참조하는 테이블의 열 암호화 속성이 업데이트되었습니다. 예를 들어, 열이 삭제되고 이름이 같은 새 열이 추가되었지만, 다른 암호화 유형, 암호화 키 또는 암호화 알고리즘이 추가되었습니다.
  • 이 모듈은 오래된 매개 변수 암호화 메타데이터가 있는 다른 모듈을 참조합니다.

테이블의 암호화 속성이 수정되면 해당 테이블을 직간접적으로 참조하는 모든 모듈에 대해 sp_refresh_parameter_encryption을 실행해야 합니다. 이 저장 프로시저는 호출자로 이동하기 전에 사용자가 먼저 내부 모듈을 새로 고칠 필요 없이 해당 모듈에서 어떤 순서로든 호출할 수 있습니다.

sp_refresh_parameter_encryption은(는) 개체와 연결된 권한, 확장 속성 또는 SET 옵션에는 영향을 주지 않습니다.

서버 수준 DDL 트리거를 새로 고치려면 데이터베이스의 컨텍스트에서 이 저장 프로시저를 실행합니다.

참고 사항

sp_refresh_parameter_encryption을 실행하면 개체와 연결된 모든 서명이 삭제됩니다.

사용 권한

모듈에 대한 ALTER 권한 및 개체가 참조하는 CLR 사용자 정의 형식과 XML 스키마 컬렉션에 대한 REFERENCES 권한이 필요합니다.

지정된 모듈이 데이터베이스 수준 DDL 트리거인 경우 현재 데이터베이스에서 ALTER ANY DATABASE DDL TRIGGER 권한이 필요합니다.

지정된 모듈이 서버 수준 DDL 트리거인 경우 CONTROL SERVER 권한이 필요합니다.

EXECUTE AS 절로 정의된 모듈의 경우 지정된 보안 주체에 대해 IMPERSONATE 권한이 필요합니다. 일반적으로 모듈이 EXECUTE AS(으)로 정의되어 있고 보안 주체의 사용자 이름이 모듈을 생성하는 시점과 다른 사용자로 확인되지 않는 한, 개체를 새로 고쳐도 EXECUTE AS USER 보안 주체는 변경되지 않습니다.

예시

다음 예시에서는 테이블과 테이블을 참조하는 프로시저를 만들고, 항상 암호화를 구성한 다음, 테이블을 변경하고 sp_refresh_parameter_encryption 프로시저를 실행하는 것을 보여 줍니다.

먼저 초기 테이블과 테이블을 참조하는 저장 프로시저를 만듭니다.

CREATE TABLE [Patients]([PatientID] [int] IDENTITY(1,1) NOT NULL,
	[SSN] [char](11), 
	[FirstName] [nvarchar](50) NULL,
	[LastName] [nvarchar](50) NOT NULL,
	[MiddleName] [nvarchar](50) NULL,
	[StreetAddress] [nvarchar](50) NOT NULL,
	[City] [nvarchar](50) NOT NULL,
	[ZipCode] [char](5) NOT NULL,
	[State] [char](2) NOT NULL,
	[BirthDate] [date] NOT NULL,
 CONSTRAINT [PK_Patients] PRIMARY KEY CLUSTERED 
(
	[PatientID] ASC
) WITH 
    (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
	 IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
	 ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY];
GO

CREATE PROCEDURE [find_patient] @SSN [char](11)
AS
BEGIN
	SELECT * FROM [Patients] WHERE SSN=@SSN
END;
GO

그런 다음, Always Encrypted 키를 설정합니다.

CREATE COLUMN MASTER KEY [CMK1]
WITH
(
	   KEY_STORE_PROVIDER_NAME = N'MSSQL_CERTIFICATE_STORE',
   	KEY_PATH = N'CurrentUser/my/A66BB0F6DD70BDFF02B62D0F87E340288E6F9305'
);
GO

CREATE COLUMN ENCRYPTION KEY [CEK1]
WITH VALUES
(
	   COLUMN_MASTER_KEY = [CMK1],
   	ALGORITHM = 'RSA_OAEP',
   	ENCRYPTED_VALUE = 
	   0x016E000001630075007200720065006E00740075007300650072002F006D0079002F006100360036006200620030006600360064006400370030006200640066006600300032006200360032006400300066003800370065003300340030003200380038006500360066003900330030003500CA0D0CEC74ECADD1804CF99137B4BD06BBAB15D7EA74E0C249A779C7768A5B659E0125D24FF827F5EA8CA517A8E197ECA1353BA814C2B0B2E6C8AB36E3AE6A1E972D69C3C573A963ADAB6686CF5D24F95FE43140C4F9AF48FBA7DF2D053F3B4A1F5693A1F905440F8015BDB43AF8A04BE4E045B89876A0097E5FBC4E6A3B9C3C0D278C540E46C53938B8C957B689C4DC095821C465C73117CBA95B758232F9E5B2FCC7950B8CA00AFE374DE42847E3FBC2FDD277035A2DEF529F4B735C20D980073B4965B4542A34723276A1646998FC6E1C40A3FDB6ABCA98EE2B447F114D2AC7FF8C7D51657550EC5C2BABFFE8429B851272086DCED94332CF18FA854C1D545A28B1EF4BE64F8E035175C1650F6FC5C4702ACF99850A4542B3747EAEC0CC726E091B36CE24392D801ECAA684DE344FECE05812D12CD72254A014D42D0EABDA41C89FC4F545E88B4B8781E5FAF40D7199D4842D2BFE904D209728ED4F527CBC169E2904F6E711FF81A8F4C25382A2E778DD2A58552ED031AFFDA9D9D891D98AD82155F93C58202FC24A77F415D4F8EF22419D62E188AC609330CCBD97CEE1AEF8A18B01958833604707FDF03B2B386487CC679D7E352D0B69F9FB002E51BCD814D077E82A09C14E9892C1F8E0C559CFD5FA841CEF647DAB03C8191DC46B772E94D579D8C80FE93C3827C9F0AE04D5325BC73111E07EEEDBE67F1E2A73580085
);
GO

마지막으로 SSN 열을 암호화된 열로 바꾼 다음 sp_refresh_parameter_encryption 프로시저를 실행하여 항상 암호화된 구성 요소를 업데이트합니다.

ALTER TABLE [Patients] DROP COLUMN [SSN];
GO

ALTER TABLE [Patients] 
    ADD	[SSN] [char](11) COLLATE Latin1_General_BIN2 
	ENCRYPTED WITH 
	    (COLUMN_ENCRYPTION_KEY = [CEK1], 
    	ENCRYPTION_TYPE = Deterministic, 
	    ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') 
	NOT NULL;
GO

EXEC sp_refresh_parameter_encryption [find_patient];
GO

참고 항목

Always Encrypted
Always Encrypted 마법사