Discussion :

[new_wave]

Bonjour,

Je me permets de revenir sur le cours lié à la création d'une clé de cryptage de Mr Brouard.

Quand on crée une clé de cryptage ainsi

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Le petit chat est mort.';
GO

Si je comprends bien, Le petit chat est mort est le mot de passe qui permet de proteger la clé symétrique créée .Comment est-il utilisé?


Ensuite, si vous choisissez de créer une clé de chiffrement symétrique avec la syntaxe suivante :CREATE SYMMETRIC KEY MA_CLEF
WITH ALGORITHM = AES_256
ENCRYPTION BY PASSWORD = 'P@ssw0rd !';
Si j'ai bien compris, le mot de passe 'P@ssw0rd ! permet de protéger la clé symétrique nouvellement créée.
Mais comment l'utiliser également ?

Pour finir, si je choisis d'ouvrir la clé symétrique dans un trigger de type logon, je lis que la syntaxe pour cela est la suivante

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
 
 
CREATE TRIGGER TR_open_key
 
ON ALL SERVER 
FOR LOGON
AS
BEGIN

Est-il possible, au lieu de specifier ON ALL SERVER, de ne spécifier le nom que d'un seul serveur ?

Vous remerciant beaucoup de votre aide sur ces différents points,

Bien cordialement.

new_wave

[SQLpro]

Pour utiliser une clef symétrique il faut l'ouvrir.
OPEN SYMMETRIC KEY...

Un trigger LOGON étant contenu dans le serveur on ne peut pas préciser son nom qui est implicite.

A +

[new_wave]

Bonjour,

Je vous remercie de votre réponse.
Voici ce que j'ai fait.
J'ai crée un champ username de type nvarchar(100) et un champ password de type nvarchar(100) dans la table Forum_Registration.

Par le biais de l'interface graphique je saisis le username + le password.
Une requete sql INSERT insère bien dans la base de données les données voulues pour ce username +password(non crypté).

J'ai executé auparavant une première et unique fois le procédure de création de la clé symétrique procedure_creation_cle_symétrique dont voici le code

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
 
CREATE SYMMETRIC KEY MA_CLEF
WITH ALGORITHM = AES_256
ENCRYPTION BY PASSWORD = 'P@ssw0rd !';
return 0
GO

Or le password stocké n'est pas crypté ; c'est à ce moment que j'exécute le code

OPEN SYMMETRIC KEY MA_CLEF
DECRYPTION BY PASSWORD = 'P@ssw0rd !';

et UPDATE Forum_Registration

SET password = ENCRYPTBYKEY(KEY_GUID('MA_CLEF'), password);

Le resultat obtenu est une valeur null à la place du password non crypté.
Je pense (peut être que je me trompe et que ce n'est pas l'unique raison) , que cela provient du type de données:le pb est que si je choisis un type varbinary pour le type du password, je ne pourrai pas le saisir via l'interface graphique qui accepte des données de type nvarchar.

Je vous remercie beaucoup de bien vouloir me conseiller à ce sujet pour résoudre ce problème .

Bien cordialement.

new_wave

[SQLpro]

À partir du moment ou vous chiffrez des données, celle-ci sont systématiquement transformées en binaire (ce dernier étant généralement plus long que la données originale en nombre d'octets) car vous pouvez chiffrer n'importe quelle type de données et la notion de type même disparait.
Votre colonne doit donc être purement binaire.

Si vous voulez que ceci soit transparent pour l'utilisateur, procédez en utilisant des vues SQL :
1) créez une vue qui déchiffre et expose les données en clair
2) créer un trigger INSTEAD OF INSERT/UPDATE qui lorsqu'il y a mise à jour du nom, chiffre les données

Démonstration :

1) la base et sa table :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
CREATE DATABASE DB_CRYPTO;
GO
 
USE DB_CRYPTO
GO
 
CREATE TABLE Forum_Registration
(username nvarchar(100) PRIMARY KEY,
 password varbinary(200));
GO

La clef :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
CREATE SYMMETRIC KEY MA_CLEF
WITH ALGORITHM = AES_256
ENCRYPTION BY PASSWORD = 'P@ssw0rd !';
GO

la vue qui servira pour les applications :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
CREATE VIEW V_Forum_Registration
AS
SELECT username,  
       CAST(DECRYPTBYKEY(password, KEY_GUID('MA_CLEF')) AS VARCHAR(100)) AS password
FROM   Forum_Registration;
GO

