在 SQL Server 中存储密码 [英] storing passwords in SQL Server

问题描述

在 SQL Server 2008 中存储用户密码的推荐做法是什么?

What is the recommended practice for storing user passwords in SQL Server 2008?

我正在为 Intranet 存储用户详细信息，并希望获得有关存储用户详细信息(例如姓名、密码和用户访问权限等)的最佳方式的建议.我正在考虑创建一个 nvarchar 列，然后加密此文本在插入表之前.

I am storing user details for an intranet, and would like to get advice on best way to store a user details such as name, password and user access privillages etc. I am thinking of creating a nvarchar column and then encrypt this text before inserting into the table.

推荐答案

通常的密码存储方式，是对密码使用哈希函数，但要预先加盐.对密码加盐"很重要，以保护自己免受彩虹表攻击.

The usual way to store password, is to use a hash function on the password, but to salt it beforehand. It is important to "salt" the password, to defend oneself against rainbow table attacks.

所以你的桌子应该看起来像这样

So your table should look something like that

._______._________________.______________.
|user_id|hash             |salt          |
|-------|-----------------|--------------|
|12     |adsgasdg@g4wea...|13%!#tQ!#3t...|
|       |...              |...           |

在检查给定密码是否与用户匹配时，您应该将盐连接到给定密码，并计算结果字符串的哈希函数.如果哈希函数输出与 hash 列匹配 - 这是正确的密码.

When checking if a given password matches a user, you should concatenate the salt to the given password, and calculate the hash function of the result string. If the hash function output matches the hash column - it is the correct password.

然而，重要的是要理解 salt-hash 的想法有一个特定的原因——防止任何有权访问数据库的人知道任何人的密码(反转散列函数输出被认为是一个难题).例如，银行的 DBA 将无法登录您的银行帐户，即使他有权访问所有列.

It is important to understand however that the salt-hash idea has a specific reason -- to prevent anyone with access to the database from knowing anyone password (it is considered difficult problem to reverse a hash function output). So for example, the DBA of the bank, wouldn't be able to log-in to your bank account, even if he has access to all columns.

如果您认为您的用户将使用敏感密码(例如他们 Gmail 帐户的密码)作为您网站的密码，您也应该考虑使用它.

You should also consider using it if you think your users will use a sensitive password (for example their password for their gmail account) as a password to your website.

恕我直言，它并不总是需要的安全功能.所以你应该考虑一下你是否想要它.

IMHO it is not always a security feature which is needed. So you should think whether or not you want it.

见这篇文章 对这种机制的一个很好的总结.

See this article for a good summary of this mechanism.

更新: 值得一提的是，为了防止针对个人密码哈希的定向攻击提供额外的安全性，您应该使用 bcrypt，它可以任意地难以计算.(但除非你真的害怕神秘的黑衣人针对你的特定数据库，我认为 sha1 已经足够好了.为了这种额外的安全性，我不会为我的项目引入另一个依赖项.也就是说，没有理由不使用 sha1100 次，这会产生类似的效果).

Update: It is worth mentioning, that for extra security against targeted attack for reversing individual password's hash, you should use bcrypt, which can be arbitrarily hard to compute. (But unless you're really afraid from mysterious man in black targeting your specific database, I think sha1 is good enough. I wouldn't introduce another dependency for my project for this extra security. That said, there's no reason not to use sha1 100 times, which would give a similar effect).

这篇关于在 SQL Server 中存储密码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！