访问登录表单密码更改和更新:SQL/VBA
问题描述:
我有一个登录表单,我打算使用它来控制用户对数据库的访问。我想用用户密码=“密码”进行初始化,并提示用户在首次登录时更改密码。我有困难更新 用于存储我的用户凭证的usertable。访问登录表单密码更改和更新:SQL/VBA
普通登录VBA与frm_Login相关联:
Option Compare Database
Option Explicit
Private Sub btnLogin_Click()
Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset("X_tblUsers", dbOpenSnapshot, dbReadOnly)
rs.FindFirst "UserName='" & Me.txtUserName & "'"
If rs.NoMatch Then
Me.lblWrongUser.Visible = True
Me.txtUserName.SetFocus
Exit Sub
End If
Me.lblWrongUser.Visible = False
If rs!Password <> Nz(Me.txtPassword, "") Then
Me.lblWrongPass.Visible = True
Me.txtPassword.SetFocus
Exit Sub
End If
Me.lblWrongPass.Visible = False
TempVars("UserName").Value = Me.txtUserName.Value
If Me.txtPassword = "password" Then
DoCmd.OpenForm "frm_PassChange"
End If
If rs!UserType = 3 Then
Dim prop As Property
On Error GoTo SetProperty
Set prop = CurrentDb.CreateProperty("AllowBypassKey", dbBoolean, False)
CurrentDb.Properties.Append prop
SetProperty:
If MsgBox("Turn on Bypass key?", vbYesNo, "Allow Bypass") = vbYes Then
CurrentDb.Properties("AllowByPassKey") = True
Else
CurrentDb.Properties("AllowByPassKey") = False
End If
End If
Me.Visible = False
Globals.Logging "Logon"
DoCmd.OpenForm "frm_Main"
End Sub
表格frm_PassChange调用如果密码是“密码”(方案已在此扩大到包括用户请求改变以及)。在这种形式我有用户输入验证新密码2倍,然后要更新用新密码的用户表,但是这是行不通的:
Private Sub btnChangePass_Click()
Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset("X_tblUsers")
If Me.txtNewPass <> Me.txtNPConfirm Then
Me.lblPassMismatch.Visible = True
Me.txtNewPass.SetFocus
Exit Sub
End If
Me.lblPassMismatch.Visible = False
TempVars("Password").Value = Me.txtNewPass.Value
CurrentDb.Execute "Update X_tblUsers SET X_tblUsers.Password = Value(" & Me.txtNewPass.Value & ")"
Me.Visible = False
Globals.Logging "PWChange"
End Sub
如何正确执行用户表更新?
(TKS史蒂夫·毕晓普帮助我走到这一步)
答
CurrentDb.Execute
"Update X_tblUsers SET X_tblUsers.Password = Value(" & Me.txtNewPass.Value & ")"
有几个问题与:
-
Value()
不属于那里 - 密码是一个字符串,所以你需要引用它:
"Update X_tblUsers SET X_tblUsers.Password = '" & Me.txtNewPass.Value & "'"
- 这将创建问题,如果t他的密码本身包含一个报价,所以:
"Update X_tblUsers SET X_tblUsers.Password = '" & Replace(Me.txtNewPass.Value, "'", "''") & "'"
- 恭喜!您刚更改了所有用户的密码!你需要一个WHERE子句:
"... WHERE UserName='" & theUserName & "'"
还要注意的是存储密码以纯文本是非常糟糕的。请阅读密码散列。
关于参数化查询。
无论是否管理员,他们都不应该被允许绕过Shift键:) :)给他们控制访问不完全访问! –