Form in backend database creates new user in frontend database

[Guest]

Here's a tough one for ya. I want to make a form in my backend database that
will take the emp# field and create a new user account in my frontend
database and then take the SSN field and use that as the user accounts'
password. Then it should add the user to the Update Data Users group. For
the Personal ID, it'll just append "pid" to the emp#

 

['69 Camaro]

Hi, Jonathan.

Here's a tough one for ya. I want to . . . take the SSN field and use that
as the user accounts' password.

Here's reality for ya: storing people's SSN's in an Access database can get
your company into legal hot water. Anyone determined enough can steal those
SSN's and any associated Employee ID's, names, et cetera, that are stored in
the Access database. (Even if there are no names, the Employee ID's can
probably be cross-referenced in other databases or on paperwork with
employee names.) If this person doesn't have the skills, it doesn't matter,
because the tools are available very inexpensively.

Think it can't happen to your company? The latest surveys show that 30% of
Americans who use the Internet have been victims of identity theft. Don't
give a disgruntled employee or an outside hacker the opportunity to steal
anyone's SSN. The chances are too high that someone eventually will.

take the emp# field and create a new user account in my frontend
database and then take the SSN field and use that as the user accounts'
password.

Again, don't store and use anyone's SSN, so use something else for the
passwords. You can view sample code for creating a new user and joining
that user to a group in section 38 in the Security FAQ on this Web page:

http://support.microsoft.com/default.aspx?scid=/support/access/content/secfaq.asp

Make sure that the code is running while the user is joined to the same
workgroup used to secure the front end database. Otherwise, the new user
will become a member of the workgroup that the current user is joined to.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)

 

[Jack MacDonald]

While heeding Gunny's advice about using the SSN, here is some code
that will create a user account. BTW, the accounts are in the
workgroup file, *not* in the frontend.

Dim wrk As Workspace
Dim usrNew As user
Dim strMsg As String
Set wrk = DBEngine.Workspaces(0)
Set usrNew = wrk.CreateUser("UserName","PID","password")
wrk.Users.Append usrNew
usrNew.Groups.Append wrk.CreateGroup("Users")

Here's a tough one for ya. I want to make a form in my backend database that
will take the emp# field and create a new user account in my frontend
database and then take the SSN field and use that as the user accounts'
password. Then it should add the user to the Update Data Users group. For
the Personal ID, it'll just append "pid" to the emp#

**********************
(e-mail address removed)
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security

 

[Guest]

Jack,

The code you gave me got me the closest I've been to fixing my problem. It
created the user account, and it added the user to the Users group, I also
added the Update Data Users group to the code. The only problem is that when
I try and login now as that user it tells me that the password is invalid.
Even by going into the User and Group Permissions window and clearing the
password for my new user it still tells me that my password is invalid. It's
kinda weird. Any idea?

 

[Jack MacDonald]

Not really... just guessing here.

Are there any issues with DAO vs ADO? AFAIK, these commands are
available only in DAO although I did not show it explicitly in the
code.

Any issue with password capitalization? The User object has a
..NewPassword method. Perhaps it can correct the password.

Here are some lines from the Jet Database Engine programmer's Guide

dim usrTemp as User
Set usrTemp = DBEngine.Workspaces(0).Users("userName")
usrTemp.NewPassword "oldPassword", "newPassword"

HTH

Jack,

The code you gave me got me the closest I've been to fixing my problem. It
created the user account, and it added the user to the Users group, I also
added the Update Data Users group to the code. The only problem is that when
I try and login now as that user it tells me that the password is invalid.
Even by going into the User and Group Permissions window and clearing the
password for my new user it still tells me that my password is invalid. It's
kinda weird. Any idea?

**********************
(e-mail address removed)
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security

 

[Guest]

Jack,

I've got it working now. Here's the code I ended up using:

Private Sub cmdSave_Click()
'Call the sCreateUser Function
If sCreateUser(Me!strUser, Me!strPID, Me!varPwd) = True Then
MsgBox "FSR successfully added"
Else
MsgBox "Unable to Add User " & Err.Number & ": " & Err.Description
End If
End Sub

Public Function sCreateUser(ByVal strUser As String, ByVal _
strPID As String, Optional varPwd As Variant) As Integer
'-----------------------------------------------------------
' Create a new user and add them to the Users group
' Returns True on success, False if user already exists
'===========================================================
Dim db As Database
Dim ws As Workspace
Dim usr As User
Dim grpUsers As Group
Dim strSQL As String
' if the password isn't supplied, make sure you
' pass an empty string for the password argument
If IsMissing(varPwd) Then varPwd = ""
Set ws = DBEngine.Workspaces(0)
ws.Users.Refresh
On Error Resume Next
' check to see if user already exists by using inline
' error handling to trap any errors caused by setting
' a reference to a possibly non-existent user
strUser = ws.Users(strUser).Name
If Err.Number = 0 Then
MsgBox "The user you are trying to add already exists.", _
vbInformation, "Can't Add User"
sCreateUser = False
Else
' go ahead and create the user account
Set usr = ws.CreateUser(strUser, strPID, varPwd)
ws.Users.Append usr
ws.Users.Refresh
' now add the user to the "Users" and "Update Data Users" groups
Set grpUsers = ws.Groups("Users")
Set usr = grpUsers.CreateUser(strUser)
grpUsers.Users.Append usr
grpUsers.Users.Refresh
Set grpUsers = ws.Groups("Update Data Users")
Set usr = grpUsers.CreateUser(strUser)
grpUsers.Users.Append usr
grpUsers.Users.Refresh
sCreateUser = True
End If
End Function


I believe in this code I'm using DAO. I was told that ADO's create user
function does not support using the PID as a property. Other differences I
don't know of.

Thanks for your help Jack

 

[Jack MacDonald]

Glad to help

Jack,

I've got it working now. Here's the code I ended up using:

Private Sub cmdSave_Click()
'Call the sCreateUser Function
If sCreateUser(Me!strUser, Me!strPID, Me!varPwd) = True Then
MsgBox "FSR successfully added"
Else
MsgBox "Unable to Add User " & Err.Number & ": " & Err.Description
End If
End Sub

Public Function sCreateUser(ByVal strUser As String, ByVal _
strPID As String, Optional varPwd As Variant) As Integer
'-----------------------------------------------------------
' Create a new user and add them to the Users group
' Returns True on success, False if user already exists
'===========================================================
Dim db As Database
Dim ws As Workspace
Dim usr As User
Dim grpUsers As Group
Dim strSQL As String
' if the password isn't supplied, make sure you
' pass an empty string for the password argument
If IsMissing(varPwd) Then varPwd = ""
Set ws = DBEngine.Workspaces(0)
ws.Users.Refresh
On Error Resume Next
' check to see if user already exists by using inline
' error handling to trap any errors caused by setting
' a reference to a possibly non-existent user
strUser = ws.Users(strUser).Name
If Err.Number = 0 Then
MsgBox "The user you are trying to add already exists.", _
vbInformation, "Can't Add User"
sCreateUser = False
Else
' go ahead and create the user account
Set usr = ws.CreateUser(strUser, strPID, varPwd)
ws.Users.Append usr
ws.Users.Refresh
' now add the user to the "Users" and "Update Data Users" groups
Set grpUsers = ws.Groups("Users")
Set usr = grpUsers.CreateUser(strUser)
grpUsers.Users.Append usr
grpUsers.Users.Refresh
Set grpUsers = ws.Groups("Update Data Users")
Set usr = grpUsers.CreateUser(strUser)
grpUsers.Users.Append usr
grpUsers.Users.Refresh
sCreateUser = True
End If
End Function


I believe in this code I'm using DAO. I was told that ADO's create user
function does not support using the PID as a property. Other differences I
don't know of.

Thanks for your help Jack

**********************
(e-mail address removed)
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security