Comment est-ce que j'écris un triggersur à la valeur de hachage avant l'insertion?

Une façon consiste à utiliser un INSTEAD OF TRIGGER:

 CREATE TRIGGER dbo.HashSSN ON dbo.tablename INSTEAD OF INSERT AS BEGIN SET NOCOUNT ON; INSERT dbo.tablename(FirstName, LastName, SSN) SELECT FirstName, LastName, HASHBYTES('SHA1', SSN) FROM inserted; END GO 

Tables de conformité aux règles métier et de stockage intermédiaire

Une autre méthode consiste à ne pas insert dans la table finale mais à utiliser une table de transfert. La table de transfert est une sorte de table temporaire permanente qui n'a pas de contraintes, autorise les NULL , se trouve dans un schéma tel que import et est simplement un conteneur dans lequel une source de données externe peut déposer des données. Le concept est alors qu'un process métier avec une logique métier appropriée peut être configuré pour fonctionner sur datatables dans le conteneur.

Ceci est une sorte de couche de "nettoyage des données" où le hachage SSN peut être effectué, ainsi que d'autres process opérationnels ou des règles métier imposés comme la nullité ou les omissions autorisées, la capitalisation, les longueurs, le nommage, l'élimination des duplicates, la search de key, le changement notification, etc, puis enfin effectuer l'insertion. L'avantage est qu'un set de mauvaises données, au lieu d'avoir été tenté d'insert, d'être forcé de revenir en arrière, puis de faire sauter le process original, peut être détecté, préservé intact sans perte et finalement être manipulé correctement (comme être déplacé à une file d'erreurs, des notifications envoyées, etc.).

Beaucoup de gens utiliseraient SSIS pour des tâches comme celle-ci, mais je trouve très difficile de travailler avec SSIS car il présente des problèmes de fragilité, de difficultés d'utilisation des SP, de défis de deployment, de sauvegarde de bases de données et autres.

Si un tel schéma vous semble trop compliqué pour que vous ne le considériez même pas, revenez un instant en arrière et réfléchissez-y: vous avez un process externe qui est censé être inséré correctement, exact, lavé, et certainement connu données dans une table. Mais, ça ne fait pas ça. Au lieu de cela, il insère des données qui ne sont pas conforms aux règles métier. Je pense que cliquer sur un triggersur pourrait être une façon de le gérer, mais c'est aussi une opportunité pour vous de réfléchir davantage à l'architecture du système et d'explorer pourquoi vous avez ce problème en premier lieu.

Comment pensez-vous que datatables non approuvées ou non conforms aux règles métier doivent être conforms aux règles de security et de confiance? Où les tâches de transformation telles que le hachage d'une colonne SSN appartiennent-elles?

Le process d'insertion devrait-il être au courant de ces règles commerciales? Si oui, est-ce cohérent à travers l'organisation, l'architecture, le type de process que l'inséreuse est? Si ce n'est pas le cas, comment allez-vous répondre à cette question afin de ne pas mettre de correctifs sur les correctifs de kluges?

L'insecurity d'un Hash SSN

De plus, j'aimerais signaler quelque chose d'autre. Il n'y a qu'environ 889 millions de SSN possibles (888.931.098) s'il n'y a pas de NIF. Combien de time pensez-vous qu'il faudrait parcourir tous et comparer le hachis à ceux de votre table? Hashing réduit certainement l'exposition rapide – vous ne pouvez pas lire le SSN extrêmement facilement. Mais étant donné que cela ne prend qu'un milliard d'essais, c'est une question de jours ou même d'heures pour les faire éclater, en fonction des ressources et de la planification.

Une table arc-en-ciel avec tous les SSN et leurs hachages SHA1 ne prendrait que de l'ordre de 25-30 Go – tout à fait réalisable même sur un ordinateur domestique relativement peu coûteux, où une fois créé il permettrait de sauter n'importe quel SSN en une fraction de seconde. Même en utilisant un hachage plus long ou plus coûteux en calcul, cela ne va pas beaucoup aider. En quelques jours ou quelques semaines, une table arc-en-ciel peut être construite. Quelques centaines de dollars peuvent acheter plusieurs téraoctets de stockage de nos jours.

Vous pouvez saler le hash du SSN, ce qui signifie que si quelqu'un lance une fissure de force brute contre votre table, il devra le faire une fois pour chaque rangée plutôt que d'être capable d'get toutes les lignes à la fois. C'est certainement mieux, mais cela ne fait que retarder l'inévitable. Un hacker sérieux a probablement une armée de bot qui le soutient et qui peut casser un simple sel SSN + en quelques secondes.

D'autres pensées

Je serais intéressé par les règles métier qui exigent d'une part de pouvoir vérifier les SSN et de les utiliser comme un type de mot de passe, mais d'autre part ne vous permettant pas de stocker les valeurs complètes. Avez-vous des problèmes de security concernant votre database? Maintenant que vous avez mis à jour votre question pour dire que ce sont des employés, mes questions sur la raison pour laquelle l'exclusion des non-détenteurs de SSN est sans object. Cependant, je suis toujours curieux de savoir pourquoi vous devez hacher les valeurs et ne pouvez pas simplement les stocker. Ce n'est pas seulement bien, mais il est nécessaire pour un employeur d'avoir les SSN de ses employés afin qu'il puisse déclarer les gains et les déductions au gouvernement.

Si d'un autre côté, votre préoccupation ne porte pas vraiment sur la security mais plutôt sur la dénégation («votre SSN n'est jamais stocké sur nos servers!») Alors ce n'est pas vraiment vrai, n'est-ce pas? Tout ce que vous avez fait est de le transformer d'une manière qui peut être inversée par la force brute, et l'espace de search est suffisamment petit pour que la force brute soit tout à fait raisonnable. Si quelqu'un vous donne le nombre 42, et multipliez-le par 2 et économisez 84, dites à la personne que son numéro n'a pas été stocké, mais vous pouvez simplement split 84 par 2 pour get le numéro d'origine, alors vous n'êtes pas vraiment complètement simple.

Certes, le hachage "unidirectionnel" est beaucoup plus difficile à inverser que multiplier, mais nous ne traitons pas un problème comme "find le document original de 200 mille caractères (ou autre) de son hash" mais "find un 9 numbers nombre de son hash ". Bien sûr, de nombreuses inputs différentes vont hachage aux mêmes valeurs qu'un SSN particulier, mais je doute qu'il y ait de très nombreuses collisions de strings de 9 caractères composées exclusivement de numbers numériques.

Test d'inversion de hachage SHA-1 SSN réel

Je viens de faire quelques tests. J'ai une table avec environ 3200 vrais SSN dedans. Je les ai hachés en utilisant SHA1 et j'ai placé ces hachages dans une table temporaire contenant juste une colonne. J'ai été en mesure de faire apparaître 1% des SSN en environ 8 minutes à la search de 001-01-0001 . En fonction de la vitesse de traitement et de l'espace total de search, cela sera fait en less de 3 heures (cela prend ~ 2 minutes par 10 millions de SSN, donc 88.89 * 2 minutes). Et c'est de l' intérieur de SQL Server, pas en cours d'exécution d'un programme compilé qui pourrait être beaucoup, beaucoup plus rapide. Ce n'est pas très sécurisé!