MSSQL网站制作之SQL SERVER2005加密解密数据

php本地模拟的prepare底层就是mysql_real_escape_string，所以必须得用mysql_set_character_set去设置mysql->charset，否则就存在字符集问题。server|加密|解密|数据报告SQLServer2005的数据加密功效和设置和怎样经由过程它完成对敏感数据的回护。


演示用的剧本供应给人人作为参考：
--------------------------------------------------------------------------------

/*[课程]利用数据库加密回护敏感数据DEMO1懂得SQL2005加密条理布局[历程]历程一共分为4个部分*/--==================(I)服务主密钥=====================--1.)备份服务主密钥到文件BACKUPSERVICEMASTERKEYTOFILE=C:DBFileSMK.bakENCRYPTIONBYPASSWORD=P@ssw0rd--2.)天生新的服务主密钥ALTERSERVICEMASTERKEYREGENERATE;GO--3.)从备份文件复原服务主密钥RESTORESERVICEMASTERKEYFROMFILE=C:DBFileSMK.bakDECRYPTIONBYPASSWORD=P@ssw0rd--==================(II)数据库主密钥=====================--1.)为Northwind数据库创立数据库主密钥USENorthwindGOCREATEMASTERKEYENCRYPTIONBYPASSWORD=P@ssw0rdGO--2.)检察数据库加密形态SELECT[name],is_master_key_encrypted_by_serverFROMsys.databasesWHEREname=Northwind;GO--3.)检察数据库主密钥的信息USENorthwindSELECT*FROMsys.symmetric_keysGO--4.)对数据库主密钥举行备份USENorthwindGOBACKUPMASTERKEYTOFILE=C:DBFileDMK.bakENCRYPTIONBYPASSWORD=P@ssw0rd!@GO--5.)删除服务主密钥对数据库主密钥的回护--创立非对称密钥乐成，主动利用服务主密钥解密并利用该数据库主密钥CREATEASYMMETRICKEYasy_TestKey1WITHALGORITHM=RSA_1024GO--删除服务主密钥对数据库主密钥的回护ALTERMASTERKEYDROPENCRYPTIONBYSERVICEMASTERKEYGO--检察数据库的加密形态SELECT[name],is_master_key_encrypted_by_serverFROMsys.databasesWHEREname=Northwind;--创立非对称密钥失利，数据库主密钥未翻开CREATEASYMMETRICKEYasy_TestKey2WITHALGORITHM=RSA_1024GO--翻开数据库主密钥未OPENMASTERKEYDECRYPTIONBYPASSWORD=P@ssw0rdSELECT*FROMsys.openkeys--创立非对称密钥乐成CREATEASYMMETRICKEYasy_TestKey2WITHALGORITHM=RSA_1024GO--恢复服务主密钥对数据库主密钥的回护ALTERMASTERKEYADDENCRYPTIONBYSERVICEMASTERKEYCLOSEMASTERKEY--==================(III)证书=====================--1.)让SQL2005创立自署名的证书USENorthwindGOCREATECERTIFICATEcert_TestCert1ENCRYPTIONBYPASSWORD=P@ssw0rdWITHSUBJECT=TestCert1,START_DATE=1/31/2006,EXPIRY_DATE=1/31/2008GOSELECT*FROMsys.certificates--2.)从文件导进证书USENorthwindGOCREATECERTIFICATEcert_TestCert2FROMFILE=C:DBFileMSCert.cerGOSELECT*FROMsys.certificates--3.)备份导出证书和私钥BACKUPCERTIFICATEcert_TestCert1TOFILE=c:DBFileTestCert1.cerWITHPRIVATEKEY(DECRYPTIONBYPASSWORD=P@ssw0rd,FILE=c:DBFileTestCert1_pvt,ENCRYPTIONBYPASSWORD=Pa$w0rd)--4.)利用证书加密、解密数据DECLARE@cleartextvarbinary(200)DECLARE@ciphervarbinary(200)SET@cleartext=CONVERT(varbinary(200),Testtextstring)SET@cipher=EncryptByCert(Cert_ID(cert_TestCert1),@cleartext)SELECT@cipherSELECTCONVERT(varchar(200),DecryptByCert(Cert_ID(cert_TestCert1),@cipher,NP@ssw0rd))AS[ClearText]--5.)删除证书私钥ALTERCERTIFICATEcert_TestCert1REMOVEPRIVATEKEYGo--加密乐成，解密失利DECLARE@cleartextvarbinary(200)DECLARE@ciphervarbinary(200)SET@cleartext=CONVERT(varbinary(200),Testtextstring)SET@cipher=EncryptByCert(Cert_ID(cert_TestCert1),@cleartext)SELECT@cipherSELECTCONVERT(varchar(200),DecryptByCert(Cert_ID(cert_TestCert1),@cipher,NP@ssw0rd))AS[ClearText]--==================(IV)非对称密钥=====================--1.)利用sn.ext天生非对成密钥文件--sn-kC:DBFileasy_Test.key--2.)从文件创立非对称密钥USENorthwindGOCREATEASYMMETRICKEYasy_TestFROMFILE=C:DBFileasy_Test.keyENCRYPTIONBYPASSWORD=P@ssw0rdGOSELECT*FROMsys.asymmetric_keys
/*
[课程]利用数据库加密回护敏感数据
DEMO2
利用密钥对列数据举行加密

[历程]
历程一共分为4个部分
*/

