SQL问题解决集锦,使用数据库加密保护敏感数据

  1. 使用参数化查询或者存储过程 注意: 动态SQL是导致SQL注入的主凶 作者: 黄钊吉

/*
[课程]使用数据库加密保护敏感数据

以下代码已经在SQLServer2008上的示例数据库测试通过问题一:如何为数据进行加密与解密,避免使用者窃取机密数据? 对于一些敏感数据,如密码、卡号,一般不能使用正常数值来存储。否则会有安全隐患。以往的加密解密都有前端应用程序来辅助完成。而数据库一般只能加密不能解密。 从2005开始提供了数据库层面的数据加密与解密。其实现方式主要有以下: 1、 利用CONVERT改变编码方式: 利用该函数把文字或数据转换成VARBINARY。但该方式不具备保护数据的能力,仅避免浏览数据的过程中能直接看到敏感数据的作用。 2、 利用对称密钥: 搭配EncryptByKey进行数据加密。使用DecryptByKey函数进行解密。这种方式比较适合大数据量。因为对称密钥的过程好用资源较少。 3、 利用非对称密钥: 搭配EncryptByAsymKey进行数据加密。使用DecryptByAsymKey函数进行解密。用于更高安全级别的加解密数据。因为耗用资源叫多。 4、 利用凭证的方式: 搭配EncryptByCert进行加密和DecryptByCert函数进行解密。比较类似非对称密钥。 5、 利用密码短语方式: 搭配EncryptBypassPhrase进行加密,使用DecryptByPassPhrase函数来解密。可以使用有意义的短语或其他数据行,当成加密、解密的关键字,比较适合一般的数据加解密。 案例: 1、 Convert方式: 复制代码 代码如下: a) USE tempdb b) GO c) CREATE TABLE test d) userID INT IDENTITY userName VARCHAR userSalary FLOAT , h) cyberalary NVARCHAR INSERT INTO TEST l) ( userName, userSalary ) m) VALUES , o) --ALTER TABLE test q) --ADD userNewSalary VARBINARY --使用转换函数把数据转换成varbinary,改变编码方式。 s) SELECT * , t) CONVERT, userSalary) u) FROM test v) --把数据转换成int,可以恢复原有编码方式 w) SELECT * , x) CONVERT FROM test 2、对称密钥: 复制代码 代码如下: a) --创建对称密钥 b) USE AdventureWorks c) GO d) CREATE SYMMETRIC KEY SymKey123 e) WITH ALGORITHM=TRIPLE_DES ENCRYPTION BY PASSWORD='P@ssw0rd' f) GO g) --注意事项:在启用时,需要先OPEN SYMMETRIC KEY 搭配密钥密码,否则所产生的数据都会是null值。而且需要搭配Key_GUID函数来使用 h) --打开对称密钥 i) OPEN SYMMETRIC KEY SymKey123 DECRYPTION BY PASSWORD='P@ssw0rd'; j) --进行数据加密 k) SELECT * ,ENCRYPTBYKEY,CONVERT,AddressLine1)) l) FROM Person.Address m) n) --检查加密后长度,利用datalength SELECT DATALENGTH(ENCRYPTBYKEY,CONVERT,AddressLine1))) p) FROM Person.Address q) GO r) --把加密后数据更新到原来另外的列上 s) UPDATE Person.Address t) SET AddressLine2=ENCRYPTBYKEY,CONVERT,AddressLine1)) u) --解密:解密过程同样需要OPEN SYMMETRIC KEY ,且需要利用DECRYPTBYKEY 和CONVERT函数 v) OPEN SYMMETRIC KEY SymKey123 DECRYPTION BY PASSWORD='P@ssw0rd'; w) x) SELECT AddressID,CONVERT ,CONVERT ,DECRYPTBYKEY FROM Person.Address 3、非对称密钥: 复制代码 代码如下: a) --非对称密钥使用两种不同的密钥,所以加密是是不需要输入密码验证,但解密时就需要 b) USE AdventureWorks c) GO d) CREATE ASYMMETRIC KEY AsymKey123 WITH ALGORITHM=RSA_2048 ENCRYPTION BY PASSWORD='P@ssw0rd'; e) GO f) g) --添加新列存储加密后的数据 h) ALTER TABLE Person.Address ADD AddressLine3 nvarchar --进行加密 k) SELECT *,ENCRYPTBYASYMKEY(ASYMKEY_ID ,CONVERT,AddressLine1)) l) FROM Person.Address m) GO n) o) --把数据更新到一个新列 p) UPDATE Person.Address q) SET AddressLine3=ENCRYPTBYASYMKEY(ASYMKEY_ID ,CONVERT,AddressLine1)) r) s) t) SELECT *--addressline3 u) FROM Person.Address v) w) --解密:此过程一定要使用密码来解密,此处的类型要与加密时相同,比如加密时用varchar,而这里用nvarchar的话是解密不了的。 x) SELECT TOP 10 AddressID,CONVERT,CONVERT ,DECRYPTBYASYMKEY(ASYMKEY_ID,AddressLine3,N'P@ssw0rd'))) AS Decryptedata y) FROM Person.Address 4、证书加密: 复制代码 代码如下: a) --证书加密:首先建立证书 CREATE CERTIFICATE certKey123--证书名 c) ENCRYPTION BY PASSWORD='P@ssw0rd'--密码 d) WITH SUBJECT='Address Certificate',--证书描述 e) START_DATE='2012/06/18',--证书生效日期 f) EXPIRY_DATE='2013/06/18' ;--证书到期日 g) GO h) --利用证书加密 i) SELECT *,ENCRYPTBYCERT,CONVERT ,AddressLine1)) cyberAddress j) FROM Person.Address k) l) --添加新列存放加密数据 m) ALTER TABLE Person.Address ADD AddressLine4 Nvarchar --把加密后数据放到新列 p) UPDATE Person.Address q) SET AddressLine4=ENCRYPTBYCERT,CONVERT ,AddressLine1)) r) s) --解密 t) SELECT AddressID,CONVERT,CONVERT,DECRYPTBYCERT,AddressLine4,N'P@ssw0rd'))) DecryAddress u) FROM Person.Address 5、短语加密: 复制代码 代码如下: a) --短语加密:该过程较为简单,只需要使用EncryptByPassPhrase函数,使用短语加密时,参考的数据航不可以变动,否则解密失败。 b) SELECT *,AddressLine5=ENCRYPTBYPASSPHRASE('P@ssw0rd',CONVERT(varbinary,AddressLine1),AddressID) c) FROM Person.Address d) e) --添加新列存放数据,注意,ENCRYPTBYPASSPHRASE函数返回的是VARBINARY类型 f) ALTER TABLE Person.Address ADD AddressLine5 VARBINARY --将数据更新,过程中使用P@ssw0rd和AddressID数据行当成密码短语 i) j) UPDATE Person.Address k) SET AddressLine5=ENCRYPTBYPASSPHRASE('P@ssw0rd',CONVERT(varbinary,AddressLine1),AddressID) l) m) SELECT * FROM Person.Address 问题二:如何保护数据库对象定义,避免发生过渡暴露敏感信息? 一般的保护措施是在创建对象时使用WITH ENCRYPTION来把对象加密,这样就无法查看定义。但是问题是对于维护来说就成了问题,而且备份还原时这部分对象是会丢失的。 其中一个解决方法是把定义语句放到对象的中保存,这样能解决上面的问题。 下面举个例子: 复制代码 代码如下: --1、建立已加密的存储过程 USE AdventureWorks GO CREATE PROC test WITH ENCRYPTION AS SELECT SUSER_SNAME GO --2、将上述定义内容去除,利用短语加密搭配EncryptByPassPhrase函数加密,然后在用sys.sp_addextendedproperty存储过程,指定一个扩展名称。 USE AdventureWorks GO DECLARE @sql VARCHAR SET @sql = 'CREATE PROC Test WITH ENCRYPTION AS SELECT suer_sname GO' --3、将内容加密后转换成sql_variant数据类型 DECLARE @bsql SQL_VARIANT SET @bsql = ( SELECT CONVERT(SQL_VARIANT, ENCRYPTBYPASSPHRASE('P@ssw0rd', CONVERT ) --4、新增到指定存储过程的扩展属性中: EXEC sys.sp_addextendedproperty @name = N'test定义', @value = N'System.Byte[]', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'PROCEDURE', @level1name = N'test' GO EXEC sys.sp_addextendedproperty @name = N'代码内容', @value = N'CREATE PROC Test WITH ENCRYPTION AS SELECT suer_sname GO', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'PROCEDURE', @level1name = N'test' GO --5、还原 DECLARE @pwd VARCHAR= 'P@ssw0rd' --密码短语 DECLARE @proc VARCHAR= 'test' --存储过程名 DECLARE @exName NVARCHAR= '代码内容' --扩充属性名 --将原本结果查询 SELECT value FROM sys.all_objects AS sp INNER JOIN sys.extended_properties AS P ON P.major_id = sp.object_id AND P.minor_id = 0 AND P.class = 1 WHERE AND ( ( sp.type = N'p' OR sp.type = N'rf' OR sp.type = 'pc' ) AND ( sp.name = @proc AND SCHEMA_NAME = N'dbo' ) ) 问题三、如何让指定用户可以对数据表进行Truncate操作? Truncate在对大表全删除操作时,会明显比Delete语句更快更有效,但是因为它不需要存放日志,并且一定是全表删除,所以造成数据的不可恢复性。也说明了它的危险性。 但是,执行Truncate需要有表拥有者、系统管理员、db_owner、db_ddladmin这些里面的其中一种高权限角色才能执行。 对此,可以使用05之后的EXECUTE AS表达式来实现权限内容的切换: 1. 切换登录:EXECUTE AS LOGIN 2. 切换用户:EXECUTE AS USER 3. 切换执行权限:EXECUTE AS owner/'user name',利用高用户权限来执行作业。此步骤可以在低权限实体下执行高权限操作,也能避免安全性漏洞。 另外,只有EXECUTE AS Caller可以跨数据库执行,而其他方式进行的权限切换仅限制于本数据库。 注意:执行EXECUTE AS USER模拟使用者切换时,需要先获得被模拟用户的授权。 可以使用REVERT来还原执行内容前的原始身份。 问题四、如何获取前端连接的信息,如IP地址和计算机名? 对于DBA工作或者某些特殊的应用程序,需要获取前端应用的系统信息。而这些信息如果用用户表来存储,代价会比直接读取数据库系统信息要大。所以建议适当读取系统表: 在连接数据库的session期间,都可以在master数据库中找到session信息,但是从05开始,有了很多DMV/DMF来实现这些功能: l Master.dbo.sysprocesses或者master.sys.sysprocesses:提供执行阶段的SPID、计算机名、应用程序名等。 l Sys.dm_exec_sessions:记录每个session的基本信息,包括id、计算机名、程序名、应用程序名等 l Sys.dm_exec_connections:记录每个连接到SQLServer实例的前端信息,包括网络位置、连接时间等等。 l select client_net_address 'Client IP Address',local_net_address 'SQL ServerIP Address',* l from sys.dm_exec_connections l where session_id=@@spid 在2005以后,建议使用DMV取代系统表。问题五、如何避免SQL注入的攻击? 对于数据库应用程序,无论是那种DBMS,SQL注入都是一大隐患。 要避免SQL注入,应该最起码做到以下几点: 1. 检查输入的数据,应用程序不要相信用户输入的数据,必须经过检验后才能输入数据库。要排除%、--等特殊符号。 2. 避免果度暴露错误信息。建议可以转换成Windows事件或者是转换成应用程序内部错误信息。

D.      服务主密钥

[过程]
过程一共分为4个部分

--5.)删除证书私钥
ALTER CERTIFICATE cert_TestCert1
    REMOVE PRIVATE KEY
Go
--    加密成功,解密失败
DECLARE @cleartext varbinary(200)
DECLARE @cipher varbinary(200)
SET @cleartext = CONVERT(varbinary(200), 'Test text string')
SET @cipher = EncryptByCert(Cert_ID('cert_TestCert1'), @cleartext)
SELECT @cipher
SELECT CONVERT(varchar(200), DecryptByCert(Cert_ID('cert_TestCert1'), @cipher, N'P@ssw0rd')) AS [ClearText]

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

D.      使用非对称密钥的私钥加密所有敏感数据,并用证书加密保护该密钥对的公钥

--4.)创建用于加密的对称密钥
CREATE SYMMETRIC KEY sym_Salary
    WITH ALGORITHM = AES_192
    ENCRYPTION BY PASSWORD = 'P@ssw0rd';

2.       当采用加密技术来保护数据库中的大量敏感数据时,为了兼顾性能和数据的安全性,最佳的做法是:(C)

UPDATE EmpSalary SET Salary

    (SELECT Salary FROM EmpSalary WHERE EmpID = 1)
    WHERE EmpID = 3

--2.)查看被攻击后解密的数据
OPEN SYMMETRIC KEY sym_Salary DECRYPTION BY PASSWORD = 'P@ssw0rd'
SELECT EmpID, Title, CAST(DecryptBykey(Salary) AS VARCHAR(20)) AS Salary FROM EmpSalary
CLOSE SYMMETRIC KEY sym_Salary

--==================(IV)使用验证器防止绕过加密数据的攻击=====================
--1.)删除前面添加的数据行
DELETE FROM EmpSalary

--2.)向表中插入数据,并对Salary列的数据使用验证器进行加密
OPEN SYMMETRIC KEY sym_Salary DECRYPTION BY PASSWORD = 'P@ssw0rd'
INSERT INTO EmpSalary VALUES (1, 'CEO', EncryptByKey(KEY_GUID('sym_Salary'), '20000', 1, '1'))
INSERT INTO EmpSalary VALUES (2, 'Manager', EncryptByKey(KEY_GUID('sym_Salary'), '10000', 1, '2'))
INSERT INTO EmpSalary VALUES (3, 'DB Admin', EncryptByKey(KEY_GUID('sym_Salary'), '5000', 1, '3'))
CLOSE SYMMETRIC KEY sym_Salary

--3.)解密并访问被加密了的数据列
OPEN SYMMETRIC KEY sym_Salary DECRYPTION BY PASSWORD = 'P@ssw0rd'
SELECT EmpID, Title, CAST(DecryptBykey(Salary, 1, CAST(EmpID AS VARCHAR(3))) AS VARCHAR(20)) AS Salary FROM EmpSalary
CLOSE SYMMETRIC KEY sym_Salary

--4.)攻击者使用相同的方法篡改数据
SELECT * FROM EmpSalary

--==================(IV)非对称密钥=====================
--1.)使用sn.ext生成非对成密钥文件
--     sn -k C:DBFileasy_Test.key

--2.)向表中插入数据,并对Salary列的数据进行加密
INSERT INTO EmpSalary VALUES (1, 'CEO', EncryptByKey(KEY_GUID('sym_Salary'), '20000'))
INSERT INTO EmpSalary VALUES (2, 'Manager', EncryptByKey(KEY_GUID('sym_Salary'), '10000'))
INSERT INTO EmpSalary VALUES (3, 'DB Admin', EncryptByKey(KEY_GUID('sym_Salary'), '5000'))

SELECT * FROM sys.openkeys        --查看打开的对称密钥

A.       使用证书加密所有敏感数据,并用对称密钥加密保护证书的私钥

--==================(III)解密并访问被加密了的数据列=====================
--1.)打开对称密钥
OPEN SYMMETRIC KEY sym_Salary DECRYPTION BY PASSWORD = 'P@ssw0rd'

--2.)生成新的服务主密钥
ALTER SERVICE MASTER KEY REGENERATE;
GO

--3.)查看数据库主密钥的信息
USE Northwind
SELECT * FROM sys.symmetric_keys
GO

SELECT CONVERT(varchar(200), DecryptByCert(Cert_ID('cert_TestCert1'), @cipher, N'P@ssw0rd')) AS [ClearText]

--2.)从文件创建非对称密钥
USE Northwind
GO
CREATE ASYMMETRIC KEY asy_Test 
    FROM FILE = 'C:DBFileasy_Test.key' 
    ENCRYPTION BY PASSWORD = 'P@ssw0rd'
GO

B.       使用非对称密钥的公钥加密所有敏感数据,并用对称密钥加密保护该密钥对的私钥

C.      加密存储过程执行返回的数据结果集

C.      使用对称密钥加密所有敏感数据,并用证书加密保护该对称密钥

B.       非对称密钥的私钥

A.       证书的私钥

--1.)打开对称密钥
OPEN SYMMETRIC KEY sym_Salary
    DECRYPTION BY PASSWORD = 'P@ssw0rd'

UPDATE EmpSalary SET Salary

    (SELECT Salary FROM EmpSalary WHERE EmpID = 1)
    WHERE EmpID = 3

--5.)被篡改后的加密了的数据列变成无效
OPEN SYMMETRIC KEY sym_Salary DECRYPTION BY PASSWORD = 'P@ssw0rd'
SELECT EmpID, Title, CAST(DecryptBykey(Salary, 1, CAST(EmpID AS VARCHAR(3))) AS VARCHAR(20)) AS Salary FROM EmpSalary
CLOSE SYMMETRIC KEY sym_Salary

/*
[课程]使用数据库加密保护敏感数据

DEMO 3
使用证书签署存储过程

[过程]
过程一共分为2个部分

*/

