《数据库加密》由会员分享，可在线阅读，更多相关《数据库加密（11页珍藏版）》请在金锄头文库上搜索。
1、USE TestDBGOCREATE TABLE #SecretInfo(Secret varbinary(8000) not null)GOINSERT #SecretInfo(Secret)SELECT ENCRYPTBYPASSPHRASE(Sun qi.,This is the text I need to seure.)SELECT SecretFROM #SecretInfoSELECT CAST(DecryptByPassPhrase(Sun qi.,Secret) as varchar(50)FROM #SecretInfoINSERT #SecretInfo(Secret)S
2、ELECT ENCRYPTBYPASSPHRASE(My Password Used To Encrypt This String in 2008.,Haha! Hello,World.)SELECT CAST(DecryptByPassPhrase(My Password Used To Encrypt This String in 2008.,Secret) as varchar(50)FROM #SecretInfoDELETE #SecretInfoWHERE 1=1DROP TABLE #SecretInfoUSE AdventureWorks;GO- Create a column
3、 in which to store the encrypted data.ALTER TABLE Sales.CreditCard ADD CardNumber_EncryptedbyPassphrase varbinary(256); GOSELECT *FROM Sales.CreditCardWHERE CreditCardID=3681- First get the passphrase from the user.DECLARE PassphraseEnteredByUser nvarchar(128);SET PassphraseEnteredByUser = A little
4、learning is a dangerous thing!;- Update the record for the users credit card.- In this case, the record is number 3681.UPDATE Sales.CreditCardSET CardNumber_EncryptedbyPassphrase = EncryptByPassPhrase(PassphraseEnteredByUser, CardNumber, 1, CONVERT( varbinary, CreditCardID)WHERE CreditCardID = 3681;
5、GOUSE BookStoreGOCREATE TABLE #INFO(NumberID int primary key not null,Content varbinary(256) not null)GOSELECT *FROM #INFOINSERT #INFO(NumberID,Content)values (1,ENCRYPTBYPASSPHRASE(deng wei.,Hello World!,1,CONVERT(varbinary,222)SELECT NumberID,cast(DECRYPTBYPASSPHRASE(deng wei.,Content,1,CONVERT(va
6、rbinary,222) as varchar(100)FROM #INFOBACKUP SERVICE MASTER KEY TO FILE=C:ApressSMK.bakENCRYPTION BY password=MakeItAGoodOne!1ABRESTORE SERVICE MASTER KEY FROM FILE=C:ApressSMK.bakDECRYPTION BY password=MakeItAGoodOne!1ABIF NOT EXISTS(SELECT name FROM sys.databases WHERE name=BookStore)BEGIN CREATE
7、DATABASE BookStoreENDGOUSE BookStoreGOCREATE MASTER KEY ENCRYPTION BY password=99a555ac-cf60-472b-9cle-ed735ffbb089USE BookStoreGOALTER MASTER KEY REGENERATE WITH ENCRYPTION BY password=uglypassword1C3ED8CFDROP MASTER KEYCREATE MASTER KEY ENCRYPTION BY password=MagneticFields!GOBACKUP MASTER KEY TO
8、FILE=C:ApressBookStroe_Master_Key.BAKENCRYPTION BY password=4D280837!RESTORE MASTER KEY FROM FILE=C:ApressBookStroe_Master_Key.BAKDECRYPTION BY PASSWORD=4D280837!ENCRYPTION BY password=MagneticFields!USE BookStoreGOALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEYOPEN MASTER KEY DECRYPTION BY p
9、assword=MagneticFields!ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEYCLOSE MASTER KEYUSE BookStoreGOCREATE ASYMMETRIC KEY asymBookSellerKeyWITH ALGORITHM=RSA_512ENCRYPTION BY password=EEB0B4DD!CREATE ASYMMETRIC KEY asymBookSellerKey1WITH ALGORITHM=RSA_512SELECT name,algorithm_desc,pvt_key_enc
10、ryption_typeFROM sys.asymmetric_keysALTER ASYMMETRIC KEY asymBookSellerKeyWITH PRIVATE key(ENCRYPTION BY PASSWORD=newpasswordE4D352F2800E0,DECRYPTION BY PASSWORD=EEB0B4DD!)CREATE TABLE dbo.BookSellerBankRouting(BookSellerID int not null primary key,BankRountingNBR varbinary(300) not null)INSERT dbo.
11、BookSellerBankRouting(BookSellerID,BankRountingNBR)values(22,ENCRYPTBYASYMKEY(AsymKey_ID(asymBookSellerKey),137492837583249ABR)SELECT BankRountingNBRFROM dbo.BookSellerBankRoutingSELECT CAST(BankRountingNBR as varchar(100) BankRountingNBRFROM dbo.BookSellerBankRoutingWHERE BookSellerID=22SELECT CAST
12、(DECRYPTBYASYMKEY(AsymKey_ID(asymBookSellerKey),BankRountingNBR,NnewpasswordE4D352F2800E0) as varchar(100) BankRountingNBRFROM dbo.BookSellerBankRoutingWHERE BookSellerID=22DROP ASYMMETRIC KEY asymBookSellerKeydrop table #InfoCREATE TABLE #Info(NumberID int primary key not null,Content varbinary(256
13、) not null)GOCREATE ASYMMETRIC KEY asymBookInfokey2WITH ALGORITHM=RSA_512ENCRYPTION BY password=FUCK YOU!INSERT #Info(NumberID,Content)values(1,cast(ENCRYPTBYASYMKEY(asymkey_id(asymBookInfokey2),13554697621) as varbinary(256)SELECT CAST(Content as varchar(50)FROM #InfoINSERT #Info(NumberID,Content)v
14、alues(2,CAST(ENCRYPTBYASYMKEY(asymkey_id(asymBookInfokey2),15807139752) as varbinary(256)SELECT NumberID,CAST(DECRYPTBYASYMKEY(asymkey_id(asymBookInfokey2),Content,NFUCK YOU!) as varchar(50)FROM #InfoCREATE TABLE #Info1(NumberID int primary key not null,Content varbinary(256) not null)GOCREATE PROCE
15、DURE dbo.InsertInfo1(NumberID int,Content varbinary(256)asBEGIN TRYINSERT #Info1(NumberID,Content)values(NumberID,Content)END TRYBEGIN CATCHROLLBACK TRANPRINT INSERT IS FAILED!END CATCHGOdrop procedure InsertInfo1EXEC dbo.InsertInfo1 3, CONVERT(varbinary(256),N15527009595)SELECT CONVERT(varbinary(25
16、6),N15527009595)USE BookStoreGOCREATE ASYMMETRIC KEY asymBookSellerKeyWITH ALGORITHM=RSA_512ENCRYPTION BY PASSWORD=EEB0B4DD!CREATE SYMMETRIC KEY sym_BookStoreWITH ALGORITHM=TRIPLE_DESENCRYPTION BY ASYMMETRIC KEY asymBookSellerKeySELECT name,algorithm_descFROM sys.symmetric_keysSELECT name,algorithm_desc,pvt_key_encryption_typeFROM
