[2k5] HASHBYTES() 사용한 암호화

1. /**********************************************************************************************
   -- Title : [2k5] HASHBYTES() 사용한 암호화
   -- Reference : feelanet.com
   -- Key word : 암호화 해쉬바이트 hashbytes 해쉬 hash
   **********************************************************************************************/
   USE master;
   GO
   
   DROP DATABASE Hashbytedb;
   GO
   
   CREATE DATABASE Hashbytedb;
   GO
   
   USE Hashbytedb;
   GO
   
   DROP TABLE contact;
   GO
   
   --adventureworks.person.contact 테이블 셈플링
   SELECT
    ContactID, NameStyle, Title, FirstName, MiddleName, --nvarchar
    LastName, CAST ('' AS VARBINARY(512)) AS LastNameEncrypt,
    Suffix,
    EmailAddress,
    CAST ('' AS VARBINARY(512)) AS EmailCert,
    CAST ('' AS VARBINARY(512)) AS EmailSym,
    CAST ('' AS VARBINARY(512)) AS EmailAsym,
    CAST ('' AS VARBINARY(512)) AS EmailPassPh,
    EmailPromotion, Phone, PasswordHash, PasswordSalt, rowguid, ModifiedDate
   FROM adventureWorks.Person.Contact;
   GO
   
   --adventureworks.person.contact를 Hashbytedb..contact로 생성
   SELECT
    ContactID, NameStyle, Title, FirstName, MiddleName, --nvarchar
    LastName, CAST ('' AS VARBINARY(512)) AS LastNameEncrypt,
    Suffix,
    EmailAddress,
    CAST ('' AS VARBINARY(512)) AS EmailCert,
    CAST ('' AS VARBINARY(512)) AS EmailSym,
    CAST ('' AS VARBINARY(512)) AS EmailAsym,
    CAST ('' AS VARBINARY(512)) AS EmailPassPh,
    EmailPromotion, Phone, PasswordHash, PasswordSalt, rowguid, ModifiedDate
   INTO contact
   FROM adventureWorks.Person.Contact;
   GO
   
   EXEC SP_SPACEUSED contact;
   /*
   name        rows        reserved           data               index_size         unused
   ----------- ----------- ------------------ ------------------ ------------------ ------------------
   contact     19972       4688 KB            4664 KB            8 KB               16 KB
   */
   GO
   
   SELECT TOP 10 * FROM contact;
   GO
   
   SELECT page_count
   FROM sys.dm_db_index_physical_stats(db_id(), object_id('contact'), NULL, NULL, DEFAULT);
   --583 => 583 * 8 = sp_spaceused.data
   GO
   
   --해서 적용 셈플(::= MD2 | MD4 | MD5 | SHA | SHA1)
   SELECT TOP 100 LastName, HashBytes('MD5', LastName), HashBytes('SHA1', LastName)
   FROM contact;
   
   UPDATE contact
   SET LastNameEncrypt = HashBytes('SHA1', LastName);
   GO
   
   ALTER INDEX ALL ON contact REBUILD;
   
   SELECT page_count
   FROM sys.dm_db_index_physical_stats(db_id(), object_id('contact'), NULL, NULL, DEFAULT)
   --638
   GO
   
   CREATE INDEX ix ON contact (LastNameEncrypt);
   GO
   
   UPDATE contact
   SET lastname = 'Rhee',  lastnameEncrypt = HashBytes('SHA1', N'Rhee')
   WHERE ContactID = 3;
   
   -- 유니코드 및 대소문자 구분 필요
   SELECT * FROM contact WHERE ContactID = 3;
   SELECT HashBytes('SHA1', N'Rhee');
   SELECT HashBytes('SHA1', N'RHEE');
   SELECT HashBytes('SHA1', 'Rhee');
   GO
   
   SELECT * FROM contact WHERE lastnameEncrypt = HashBytes('SHA1', 'RHEE');
   SELECT * FROM contact WHERE lastnameEncrypt = HashBytes('SHA1', 'Rhee');
   SELECT * FROM contact WHERE lastnameEncrypt = HashBytes('SHA1', N'Rhee');
   GO
   
   SELECT TOP 100 LastName, HashBytes('SHA1', LastName), lastnameEncrypt
   FROM contact;
   
   SELECT * FROM contact
   WHERE lastnameEncrypt = HashBytes('SHA1', N'Acevedo');
   GO