--==================(I)示例准备=====================
--1.)创建数据库主密钥
USE Northwind
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@ssw0rd'

--2.)创建签署存储过程所需要的证书
CREATE CERTIFICATE cert_Products
    WITH SUBJECT = 'Products Sign',
    START_DATE = '2006/1/1',
    EXPIRY_DATE = '2008/1/1'

--3.)创建SPDeveloper登录帐户和用户,该用户创建访问Products表的存储过程
CREATE LOGIN [SPDeveloper] WITH PASSWORD=N'P@ssw0rd', DEFAULT_DATABASE=[Northwind]
GO
CREATE USER [SPDeveloper] FOR LOGIN SPDeveloper WITH DEFAULT_SCHEMA=[SPDeveloper]
GO
CREATE SCHEMA products AUTHORIZATION SPDeveloper
GO
EXEC sp_addrolemember @rolename = 'db_owner', @membername = 'SPDeveloper'

--4.)以SPDeveloper的身份创建存储过程products.usp_Products
EXECUTE AS USER = 'SPDeveloper'
GO
CREATE PROCEDURE products.usp_Products
AS
    SELECT TOP 5 * FROM dbo.Products
GO

REVERT
SELECT USER

--4.)创建普通用户Jerry
CREATE LOGIN jerry WITH PASSWORD=N'P@ssw0rd', DEFAULT_DATABASE=[Northwind]
CREATE USER jerry FOR LOGIN jerry

