安全性凭证

[Jenny_Ming]

各位大大,想请问一下...
我今天要用script的方式到另一台机器上建一模一样的资料库...
但它安全性里的凭证我要怎么建出的它script呢??
我有上网爬文说可以用

CREATE CERTIFICATE AAAAA   
    ENCRYPTION BY PASSWORD = '?????'  
    WITH SUBJECT = 'Credit Card Certificate',    
    START_DATE ='2012/01/01',   
    EXPIRY_DATE = '2012/06/30';   

建凭证,但我不知这它密码是什么??我有什么方式可以取得密码吗??
还是这个密码我可以随便设定呢??

[阳泉酒家小当家]

密码可以随便写，只要你自己记住 就可以

[發糞塗牆]

应该不能取得密码，不然就没安全性了

[阳泉酒家小当家]

可以参考这个：

--1.1创建证书
create certificate certEncrypt 
encryption by password = 'cert12345'
with subject = 'encryption certificate',
     start_date = '2012-10-01',
     expiry_date = '2012-10-31'


--1.2.1创建数据库主密钥     
create master key 
encryption by password = 'excel12345'


--1.2.2不提供密码时,会自动用数据库主密钥来加密私钥
create certificate certEncrypt1
with subject = 'encryption certificate',
     start_date = '2012-10-01',
     expiry_date = '2012-10-31'


--1.2.3删除证书,然后才可以删除数据库主密钥,否则会报错
drop certificate certencrypt1

drop master key



--2.查看证书信息
select name,
       certificate_id,
       pvt_key_encryption_type_desc,--对私钥加密方式的说明
       is_active_for_begin_dialog,  --对于当前会话，是否启用了证书加密
       issuer_name,                 --证书颁发者的名称
       subject,                     --证书的主题
       start_date,
       expiry_date,
       pvt_key_last_backup_date     --上一次导出证书的私钥的日期和时间
from sys.certificates



--3.备份证书
--备份证书时用于加密私钥的密码与对证书的私钥进行加密的密码,不是同一个密码
backup certificate certEncrypt
to file = 'c:\certEncrypt.bak'   --证书的备份文件
with private key 
(
 file = 'c:\certEncrypt_privateKey.bak',--私钥文件
 decryption by password = 'cert12345',  --这个密码是在创建证书时指定的,
                                        --用于在备份密钥之前对私钥进行解密的密码
     
 encryption by password = 'encryptCert123456'--在解密取得私钥后,
                                             --在将密钥写入私钥的备份文件之前,
                                             --对私钥进行加密的密码
)



--4.还原证书
--4.1先删除证书,这样才能重建证书
drop certificate certEncrypt


--4.2然后从证书备份文件和私钥文件中,再次创建证书
create certificate certEncrypt
from file = 'c:\certEncrypt.bak' 
with private key 
(
 file = 'c:\certEncrypt_privateKey.bak',
 decryption by password = 'encryptCert123456',  --用于解密私钥文件中的私钥的密码
 encryption by password = 'cert12345'           --用于加密私钥的密码
)



--5.管理证书的私钥
--5.1删除私钥的密码，这样默认通过数据库主密钥对私钥进行加密
alter certificate certEncrypt
remove private key


--5.2通过备份的私钥文件，为已经存在的证书重新增加私钥的密码
alter certificate certencrypt
with private key
(
 file = 'c:\certEncrypt_privateKey.bak',
 decryption by password = 'encryptCert123456',
 encryption by password = 'cert12345'
)


--5.3修改私钥的密码
alter certificate certencrypt
with private key
(
 decryption by password = 'cert12345',
 encryption by password = '12345cert'
)



--6.使用证书加密,解密
create table t
(id int not null primary key,
 question varchar(100) not null,
 answer varbinary(200) not null)

--6.1加密数据
insert into t
values(1,
       'who are you?',
       ENCRYPTBYCERT(
                      cert_id('certencrypt'),  --证书的id
                      'kaka'                   --需要加密的文本
                    )
      )

--6.2直接查看,发现是乱码
select ID,
       question,
       answer,
       CAST(answer AS varchar(200))
from t


--6.3解密,显示正确数据
select ID,
       question,
       answer,
       CAST(DECRYPTBYCERT(
                   cert_id('certencrypt'),
                   answer,
                   N'12345cert'    --私钥的密码,用来解密取得私钥,再用私钥来解密数据
                  ) AS varchar(200)
            )
from t



