sp_refresh_parameter_encryption (Transact-SQL)

  • 项目

适用于： SQL Server 2016 (13.x) 及更高版本 Azure SQL 数据库Azure SQL 托管实例

更新当前数据库中指定的非绑定到架构的存储过程、用户定义函数、视图、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（成功）或非零数字（失败）

注解

如果出现以下情况，模块参数的加密元数据可能会过时：

  • 模块引用的表中列的加密属性已更新。 例如，删除了一列，并添加了具有相同名称但不同加密类型、加密密钥或加密算法的新列。
  • 该模块引用另一个具有过时参数加密元数据的模块。

修改表的加密属性时，应对直接或间接引用该表的任何模块运行 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 定义的，并且主体的用户名现在解析为与创建模块时的用户不同的用户。

示例

以下示例创建一个表和一个引用该表的过程，配置 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 列替换为加密列，然后运行 sp_refresh_parameter_encryption 过程来更新 Always Encrypted 组件。

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
始终加密向导