--==================(I)筹办=====================
--1.)创立示例表
USENorthwind
IFEXISTdbo.EmpSalaryDROPTABLEdbo.EmpSalary;
CREATETABLEdbo.EmpSalary(
EmpIDint,
Titlenvarchar(50),
Salaryvarbinary(500)
)
GO
--2.)创立数据库主密钥
CREATEMASTERKEYENCRYPTIONBYPASSWORD=P@ssw0rd
GO
--3.)

--4.)创立用于加密的对称密钥
CREATESYMMETRICKEYsym_Salary
WITHALGORITHM=AES_192
ENCRYPTIONBYPASSWORD=P@ssw0rd;
SELECT*FROMsys.symmetric_keysWHERE[name]=sym_Salary

--==================(II)加密列数据=====================
--1.)翻开对称密钥
OPENSYMMETRICKEYsym_Salary
DECRYPTIONBYPASSWORD=P@ssw0rd
SELECT*FROMsys.openkeys--检察翻开的对称密钥
--2.)向表中拔出数据，并对Salary列的数据举行加密
INSERTINTOEmpSalaryVALUES(1,CEO,EncryptByKey(KEY_GUID(sym_Salary),20000))
INSERTINTOEmpSalaryVALUES(2,Manager,EncryptByKey(KEY_GUID(sym_Salary),10000))
INSERTINTOEmpSalaryVALUES(3,DBAdmin,EncryptByKey(KEY_GUID(sym_Salary),5000))
--3.)封闭翻开的对称密钥
CLOSESYMMETRICKEYsym_Salary
SELECT*FROMsys.openkeys--检察翻开的对称密钥
--4.)检察表中寄存的数据
SELECT*FROMEmpSalary
--==================(III)解密并会见被加密了的数据列=====================
--1.)翻开对称密钥
OPENSYMMETRICKEYsym_SalaryDECRYPTIONBYPASSWORD=P@ssw0rd
--2.)利用对称密钥解密并会见被加密了的数据列
SELECTEmpID,Title,CAST(DecryptBykey(Salary)ASVARCHAR(20))ASSalaryFROMEmpSalary
--3.)封闭对称密钥
CLOSESYMMETRICKEYsym_Salary

--==================(III)绕过加密数据的打击=====================
--1.)打击者利用别的数据行的加密数据交换某一行的数据
SELECT*FROMEmpSalary
UPDATEEmpSalarySETSalary=
(SELECTSalaryFROMEmpSalaryWHEREEmpID=1)
WHEREEmpID=3
--2.)检察被打击后解密的数据
OPENSYMMETRICKEYsym_SalaryDECRYPTIONBYPASSWORD=P@ssw0rd
SELECTEmpID,Title,CAST(DecryptBykey(Salary)ASVARCHAR(20))ASSalaryFROMEmpSalary
CLOSESYMMETRICKEYsym_Salary

--==================(IV)利用考证器避免绕过加密数据的打击=====================
--1.)删除后面增加的数据行
DELETEFROMEmpSalary
--2.)向表中拔出数据，并对Salary列的数据利用考证器举行加密，第四个参数是加密因子
OPENSYMMETRICKEYsym_SalaryDECRYPTIONBYPASSWORD=P@ssw0rd
INSERTINTOEmpSalaryVALUES(1,CEO,EncryptByKey(KEY_GUID(sym_Salary),20000,1,1))
INSERTINTOEmpSalaryVALUES(2,Manager,EncryptByKey(KEY_GUID(sym_Salary),10000,1,2))
INSERTINTOEmpSalaryVALUES(3,DBAdmin,EncryptByKey(KEY_GUID(sym_Salary),5000,1,3))
CLOSESYMMETRICKEYsym_Salary
--3.)解密并会见被加密了的数据列
OPENSYMMETRICKEYsym_SalaryDECRYPTIONBYPASSWORD=P@ssw0rd
SELECTEmpID,Title,CAST(DecryptBykey(Salary,1,CAST(EmpIDASVARCHAR(3)))ASVARCHAR(20))ASSalaryFROMEmpSalary
CLOSESYMMETRICKEYsym_Salary