--7.使用对称密钥执行解密，而该对称密钥则使用非对称密钥进行自动解密
create table tx
(id int not null primary key,
 question varchar(100) not null,
 answer varbinary(200) not null)


--7.1非对称密钥的私钥用数据库主密钥加密
--7.1.1创建数据库主密钥
create master key 
encryption by password = 'excel12345'


--7.1.2创建非对称密钥,自动用数据库主密钥加密私钥
create asymmetric key asymKey
with algorithm = RSA_512


--7.1.3创建对称密钥,用非对称加密的私钥来加密对称密钥
CREATE symmetric key symKey
with algorithm = TRIPLE_DES
ENCRYPTION BY asymmetric key asymKey


--7.1.4打开对称密钥
open symmetric key symKey
decryption by asymmetric key asymKey


--7.1.5用对称密钥加密
insert into tx
values(1,
       'who are you?',
       ENCRYPTBYKEY(KEY_GUID('symKey'),
                    'kk')
       )

--7.1.6关闭对称密钥
close symmetric key symKey


--7.1.7由于非对称加密的私钥是由数据库主密钥加密的,所以不需要提供私钥的密码,
--首先用数据库主密钥解密,取得非对称加密的私钥,
--然后用私钥解密,取得对称加密的公钥,用公钥解密被加密的文本
select id,
       question,
       answer,
       cast(DecryptBykeyAutoAsymkey(
                    asymkey_id('asymKey'), --用于保护对称密钥的非对称密钥的ID
                    null,                  --用于保护非对称密钥私钥的密码
                                           --如果私钥受数据库主密钥保护，则该值可以是 NULL
                    answer
                   )
               as varchar)
from tx

delete from tx


--7.1非对称密钥的私钥用密码来加密
--7.2.1创建非对称密钥
create asymmetric key asymKey1
with algorithm = RSA_512
encryption by password = 'asymKey123456'


--7.2.2创建对称密钥,用非对称加密的私钥来加密对称密钥
CREATE symmetric key symKey1
with algorithm = TRIPLE_DES
ENCRYPTION BY asymmetric key asymKey1


--7.2.3打开对称密钥
open symmetric key symKey1
decryption by asymmetric key asymKey1
with password = 'asymKey123456'    --非对称密钥的私钥的密码


--7.2.4用对称密钥加密
insert into tx
values(1,
       'who are you?',
       ENCRYPTBYKEY(KEY_GUID('symKey1'),
                    'kk')
       )

--7.2.5关闭对称密钥
close symmetric key symKey1


--7.2.6提供非对称加密的私钥的密码,首先用这个密码来解密,取得私钥,
--然后用私钥解密,取得对称加密的公钥,用公钥解密被加密的文本
select id,
       question,
       answer,
       cast(DecryptBykeyAutoAsymkey(
                    asymkey_id('asymKey1'),--用于保护对称密钥的非对称密钥的ID
                    N'asymKey123456',      --保护非对称密钥私钥的密码,类型为nvarchar
                                           --如果私钥受数据库主密钥保护，则该值可以是 NULL
                    answer
                   )
               as varchar)
from tx

delete from tx


--7.3用证书的私钥用密码来加密
--7.3.1创建证书
create certificate certEncrypt1 
encryption by password = 'cert12345'
with subject = 'encryption certificate',
     start_date = '2012-10-01',
     expiry_date = '2012-10-31'


--7.3.2创建对称密钥,用证书的私钥来加密对称密钥
CREATE symmetric key symKey11
with algorithm = TRIPLE_DES
ENCRYPTION BY certificate certEncrypt1


--7.3.3打开对称密钥
open symmetric key symKey11
decryption by certificate certEncrypt1
with password = 'cert12345'    --非对称密钥的私钥的密码


--7.3.4用对称密钥加密
insert into tx
values(1,
       'who are you?',
       ENCRYPTBYKEY(KEY_GUID('symKey11'),
                    'kk')
       )

--7.3.5关闭对称密钥
close symmetric key symKey11


--7.3.6提供证书的私钥的密码,首先用这个密码来解密,取得私钥,
--然后用私钥解密,取得对称加密的公钥,用公钥解密被加密的文本
select id,
       question,
       answer,
       cast(DecryptBykeyAutoCert(
                 cert_id('certEncrypt1'),--用于保护对称密钥的证书的ID
                 N'cert12345',           --保护证书的私钥的密码,类型为nvarchar
                                         --如果私钥受数据库主密钥保护，则为NULL
                 answer
              )
             as varchar)
from tx


--8.删除证书
drop certificate certEncrypt