--==================(II)使用证书签署存储过程=====================
--1.)授予用户Jerry执行存储过程的权限
GRANT EXECUTE ON products.usp_Products TO jerry

--2.)以Jerry的身份执行存储过程失败,因为拥有全链是断裂的
EXECUTE AS USER = 'jerry'
SELECT USER
GO

EXECUTE products.usp_Products
GO

REVERT

--3.)使用证书在当前数据库创建用户ProductsReader,
--     并为该用户赋予读取Products表的权限
CREATE USER ProductsReader FOR CERTIFICATE cert_Products
GO
GRANT SELECT ON Products TO ProductsReader

--4.)使用证书签署当前存储过程
ADD SIGNATURE TO products.usp_Products BY CERTIFICATE cert_Products

--4.)以Jerry的身份重新执行存储过程,成功,
--     因为存储过程将以ProductsReader的权限上下文执行
EXECUTE AS USER = 'jerry'
SELECT  USER
GO
EXECUTE products.usp_Products

问题:如果没有使用密钥,会出现什么样的安全问题呢?
回答:只有在数据库中有需要保护的敏感数据时,才使用数据库加密,所以如果在数据库中没有这样的数据信息,那么也就可以不启用数据库加密,也就不用创建相应的密钥了。

问题:当使用OPEN SYMMETRIC KEY打开多个对称密钥时,用函数DecryptByKey解密时SQL 2005会使用那个密钥呢?
回答:使用对称密钥加密的密文起始位置表明了生成该密文对称密钥的GUID,所以它会自动根据密钥的GUID来选择相应的密钥进行解密。

