GO
CREATE TABLE #SecretInfo
(Secret varbinary(8000) not null)
GO
INSERT #SecretInfo
(Secret)
演讲技巧六种方法
SELECT ENCRYPTBYPASSPHRASE('Sun qi.',
'This is the text I need to ure.')
SELECT Secret
FROM #SecretInfo
SELECT CAST(DecryptByPassPhra('Sun qi.',Secret) as varchar(50))
FROM #SecretInfo
INSERT #SecretInfo
(Secret)
SELECT ENCRYPTBYPASSPHRASE('My Password Ud To Encrypt This String in 2008.',
'Haha! Hello,World.')
SELECT CAST(DecryptByPassPhra('My Password Ud To Encrypt This String in 2008.',Secret) as varchar(50))
FROM #SecretInfo
DELETE #SecretInfo
WHERE 1=1
DROP TABLE #SecretInfo
USE AdventureWorks;
GO
-- Create a column in which to store the encrypted data.
ALTER TABLE Sales.CreditCard
ADD CardNumber_EncryptedbyPassphra varbinary(256);
GO
SELECT *
FROM Sales.CreditCard
WHERE CreditCardID=3681
-- First get the passphra from the ur.
DECLARE @PassphraEnteredByUr nvarchar(128);
SET @PassphraEnteredByUr
= 'A little learning is a dangerous thing!';
-- Update the record for the ur's credit card.
-- In this ca, the record is number 3681.
UPDATE Sales.CreditCard颛顼是谁
SET CardNumber_EncryptedbyPassphra = EncryptByPassPhra(@PassphraEnteredByUr
, CardNumber, 1, CONVERT( varbinary, CreditCardID))
WHERE CreditCardID = '3681';
GO
USE BookStore
GO
CREATE TABLE #INFO
(NumberID int primary key not null,
Content varbinary(256) not null)
GO
SELECT *
FROM #INFO
INSERT #INFO
(NumberID,Content)
values (1,ENCRYPTBYPASSPHRASE('deng wei.','Hello World!',1,CONVERT(varbinary,222)))
SELECT NumberID,cast(DECRYPTBYPASSPHRASE('deng wei.',Content,1,CONVERT(varbinary,222)) as varchar(100))
FROM #INFO
BACKUP SERVICE MASTER KEY TO FILE='C:\Apress\SMK.bak'
ENCRYPTION BY password='MakeItAGoodOne!1AB'
RESTORE SERVICE MASTER KEY FROM FILE='C:\Apress\SMK.bak'
DECRYPTION BY password='MakeItAGoodOne!1AB'
IF NOT EXISTS(SELECT name FROM sys.databas WHERE name='BookStore')
BEGIN 别人帮助我的作文
CREATE DATABASE BookStore
END
GO
USE BookStore
GO
CREATE MASTER KEY
ENCRYPTION BY password='99a555ac-cf60-472b-9cle-ed735ffbb089'
USE BookStore
GO
ALTER MASTER KEY
REGENERATE WITH ENCRYPTION BY password='uglypassword1C3ED8CF'
DROP MASTER KEY
CREATE MASTER KEY ENCRYPTION BY password='MagneticFields!'
GO
BACKUP MASTER KEY TO FILE='C:\Apress\BookStroe_Master_Key.BAK'
ENCRYPTION BY password='4D280837!!!'
RESTORE MASTER KEY FROM FILE='C:\Apress\BookStroe_Master_Key.BAK'
DECRYPTION BY PASSWORD='4D280837!!!'
ENCRYPTION BY password='MagneticFields!'
USE BookStore
GO
ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY
OPEN MASTER KEY DECRYPTION BY password='MagneticFields!'
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
CLOSE MASTER KEY
USE BookStore
GO
CREATE ASYMMETRIC KEY asymBookSellerKey
WITH ALGORITHM=RSA_512
ENCRYPTION BY password='EEB0B4DD!!!'
CREATE ASYMMETRIC KEY asymBookSellerKey1
WITH ALGORITHM=RSA_512
SELECT name,algorithm_desc,pvt_key_encryption_type
FROM sys.asymmetric_keys
ALTER ASYMMETRIC KEY asymBookSellerKey
WITH 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.BookSellerBankRouting
(BookSellerID,BankRountingNBR)
values(22,ENCRYPTBYASYMKEY(AsymKey_ID('asymBookSellerKey'),'137492837583249ABR'))
SELECT BankRountingNBR
FROM dbo.BookSellerBankRouting
SELECT CAST(BankRountingNBR as varchar(100)) BankRountingNBR
FROM dbo.BookSellerBankRouting
WHERE BookSellerID=22
SELECT CAST(DECRYPTBYASYMKEY(AsymKey_ID('asymBookSellerKey'),BankRountingNBR,N'n
ewpasswordE4D352F2800E0') as varchar(100)) BankRountingNBR
FROM dbo.BookSellerBankRouting
WHERE BookSellerID=22
DROP ASYMMETRIC KEY asymBookSellerKey
drop table #Info
CREATE TABLE #Info
(NumberID int primary key not null,
Content varbinary(256) not null)
GO
CREATE ASYMMETRIC KEY asymBookInfokey2
WITH ALGORITHM=RSA_512
ENCRYPTION BY password='FUCK YOU!!!'
INSERT #Info
(NumberID,Content)
values(1,cast(ENCRYPTBYASYMKEY(asymkey_id('asymBookInfokey2'),'135********') as varbinary(256)))
SELECT CAST(Content as varchar(50))
FROM #Info
INSERT #Info
(NumberID,Content)
values(2,CAST(ENCRYPTBYASYMKEY(asymkey_id('asymBookInfokey2'),'158********') as varbinary(256)))
SELECT NumberID,CAST(DECRYPTBYASYMKEY(asymkey_id('asymBookInfokey2'),Content,N'FU
CK YOU!!!') as varchar(50))
FROM #Info
CREATE TABLE #Info1
(NumberID int primary key not null,
Content varbinary(256) not null)
GO
CREATE PROCEDURE dbo.InrtInfo1
(@NumberID int,@Content varbinary(256))
as
BEGIN TRY
INSERT #Info1
(NumberID,Content)
values(@NumberID,@Content)
END TRY
BEGIN CATCH
ROLLBACK TRAN
PRINT 'INSERT IS FAILED!!!'
END CATCH
GO
drop procedure InrtInfo1
EXEC dbo.InrtInfo1 3, CONVERT(varbinary(256),N'155********')
SELECT CONVERT(varbinary(256),N'155********')
USE BookStore
四十岁
GO
CREATE ASYMMETRIC KEY asymBookSellerKey
WITH ALGORITHM=RSA_512
ENCRYPTION BY PASSWORD='EEB0B4DD!!!'
CREATE SYMMETRIC KEY sym_BookStore
WITH ALGORITHM=TRIPLE_DES
ENCRYPTION BY ASYMMETRIC KEY asymBookSellerKey
SELECT name,algorithm_desc
FROM sys.symmetric_keys
SELECT name,algorithm_desc,pvt_key_encryption_type
FROM sys.asymmetric_keys
OPEN SYMMETRIC KEY sym_BookStore
DECRYPTION BY ASYMMETRIC KEY asymBookSellerKey
WITH password='EEB0B4DD!!!'
ALTER SYMMETRIC KEY sym_BookStore
ADD ENCRYPTION BY password='hushhush!123'
ALTER SYMMETRIC KEY sym_BookStore
DROP ENCRYPTION BY ASYMMETRIC KEY asymBookSellerKey
CLOSE SYMMETRIC KEY sym_BookStore
USE BookStore
GO
CREATE TABLE dbo.PasswordHint
(CustomerID int not null prim
ary key,
PasswordHintQuestion varchar(300) not null,
PasswordHintAnswer varbinary(200) not null)
GO
OPEN SYMMETRIC KEY sym_BookStore
DECRYPTION BY PASSWORD='hushhush!123'
INSERT dbo.PasswordHint
(CustomerID,PasswordHintQuestion,PasswordHintAnswer)
values
(23,'What is the name of the hospital you were born in?',
ENCRYPTBYKEY(Key_GUID('sym_BookStore'),'Mount Marie'))
CLOSE SYMMETRIC KEY sym_BookStore
SELECT *
FROM dbo.PasswordHint
OPEN SYMMETRIC KEY sym_BookStore
DECRYPTION BY PASSWORD='hushhush!123'
SELECT CAST(DecryptBykey(PasswordHintAnswer) as varchar(200)) PasswordHintAnswer
FROM dbo.PasswordHint
CLOSE SYMMETRIC KEY sym_BookStore
CREATE TABLE dbo.BookSellerLogins
(LoginID int not null primary key,
Password varbinary(256) not null)
GO
INSERT dbo.BookSellerLogins
(LoginID,Password)
values(22,CAST('myeaspassword' as varbinary))
SELECT CAST(Password as CHAR(100))
FROM dbo.BookSellerLogins
OPEN SYMMETRIC KEY sym_BookStore
DECRYPTION BY PASSWORD='hushhush!123'
UPDATE dbo.BookSellerLogins
SET Password=ENCRYPTBYKEY(key_GUID('sym_BookStore'),Password,1,CAST(LoginID as varbinary))
CLOSE symmetric key sym_BookStore
SELECT *
FROM dbo.BookSellerLogins
OPEN SYMMETRIC KEY sym_BookStore
DECRYPTION BY PASSWORD='hushhush!123'
SELECT LoginID,CAST(DECRYPTBYKEY(Password,1,Cast(LoginID as varbinary)) as CHAR(50)) Password
FROM dbo.BookSellerLogins
CLOSE symmetric key sym_BookStore
DROP SYMMETRIC KEY sym_BookStore邵东创新实验学校
USE BookStore
GO
CREATE CERTIFICATE cert_BookStore
ENCRYPTION BY PASSWORD='AA5FA6AC!!!'
WITH SUBJECT='BookStore Databa Encryption Certificate',
START_DATE='12/08/2010',EXPIRY_DATE='5/20/2011'
SELECT name,pvt_key_encryption_type,issuer_name
ificates
BACKUP CERTIFICATE cert_BookStore
TO FILE='C:\Apress\certBookStore.bak'
WITH PRIVATE KEY (FILE='C:\Apress\certBookStorePK.bak',
ENCRYPTION BY PASSWORD='3439F6A!!!',
DECRYPTION BY PASSWORD='AA5FA6AC!!!')
DROP CERTIFICATE cert_BookStore
CREATE CERTIFICATE cert_BookStore
FROM FILE='C:\Apress\certBookStore.bak'
WITH PRIVATE KEY (FILE='C:\Apress\certBookStorePK.bak',
DECRYPTION BY PASSWORD='3439F6A!!!',
ENCRYPTION BY PASSWORD='AA5FA6AC!!!')
ALTER CERTIFICATE cert_BookStore
REMOVE private key
ALTER CERTIFICATE cert_BookStore
WITH private key
(FILE='C:\Apress\CertBookStorePK.bak',
DECRYPTION BY PASSWORD='3439F6A!!!',
ENCRYPTION BY PASSWORD='AA5FA6AC!!!')
ALTER CERTIFICATE cert_BookStore
WITH private key
(DECRYPTION BY PASSWORD='AA5FA6AC!!!',数据调查
ENCRYPTION BY PASSWORD='my newpassword!!!Efsj')
SELECT *
FROM dbo.PasswordHint
INSERT dbo.PasswordHint
(CustomerID,PasswordHintQuestion,PasswordHintAnswer)
values(1,'What is the name of the hospital you were born in?',
ENCRYPTBYCERT(CERT_ID('cert_BookStore'),'Hickman Hospital'))
SELECT CustomerID,PasswordHintQuestion,
cast(DECRYPTBYCERT(CERT_ID('cert_BookStore'),PasswordHintAnswer,N
'my newpassword!!!Efsj') as CHAR(30)) PasswordHintAnswer
FROM dbo.PasswordHint
WHERE CustomerID=1
USE BookStore
GO
CREATE ASYMMETRIC KEY asymBookSell_v2
WITH ALGORITHM=RSA_512
CREATE SYMMETRIC KEY sym_BookSell_v2
法网男单决赛直播WITH ALGORITHM=TRIPLE_DES
ENCRYPTION BY ASYMMETRIC KEY asymBookSell_v2
OPEN SYMMETRIC KEY sym_BookSell_v2
DECRYPTION BY ASYMMETRIC KEY asymBookSell_v2
INSERT dbo.PasswordHint
(CustomerID,PasswordHintQuestion,PasswordHintAnswer)
values
溥杰的妻子(45,'What is the name of the hospital you were born in?',
ENCRYPTBYKEY(Key_guid('sym_BookSell_v2'),'Sister Helen'))
CLOSE SYMMETRIC KEY sym_BookSell_v2
SELECT *
FROM dbo.PasswordHint
WHERE CustomerID=45
SELECT CAST(DECRYPTBYKEYAUTOASYMKEY(ASYMKEY_ID('asymBookSell_v2'),NULL,PasswordHintAnswer) as varchar)
FROM dbo.PasswordHint
WHERE CustomerID=45
--TED
USE master
GO
CREATE MASTER KEY ENCRYPTION
BY password='834BACDA-10E6-4BBC-A698-952533E54337'
GO
CREATE CERTIFICATE TDE_Server_Certificate
WITH SUBJECT='Server-level cert for TDE'
GO
USE BookStore
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM=TRIPLE_DES_3KEY
ENCRYPTION BY SERVER CERTIFICATE TDE_Server_Certificate
GO
--警告: 用于对数据库加密密钥进行加密的证书尚未备份。应当立即备份该证书以及与该证书关联的私钥。如果该证书不可用，或者您必须在另一台服务器上还原或附加数据库，则必须对该证书和私钥均进行备份，否则将无法打开该数据库。
ALTER DATABASE BookStore
SET ENCRYPTION ON
GO
SELECT is_encrypted
FROM sys.databas
WHERE name='BookStore'
