Add new user without Admin rights
Jeff Conrad
Using Access 97 here.
The last step(s) I'm taking to finish implementing User
Level Security is to create my own custom forms for adding
new users, deleting users, changing passwords, and
assigning groups. I will not be able to use the default
Access UI forms for this for two reasons:
1. I do not want to give "regular" people access to the
other functions in there.
2. This may at some point be migrated to a run-time app.
First brick wall I've hit so far is that to do all of
these functions a user has to be a member of the Admins
group!!! I don't WANT to do this!
Forgive my complete ignorance here, but all this time
setting up security seems now a waste if some users will
now have to be given the "keys to the vault" so to speak!
I guess I'm probably over-thinking this, but what risks am
I facing by having some users part of the Admins group?
My set-up and background is as follows. And yes, I've
given this a LOT of thought.
Three groups:
1. Developer
2. Application Admins
3. Application Users
This will be a restaurant management system. Single
workstation setup (but still FE/BE) with at the most only
a handful of users. Nothing really complicated. I will be
the only member of the Developer group. I will ship the
MDW with one pre-made user set up in the Application
Admins group. Let's just call this pre-made
user "ProgramAdmin" for now. I will make it so the
Developer Group is never seen and the "ProgramAdmin" user
cannot be changed/deleted. The first time they run the app
they will log in as "ProgramAdmin" and create new users.
My reasoning behind this is because let's say they have a
disgruntled shift leader delete all the users out of the
system before leaving. Well, how are they going to get
back in? I certainly don't want to give them my user
information! So this default saved user account can get
them back in. Follow me?
OK, I wanted my user account to be the only person in the
Admins group. Everyone else will be Application Admins or
in Application Users group. I would like to have everyone
in the Application Admins group be able to
add/change/delete user information. I have removed all
permissions to EVERYTHING from the Admins group and Users
group. I will have very restrictive start-up properties
and the shift key disabled using Michka's code. So, what
risks and/or holes will I be opening up if I let the
Application Admins be a member of the Admins group? I know
I'm over-thinking this so I could use some expert advice.
Thanks for any ideas, thoughts, aspirin.
Jeff Conrad
Bend, Oregon
TC
news:055e01c2e98c$7c2f40c0$3001...@phx.gbl...
> Hi,
>
> Using Access 97 here.
>
> The last step(s) I'm taking to finish implementing User
> Level Security is to create my own custom forms for adding
> new users, deleting users, changing passwords, and
> assigning groups. I will not be able to use the default
> Access UI forms for this for two reasons:
> 1. I do not want to give "regular" people access to the
> other functions in there.
> 2. This may at some point be migrated to a run-time app.
>
> First brick wall I've hit so far is that to do all of
> these functions a user has to be a member of the Admins
> group!!! I don't WANT to do this!
>
> Forgive my complete ignorance here, but all this time
> setting up security seems now a waste if some users will
> now have to be given the "keys to the vault" so to speak!
> I guess I'm probably over-thinking this, but what risks am
> I facing by having some users part of the Admins group?
You do not need to do that.
Say that a user 'A' needs to do a task that requires him to be a member of
the Admins group - but you do not want to put him in the Admins group. You
can do this, if you are prepared to "hard code" the username & password of
another user 'U' who *is* a member of the Admins group. No human person
would know the username or password of 'U'. You have created 'U' for the
sole purpose of handling the problem that you currently face. Here's how it
works:
dim ws as workspace, u as user
set ws = dbengine.createworkspace ("", <U's username>, <U's password>)
set u = ws.createuser (...)
...
set ws = nothing
This lets 'A' impersonate 'U', to do things that 'U' (but not 'A') is
permitted to do.
Obviously you have to prevent user 'A' from viewing that code, or he will be
able to see user 'U's username & password - which he could then use
manually, to log on as a member of the Admins group. Also, you should
specify user 'U's username & password as string expressions: chr$(41) &
chr$(42) & ..., not simple strings: "ab...", so they can't be seen in the
MDB file with a disk editor.
I don't have Access here to check the syntax above, but it should be
correct. Perhaps search google for other posts in forums named *access*
containing the word "createworkspace".
HTH,
TC
Jeff Conrad
Thanks for the response.
I was thinking along the same lines, but I have still been
unsuccessful. I had a very similar situation not too long
ago with needing to change the application title when
someone was not a member of the Admins group. A very nice
person named David provided this code which was a "work-
around."
>>>>>>>>>
' Set Application Title to Current User
Dim dbs As DAO.Database
Dim wrk As DAO.Workspace
Set wrk = DBEngine.CreateWorkspace
("", "AdminUserNameHere", "AdminUserPasswordHere",
dbUseJet)
Set dbs = wrk.OpenDatabase(CurrentDb.Name)
dbs.Properties!AppTitle = "MyAppTitle - " & CurrentUser()
Application.RefreshTitleBar
Set wrk = Nothing
Set dbs = Nothing
>>>>>>>>>>
I have been trying and trying and trying and trying and
trying and trying to get this to work with adding new
user/deleting user/changing password, but no matter what I
do, I still get the "You do not have permissions error.
I tried this in the form module on a command button:
>>>>>>>>>
Dim dbsAdmin As DAO.Database
Dim wrkAdmin As DAO.Workspace
Set wrkAdmin = DBEngine.CreateWorkspace
("", "AdminUserNameHere", "AdminUserPasswordHere",
dbUseJet)
Set dbsAdmin = wrkAdmin.OpenDatabase(CurrentDb.Name)
' This is the ADH code to create the user
fOk = adhCreateUser(Me.txtUserName, Me.txtPID,
Me.txtPassword)
Set wrkAdmin = Nothing
Set dbsAdmin = Nothing
>>>>>>>>>>
I have even put this code "around" the ADH code in the
module as well, but no matter what I try it still doesn't
work!
I'm sure this can be done and I have full confidence in
your abilities TC. You're probably a thousand times more
knowledgeable than me. Do you have some actual code
samples that work with this method? I would be forever
grateful if you can help me solve this!
Thanks,
Jeff Conrad
Bend, Oregon
>....
>.
>
TC
Sorry for the slow response, but I was busy yesterday (Friday here) & also
today (Saturday) & tomorrow. What you want, can definitely be done, using
the method that I outlined before. We just need to work out the details!
Both of your code samples create a temporary workspace for an Admins level
user. That's correct. But both of them then re-open the database
(OpenDataBase) using that workspace. I don't believe you need to re-open the
database. You can create a new user by applying the CreateUser method
directly on the new workspace.
Jeff, I don't have Access here to check this, so perhaps someone else can
correct my code if necessary. Please try the following code, & tell me
exactly what error it gets, at what line:
Dim wrk As DAO.Workspace, usr as dao.user
set wrk = dbengine.createworkspace ("", "AdminUserNameHere",
"AdminUserPasswordHere")
set usr = wrk.createuser ("usernamehere", "userPIDhere") ' <- check the
parameters!
wrk.users.append usr
set usr = nothing
set wrk = nothing
Cheers,
TC
"Jeff Conrad" <je...@ernstbrothers.com> wrote in message
news:03cb01c2ea4b$6b07df40$3001...@phx.gbl...
Jeff Conrad
No problem on the delay. If you can help me work through
these last few issues, I don't mind waiting a little bit!
The code you provided works great! I used the following
code to add a new user:
' Create the new user now
Dim wrk As DAO.Workspace
Dim usr As DAO.User
Set wrk = DBEngine.CreateWorkspace
("", "AdminUserName", "AdminUserPassword", dbUseJet)
Set usr = wrk.CreateUser(Me.txtUserName, Me.txtPID,
Me.txtPassword)
wrk.Users.Append usr
Set wrk = Nothing
Set usr = Nothing
First question: Is it possible to modify this slightly to
automatically add this new user to the "Users" group as
well?
I also worked through deleting a user with this code:
' Delete User
Dim wrk As DAO.Workspace
Dim usr As DAO.User
Set wrk = DBEngine.CreateWorkspace
("", "AdminUserName", "AdminUserPassword", dbUseJet)
wrk.Users.Delete Me.cboUser
Set wrk = Nothing
Set usr = Nothing
My last two steps are to be able to add/remove users from
a group and changing passwords. I haven't been attempting
the password change yet, but I think anyone can change
their OWN password correct? I still have to come up with a
way for a person in my "Application Admins" group to
change other people's passwords. I already have a form
created for this that works fine, I just have to make them
not be a member of the "Admins" group to finish it off.
Right now I'm trying to work through adding/removing
people from Security Groups. I keep hitting brick walls
though. Again, I already have a form set up for this, but
it only works for people in the Admins group. How can I
create a workspace with my special admin user to
accomplish this. I thought the following code would work,
but no luck:
' Add user to Security Group
Dim wrk As DAO.Workspace
Dim usr As DAO.User
Dim grp As DAO.Group
Set wrk = DBEngine.CreateWorkspace
("", "AdminUserName", "AdminUserPassword", dbUseJet)
Set grp = wrk.Groups(Me.lstGroups)
Set usr = grp.CreateUser(Me.cboUser)
grp.Users.Append Me.cboUser
grp.Users.Refresh
Set grp = Nothing
Set wrk = Nothing
Set usr = Nothing
The available groups are in lstGroups and the user name is
in cboUser. There is also another list box that displays
the groups the user is a member of. I have "Add"
and "Remove" buttons. Everything works peachy if you're a
member of the Admins group.
Do you have any suggestions on the questions/problems I'm
having? Your continued assistance is MOST appreciated!!
I'm very excited about nearing completion on this!
Thanks again,
TC
> Hi TC,
>
> No problem on the delay. If you can help me work through
> these last few issues, I don't mind waiting a little bit!
>
> The code you provided works great! I used the following
> code to add a new user:
>
> ' Create the new user now
> Dim wrk As DAO.Workspace
> Dim usr As DAO.User
> Set wrk = DBEngine.CreateWorkspace
> ("", "AdminUserName", "AdminUserPassword", dbUseJet)
> Set usr = wrk.CreateUser(Me.txtUserName, Me.txtPID,
> Me.txtPassword)
> wrk.Users.Append usr
<====>
> Set wrk = Nothing
> Set usr = Nothing
Glad it's working for you!
> First question: Is it possible to modify this slightly to
> automatically add this new user to the "Users" group as
> well?
Sure. Put the next statement at <===> above. Again, I don't have Access here
to check, so this is off the top of my head:
' add new user to Users group:
wrk.groups("Users").users.Append usr
(if that doesn't work, try:
wrk.groups("Users").users.Append usr.NAME
I can't quite remember the syntax)
The next examples should answer your following questions. Put them in the
same place noted above, but omit the statements referencing usr. (In these
cases, you are not creating a new user.) You just need the temporary Admins
group workspace:
' delete user ABC from the DEF group:
wrk.groups("DEF").users.delete "ABC"
' change user ABC's password:
Can't remember: check the .NewPassword method.
HTH,
TC
TC
> ' delete user ABC from the DEF group:
> wrk.groups("DEF").users.delete "ABC"
That might be:
wrk.groups("DEF").users.REMOVE "ABC"
Don't have Access here to check!
TC
Jeff Conrad
First let me say, you da man TC!
Well I believe I have everything working now. Spent a lot
of time on the weekend doing a lot of trial and error, but
I think I have everything set OK now.
Just for your information I couldn't quite get the new
user to be added to the "Users" group automatically using
your code samples.
This code:
' Add new user to Users Group
wrk.Groups("Users").users.Append usr
returned Error #3219: Invalid Operation. I'm not quite
sure why because it seemed to be OK after looking at the
Help files.
This code:
' Add new user to Users Group
wrk.Groups("Users").users.Append usr.Name
returned a syntax error immediately on compile. It
said "Type mismatch" and highlighted the .Name part
I have some security functions from the ADH book and some
from a helpful person who sent me some a while back. I was
finally able to add/remove people from groups on my custom
form by creating the workspace around those functions. So
I thought maybe if I just called that function to add the
new user to the Users group from inside this routine it
would work. Bingo it did! So I'm just going to leave it as
it is!!
I worked through changing passwords as well so now I
finally have custom forms for adding users, deleting
users, changing passwords, and adding/removing people from
my custom groups. And no one but me will be a member of
the Admins group!! Yee-Haa!
Can I bother you for just another quick question? In your
first post you mentioned something about:
"...Also, you should specify user 'U's username & password
as string expressions: chr$(41) & chr$(42) & ..., not
simple strings: "ab...", so they can't be seen in the
MDB file with a disk editor..."
I'm going to be using MDEs and my users are NOT very
technical so I don't think I need to worry about this, but
are you saying you can use characters to represent the
actual user name and password in the code? Can you give me
a quick example? Can this be used with symbols and numbers
as well?
Thanks for all your help!
Jeff Conrad
Bend, Oregon
>-----Original Message-----
>
TC
> Hi TC,
>
> First let me say, you da man TC!
Oh, you flatterer, you!
> Well I believe I have everything working now. Spent a lot
> of time on the weekend doing a lot of trial and error, but
> I think I have everything set OK now.
Well done.
> Just for your information I couldn't quite get the new
> user to be added to the "Users" group automatically using
> your code samples.
>
> This code:
> ' Add new user to Users Group
> wrk.Groups("Users").users.Append usr
>
> returned Error #3219: Invalid Operation. I'm not quite
> sure why because it seemed to be OK after looking at the
> Help files.
>
> This code:
> ' Add new user to Users Group
> wrk.Groups("Users").users.Append usr.Name
>
> returned a syntax error immediately on compile. It
> said "Type mismatch" and highlighted the .Name part
>
> I have some security functions from the ADH book and some
> from a helpful person who sent me some a while back. I was
> finally able to add/remove people from groups on my custom
> form by creating the workspace around those functions. So
> I thought maybe if I just called that function to add the
> new user to the Users group from inside this routine it
> would work. Bingo it did! So I'm just going to leave it as
> it is!!
Just for my information, could you post the 'wrk.Groups...Append...user'
line from your working code? (just that one line)
> I worked through changing passwords as well so now I
> finally have custom forms for adding users, deleting
> users, changing passwords, and adding/removing people from
> my custom groups. And no one but me will be a member of
> the Admins group!! Yee-Haa!
That is a good achivement. It's a common requirement in a secured system, to
have certain users be able to add/remove users, set passwords, & so on - but
there is no way you could expose the default Access menu options for doing
those things. Those options are far too confusing for the normal user, in my
opinion.
Also, with a "roll your own" form, you have the opportunity to implement
application-specific security options. For example, the form might have a
label "Invoices", with checkbox options "View", "Edit", "Delete". If the
authorized user checked "Edit", your custom form would set whatever security
options were necessary to let the selected user edit invoices.
> Can I bother you for just another quick question? In your
> first post you mentioned something about:
> "...Also, you should specify user 'U's username & password
> as string expressions: chr$(41) & chr$(42) & ..., not
> simple strings: "ab...", so they can't be seen in the
> MDB file with a disk editor..."
>
> I'm going to be using MDEs and my users are NOT very
> technical so I don't think I need to worry about this, but
> are you saying you can use characters to represent the
> actual user name and password in the code? Can you give me
> a quick example? Can this be used with symbols and numbers
> as well?
It's a precaution worth taking, because it is very easy, & quite effective.
Go to the debug window (press Ctrl-G) and type:
? asc("A")
This will display a number (65, from memory). That number is the internal
code for uppercase A. Now type the following (where 65 is whatever number
you got):
? chr$(65)
That will display a capital A.
Now type:
? chr$(65) & chr$(66)
That should display the string "AB".
The point is this. If you code the createworkspace statement normally, the
username (say "ABC") and password are directly visible in the MDE file:
createworkspace "ABC", ...
but if you code it like this, those strings are *not* directly visible in
the MDE file:
crteateworkspace chr$(65) & chr$(66) & chr$(67), ...
> Thanks for all your help!
Glad to be able to.
Jeff Conrad
>Just for my information, could you post
>the 'wrk.Groups...Append...user'
>line from your working code? (just that one line)
Well just the one line won't help you much because it
calls a function in a module. Normally the line would read:
AddToGroup strUser, strGrp
But that wouldn't quite work in this situation since I
wanted it to only add the Users group. The final code
behind the Add New User button is this (excluding checks
for empty fields):
' Create the new user now
Dim wrk As DAO.Workspace
Dim usr As DAO.User
Dim grp As DAO.Group
Dim StrGrp As String
StrGrp = "Users"
Set wrk = DBEngine.CreateWorkspace
("", "AdminUserName", "AdminUserPassword", dbUseJet)
Set usr = wrk.CreateUser(Me.txtUserName, Me.txtPID,
Me.txtPassword)
wrk.Users.Append usr
AddToGroup Me.txtUserName, StrGrp
fOk = True
Set wrk = Nothing
Set usr = Nothing
Set grp = Nothing
The function that it calls is this (Sorry for the horrible
line wrapping here):
Public Function AddToGroup(ByVal strUser As String, ByVal
strGroup As String) As Integer
On Error GoTo ErrorHandler
'----------------------------------------------------
' Add user to selected group
' Returns True on success, False if user not a member
'====================================================
Dim wrk As DAO.Workspace
Dim usr As DAO.User
Dim grpUsers As DAO.Group
Dim strSQL As String
Set wrk = DBEngine.CreateWorkspace
("", "AdminUserName", "AdminUserPassword", dbUseJet)
wrk.Users.Refresh
On Error Resume Next
' Now add the user to the group
Set grpUsers = wrk.Groups(strGroup)
Set usr = grpUsers.CreateUser(strUser)
grpUsers.Users.Append usr
grpUsers.Users.Refresh
wrk.Users.Refresh
' We're done
AddToGroup = True
ExitPoint:
Set grpUsers = Nothing
Set usr = Nothing
Set wrk = Nothing
Exit Function
ErrorHandler:
fncErrMessage err.Number, err.Description
Resume ExitPoint
End Function
>That is a good achivement. It's a common requirement in a
>secured system, to have certain users be able to
>add/remove users, set passwords, & so on - but
>there is no way you could expose the default Access menu
>options for doing those things. Those options are far too
>confusing for the normal user, in my opinion.
My thoughts exactly! I work with VERY low tech users.
There's no telling what kind of mess they could create!
Plus, this may at some point become a Run-time
application, in which case you HAVE to create custom
menus, toolbars, and forms for this type of thing. Seemed
at first to be such a waste if I had to have other users
be a part of the Admins group.
>Also, with a "roll your own" form, you have the
>opportunity to implement application-specific security
>options. For example, the form might have a
>label "Invoices", with checkbox
>options "View", "Edit", "Delete". If the
>authorized user checked "Edit", your custom form would
>set whatever security options were necessary to let the
>selected user edit invoices.
Actually I already have things very similar to this setup.
I have code in every form to check the security group
level and pop up my own message box if they are not
allowed access. Much better looking than the default
Access "You do not have permissions..." message.
>It's a precaution worth taking, because it is very easy,
& quite effective.
>
>Go to the debug window (press Ctrl-G) and type:
>? asc("A")
>
>This will display a number (65, from memory). That number
is the internal code for uppercase A. Now type the
following (where 65 is whatever number you got):
>? chr$(65)
>
>That will display a capital A.
>
>Now type:
>? chr$(65) & chr$(66)
>
>That should display the string "AB".
>
>The point is this. If you code the createworkspace
statement normally, the
>username (say "ABC") and password are directly visible in
the MDE file: createworkspace "ABC", ...
>
>but if you code it like this, those strings are *not*
directly visible in the MDE file:
>crteateworkspace chr$(65) & chr$(66) & chr$(67), ...
Well this is WAY too cool! I will definitely change my
code to this type of set-up.
Thanks again TC for all your wonderful help! It is VERY
much appreciated!
Jeff Conrad
Bend, Oregon