Le déclencheur qui gérera INSERT et UPDATE sur la vue :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE TRIGGER E_I_IU_Forum_Registration
ON V_Forum_Registration
INSTEAD OF INSERT, UPDATE
AS
IF NOT EXISTS(SELECT * FROM deleted)
   INSERT INTO Forum_Registration
   SELECT username, ENCRYPTBYKEY(KEY_GUID('MA_CLEF'), password)
   FROM   inserted;
ELSE
   UPDATE T
   SET    password = ENCRYPTBYKEY(KEY_GUID('MA_CLEF'), i.password)
   FROM   inserted AS i
          JOIN Forum_Registration AS T
		       ON i.username = T.username;
GO

A +

PS : je n'ai pas testé

En complément, lisez notre ouvrage :

[new_wave]

Merci beaucoup à vous.

Je teste et reviens vers vous pour vous dire ce que j'obtiens.

Bien cordialement.

new_wave

[new_wave]

Bonjour,

Je reviens vers vous concernant les différentes procédures proposées.

Tout d'abord, au lancement du script de création de la vue [V_forum_registration_test], j'obtiens ce message qui informe que le type du second argument de la fonction DecryptByKey est invalide.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
 
Creating [dbo].[V_forum_registration_test]...
(59,1): SQL72014: .Net SqlClient Data Provider: Msg 8116, Level 16, State 1, Procedure V_forum_registration_test, Line 2 Argument data type uniqueidentifier is invalid for argument 2 of DecryptByKey function.
(58,0): SQL72045: Script execution error.  The executed script:
CREATE VIEW [dbo].[V_forum_registration_test]

Ensuite, quand je saisis les données via l'interface graphique, je reçois un message d'erreur m'informant qu'il n'y a pas possibilité de convertir un string en []byte, ce que je conçois.
Est il nécessaire d'utiliser au niveau du code C# une fonction de conversion de string en bytes?

Vous remerciant beaucoup de votre réponse,

Bien cordialement.

new_wave

[SQLpro]

Je ne sais pas avec quel outil vous lancez vos scripts mais par SSMS cela passe très bien. Faites toutes vos créations d'objet avec SSMS sinon l'outil l va vous remonter des erreurs spécifique à l'outil et non des erreurs SQL. Tout ceci à parfaitement été créé chez moi via SSMS.

A +

[new_wave]

Merci de votre conseil.Je vais utiliser SSMS.

Bien cordialement.

new_wave

[new_wave]

Rebonjour,

En créant la vue, j'ai le même message "Msg 8116, Niveau 16, État 1, Procédure V_Forum_Registration, Ligne 3 [Ligne de départ du lot 0]
Argument data type uniqueidentifier is invalid for argument 2 of DecryptByKey function."

Avez-vous saisi des données dans la table Forum_Registration, ce qui n'est pas mon cas car les données saisies pour le champ password sont de type VarBinary et celle saisies au niveau de l'interface graphique sont de type NVarchar.


Vous remerciant encore de votre réponse,

Cordialement.

new_wave

[SQLpro]

Je n'ai pas SQL Server 2005 sous la main donc je ne peut pas faire les tests. mais effectivement il y a une erreur dans la création de ma (bé)vue

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
CREATE VIEW V_Forum_Registration
AS
SELECT username,  
       CAST(DECRYPTBYKEY(password) AS VARCHAR(100)) AS password
FROM   Forum_Registration;
GO

[new_wave]

Rebonjour,

La création de la vue s'est bien déroulée.

A présent , à la création du trigger les deux champs username et password sont soulignés avec le message "nom de colonne invalide".
ainsi que le message suivant :

Msg 213, Niveau 16, État 1, Procédure E_I_IU_Forum_Registration, Ligne 6 [Ligne de départ du lot 5]
Column name or number of supplied values does not match table definition.

Par ailleurs il est mentionné que l'objet V_Forum_Registration n'est pas valide ou n'existe pas pour cette opération.(je travaille bien avec Sql Serveur Management Studio)

J'ai remplacé son nom par [dbo].[V_Registration] mais sans changement de message.
Vous remerciant encore de bien vouloir m'aider à y a voir plus clair.

Bien cordialement.

new_wave

[SQLpro]

Tout passe chez moi.
Soit vous vous êtes trompé de base et avez fait cela dans master au lieu de la base cible, soit vous utilisez une base en mode de rétrocompatibilité 2000 ou 7 qui accepte pas quelque chose dans la syntaxe !

A -

