以下是表结构及加密解密代码和新增、查询、修改功能接口存储过程。
功能接口使用方法请查看：http://itjoy.net/262.html
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 |
/* CREATE TABLE [dbo].[pswmanage]( [fuse] [nvarchar](20) NOT NULL, [fserver] [nvarchar](20) NOT NULL, [fsystem] [nvarchar](50) NULL, [fenuser] [nvarchar](50) NOT NULL, [fenpsw] [nvarchar](50) NOT NULL, [fnote] [nvarchar](200) NULL, [fenkey] [nvarchar](200) NOT NULL, CONSTRAINT [IX_pswmanage] UNIQUE NONCLUSTERED ( [fuse] ASC, [fserver] ASC, [fsystem] ASC, [fenuser] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] */ GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO /*1. 核心加密解密函数 加密字串 nvarchar(50)=dbo.enpsw('字串 nvarchar(50)','密钥 nvarchar(50)') 加密字串用相同密钥再次加密即为解密 */ IF OBJECT_ID('dbo.enpsw', 'FN') IS NOT NULL DROP FUNCTION dbo.enpsw; GO create FUNCTION [dbo].[enpsw](@password as nvarchar(50),@key as nvarchar(50)) RETURNS nvarchar(50) AS BEGIN declare @passworden nvarchar(50) declare @i int,@j int declare @ci nchar,@cj nchar set @i=0 set @j=0 set @passworden ='' set @key=HashBytes('MD5',@key) --使用KEY的MD5特征码作为密钥 while(@i < len(@password)) begin --逐字加密 if @j>len(@key) --待加密字串长于密钥时，循环使用密钥 set @j=@j-len(@key) SET @ci=substring(@password,@i+1,1) SET @cj=substring(@key,@j+1,1) set @passworden=@passworden+NCHAR(UNICODE(@ci)^UNICODE(@cj)) --使用密钥可逆加密 set @i=@i+1 set @j=@j+1 END RETURN @passworden end GO /*2. 新增资料 exec dbo.addpsw 密钥，使用人，服务器，系统，用户名，密码[，备注] */ IF OBJECT_ID('dbo.addpsw', 'P') IS NOT NULL DROP PROCEDURE dbo.addpsw; GO create PROC addpsw ( @key nvarchar(50), @fuse nvarchar(20), @fserver nvarchar(20), @fsystem nvarchar(50), @fuser nvarchar(50), @fpsw nvarchar(50), @fnote nvarchar(200)=NULL ) AS SET NOCOUNT ON insert into pswmanage(fuse,fserver,fsystem,fenuser,fenpsw,fnote,fenkey) select @fuse,@fserver,@fsystem ,dbo.enpsw(@fuser,@key+@fuse) --用户名加盐 ,dbo.enpsw(@fpsw,@key+@fserver) --密码加盐 ,@fnote ,dbo.enpsw(@key,@fuse) --key加密，用于删除修改权限验证 GO /*3. 查询数据 exec dbo.getpsw 密钥，使用人[，服务器[，系统[，用户名]]] */ IF OBJECT_ID('dbo.getpsw', 'P') IS NOT NULL DROP PROCEDURE dbo.getpsw; GO create PROC getpsw ( @key nvarchar(50), @fuse nvarchar(20), @fserver nvarchar(20)=NULL, @fsystem nvarchar(50)=NULL, @fuser nvarchar(50)=NULL ) AS SET NOCOUNT ON select fuse,fserver,fsystem ,dbo.enpsw(fenuser,@key+@fuse) as username ,dbo.enpsw(fenpsw,@key+fserver) as 'password' ,fnote from pswmanage where fuse=@fuse --and fenkey=dbo.enpsw(@key,@fuse) --验证权限 and (fserver=@fserver or ISNULL(@fserver,'')='') and (fsystem=@fsystem or ISNULL(@fsystem,'')='') and (fenuser=dbo.enpsw(@fuser,@key+@fuse) or ISNULL(@fuser,'')='') order by fuse,fserver,fsystem GO /*4. 批量删除数据 exec delpsw 密钥，使用人[，服务器[，系统[，用户名]]] */ IF OBJECT_ID('dbo.delpsw', 'P') IS NOT NULL DROP PROCEDURE dbo.delpsw; GO create PROC delpsw ( @key nvarchar(50), @fuse nvarchar(20), @fserver nvarchar(20)=NULL, @fsystem nvarchar(50)=NULL, @fuser nvarchar(50)=NULL ) AS SET NOCOUNT ON delete from pswmanage where fuse=@fuse and fenkey=dbo.enpsw(@key,@fuse) --验证权限 and (fserver=@fserver or ISNULL(@fserver,'')='') and (fsystem=@fsystem or ISNULL(@fsystem,'')='') and (fenuser=dbo.enpsw(@fuser,@key+@fuse) or ISNULL(@fuser,'')='') GO /*5. 批量修改密钥 exec chgpswkey 旧密钥，新密钥，使用人[，服务器[，系统[，用户名]]] */ IF OBJECT_ID('dbo.chgpswkey', 'P') IS NOT NULL DROP PROCEDURE dbo.chgpswkey; GO create PROC chgpswkey ( @oldkey nvarchar(50), @newkey nvarchar(50), @fuse nvarchar(20), @fserver nvarchar(20)=NULL, @fsystem nvarchar(50)=NULL, @fuser nvarchar(50)=NULL ) AS SET NOCOUNT ON update pswmanage set fenuser=dbo.enpsw(dbo.enpsw(fenuser,@oldkey+@fuse),@newkey+@fuse), --按原密钥解密后再用新密钥加密 fenpsw=dbo.enpsw(dbo.enpsw(fenpsw,@oldkey+fserver),@newkey+fserver), --按原密钥解密后再用新密钥加密 fenkey=dbo.enpsw(@newkey,@fuse) where fuse=@fuse and fenkey=dbo.enpsw(@oldkey,@fuse) --验证权限 and (fserver=@fserver or ISNULL(@fserver,'')='') and (fsystem=@fsystem or ISNULL(@fsystem,'')='') and (fenuser=dbo.enpsw(@fuser,@oldkey+@fuse) or ISNULL(@fuser,'')='') GO /*6. 复制资料 exec copypsw 旧密钥，新密钥，旧使用人，新使用人[，服务器[，系统[，用户名]]] */ IF OBJECT_ID('dbo.copypsw', 'P') IS NOT NULL DROP PROCEDURE dbo.copypsw; GO create PROC copypsw ( @oldkey nvarchar(50), @newkey nvarchar(50), @folduse nvarchar(20), @fnewuse nvarchar(20), @fserver nvarchar(20)=NULL, @fsystem nvarchar(50)=NULL, @fuser nvarchar(50)=NULL ) AS SET NOCOUNT ON insert into pswmanage(fuse,fserver,fsystem,fenuser,fenpsw,fnote,fenkey) select @fnewuse,fserver,fsystem, dbo.enpsw(dbo.enpsw(fenuser,@oldkey+@folduse),@newkey+@fnewuse) as fenuser, --按原密钥解密后再用新密钥加密 dbo.enpsw(dbo.enpsw(fenpsw,@oldkey+fserver),@newkey+fserver) as fenpsw, --按原密钥解密后再用新密钥加密 fnote, dbo.enpsw(@newkey,@fnewuse) as fenkey from pswmanage where fuse=@folduse and fenkey=dbo.enpsw(@oldkey,@folduse) --验证权限 and (fserver=@fserver or ISNULL(@fserver,'')='') and (fsystem=@fsystem or ISNULL(@fsystem,'')='') and (fenuser=dbo.enpsw(@fuser,@oldkey+@folduse) or ISNULL(@fuser,'')='') GO |
© 2017, ITJOY.NET. 版权所有. 如未注明，均为原创，转载请注明出处。
“基于SQL Server 的密码管理解决方案”的一个回复