T-SQL问题解决集锦 数据加解密全集

MsSQL 前端之家
前端之家收集整理的这篇文章主要介绍了T-SQL问题解决集锦 数据加解密全集，前端之家小编觉得挺不错的，现在分享给大家，也给大家做个参考。

以下代码已经在sqlServer2008上的示例数据库测试通过

问题一：如何为数据进行加密与解密，避免使用者窃取机密数据？


对于一些敏感数据，如密码、卡号，一般不能使用正常数值来存储。否则会有安全隐患。以往的加密解密都有前端应用程序来辅助完成。而数据库一般只能加密不能解密。 从2005开始提供了数据库层面的数据加密与解密。其实现方式主要有以下： 1、 利用CONVERT改变编码方式：
利用该函数把文字或数据转换成VARBINARY。但该方式不具备保护数据的能力，仅避免浏览数据的过程中能直接看到敏感数据的作用。 2、 利用对称密钥：
搭配EncryptByKey进行数据加密。使用DecryptByKey函数进行解密。这种方式比较适合大数据量。因为对称密钥的过程好用资源较少。 3、 利用非对称密钥：
搭配EncryptByAsymKey进行数据加密。使用DecryptByAsymKey函数进行解密。用于更高安全级别的加解密数据。因为耗用资源叫多。 4、 利用凭证的方式：
搭配EncryptByCert进行加密和DecryptByCert函数进行解密。比较类似非对称密钥。 5、 利用密码短语方式：
搭配EncryptBypassPhrase进行加密，使用DecryptByPassPhrase函数来解密。可以使用有意义的短语或其他数据行，当成加密、解密的关键字，比较适合一般的数据加解密。
案例：
1、 Convert方式：
<div class="codetitle"><a style="CURSOR: pointer" data="94572" class="copybut" id="copybut94572" onclick="doCopy('code94572')"> 代码如下:
<div class="codebody" id="code94572">
a) USE tempdb
b) GO
c) CREATE TABLE test
d) (
e) userID INT IDENTITY(1,1),
f) userName VARCHAR(10),
g) userSalary FLOAT,
h) cyberalary NVARCHAR(MAX)
i) ) ;
j)
k) INSERT INTO TEST
l) ( userName,userSalary )
m) VALUES ( 'taici',1234 ),
n) ( 'hailong',3214 ),
o) ( 'meiyuan',1111 )
p) --ALTER TABLE test
q) --ADD userNewSalary VARBINARY(512)
r) --使用转换函数把数据转换成varbinary，改变编码方式。
s) SELECT ,
t) CONVERT(VARBINARY(512),userSalary)
u) FROM test
v) --把数据转换成int，可以恢复原有编码方式
w) SELECT ,
x) CONVERT(INT,userSalary)
y) FROM test

2、对称密钥:
<div class="codetitle"><a style="CURSOR: pointer" data="86286" class="copybut" id="copybut86286" onclick="doCopy('code86286')"> 代码如下:
<div class="codebody" id="code86286">
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(KEY_GUID('SymKey123'),CONVERT(VARCHAR(max),AddressLine1))
l) FROM Person.Address
m)
n) --检查加密后长度，利用datalength()函数
o) SELECT DATALENGTH(ENCRYPTBYKEY(KEY_GUID('SymKey123'),CONVERT(VARCHAR(MAX ),AddressLine1)))
p) FROM Person.Address
q) GO
r) --把加密后数据更新到原来另外的列上
s) UPDATE Person.Address
t) SET AddressLine2=ENCRYPTBYKEY(KEY_GUID('SymKey123'),AddressLine1))
u) --解密:解密过程同样需要OPEN SYMMETRIC KEY,且需要利用DECRYPTBYKEY 和CONVERT函数
v) OPEN SYMMETRIC KEY SymKey123 DECRYPTION BY PASSWORD='P@ssw0rd';
w)
x) SELECT AddressID,CONVERT (VARCHAR(MAX ),DECRYPTBYKEY(AddressLine2)))
y) FROM Person.Address

3、非对称密钥：
<div class="codetitle"><a style="CURSOR: pointer" data="64259" class="copybut" id="copybut64259" onclick="doCopy('code64259')"> 代码如下:
<div class="codebody" id="code64259">
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(MAX)
i) GO
j) --进行加密
k) SELECT ,ENCRYPTBYASYMKEY(ASYMKEY_ID ('AsymKey123'),AddressLine1))
l) FROM Person.Address
m) GO
n)
o) --把数据更新到一个新列
p) UPDATE Person.Address
q) SET AddressLine3=ENCRYPTBYASYMKEY(ASYMKEY_ID ('AsymKey123'),AddressLine1))
r)
s)
t) SELECT --addressline3
u) FROM Person.Address
v)
w) --解密：此过程一定要使用密码来解密，此处的类型要与加密时相同，比如加密时用varchar,而这里用nvarchar的话是解密不了的。
x) SELECT TOP 10 AddressID,CONVERT(VARCHAR(MAX),DECRYPTBYASYMKEY(ASYMKEY_ID('AsymKey123'),AddressLine3,N'P@ssw0rd'))) AS Decryptedata
y) FROM Person.Address

