日期：2012-06-27 04:57:00 来源：本站整理
T-SQL问题办理集锦 数据加解密全集[MSSQL防范]
本文“T-SQL问题办理集锦 数据加解密全集[MSSQL防范]”是由七道奇为您精心收集，来源于网络转载，文章版权归文章作者所有，本站不对其观点以及内容做任何评价，请读者自行判断，以下是其具体内容：
以下代码已经在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) (
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、对称密钥:
复制代码 代码以下:
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'),CONVERT(VARCHAR(max),AddressLine1))
u) --解密:解密历程一样需求OPEN SYMMETRIC KEY ,且需求操纵DECRYPTBYKEY 和CONVERT函数
v) OPEN SYMMETRIC KEY SymKey123 DECRYPTION BY PASSWORD='P@ssw0rd';
w)
x) SELECT AddressID,CONVERT(VARCHAR(MAX ) ,CONVERT (VARCHAR(MAX ),DECRYPTBYKEY(AddressLine2)))
y) 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(MAX)
i) GO
j) --举行加密
k) SELECT *,ENCRYPTBYASYMKEY(ASYMKEY_ID ('AsymKey123'),CONVERT(VARCHAR(MAX ),AddressLine1))
l) FROM Person.Address
m) GO
n)
o) --把数据更新到一个新列
p) UPDATE Person.Address
q) SET AddressLine3=ENCRYPTBYASYMKEY(ASYMKEY_ID ('AsymKey123'),CONVERT(VARCHAR(MAX ),AddressLine1))
r)
s)
t) SELECT *--addressline3
u) FROM Person.Address
v)
w) --解密:此历程一定要利用密码来解密,此处的范例要与加密时相同,比方加密时用varchar,而这里用nvarchar的话是解密不了的.
x) SELECT TOP 10 AddressID,CONVERT(VARCHAR(MAX),CONVERT (VARCHAR(MAX ),DECRYPTBYASYMKEY(ASYMKEY_ID('AsymKey123'),AddressLine3,N'P@ssw0rd'))) AS Decryptedata
y) FROM Person.Address
4、证书加密:
复制代码 代码以下:
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'),CONVERT (VARCHAR(MAX ),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'),CONVERT (VARCHAR(MAX ),AddressLine1))
r)
s) --解密
t) SELECT AddressID,CONVERT(VARCHAR(MAX ),CONVERT(VARCHAR(MAX ),DECRYPTBYCERT(CERT_ID('certKey123'),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(256)
g)
h) --将数据更新,历程中利用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() ,
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',
@level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'PROCEDURE',
@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'
)
)
问题3、若何让指定用户可以对数据表举行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来复原履行内容前的原始身份.
问题4、若何获得前端衔接的信息,如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代替系统表.
问题5、若何避免SQL注入的攻击?
关于数据库利用程序,无论是那种DBMS,SQL注入都是一大隐患.
要避免SQL注入,应当最最少做到以下几点:
1. 查抄输入的数据,利用程序不要相信誉户输入的数据,必须经过查验后才能输入数据库.要解除%、--等特别标记.
2. 避免果度表露错误信息.倡议可以转换成Windows事件大概是转换成利用程序内部错误信息.
3. 利用参数化查询大概存储历程
注意:
动态SQL是招致SQL注入的主凶
作者: 黄钊吉
以上是“T-SQL问题办理集锦 数据加解密全集[MSSQL防范]”的内容，如果你对以上该文章内容感兴趣，你可以看看七道奇为您推荐以下文章：
| 本文地址： | 与您的QQ/BBS好友分享! |
- 好的评价 如果您觉得此文章好，就请您
0%(0) 
- 差的评价 如果您觉得此文章差，就请您
0%(0) 
中查找“T-SQL问题办理集锦 数据加解密全集”更多相关内容
中查找“T-SQL问题办理集锦 数据加解密全集”更多相关内容评论内容只代表网友观点，与本站立场无关！
评论摘要(共 0 条，得分 0 分，平均 0 分)
查看完整评论