问题:如何备份对称密钥和非对称密钥?
回答:对称密钥和非对称密钥是数据库级别的对象,虽然没有提供直接备份它们的机制,但是可以通过对数据库备份来实现对密钥的备份。另外在生成对称密钥时可以使用密码短语来生成密钥,这样保存了密码短语就备份了相应的对称密钥。对于非对称密钥可以使用有强名称文件来生成密钥对,只要保存了该文件也就备份了相应的非对称密钥。

--     创建非对称密钥失败,数据库主密钥未打开
CREATE ASYMMETRIC KEY asy_TestKey2 WITH ALGORITHM = RSA_1024
GO

--2.)使用对称密钥解密并访问被加密了的数据列
SELECT EmpID, Title, CAST(DecryptBykey(Salary) AS VARCHAR(20)) AS Salary FROM EmpSalary

--==================(III)证书=====================
--1.)让SQL2005创建自签名的证书
USE Northwind
GO
CREATE CERTIFICATE cert_TestCert1
    ENCRYPTION BY PASSWORD = 'P@ssw0rd'
    WITH SUBJECT = 'TestCert1',
    START_DATE = '1/31/2006',
    EXPIRY_DATE = '1/31/2008'
GO

[过程]
过程一共分为4个部分

SELECT * FROM sys.certificates

