sp_refresh_parameter_encryption (Transact-SQL)

Article
07/12/2024

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, 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és dans la base de données actuelle.

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' est le nom de la procédure stocké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. 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é à un schéma. module_name est nvarchar, sans valeur par défaut. module_name peut être un identifiant en plusieurs parties, mais il ne peut faire référence qu'à des objets de la base de données actuelle.

[ @namespace = ] ' < class > ' est la classe du module spécifié. Lorsque module_name est un déclencheur DDL, <class> est requis. <class> est 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)

Notes

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 dans une table à laquelle le module fait référence ont été mises à jour. Par exemple, une colonne a été supprimée et une nouvelle colonne portant le même nom, mais avec un type de chiffrement, une clé de chiffrement ou un algorithme de chiffrement différent a été ajoutée.
Le module fait référence à un autre module dont les métadonnées de chiffrement des paramètres sont obsolètes.

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

sp_refresh_parameter_encryption n’affecte aucune autorisation, propriétés étendues ou les options SETassocié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.

Remarque

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

autorisations

Requiert l'autorisation ALTER sur le module ainsi que l'autorisation REFERENCES sur les types CLR (Common Language Runtime) définis par l'utilisateur et sur les collections de schémas XML référencés par l'objet.

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

Lorsque le module spécifié est un déclencheur DDL au niveau du serveur, l'autorisation CONTROL SERVER est requise.

Pour les modules définis à l'aide de la clause EXECUTE AS, l'autorisation IMPERSONATE est nécessaire sur le principal spécifié. Généralement, l'actualisation d'un objet ne modifie pas son principal EXECUTE AS, sauf si le module a été défini avec EXECUTE AS USER et que le nom d'utilisateur du principal correspond désormais à un utilisateur différent de celui qu'il était au moment de la création du module.

Exemples

L'exemple suivant crée une table et une procédure faisant référence à la table, configure l'option Always Encrypted, puis démontre la modification de la table et l'exécution de la procédure sp_refresh_parameter_encryption.

Créez d'abord la table initiale et une procédure stockée faisant référence à 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 remplaçons la colonne SSN par la colonne chiffrée, puis nous exécutons la procédure sp_refresh_parameter_encryption 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.

Partager via