mssql2000에서의 패스워드 암호화

패스워드같은 경우는 일반적으로 디비에 암호화해서 넣죠.

저같은 경우는 그동안 클라이언트에서 md5등을 이용해서 암호화하고, 그걸 시퀄에 업데이트하는 식으로 개발을 해 왔는데요.
이제 보니 시퀄자체에 그런 함수가 있었네요.
문서화되지 않은 함수라서 도움말에는 나오지 않는군요.

어쨌든...
그 함수는 pwdencrypt과 pwdcompare입니다.

첫번째는
pwdencrypt('1234')
요렇게 쓰면 암화화된 값을 리턴합니다.

update 사용자테이블
set 암호컬럼=pwdencrypt('1234')
요런식으로 쓰면 되겠네요.

두번째 함수는 암호화된 컬럼과 비교하는 함수인데요.
처음엔
암호컬럼=pwdencrypt('1234')

이렇게 쓰면 되겠지 싶었는데...
이게 false가 되더군요.

한참을 내가 뭘 잘못했나 싶었는데...
하하하...
pwdcompare 요게 따로 있더군요.

사용법은

pwdcompare('1234', 암호컬럼)
이게 일치하면 1을 리턴하네요.

참고로 암호컬럼은 varchar를 쓰면 안되구요.
varbinary를 써야 한다는 사실.
그리고 컬럼길이도 넉넉히 잡아주어야 한다는 사실.

mssql2005부터는 문서화된 암호화함수가 있더군요.
하지만 저같은 경우는 백업이나 기타등등 때문에 에이전트가 필요한데, 2005익스프레스는 에이전트가 제공이 안되어서 --;
걍 msde2000을 거래처 배포용으로 사용하고 있습니다.

참고로 제가 개인적으로 사용하는 로그인 관련 프로시저를 첨부합니다.
여기에 이 함수를 사용했거든요.

  1 create proc dbo.uspLogin


  2     @userCode varchar(15)


  3   , @pwd varchar(100)


  4   , @clientVersion varchar(100)


  5   , @ipAddress varchar(100)


  6   , @devLogin tinyint=0


  7 with encryption


  8 as


  9 


 10 set nocount on


 11 


 12 declare @station varchar(100)


 13 declare @userID smallint


 14 declare @userName varchar(100)


 15 declare @macAddress varchar(100)


 16 declare @netAddress varchar(100)


 17 declare @host varchar(100)


 18 declare @pwdChangePeriod int


 19 declare @lastPwdChange smalldatetime


 20 declare @requirePwdChange tinyint


 21 


 22 /**************************************************


 23 개발자 로그인이면 걍 패스


 24 **************************************************/


 25 if @devLogin=1 begin


 26     select '개발팀' as station


 27          , 1 as userID


 28          , '관리자' as userName


 29          , 0 as requirepwdChange


 30     return


 31 end


 32 


 33 /**************************************************


 34 로그기록


 35 **************************************************/


 36 declare @tmp varchar(8000)


 37 set @tmp='exec dbo.uspLogin'


 38 + ' @userCode='+coalesce(''''+replace(@userCode, '''', '''''')+'''', 'null')


 39 --+', @pwd='+coalesce(''''+replace(@pwd, '''', '''''')+'''', 'null')


 40 +', @clientVersion='+coalesce(''''+replace(@clientVersion, '''', '''''')+'''', 'null')


 41 +', @ipAddress='+coalesce(''''+replace(@ipAddress, '''', '''''')+'''', 'null')


 42 +', @devLogin='+coalesce(cast(@devLogin as varchar), 'null')


 43 exec dbo.uspLogUpdate @logType=2, @userID=null, @description=@tmp


 44 


 45 /**************************************************


 46 등록된 계정인지 확인


 47 pwdencrypt함수를 사용해서 저장했으므로 pwdcompare를 사용해서 비교


 48 **************************************************/


 49 select @station=s.station


 50      , @userID=u.userID


 51      , @userName=u.userName


 52      , @macAddress=macAddress


 53 from dbo.stations s


 54 inner join dbo.users u on s.stationID=u.stationID


 55 where u.userCode=@userCode


 56   and pwdcompare(@pwd, u.pwd)=1


 57   and u.closed=0


 58 


 59 if @userID is null begin


 60     raiserror('등록되지 않은 유저이거나 암호가 잘못되었습니다.', 16, 1)


 61     return


 62 end


 63 


 64 /*********************************************


 65 중복로그인 체크


 66 로그인한 유저의 ID로 다른 활성 프로세스가 있다면 문제


 67 *********************************************/


 68 select @netAddress=net_address


 69      , @host=rtrim(hostname)


 70 from master.dbo.sysprocesses


 71 where spid=@@spid


 72 


 73 if exists(select *


 74           from master.dbo.sysdatabases d


 75           inner join master.dbo.sysprocesses p on d.dbid=p.dbid


 76           inner join dbo.users u on p.net_Address=u.macAddress


 77           where d.name=db_name()


 78             and p.net_address<>@netAddress


 79             and u.userID=@userID) begin


 80     raiserror('이미 다른 곳에서 접속중인 아이디입니다.', 16, 1)


 81     return


 82 end


 83 


 84 /*********************************************


 85 유저의 접속정보를 갱신


 86 같은 컴퓨터에서 다른 아이디로 로그인했을 수 있으므로


 87 맥어드레스가 같은 아이디가 있으면 맥어드레스값을 지움


 88 *********************************************/


 89 begin tran


 90 


 91 update dbo.users


 92 set macAddress=null


 93 where userID<>@userID


 94   and macAddress=@netAddress


 95 


 96 update dbo.users


 97 set clientVersion=@clientVersion


 98   , host=@host


 99   , ipAddress=@ipAddress


100   , macAddress=@netAddress


101   , lastLogin=getdate()


102 where userID=@userID


103 


104 commit tran


105 


106 /**************************************************


107 암호변경기간이 설정되어 있는지 확인


108 설정되어 있다면 가장 최근에 언제 변경했는지 확인


109 바꿔야 할 날짜가 지났으면 다시 변경해야 할 필요가 있음


110 **************************************************/


111 select @pwdChangePeriod=cast(value as int)


112 from dbo.config


113 where item='passwordChangePeriod'


114   and isnumeric(value)=1


115 


116 if @pwdChangePeriod>0 begin


117     select top 1 @lastPwdChange=jobDate


118     from dbo.passwordChangeLog


119     where userID=@userID


120     order by logID desc


121     if datediff(d, isnull(@lastPwdChange, '2001-01-01'), getdate())>@pwdChangePeriod begin


122         set @requirePwdChange=1


123     end


124 end


125 


126 /**************************************************


127 결과


128 **************************************************/


129 select @station as station


130      , @userID as userID


131      , @userName as userName


132      , isnull(@requirePwdChange, 0) as requirepwdChange