--3.)备份导出证书和私钥
BACKUP CERTIFICATE cert_TestCert1
    TO FILE = 'c:DBFileTestCert1.cer'
    WITH PRIVATE KEY
        (DECRYPTION BY PASSWORD = 'P@ssw0rd' ,
         FILE = 'c:DBFileTestCert1_pvt' ,
         ENCRYPTION BY PASSWORD = 'Pa$$w0rd')

--==================(II)加密列数据=====================

DEMO 2
使用密钥对列数据进行加密

--3.)从备份文件还原服务主密钥
RESTORE SERVICE MASTER KEY FROM FILE = 'C:DBFileSMK.bak'
DECRYPTION BY PASSWORD = 'P@ssw0rd'

DEMO 1
了解SQL2005加密层次结构

/*
[课程]使用数据库加密保护敏感数据

--4.)对数据库主密钥进行备份
USE Northwind
GO
BACKUP MASTER KEY
    TO FILE = 'C:DBFileDMK.bak'
    ENCRYPTION BY PASSWORD = 'P@ssw0rd!@'
GO

A.       确保只有拥有该证书对应私钥的用户才能执行该存储过程

--2.)创建数据库主密钥
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@ssw0rd'
GO

--     创建非对称密钥成功
CREATE ASYMMETRIC KEY asy_TestKey2 WITH ALGORITHM = RSA_1024
GO

--==================(I)服务主密钥=====================
--1.)备份服务主密钥到文件
BACKUP SERVICE MASTER KEY TO FILE = 'C:DBFileSMK.bak'
ENCRYPTION BY PASSWORD = 'P@ssw0rd'

B.       加密存储过程,防止其它人查看到存储过程中的T-SQL语句

--2.)查看数据库加密状态
SELECT [name], is_master_key_encrypted_by_server
    FROM sys.databases WHERE name = 'Northwind';
GO

--==================(II)数据库主密钥=====================
--1.)为Northwind数据库创建数据库主密钥
USE Northwind
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@ssw0rd'
GO

--3.)

--     打开数据库主密钥未
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'P@ssw0rd'
SELECT * FROM sys.openkeys

--==================(III)绕过加密数据的攻击=====================
--1.)攻击者使用其它数据行的加密数据替换某一行的数据
SELECT * FROM EmpSalary

--4.)使用证书加密、解密数据
DECLARE @cleartext varbinary(200)
DECLARE @cipher varbinary(200)
SET @cleartext = CONVERT(varbinary(200), 'Test text string')
SET @cipher = EncryptByCert(Cert_ID('cert_TestCert1'), @cleartext)
SELECT @cipher

C.      非对称密钥的公钥

--==================(I)准备=====================
--1.)创建示例表
USE Northwind
IF EXIST dbo.EmpSalary DROP TABLE dbo.EmpSalary;

*/