[new_wave]

Bonjour,

Je vous remercie de vos conseils.
J'ai recrée ma base de données avec Management Studio et j'ai pu exécuter toutes les requêtes sans problème(même s'il reste quelques champs soulignés je ne sais pourquoi).


Maintenant, la situation est la suivante :puisque les données saisies via l'interface graphique pour le champ password sont de type varchar (ex: si je saisis un mdp ), alors que le type de stockage du password est varbinary(500),

Quand on crée la vue V_Forum_Registration, il y a un cast du password vers le type VARCHAR(100) des données qui sont normalement saisies dans la base au type VarBinay(500).
Or ce n'est pas le cas puisque les données sont saisies via l'interface graphique au type VARCHAR et non pas au type Varbinay .
Je reçois en effet le message suivant à la saisie

Une erreur est survenue Échec de la conversion de la valeur de paramètre d'un String en un Byte[].Son détail est le suivant à System.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType destinationType, Boolean& coercedToDataFeed, Boolean& typeChanged, Boolean allowStreaming)

Voici le code C# pour l'insertion en BDD

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
 
 protected void Btn_Registration_Click(object sender, EventArgs e)
        {
            try
            {
 
                using (SqlConnection connexion = new SqlConnection(ConnectionStrings.FrCon))
                {
 
                    sqlquery = "Insert into Forum_Registration values (@username , @password)";
 
                    sqlCommand = new SqlCommand(sqlquery, connexion);
 
 
 
                    //création et déclaration des paramètres @username, @password,@fullname,@rbl_gender,@txtdob,@emailid
                    sqlCommand.Parameters.Add(new SqlParameter("@username", SqlDbType.NVarChar));
                    //insérer un message de saisie selon une expression régulière 
 
                    sqlCommand.Parameters.Add(new SqlParameter("@password", SqlDbType.VarBinary));
 
 
                    //attribution de valeur aux paramètres 
                    sqlCommand.Parameters["@username"].Value = txtusername.Text;
 
 
                    sqlCommand.Parameters["@password"].Value = txtpassword.Text;
 
                   //ouverture de la connexion 
                    connexion.Open();
 
                    sqlCommand.ExecuteNonQuery();
 
                }//fin de using

Je vous remercie encore de bien vouloir m'aider sur ce point, car j'aimerais bien débloquer cette situation pour que soit bien crypté le mot de passe.

Bien cordialement.

new_wave

[SQLpro]

C'est un problème applicatif, par SQL.
1) vous devez utiliser la vue et uniquement la vue pour présenter les données et pour toutes les mises à, jour.
2) vous devez faire en sorte que votre code accepte la mise à jour des vues, ce qui n'est généralement pas le cas par défaut dans les objets qui réceptionne les vues.

En bref, tournez vous vers le forum adéquat relatif à votre langage.

A +

[new_wave]

Merci de votre réponse.

J'ai émis une demande sur le forum C# correspondante.

Bien cordialement.

new_wave

[new_wave]

Bonjour,

J'ai bien trouvé le code qui permet le cast d'un string saisi en C# au niveau du champ password

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
 byte[] txtpassword_encode = System.Text.Encoding.UTF8.GetBytes(" txtpassword.Text");

Du fait qu'ensuite je fais , dans le code une requete sql INSERT

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
 
 sqlquery = "Insert into Forum_Registration values (@username , @password, @fullname , @rbl_gender, @txtdob,  @emailid)";
 
 sqlCommand.Parameters.Add(new SqlParameter("@password", SqlDbType.VarBinary));
 
byte[] txtpassword_encode = System.Text.Encoding.UTF8.GetBytes(" txtpassword.Text");
 
sqlCommand.Parameters["@password"].Value = txtpassword_encode;

Quand je regarde au niveau de la vue les données qui sont sélectionnées je vois bien le username, mais au niveau du password j'obtiens une valeur null.
Est-ce normal?

Pourquoi n'y a -t-il pas eu conversion du type du password en varchar (100).
Je précise que dans le champ password je vois bien le password encodé car la fonction UTF8.GetBytes a permis cet encodage.

Ensuite, au niveau du trigger , puisqu'au niveau de la vue il n'y a pas de cast du password en varchar(100), il ne peut y avoir de cryptage en varbinary.
Pouvez-vous m'expliquer pourquoi au niveau de la vue je ne récupère pas le password en varchar(100).

Merci beaucoup encore à vous de votre aide.

Bien cordialement.

new_wave