防止链接到SQL Server的表显示二进制的实际值 [英] Prevent Linked Table to SQL Server Showing Actual Values for Binary

问题描述

我们在SQL Server 2014中有一个用户表，我在Access数据库前端链接到该用户表，并且密码采用二进制64，因此，如果有人以某种方式在SSMS中打开该表，则看不到密码.

We have a user table in a SQL Server 2014 that I link to in an Access database front end and the password is in binary 64 so that the password cannot be seen if someone were to open the table somehow in SSMS.

但是Access知道所有这些信息，并将其完全转换为实际密码.如何解决这个问题，但仍使用它来验证输入到登录表单中的数据?

But Access knows all this and completely converts it to the actual password. How do I get around this yet still use it to validate data entered into a login form?

推荐答案

您对密码进行哈希处理.将密码存储为纯文本而不进行哈希处理是主要的不良习惯.

You hash the password. Storing passwords as plaintext without hashing is a major bad practice.

在Wikipedia上了解有关散列的更多信息 .简短的版本是单向操作:如果您具有密码，则可以创建哈希，但是如果您具有哈希，则无法获取密码，只能尝试对随机密码进行哈希处理，看看它们是否相同

Read more about hashing on Wikipedia. The short version it's a one-way operation: if you have the password, you can create the hash, but if you have the hash, there's no way to get the password except trying to hash random passwords and see if they're the same.

但是，VBA中的散列相当复杂.使用.Net哈希对象还有更多简单的答案，但是我使用CNG API，它具有许多优势，例如硬件加密支持，零依赖性和算法选择的灵活性:

However, hashing in VBA is rather complicated. There are more simple answers that use .Net hashing objects, but I use the CNG API, which has numerous advantages such as hardware crypto support, zero dependencies and flexibility in the choice of algorithm:

Public Declare PtrSafe Function BCryptOpenAlgorithmProvider Lib "BCrypt.dll" (ByRef phAlgorithm As LongPtr, ByVal pszAlgId As LongPtr, ByVal pszImplementation As LongPtr, ByVal dwFlags As Long) As Long
Public Declare PtrSafe Function BCryptCloseAlgorithmProvider Lib "BCrypt.dll" (ByVal hAlgorithm As LongPtr, ByVal dwFlags As Long) As Long
Public Declare PtrSafe Function BCryptCreateHash Lib "BCrypt.dll" (ByVal hAlgorithm As LongPtr, ByRef phHash As LongPtr, pbHashObject As Any, ByVal cbHashObject As Long, ByVal pbSecret As LongPtr, ByVal cbSecret As Long, ByVal dwFlags As Long) As Long
Public Declare PtrSafe Function BCryptHashData Lib "BCrypt.dll" (ByVal hHash As LongPtr, pbInput As Any, ByVal cbInput As Long, Optional ByVal dwFlags As Long = 0) As Long
Public Declare PtrSafe Function BCryptFinishHash Lib "BCrypt.dll" (ByVal hHash As LongPtr, pbOutput As Any, ByVal cbOutput As Long, ByVal dwFlags As Long) As Long
Public Declare PtrSafe Function BCryptDestroyHash Lib "BCrypt.dll" (ByVal hHash As LongPtr) As Long
Public Declare PtrSafe Function BCryptGetProperty Lib "BCrypt.dll" (ByVal hObject As LongPtr, ByVal pszProperty As LongPtr, ByRef pbOutput As Any, ByVal cbOutput As Long, ByRef pcbResult As Long, ByVal dfFlags As Long) As Long

Public Function NGHash(pData As LongPtr, lenData As Long, Optional HashingAlgorithm As String = "SHA1") As Byte()
    'Erik A, 2019
    'Hash data by using the Next Generation Cryptography API
    'Loosely based on https://docs.microsoft.com/en-us/windows/desktop/SecCNG/creating-a-hash-with-cng
    'Allowed algorithms:  https://docs.microsoft.com/en-us/windows/desktop/SecCNG/cng-algorithm-identifiers. Note: only hash algorithms, check OS support
    'Error messages not implemented
    On Error GoTo VBErrHandler
    Dim errorMessage As String

    Dim hAlg As LongPtr
    Dim algId As String

    'Open crypto provider
    algId = HashingAlgorithm & vbNullChar
    If BCryptOpenAlgorithmProvider(hAlg, StrPtr(algId), 0, 0) Then GoTo ErrHandler

    'Determine hash object size, allocate memory
    Dim bHashObject() As Byte
    Dim cmd As String
    cmd = "ObjectLength" & vbNullString
    Dim Length As Long
    If BCryptGetProperty(hAlg, StrPtr(cmd), Length, LenB(Length), 0, 0) <> 0 Then GoTo ErrHandler
    ReDim bHashObject(0 To Length - 1)

    'Determine digest size, allocate memory
    Dim hashLength As Long
    cmd = "HashDigestLength" & vbNullChar
    If BCryptGetProperty(hAlg, StrPtr(cmd), hashLength, LenB(hashLength), 0, 0) <> 0 Then GoTo ErrHandler
    Dim bHash() As Byte
    ReDim bHash(0 To hashLength - 1)

    'Create hash object
    Dim hHash As LongPtr
    If BCryptCreateHash(hAlg, hHash, bHashObject(0), Length, 0, 0, 0) <> 0 Then GoTo ErrHandler

    'Hash data
    If BCryptHashData(hHash, ByVal pData, lenData) <> 0 Then GoTo ErrHandler
    If BCryptFinishHash(hHash, bHash(0), hashLength, 0) <> 0 Then GoTo ErrHandler

    'Return result
    NGHash = bHash
ExitHandler:
    'Cleanup
    If hAlg <> 0 Then BCryptCloseAlgorithmProvider hAlg, 0
    If hHash <> 0 Then BCryptDestroyHash hHash
    Exit Function
VBErrHandler:
    errorMessage = "VB Error " & Err.Number & ": " & Err.Description
ErrHandler:
    If errorMessage <> "" Then MsgBox errorMessage
    Resume ExitHandler
End Function


Public Function HashBytes(Data() As Byte, Optional HashingAlgorithm As String = "SHA512") As Byte()
    HashBytes = NGHash(VarPtr(Data(LBound(Data))), UBound(Data) - LBound(Data) + 1, HashingAlgorithm)
End Function

Public Function HashString(str As String, Optional HashingAlgorithm As String = "SHA512") As Byte()
    HashString = NGHash(StrPtr(str), Len(str) * 2, HashingAlgorithm)
End Function

您现在可以使用 HashString 函数对密码进行哈希处理.当有人输入密码时，请始终使用 HashString(password)查找密码或存储哈希密码.您永远不会存储未加密的实际密码.

You can now use the HashString function to hash passwords. When someone enters a password, always use HashString(password) to look up the password or store a hashed password. You never store an actual unhashed password.

当然，这也意味着即使您无法查看用户的密码，也只能查看其哈希值.

Of course, this also means that even you can not view passwords of users, only their hashes.

如果您想进一步改善它，可以使用盐避免彩虹表攻击.但是，仅添加哈希就已经可以大大提高安全性.

If you want to improve this further, you can use a salt to avoid rainbow table attacks. But only adding a hash will already substantially improve security.

这篇关于防止链接到SQL Server的表显示二进制的实际值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！