SELECT * FROM sys.asymmetric_keys

3.       在SQL Server 2005中使用证书签署存储过程的目的是:(D)

*/

--3.)关闭打开的对称密钥
CLOSE SYMMETRIC KEY sym_Salary

--4.)查看表中存放的数据
SELECT * FROM EmpSalary           

CREATE TABLE dbo.EmpSalary(
    EmpID int,
    Title nvarchar(50),
    Salary varbinary(500)
)
GO

--2.)从文件导入证书
USE Northwind
GO
CREATE CERTIFICATE cert_TestCert2
    FROM FILE = 'C:DBFileMSCert.cer'
GO

1.       在SQL Server 2005中,数据库的主密钥可以直接用来加密保护:(AB)

SELECT * FROM sys.symmetric_keys WHERE [name] = 'sym_Salary'

--     恢复服务主密钥对数据库主密钥的保护
ALTER MASTER KEY
    ADD ENCRYPTION BY SERVICE MASTER KEY
CLOSE MASTER KEY

SELECT * FROM sys.openkeys        --查看打开的对称密钥

--5.)删除服务主密钥对数据库主密钥的保护
--     创建非对称密钥成功,自动使用服务主密钥解密并使用该数据库主密钥
CREATE ASYMMETRIC KEY asy_TestKey1 WITH ALGORITHM = RSA_1024
GO
--     删除服务主密钥对数据库主密钥的保护
ALTER MASTER KEY
    DROP ENCRYPTION BY SERVICE MASTER KEY
GO
--      查看数据库的加密状态
SELECT [name], is_master_key_encrypted_by_server
    FROM sys.databases WHERE name = 'Northwind';

--3.)关闭对称密钥
CLOSE SYMMETRIC KEY sym_Salary

SELECT * FROM sys.certificates

本文由澳门威斯尼人平台登录发布于澳门威斯尼人平台登录,转载请注明出处:SQL问题解决集锦,使用数据库加密保护敏感数据

相关阅读