4、证书加密：
<div class="codetitle"><a style="CURSOR: pointer" data="3780" class="copybut" id="copybut3780" onclick="doCopy('code3780')"> 代码如下:
<div class="codebody" id="code3780">
a) --证书加密：首先建立证书（certificate）
b) 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(CERT_ID('certKey123'),AddressLine1)) cyberAddress
j) FROM Person.Address
k)
l) --添加新列存放加密数据
m) ALTER TABLE Person.Address ADD AddressLine4 Nvarchar(MAX )
n)
o) --把加密后数据放到新列
p) UPDATE Person.Address
q) SET AddressLine4=ENCRYPTBYCERT(CERT_ID('certKey123'),AddressLine1))
r)
s) --解密
t) SELECT AddressID,DECRYPTBYCERT(CERT_ID('certKey123'),AddressLine4,N'P@ssw0rd'))) DecryAddress
u) FROM Person.Address

5、短语加密：
<div class="codetitle"><a style="CURSOR: pointer" data="85217" class="copybut" id="copybut85217" onclick="doCopy('code85217')"> 代码如下:<div class="codebody" id="code85217">
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(256)
g)
h) --将数据更新，过程中使用P@ssw0rd和AddressID数据行当成密码短语
i)
j) UPDATE Person.Address
k) SET AddressLine5=ENCRYPTBYPASSPHRASE('P@ssw0rd',AddressID)
l)
m) SELECT FROM Person.Address

问题二：如何保护数据库对象定义，避免发生过渡暴露敏感信息？

一般的保护措施是在创建对象时使用WITH ENCRYPTION来把对象加密，这样就无法查看定义。但是问题是对于维护来说就成了问题，而且备份还原时这部分对象是会丢失的。 其中一个解决方法是把定义语句放到对象的【扩展属性】中保存，这样能解决上面的问题。 下面举个例子:
<div class="codetitle"><a style="CURSOR: pointer" data="67464" class="copybut" id="copybut67464" onclick="doCopy('code67464')"> 代码如下:<div class="codebody" id="code67464">
--1、建立已加密的存储过程
USE AdventureWorks
GO
CREATE PROC test
WITH ENCRYPTION
AS
SELECT SUSER_SNAME(),
USER_NAME()
GO
--2、将上述定义内容去除，利用短语加密搭配EncryptByPassPhrase函数加密，然后在用sys.sp_addextendedproperty存储过程，指定一个扩展名称。
USE AdventureWorks
GO
DECLARE @sql VARCHAR(MAX)
SET @sql = 'CREATE PROC Test WITH ENCRYPTION AS SELECT suer_sname(),user_name() GO' --3、将内容加密后转换成sql_variant数据类型
DECLARE @bsql sql_VARIANT
SET @bsql = ( SELECT CONVERT(sql_VARIANT,ENCRYPTBYPASSPHRASE('P@ssw0rd',
CONVERT(VARCHAR(MAX),@sql)))
) --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(),user_name() GO',
@level1name = N'test'
GO --5、还原
DECLARE @pwd VARCHAR(100)= 'P@ssw0rd'
--密码短语 DECLARE @proc VARCHAR(100)= 'test'
--存储过程名 DECLARE @exName NVARCHAR(100)= '代码内容'
--扩充属性名
--将原本结果查询
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 ( P.name = @exName )
AND ( ( sp.type = N'p'
OR sp.type = N'rf'
OR sp.type = 'pc'
)
AND ( sp.name = @proc
AND SCHEMA_NAME(sp.schema_id) = 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事件或者是转换成应用程序内部错误信息。 3. 使用参数化查询或者存储过程 注意：
动态sql是导致sql注入的主凶 作者： 黄钊吉

数据加解密

猜你在找的MsSQL相关文章

SQL Server2012高可用之日志传送测试
（一）日志传送架构 （1.1）相关服务器 主服务器 ：用于生产的服务器，上面运行这生产SQL ...
SQL server事物复制报错：要复制的 LOB 数据的长度(xxxxx)超出了配置的最大值 65536
(一)事故背景 最近在SQL Server 2012生产数据库上配置完事物复制（发布订阅）后，生产数据...
SQL Server2012高可用之事物复制(发布订阅)测试
（一）测试目的 目前公司使用的SQL SERVER 2012高可用环境为主备模式，其中主库可执行读写...
使用zabbix监控sql server的发布订阅
（一）背景个人在使用sql server时，用到了sql server的发布订阅来做主从同步，类似MySQL的...
SQL学习（四）集合运算
UNION和OR谓词 找出 product 和 product2 中售价高于 500 的商品的基本信息. select * fro...
SQL学习（五） 高级处理
5.1 请说出针对本章中使用的 product（商品）表执行如下 SELECT 语句所能得到的结果。 SEL...
SQL学习(一）初识数据库
datawhale组队学习SQL task01
SQL学习(二）基础查询与排序
datawhale数据库组队学习第二天打卡
SQL Server 2005 返回修改后的数据
今天在公司看到同事写了个SQL2005的新特性的文章，觉得很实用，在这里和大家分享下。原著作...
SQL Server 数据库最小宕机迁移方案
一、目的 在做SQL Server数据库维护的时候，当上司要求我们把几十G的数据文件搬动到其它服...
MySQLMsSQLOracleSqlitePostgre SQLMariadbMongoDBNoSQLHBaseJDBC
undo日志persistent-cmysql-error-postal-codesql-match-almysql-5.6mysql-8.0database-tri安装路径系统错误data_dir丢失文件主从同步sql_mode数据库目录匿名用户character_seID归零数据库位置查询表重复字段查询字段截断日志SUSPECT7391Remote ServeLinked Serve玄学问题登录不上开启远程访问