sp_refresh_parameter_encryption (Transact-SQL)

Article
08/23/2023

S’applique à : SQL Server 2016 (13.x) et versions ultérieures Azure SQL Database Azure SQL Managed Instance

Met à jour les métadonnées Always Encrypted pour les paramètres de la procédure stockée non liée au schéma spécifiée, fonction définie par l’utilisateur, vue, déclencheur DML, déclencheur DDL au niveau de la base de données ou déclencheur DDL au niveau du serveur spécifié dans la base de données active.

Conventions de la syntaxe Transact-SQL

Syntaxe

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

<class> ::=
{ DATABASE_DDL_TRIGGER | SERVER_DDL_TRIGGER }

Arguments

[ @name = ] 'module_name' Nom de la procédure stockée, de la fonction définie par l’utilisateur, de la vue, du déclencheur DML, du déclencheur DDL au niveau de la base de données ou du déclencheur DDL au niveau du serveur. module_name ne peut pas être une procédure stockée CLR (Common Language Runtime) ou une fonction CLR. module_name ne peut pas être lié au schéma. module_name est nvarchar, sans valeur par défaut. module_name peut être un identificateur en plusieurs parties, mais ne peut faire référence qu’aux objets de la base de données active.

[ @namespace = ] ' < class > ' Classe du module spécifié. Lorsque module_name est un déclencheur DDL, <class> il est nécessaire. <class> a la valeur nvarchar(20). Les entrées valides sont DATABASE_DDL_TRIGGER et SERVER_DDL_TRIGGER.

Codet de retour

0 (réussite) ou un nombre différent de zéro (échec)

Remarques

Les métadonnées de chiffrement des paramètres d’un module peuvent devenir obsolètes, si :

Les propriétés de chiffrement d’une colonne d’une table les références de module ont été mises à jour. Par exemple, une colonne a été supprimée et une nouvelle colonne portant le même nom, mais un autre type de chiffrement, clé de chiffrement ou algorithme de chiffrement a été ajouté.
Le module fait référence à un autre module avec des métadonnées de chiffrement de paramètre obsolètes.

Lorsque les propriétés de chiffrement d’une table sont modifiées, sp_refresh_parameter_encryption elles doivent être exécutées pour tous les modules directement ou indirectement faisant référence à la table. Cette procédure stockée peut être appelée sur ces modules dans n’importe quel ordre, sans exiger que l’utilisateur actualise d’abord le module interne avant de passer à ses appelants.

sp_refresh_parameter_encryption n’affecte aucune autorisation, propriétés étendues ou SET options associées à l’objet.

Pour actualiser un déclencheur DDL au niveau du serveur, exécutez cette procédure stockée à partir du contexte de toute base de données.

Note

Toutes les signatures associées à l’objet sont supprimées lorsque vous exécutez sp_refresh_parameter_encryption.

Autorisations

Nécessite ALTER l’autorisation sur le module et REFERENCES l’autorisation sur les types définis par l’utilisateur CLR et les collections de schémas XML référencées par l’objet.

Lorsque le module spécifié est un déclencheur DDL au niveau de la base de données, nécessite ALTER ANY DATABASE DDL TRIGGER une autorisation dans la base de données active.

Lorsque le module spécifié est un déclencheur DDL au niveau du serveur, nécessite CONTROL SERVER une autorisation.

Pour les modules définis avec la EXECUTE AS clause, IMPERSONATE l’autorisation est requise sur le principal spécifié. En règle générale, l’actualisation d’un objet ne modifie pas son EXECUTE AS principal, sauf si le module a été défini et EXECUTE AS USER que le nom d’utilisateur du principal est désormais résolu en un autre utilisateur qu’au moment de la création du module.

Examples

L’exemple suivant crée une table et une procédure référençant la table, configure Always Encrypted, puis illustre la modification de la table et l’exécution de la sp_refresh_parameter_encryption procédure.

Commencez par créer la table initiale et une procédure stockée référençant la table.

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

Configurez ensuite les clés 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

Enfin, nous remplacez la colonne SSN par la colonne chiffrée, puis exécute la sp_refresh_parameter_encryption procédure pour mettre à jour les composants 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

Voir aussi

Always Encrypted
Assistant Chiffrement intégral.