sp_refresh_parameter_encryption(Transact-SQL)

  • 아티클

적용 대상: SQL Server 2016(13.x) 이상 Azure SQL DatabaseAzure 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_TRIGGERSERVER_DDL_TRIGGER.

반환 코드 값

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

설명

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

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

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

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

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

참고 항목

개체와 연결된 모든 서명은 실행할 sp_refresh_parameter_encryption때 삭제됩니다.

사용 권한

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

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

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

절 IMPERSONATE 로 정의된 모듈의 EXECUTE AS 경우 지정된 보안 주체에 대한 권한이 필요합니다. 일반적으로 모듈을 사용하여 정의하고 보안 주체의 사용자 이름이 모듈을 만들 때와 다른 사용자로 EXECUTE AS USER 확인되지 않는 한 개체를 새로 고치는 것은 보안 주체를 변경하지 EXECUTE AS 않습니다.

예

다음 예제에서는 테이블을 참조하는 테이블 및 프로시저를 만들고 Always Encrypted를 구성한 다음 테이블을 변경하고 프로시저를 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 열을 암호화된 열로 바꾼 다음, Always Encrypted 구성 요소를 업데이트하는 절차를 실행 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 마법사