T-SQL:加盐密码 [英] T-SQL: Salted Passwords

问题描述

我正在寻找一个使用T-SQL存储过程为密码加盐的示例.当然，还有匹配过程可以验证用户.

I am looking for an example of salting passwords withing a T-SQL Stored Procedure. And of course the matching proc to validate a user.

创建过程ChangePassword(@Username nVarChar(50)，@Password nVarChar(50))

CREATE PROC ChangePassword(@Username nVarChar(50), @Password nVarChar(50))

创建PROC ValidateUser(@Username nVarChar(50)，@Password nVarChar(50))

CREATE PROC ValidateUser(@Username nVarChar(50), @Password nVarChar(50))

推荐答案

首先，我会在这里闲逛一下，说在安全方面，对数据库中的哈希密码进行加密通常是一种不好的做法.您将无法避免流量嗅探器监视到数据库的流量.防止这种情况的唯一方法是确保对数据库的连接进行加密，这通常意味着到数据库的所有其他通信都将被加密.可以解决此问题，但是更好的解决方案是让应用程序进行哈希处理.

First, I'm going to go out on a limb here and say that hashing passwords in the database is in general a bad practice with respect to security. You would not be protected against traffic sniffers watching traffic to the database. The only way to protect against that is to ensure your connection to the database was encrypted which generally means all other traffic to the database is going to be encrypted. It's possible to work around this, but the better solution is to have the application(s) do the hashing.

如Sam Saffron所述，您可以使用Hashbytes函数获取SHA1哈希.如果您想要更好的算法，则需要创建CLR过程.加盐将涉及为每个用户存储一个密码随机值，然后将该值附加到密码上并通过Hashbytes运行它:

As Sam Saffron stated, you can use the Hashbytes functions to get SHA1 hashing. If you want better algorithms you would need to create a CLR procedure. Salting would involve storing a cryptographically random value for each user, then appending that value to the password and running it through Hashbytes:

Create Procedure ValidateUser
    @Username nvarchar(50)
    , @Password nvarchar(50)
As

Declare @PasswordSalt varbinary(256)

Set @PasswordSalt = ( Select PasswordSalt From Users Where Username = @Username )

If @PasswordSalt Is Null
        -- generate a salt? 

Declare @Hash varbinary(max)
Set @Hash = Hashbytes('SHA1', @PasswordSalt + Cast('|' As binary(1)) + Cast(@Password As varbinary(100))

If Exists(  Select 1
            From Users
            Where Username = @Username
                And PasswordHash = @Hash )
    -- user is valid

Else
    -- user is not valid

请记住，盐应该是密码随机的，因此我不建议使用NewId().取而代之的是，我将使用.NET的RNGCryptoServiceProvider类之类的东西来生成该信息.

Remember that the salt should be cryptographically random so I would not recommend using NewId(). Instead, I would generate that using something like .NET's RNGCryptoServiceProvider class.

这篇关于T-SQL:加盐密码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！