creating "semi-admin" user group

[Justin To via AccessMonster.com]

i was wondering if it's possible to create a user group such as "security admin" where they have permission to add/remove/edit other users from their group or set passwords (basically, able to use Tools > Securities > User and Group Accounts) but at the same time, this group would not be able to modify any data objects (kind of like a limited Admins group)

thanks in advance

Justin

 

[Jeff Conrad]

I was wondering if it's possible to create a user group such as "security admin"
where they have permission to add/remove/edit other users from their group or
set passwords (basically, able to use Tools > Securities > User and Group
Accounts) but at the same time, this group would not be able to modify any data
objects (kind of like a limited Admins group)

Hi Justin,

The short answer to your question is "Yes, you can do *something* like this."
The long answer means this will take a LOT of work on your part.

Here is a past post of mine to John Vinson that touches on this subject. I will
give further explanation at the end.

Begin Quoted material>>>

I have done something *similar* for a client application. Although it did not involve
subforms, it did involve temporarily giving someone permissions they would not
otherwise have, given their current security clearance level.

I am the "Super User" of the development MDW file, but I am not in the distributed
MDW file to add additional security. The distributed MDW file only has one person
who is a member of the Admins group. Through careful coding procedures the only
thing this person can do besides opening the database is "Diddly Squat." None of the
regular users of the program have Admin rights which presented a problem when I
wanted to do these functions:
1. Create a user
2. Change Passwords
3. Delete a user
4. Assign/change user's security levels
5. Change the startup form

As you can see, only a member of the Admins group can do these functions.

My workaround was to create a temporary workspace using my Logon specs
'wrapped' around whatever *Admin* code I needed to perform. Something like so:

Dim wrk As DAO.Workspace
Set wrk = DBEngine.CreateWorkspace("", "MyUserName, "MyPassword", dbUseJet)
'
' Yadda, yadda whatever code here
'
Set wrk = Nothing

By doing it this way I do not have to 'give the keys to the vault' to anyone.
The users are temporarily authorized to do some function in the blink of an eye
and then everything goes back to normal. This has worked quite well for a long
time and not a single problem has surfaced. And since I distribute MDE files,
the code (and my user information) is protected.

End Of Quoted material>>>

So what I had to do was create a temporary workspace for any function that
required the user to have Admin rights. For example, to create a new user, I
would wrap the existing "Create User" code inside the temporary workspace.
This was a LOT of work trust me! It is also imperative that you lock down
the file tight. Distribute only MDE files, create custom menu bars/toolbars and hide
any existing Access ones to prevent the users from getting access to other
functions. Also, you should hide the Database Window, set limited Startup
Options, and disable the Shift key bypass.

Good luck,

 

[Justin To via AccessMonster.com]

hmm...part of my enhancements to the project that I'm working on required me to put in custom tool/menubars, disable shift-key, and limiting the start up options as well...however...I don't quite understand what you just said about "wrap the existing 'Create User' code inside the temporary workspace"

are you saying that for a certain user to do create user (say John), change password, etc without actually being an admin...we have to temporarily authorize John to be an admin, then carry out those tasks, then revoke those admin rights from him again? if so...how am I suppose to that?

before seeing your post, I was reading up on MS Access FAQ page (Item #33)...they said something about creating two workgroup info file...one for development (DEVELOP.MDW) and one for distribution (USER.MDW)...and I ran into a wall trying to work it out...is it alright if you point out a few things about that too? Wondering if the FAQ and what you suggested are related

Much appreciated,
Justin

 

[Jeff Conrad]

Hi Justin,

Sure, I can help some more with this issue.
It will have to wait a bit as I will be quite
swamped with work for the next few hours.

I'll get back to you on this.
In the meantime, read this thread to get
acquainted with the setup. This is how
I learned how to do this.

http://tinyurl.com/4yo26

--
Jeff Conrad
Access Junkie
Bend, Oregon

hmm...part of my enhancements to the project that I'm working on required me to put in custom

tool/menubars, disable shift-key, and limiting the start up options as well...however...I don't
quite understand what you just said about "wrap the existing 'Create User' code inside the temporary
workspace"

are you saying that for a certain user to do create user (say John), change password, etc without

actually being an admin...we have to temporarily authorize John to be an admin, then carry out those
tasks, then revoke those admin rights from him again? if so...how am I suppose to that?

before seeing your post, I was reading up on MS Access FAQ page (Item #33)...they said something

about creating two workgroup info file...one for development (DEVELOP.MDW) and one for distribution
(USER.MDW)...and I ran into a wall trying to work it out...is it alright if you point out a few
things about that too? Wondering if the FAQ and what you suggested are related

 

[Justin To via AccessMonster.com]

Hi Jeff,

Thank you so much for sending me that link, I just read it, and it's the exact same idea that I was having. Now, I just got to do the coding and see if it works, most likely though. Thanks once again =)

 

[Justin To via AccessMonster.com]

Hi again, your solution was perfect, however, the way I set up my UI (like the user/group accounts dialog from Access) I have two list boxes. But they dont update/refresh after i add/remove a user from a group

here's the code for clicking on the add button

Private Sub cmd_add_Click()
On Error GoTo Err_cmd_add_Click
Dim wrk As DAO.Workspace
Dim grp As DAO.Group
Dim usr As DAO.User

Dim selectedGroup As String

Set wrk = DBEngine.CreateWorkspace("", "tmsadmin", "retroo", dbUseJet)
selectedGroup = Me.lst_available.value

wrk.Users.Refresh

' Now add the user to the group
Set grp = wrk.Groups(selectedGroup)
Set usr = grp.CreateUser(Me.cmb_empID)
grp.Users.Append usr

grp.Users.Refresh
wrk.Groups.Refresh
wrk.Users.Refresh

Exit_cmd_add_Click:
Set grp = Nothing
Set usr = Nothing
Set wrk = Nothing
refresh_lst_member

Exit Sub
Err_cmd_add_Click:
MsgBox Err.Number & ": " & Err.Description
Resume Exit_cmd_add_Click
End Sub

and the refresh sub

Private Sub refresh_lst_member()
On Error GoTo Err_refresh_lst_member

Dim db As Database
Dim usr As User

Dim memberGroups As String

Set db = CodeDb

'Get "member of" groups
Set usr = DBEngine.Workspaces(0).Users(Me.cmb_empID)
For i = 0 To usr.Groups.Count - 1
memberGroups = memberGroups & """" & usr(i).Name & """" & ";"
Next
memberGroups = Left$(memberGroups, Len(memberGroups) - 1)
Me.lst_member.RowSourceType = "Value List"
Me.lst_member.RowSource = memberGroups

Exit_refresh_lst_member:
Set db = Nothing
Set usr = Nothing
Exit Sub
Err_refresh_lst_member:
MsgBox Err.Number & ": " & Err.Description
Resume Exit_refresh_lst_member
End Sub


Thanks once again!

 

[Jeff Conrad]

Hi Justin,

Good to hear you are making progress.
I'll go over your code, as well as some code
I have, and get back to you.
Still tied up quite a bit.

--
Jeff Conrad
Access Junkie
Bend, Oregon

Hi again, your solution was perfect, however, the way I set up my UI (like the user/group accounts

dialog from Access) I have two list boxes. But they dont update/refresh after i add/remove a user
from a group

 

[Jeff Conrad]

message
Hi Justin,

Comments in-line and below.....

Hi again, your solution was perfect, however, the way I set up my UI (like the
user/group accounts dialog from Access) I have two list boxes. But they don't
update/refresh after I add/remove a user from a group

Good to hear you are making progress.

here's the code for clicking on the add button

Private Sub cmd_add_Click()
On Error GoTo Err_cmd_add_Click
Dim wrk As DAO.Workspace
Dim grp As DAO.Group
Dim usr As DAO.User

Dim selectedGroup As String

Set wrk = DBEngine.CreateWorkspace("", "tmsadmin", "retroo", dbUseJet)
selectedGroup = Me.lst_available.value

wrk.Users.Refresh

' Now add the user to the group
Set grp = wrk.Groups(selectedGroup)
Set usr = grp.CreateUser(Me.cmb_empID)
grp.Users.Append usr

grp.Users.Refresh
wrk.Groups.Refresh
wrk.Users.Refresh

Exit_cmd_add_Click:
Set grp = Nothing
Set usr = Nothing
Set wrk = Nothing
refresh_lst_member

Exit Sub
Err_cmd_add_Click:
MsgBox Err.Number & ": " & Err.Description
Resume Exit_cmd_add_Click
End Sub

That all looks pretty good, but I have suggestions for improvements
further down.

Comments inside the next code section below....

and the refresh sub

Private Sub refresh_lst_member()
On Error GoTo Err_refresh_lst_member

Dim db As Database

I'm not sure why you need this at all.

Dim usr As User

Dim memberGroups As String

Set db = CodeDb

You do not need to set a reference to CodeDb or CurrentDb
in this instance. I am also wondering why you are using CodeDb,
but we will be removing it anyway so it does not matter.

'Get "member of" groups
Set usr = DBEngine.Workspaces(0).Users(Me.cmb_empID)

Right here you should Refresh the Groups Collection for this user.

For i = 0 To usr.Groups.Count - 1

I believe you forgot to Dim I as an Integer.
You would have received an error right off the bat.
You may have it declared and it just did not show up in your copy/paste.

memberGroups = memberGroups & """" & usr(i).Name & """" & ";"
Next
memberGroups = Left$(memberGroups, Len(memberGroups) - 1)
Me.lst_member.RowSourceType = "Value List"

If your List Box is already set to "Value List" you do not need this.

Me.lst_member.RowSource = memberGroups

Exit_refresh_lst_member:
Set db = Nothing

Again, not needed.

Set usr = Nothing
Exit Sub
Err_refresh_lst_member:
MsgBox Err.Number & ": " & Err.Description
Resume Exit_refresh_lst_member
End Sub

After Dimming I as an Integer, your code worked just fine for me when clicking the
Add button. The selected group was now shown in the lst_member List Box.
However, if I chose another one, the list box would not show this second one
as you already observed.

Also, I think it would be a good idea to handle the situation (quite likely) when
trying to put someone into a Group that they are already a member of. Trust me,
someone will accidentally do this at some point.

Are you open to some coding style changes? If so, make a backup of your
database and copy/paste this new code into those two procedures. Compile
the code, save and close the form. Then test it out.

'*****Start Of New Code*****
Private Sub cmd_add_Click()
On Error GoTo Err_cmd_add_Click

Dim wrk As DAO.Workspace
Dim grp As DAO.Group
Dim usr As DAO.User

Dim strSelectedGroup As String

If (Nz(Me![cmb_empID], "") = "") Then
' No User has been selected
MsgBox "Please select a User from the list provided " _
& "before continuing.", vbExclamation, "Which User?"
Me.cmb_empID.SetFocus
GoTo Exit_cmd_add_Click
End If

If Me.lst_available.ItemsSelected.Count = 0 Then
' No Group was selected
MsgBox "Please select an available group from the list " _
& "provided before continuing.", vbExclamation, "Which Group?"
Me.lst_available.SetFocus
GoTo Exit_cmd_add_Click
End If

Set wrk = DBEngine.CreateWorkspace("", "tmsadmin", "retroo", dbUseJet)
strSelectedGroup = Me.lst_available.Value

' Refresh the Users collection
wrk.Users.Refresh

' Now add the user to the group
Set grp = wrk.Groups(strSelectedGroup)
Set usr = grp.CreateUser(Me.cmb_empID)
grp.Users.Append usr

' Refresh all the various collections
grp.Users.Refresh
wrk.Groups.Refresh
wrk.Users.Refresh

' Refresh the list of group membership
Refresh_lst_member

Exit_cmd_add_Click:
On Error Resume Next
Set grp = Nothing
Set usr = Nothing
Set wrk = Nothing
Exit Sub

Err_cmd_add_Click:
If Err.Number = 3032 Then
' This person already belongs to this group
MsgBox Me.cmb_empID & " is already a member of the " _
& Me.lst_available & " group.", vbInformation, _
"Membership Already Exists"
Else
MsgBox Err.Number & ": " & Err.Description
End If
Resume Exit_cmd_add_Click

End Sub

Private Sub Refresh_lst_member()
On Error GoTo Err_Refresh_lst_member

Dim usr As DAO.User
Dim intI As Integer
Dim strMemberGroups As String

' Gather a list of groups this person is a member of
Set usr = DBEngine.Workspaces(0).Users(Me.cmb_empID)
' Refresh this User's Groups collection
usr.Groups.Refresh
' Loop through each group this person is a member of
' and build a text string for our list box
For intI = 0 To usr.Groups.Count - 1
strMemberGroups = strMemberGroups & """" & usr(intI).Name & """" & ";"
Next
' Strip trailing ;
strMemberGroups = Left$(strMemberGroups, Len(strMemberGroups) - 1)
' Pass the completed list of group membership to list box
Me.lst_member.RowSource = strMemberGroups

Exit_Refresh_lst_member:
On Error Resume Next
Set usr = Nothing
Exit Sub

Err_Refresh_lst_member:
MsgBox Err.Number & ": " & Err.Description
Resume Exit_Refresh_lst_member

End Sub
'*****End of Revised Code*****

Seems to work just fine in my testing.

Also, I would assume you have already set your combo box of users
to "Limit To List" and coded a Not In List event procedure.

One last thing. On your Remove code button, make sure you prevent
data entry people from trying to remove someone from the Users Group.
Everyone has to be a member of the Users Group.

Thanks once again!

You're very welcome, I hope it helps.

 

[Justin To via AccessMonster.com]

Thanks!!! it all works nicely, but you forgot this:

Me.lst_member.RowSourceType = "Value List"

in the refresh sub ^^;;

I forgot to dim i as an int since I'm used to writing code in C++/Java where you can just go for(int i=0;......)..heh

I just finished adding the add/delete user, and remove user from group with help from ur Google Group, once again, I can't say how much I appreciated your help =)

Justin

 

[Jeff Conrad]

Hi Justin,

Thanks!!! it all works nicely, but you forgot this:

Me.lst_member.RowSourceType = "Value List"

in the refresh sub ^^;;

As I mentioned in the previous post, that line was not entirely
necessary if you have already set your list box's Row Source Type
to "Value List." I had no problems leaving that line out. If,
however, you are changing the Row Source Type by code
elsewhere, then yes, you would need to include that line.
Leaving it in should be just fine.

I forgot to dim i as an int since I'm used to writing code in
C++/Java where you can just go for(int i=0;......)..heh

Ahh, I see.

I just finished adding the add/delete user, and remove user from
group with help from ur Google Group, once again, I can't say
how much I appreciated your help =)

You're very welcome, glad I could help.
And thank you for the kind words.

Good luck with the rest of your project.

 

[TC]

Hi Justin,
(snip)

This is how I learned how to do this.
http://tinyurl.com/4yo26

I da man!

TC

 

[Jeff Conrad]

Of course you are the man TC!
Thanks for teaching me that trick.

 

[TC]

Of course you are the man TC!
Thanks for teaching me that trick.

No probs Jeff. I'm sure you've passed on your own tricks to many other
people. The newsgroups are surely an amazing resource of technical
information!

Cheers,
TC

 

[Justin To via AccessMonster.com]

hey guys, sorry to bother you again. but my boss didnt like the idea of
hardcoding the password...is there another possible solutions?

Thanks!
Justin

 

[Jeff Conrad]

Hi Justin,

If you are distributing MDE files (which you probably should be),
then your password information would be pretty darn secure.
If you are concerned about hackers or something then you really
should not even be using Access at all! A file-based system is not
as secure as a server-based system.

However, another alternative was discussed between TC and myself.
Re-read the thread again and especially the stuff near the bottom. TC
suggested a way to "hide" the Password and User Name from prying eyes:

http://tinyurl.com/4yo26

 

[Justin To via AccessMonster.com]

Yes, I read about the part about changing the password string into a series
or chr(#)'s. however, my boss just doesn't like the idea of explicity
putting a password into the code since a admin password change would requre
going thru all the code again and changing it...

I was looking at the MS Access Security FAQ @
http://support.microsoft.com/default.aspx?scid=/support/access%
2Fcontent%2Fsecfaq.asp item #33...I'm not sure how to implement this,
having a bit of trouble just to make the two .mdw files working. the next
thing that comes into mind is that if I have to make a new .mdb file and
import all the objects from the existing db into the new one...(I tried to
import them into a brand new db, but Access said that I have no permission
to import objects..o_O) another concern that I have is that if I have to re-
create every exister user in the new .mdw...hmm...lots of
questions...thanks for any help!

Justin

 

[Jeff Conrad]

Hi Justin,

Comments in-line...

Yes, I read about the part about changing the password string into a series
or chr(#)'s. however, my boss just doesn't like the idea of explicitly
putting a password into the code since a admin password change would require
going through all the code again and changing it...

While essentially that is correct, surely this would not take very long!
We're talking what, maybe a few forms at most?
Maybe one form for creating users, one for changing passwords, one
for deleting users, and one for assigning security groups, right?
If you are using a split FE/BE situation and distributing MDE files
then it would seem to me that it would not take very long to update
that information. Just my opinion though.

I was looking at the MS Access Security FAQ @
http://support.microsoft.com/default.aspx?scid=/support/access%
2Fcontent%2Fsecfaq.asp item #33...I'm not sure how to implement this,
having a bit of trouble just to make the two .mdw files working.

What part of this are you having difficulty with? The steps are outlined
pretty well. Which area are you stuck on in the steps?

The next thing that comes into mind is that if I have to make a new .mdb file
and import all the objects from the existing db into the new one...

This is not essential for this area. Section 33 just discusses setting up two
MDW files, not two MDB files. You will not need to modify the MDB
file. What *IS* essential is having all the correct PID information for the
various groups and workgroup files. I actually had to start over from
scratch when I did this because I had lost the PID information that I
wrote down. DOH!

(I tried to import them into a brand new db, but Access said that I have no
permission to import objects..o_O)...

Most likely you were not using the correct workgroup file when you tried
to do the import. You were probably joined to the default system workgroup
file at the time.

...another concern that I have is that if I have to re-
create every existing user in the new .mdw...hmm...

Yes, you will need to re-create all the users in the distributed MDW
file. If you have all the PIDs then it should be a piece of cake. If, however,
you do not have this information, then you can join me in DOH-land!

lots of questions...

I have lots of answers.
It is 107 days left in case you are wondering.

thanks for any help!

No problem, glad to help.

 

[TC]

Jeff, I used to think that constructing the password at runtime (eg. by
appending CHR()'s) was enough to hide it from an ascii scan of the MDE
file.

However, I now know that that is not the case! :-((

As you probably know already, Jet usually does not "zero out" unused
data space within an MDB *or MDE* file. Thus, an MDB or MDE file can
potentially contain whatever was left-around in memory at the time you
saved that file.

I have seen an MDE that contained, *in plain text form*, part of the
compiled source-code of a confidential registration module within that
MDE! I believe this occurred for the reason stated above. The
structured-storage stream containing the source-code was doubtless
discarded from the MDE, by Jet. But some of the data contained within
that stream was still in RAM, and was swept up, unintentionally, &
included in an unallocated buffer or page within the MDE!

Clearly, the chance of this occurring in any particular case, is highly
dependent on unpredictable factors; but it certainly can occur.

I've suggested to the OP an alternative method that he could use if he
reeeely reeeely reeeely had to worry about that level of detail. But I
concur with you that there comes a point where Access's security may
not be suitable for the task at hand.

Cheers,
TC

 

[TC]

Justin To via AccessMonster.com wrote:
-

Yes, I read about the part about changing the password string into a series
or chr(#)'s. however, my boss just doesn't like the idea of explicity
putting a password into the code since a admin password change would requre
going thru all the code again and changing it...

-
A fair objection IMO - but there are lots of ways around it.

For example:

(1) Define the password in one place only - not everywhere the database
needs it. Then, if you had to change it, there would only be one place
to change it. For example, define it via a public function in a
standard module:

public function AdminPassword() as string
AdminPassword = chr$(..) & chr$(..) etc.
end function

(2) Store the password in a registry item, in an obfuscated form.
When-even the database needed that password, it would retrive the value
from the registry, and then de-obfuscate it at runtime. A casual user,
browsing the registry, would only see the obfuscated value - which is
not of any use to him, unless he knows how it was obfuscated!

(Method (2) relies on the "security by obscurity" of the obfuscation
method. A professional cryptographer would reject that out of hand; but
it might be ok for your purposes.)

(3) Various other methods - but how complex do you want to go?
-
HTH,
TC

 

[TC]

Yes, I read about the part about changing the password string into a series
or chr(#)'s. however, my boss just doesn't like the idea of explicity
putting a password into the code since a admin password change would requre
going thru all the code again and changing it...

A reasonable objection IMO - but there are lots of ways around it.

For example:

(1) Define the password in one place only - not everywhere the database
needs it. Then, if you had to change it, there would only be one place
to change it. For example, define it via a public function in a
standard module:

public function AdminPassword() as string
AdminPassword = chr$(..) & chr$(..) etc.
end function

(2) Store the password in a registry item, in an obfuscated form.
When-even the database needed that password, it would retrive the value
from the registry, and then de-obfuscate it at runtime. A casual user,
browsing the registry, would only see the obfuscated value - which is
not of any use to him, unless he knows how it was obfuscated!

(Method (2) relies on the "security by obscurity" of the obfuscation
method. A professional cryptographer would reject that out of hand; but
it might be ok for your purposes.)

(3) Various other methods - but how complex do you want to go?
-
-pls leave a blank line here, google!!
-
HTH,
TC


Anyway