--4.)打击者利用不异的办法改动数据
SELECT*FROMEmpSalary
UPDATEEmpSalarySETSalary=
(SELECTSalaryFROMEmpSalaryWHEREEmpID=1)
WHEREEmpID=3
--5.)被改动后的加密了的数据列酿成有效
OPENSYMMETRICKEYsym_SalaryDECRYPTIONBYPASSWORD=P@ssw0rd
SELECTEmpID,Title,CAST(DecryptBykey(Salary,1,CAST(EmpIDASVARCHAR(3)))ASVARCHAR(20))ASSalaryFROMEmpSalary
CLOSESYMMETRICKEYsym_Salary
/*
[课程]利用数据库加密回护敏感数据
DEMO3
利用证书签订存储历程

[历程]
历程一共分为2个部分
*/
--==================(I)示例筹办=====================
--1.)创立数据库主密钥
USENorthwind
GO
CREATEMASTERKEYENCRYPTIONBYPASSWORD=P@ssw0rd
--2.)创立签订存储历程所必要的证书
CREATECERTIFICATEcert_Products
WITHSUBJECT=ProductsSign,
START_DATE=2006/1/1,
EXPIRY_DATE=2008/1/1
--3.)创立SPDeveloper登录帐户和用户，该用户创立会见Products表的存储历程
CREATELOGIN[SPDeveloper]WITHPASSWORD=NP@ssw0rd,DEFAULT_DATABASE=[Northwind]
GO
CREATEUSER[SPDeveloper]FORLOGINSPDeveloperWITHDEFAULT_SCHEMA=[SPDeveloper]
GO
CREATESCHEMAproductsAUTHORIZATIONSPDeveloper
GO
EXECsp_addrolemember@rolename=db_owner,@membername=SPDeveloper
--4.)以SPDeveloper的身份创立存储历程products.usp_Products
EXECUTEASUSER=SPDeveloper
GO
CREATEPROCEDUREproducts.usp_Products
AS
SELECTTOP5*FROMdbo.Products
GO
REVERT
SELECTUSER
--4.)创立一般用户Jerry
CREATELOGINjerryWITHPASSWORD=NP@ssw0rd,DEFAULT_DATABASE=[Northwind]
CREATEUSERjerryFORLOGINjerry

--==================(II)利用证书签订存储历程=====================
--1.)授与用户Jerry实行存储历程的权限
GRANTEXECUTEONproducts.usp_ProductsTOjerry
--2.)以Jerry的身份实行存储历程失利，由于具有全链是断裂的
EXECUTEASUSER=jerry
SELECTUSER
GO
EXECUTEproducts.usp_Products
GO
REVERT
--3.)利用证书在以后数据库创立用户ProductsReader，
--并为该用户付与读取Products表的权限
CREATEUSERProductsReaderFORCERTIFICATEcert_Products
GO
GRANTSELECTONProductsTOProductsReader
--4.)利用证书签订以后存储历程
ADDSIGNATURETOproducts.usp_ProductsBYCERTIFICATEcert_Products
--4.)以Jerry的身份从头实行存储历程，乐成，
--由于存储历程将以ProductsReader的权限高低文实行
EXECUTEASUSER=jerry
SELECTUSER
GO
EXECUTEproducts.usp_Products



讲师:牛可
工夫:2006年8月9日10:00--11:30
产物:SQLServer
手艺品级:200
接待人人主动介入会商
课后成绩及谜底

1.在SQLServer2005中，数据库的主密钥能够间接用来加密回护：（AB）

A.证书的私钥

B.非对称密钥的私钥

C.非对称密钥的公钥

D.服务主密钥



2.当接纳加密手艺来回护数据库中的大批敏感数据时，为了分身功能和数据的平安性，最好的做法是：（C）

A.利用证书加密一切敏感数据，并用对称密钥加密回护证书的私钥

B.利用非对称密钥的公钥加密一切敏感数据，并用对称密钥加密回护该密钥对的私钥

C.利用对称密钥加密一切敏感数据，并用证书加密回护该对称密钥

D.利用非对称密钥的私钥加密一切敏感数据，并用证书加密回护该密钥对的公钥



3.在SQLServer2005中利用证书签订存储历程的目标是：（D）

A.确保只要具有该证书对应私钥的用户才干实行该存储历程

B.加密存储历程，避免别的人检察到存储过程当中的T-SQL语句

C.加密存储历程实行前往的数据了局集

D.让该存储历程以证书所对应的数据库用户的权限实行

在Windows中MySQL以服务形式存在，在使用前应确保此服务已经启动，未启动可用netstartmysql命令启动。而Linux中启动时可用“/etc/rc.d/init.d/mysqldstart"命令，注意启动者应具有管理员权限。

XML字段类型更好的解决了XML数据的操作。XQuery确实不错，但是个人对其没好感。（CSDN的开发者应该是相当的熟了！）

XML字段类型更好的解决了XML数据的操作。XQuery确实不错，但是个人对其没好感。（CSDN的开发者应该是相当的熟了！）

是否碎片会引发效率问题？这都是需要进一步探讨的东西。varbinary(max)代替image也让SQLServer的字段类型更加简洁统一。

连做梦都在想页面结构是怎么样的，绝非虚言

对于数据库来说，查询是数据库的灵魂，那么SQL查询效率究竟效率如何呢？下文将带对SQL查询的相关问题进行讨论，供您参考。

无法深入到数据库系统层面去了解和探究

所以你总能得到相应的升级版本，来满足你的需求。

这是一个不错的新特性。虽然索引的附加字段没有索引键值效率高，但是相对映射到数据表中效率还是提高了很多。我做过试验，在我的实验环境中会比映射到表中提高30%左右的效率。