基本含义：RSA公开密钥密码体制。所谓的公开密钥密码体制就是使用不同的加密密钥与解密密钥，是一种“由已知加密密钥推导出解密密钥在计算上是不可行的”密码体制。
在SQL SERVER中实现RSA加密算法
--判断是否为素数
if object_id('f_primeNumTest') is not null
drop function f_primeNumTest
create function [dbo].[f_primeNumTest]
(@p int)
returns bit
begin
declare @flg bit,@i int
select @flg=1, @i=2
while @i<=sqrt(@p)
begin
if(@p%@i=0 )
begin
set @flg=0
break
end
set @i=@i+1
end
return @flg
end
go
--判断两个数是否互素
if object_id('f_isNumsPrime') is not null
drop function f_isNumsPrime
go
create function f_isNumsPrime
(@num1 int,@num2 int)
returns bit
begin
declare @tmp int,@flg bit
set @flg=1
while (@num2%@num1<>0)
begin
select @tmp=@num1,@num1=@num2%@num1,@num2=@tmp
end
if @num1=1
set @flg=0
return @flg
end
go
--产生密钥对
if object_id('p_createKey') is not null
drop proc p_createKey
go
create proc p_createKey
@p int,@q int
as
begin
declare @n bigint,@t bigint,@flag int,@d int
if dbo.f_primeNumTest(@p)=0
begin
print cast(@p as varchar)+'不是素数，请重新选择数据'
return
end
if dbo.f_primeNumTest(@q)=0
begin
print cast(@q as varchar)+'不是素数，请重新选择数据'
return
end
print '请从下列数据中选择其中一对，作为密钥'
select @n=@p*@q,@t=(@p-1)*(@q-1)
declare @e int
set @e=2
while @e<@t
begin
if dbo.f_isNumsPrime(@e,@t)=0
begin
set @d=2
while @d<@n
begin
if(@e*@d%@t=1)
print cast(@e as varchar)+space(5)+cast(@d as varchar)
set @d=@d+1
end
end
set @e=@e+1
end
end
/*加密函数说明，@key 为上一个存储过程中选择的密码中的一个,@p ,@q 产生密钥对时选择的两个数。获取每一个字符的unicode值，然后进行加密，产生个字节的位数据*/
if object_id('f_RSAEncry') is not null
drop function f_RSAEncry
go
create function f_RSAEncry
(@s varchar(100),@key int ,@p int ,@q int)
returns nvarchar(4000)
as
begin
declare @crypt varchar(8000)
set @crypt=''
while len(@s)>0
begin
declare @i bigint,@tmp varchar(10),@k2 int,@leftchar int
select @leftchar=unicode(left(@s,1)),@k2=@key/2,@i=1
while @k2>0
begin
set @i=(cast(power(@leftchar,2) as bigint)*@i)%(@p*@q)
set @k2=@k2-1
end
set @i=(@leftchar*@i)%(@p*@q)
set @tmp=''
select @tmp=case when @i%16 between 10 and 15 then char( @i%16+55) else cast(@i%16 as varchar) end+@tmp,@i=@i/16
from (select number from master.dbo.spt_values where type='p' and number<10 )K
order by number desc
set @crypt=@crypt+right(@tmp,6)
set @s=stuff(@s,1,1,'')
end
return @crypt
end
--解密：@key 为一个存储过程中选择的密码对中另一个数字,@p ,@q 产生密钥对时选择的两个数
if object_id('f_RSADecry') is not null
drop function f_RSADecry
go
create function f_RSADecry
(@s nvarchar(4000),@key int ,@p int ,@q int)
returns nvarchar(4000)
as
begin
declare @crypt varchar(8000)
set @crypt=''
while len(@s)>0
begin
declare @leftchar bigint
select @leftchar=sum(data1)
from (select case upper(substring(left(@s,6), number, 1)) when 'A' then 10
when 'B' then 11
when 'C' then 12
when 'D' then 13
when 'E' then 14
when 'F' then 15
else substring(left(@s,6), number, 1)
end* power(16, len(left(@s,6)) - number) data1
from (select number from master.dbo.spt_values where type='p')K
where number <= len(left(@s,6))
) L
declare @k2 int,@j bigint
select @k2=@key/2,@j=1
while @k2>0
begin
set @j=(cast(power(@leftchar,2)as bigint)*@j)%(@p*@q)
set @k2=@k2-1
end
set @j=(@leftchar*@j)%(@p*@q)
set @crypt=@crypt+nchar(@j)
set @s=stuff(@s,1,6,'')
end
return @crypt
end
使用方法：
1、先使用p_createkey生成一对密钥，参数为两个参数
2、调用相应进行加密、解密
编写触发器，使两表有同步加密的效果
USE [test]
GO
/****** Object: Trigger [dbo].[trig_insert] Script Date: 07/24/2018 14:33:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--创建insert触发器
ALTER trigger [dbo].[trig_insert]
on [dbo].[tt]
after insert,update,delete
as
begin
if object_id(N'ttt',N'U') is null
create table ttt(id int,name varchar(50),description varchar(50));
truncate table ttt;
insert into ttt(id,name,description) select id,name,dbo.f_RSAEncry(description,779,1163,59) from tt
end
创建解密视图
create view ttt_view with encryption as select id,name,dbo.f_RSADecry(description,35039,1163,59) description from ttt;
随时随地看视频
