creating "semi-admin" user group

[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

 

[Justin To via AccessMonster.com]

Thanks for your help guys! =) I found another MS KB somewhere that's
similar to item #33 in the FAQ, I got the different workgroup file working,
and I was able to import the objects from the original DB, guess I had to
rejoin the old workgroup file in order to do that...heh...

I think my boss just don't like having the idea of using a password in the
code in any form..lol..the only down side of the current solution of using
2 .mdw file is that we have to recreate all the users again, and the
previous developer didn't keep track of the PID's so I guess I'm with you
in DOH-land hehe

Just to make sure...I don't need to make a new .mdb file and import the
objects..? if so...how would I gain access to the original .mdb since we
didn't write down all the various ID's

Can't stress this enough, thank you so much! don't know where I would be
without your help

Justin

 

[Joan Wild]

..the only down side of the current
solution of using 2 .mdw file is that we have to recreate all the
users again, and the previous developer didn't keep track of the
PID's so I guess I'm with you in DOH-land hehe

It isn't necessary to create all the users in both the development mdw and
the production mdw, provided you assign permissions to groups rather than
users. That is always adviseable - create groups in your development mdw
and assign permissions to the groups. You then only have to create the same
groups in the production mdw, and then create all the users you need in only
the production mdw. If you didn't keep track of PIDs, you'll have to start
over, and write down the information this time.

 

[Justin To via AccessMonster.com]

Since I have an existing mdw...would I have to recreate the groups? because
I do not have records of the GroupID of these groups

Just to confirm the steps:
1. recreate groups and record GroupID's in original mdw (development)
2. assign permissions to the newly created groups as appropriate
3. create new mdw file (production)
4. create the exact same groups with same GroupID's in the new mdw
5. create users on the new mdw
6. ?delete the users on the original mdw?

Questions:
1. at the end...there should only be the "full admin" on the development
mdw?
2. in the development mdw, remove all the permissions on admins/users
groups? and explicitly assign full permissions to the "full admin"?
3. in the future, if we need to add new groups, we need to create them in
both development and production mdw with exact GroupID's?

to make a "full admin", do we need to create a user to both mdw? or we can
just keep him in the development one, and there's no need to create one on
the new (production) mdw. However, this "full admin" would not be able to
create/remove/modify the users on the production mdw then right?

And as for the "semi admin", we just need to add him to the new (production)
mdw and put him to Admins group

Thanks for all you time!!! =)

Justin

 

[Joan Wild]

Since I have an existing mdw...would I have to recreate the groups?
because I do not have records of the GroupID of these groups

Just to confirm the steps:

You'd need to remove all the existing permissions in the mdb for your
existing groups/users..then

1. recreate groups and record GroupID's in original mdw (development)
2. assign permissions to the newly created groups as appropriate
3. create new mdw file (production)
4. create the exact same groups with same GroupID's in the new mdw
5. create users on the new mdw
6. ?delete the users on the original mdw? yes

Questions:
1. at the end...there should only be the "full admin" on the
development mdw?
yes

2. in the development mdw, remove all the permissions on admins/users
groups? and explicitly assign full permissions to the "full admin"?

No need to remove all permissions on the admins group, since this group is
different than the admins group in your production mdw. Just remove all
permissions from the Users Group, create a superuser to be a member of the
Admins group, and remove Admin from the Admins group. Ensure that the Admin
user doesn't own anything.

3. in the future, if we need to add new groups, we need to create
them in both development and production mdw with exact GroupID's?
Yes

to make a "full admin", do we need to create a user to both mdw? or
we can just keep him in the development one, and there's no need to
create one on the new (production) mdw.

The latter is correct.

However, this "full admin"
would not be able to create/remove/modify the users on the production
mdw then right?

Correct, however this full admin user (what I called superuser above) can
log into the production mdw as the user that's a member of the Admins group
in that mdw.

And as for the "semi admin", we just need to add him to the new
(production) mdw and put him to Admins group

That's right - and as I said above, your full admin could log into the
production mdw as semi admin to create/remove/modify memberships and clear
passwords.

 

[Jeff Conrad]

Hi 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! :-((

Humm, that's not good!

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.

Yes, actually I did know that. I have seen these types of issues just
recently while developing some add-ins!

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!

Not good!

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

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.

I concur completely.
You once told me this yourself TC:
"A small amount of effort will keep 99% of people out. But even a huge
amount of effort will not keep the remaining 1% of people out."

At some point you have to say, "OK, this is as good as it is going to
get using Jet."

 

[Jeff Conrad]

Hi Justin,

Thanks for your help guys! =) I found another MS KB somewhere that's
similar to item #33 in the FAQ, I got the different workgroup file working,
and I was able to import the objects from the original DB, guess I had to
rejoin the old workgroup file in order to do that...heh...

Yep, sometimes it easy to forget which workgroup file you are using.

I think my boss just don't like having the idea of using a password in the
code in any form..lol..the only down side of the current solution of using
2 .mdw file is that we have to recreate all the users again, and the
previous developer didn't keep track of the PID's so I guess I'm with you
in DOH-land hehe

Join the club.

I posted a message yesterday from a different location and I'm pretty sure
it showed up, but today that message is not showing up on the computer
in front of me. Since you made a reference to DOH I must assume you
saw the message. I apologize if I restate the same things again.

Just to make sure...I don't need to make a new .mdb file and import the
objects..? if so...how would I gain access to the original .mdb since we
didn't write down all the various ID's

No, you do not need to create a new MDB file and import everything.
All you are doing is creating another workgroup file.

You do not need to worry about accessing the original MDB file since
you will not be creating a new one.

Can't stress this enough, thank you so much! don't know where I would be
without your help

No problem, glad to help.

 

[Jeff Conrad]

Hi Justin,

Since I have an existing mdw...would I have to recreate the groups? because
I do not have records of the GroupID of these groups

Just to confirm the steps:
1. recreate groups and record GroupID's in original mdw (development)
2. assign permissions to the newly created groups as appropriate
3. create new mdw file (production)
4. create the exact same groups with same GroupID's in the new mdw
5. create users on the new mdw
6. ?delete the users on the original mdw?

Joan has joined the thread as well and has answered your questions, but
I will add my own thoughts as well.

Yes, you will need to re-create the groups that you would need. Best to
remove the ones you have and start over like I did. However, make LOTS
of backups of both the MDB file and the workgroup file before beginning!!
You never know if you may need them.

I probably was not too clear in my post yesterday (but I cannot check either).
I had lost the PIDs for my two custom groups and also my two default user
accounts. Not a whole lot of work really to re-create. However, if you have
lots of groups and/or a large database, you may have a fair amount of work
ahead of you.

It has been discussed on the side, but is worth stressing something. Users
(except your SuperUser) are worthless. Groups have all the power. Individual
users should really have zero permissions. They should inherit the permissions
granted to them by the group(s) they belong to. That is why it is essential to
maintain a list of the Group PIDs.

Questions:
1. at the end...there should only be the "full admin" on the development mdw?

SuperUser in the development MDW file? Yes.

2. in the development mdw, remove all the permissions on admins/users
groups? and explicitly assign full permissions to the "full admin"?

Not really. But be sure to remove all permissions from the Users group, the
Admin user, and make sure the Admin user is not member of the Admins
group. I actually made a SuperGroup so to speak and put my SuperUser
in that SuperGroup and in the Admins group. I then removed all permissions
for the Admins group.

3. in the future, if we need to add new groups, we need to create them in
both development and production mdw with exact GroupID's?

That is correct.

to make a "full admin", do we need to create a user to both mdw? or we can
just keep him in the development one, and there's no need to create one on
the new (production) mdw.

If you mean another person similar to the SuperUser who access just about
everything, then you do not need to put that person in production MDW file.

However, this "full admin" would not be able to
create/remove/modify the users on the production mdw then right?
Yep.

And as for the "semi admin", we just need to add him to the new (production)
mdw and put him to Admins group

Yes, exactly.
This semi admin could do all the creating/modifying/deleting of users.

What I actually did was take it one step further and made this semi admin
*invisible* in the production MDW. There is no sign of this user in any
of the forms to add/edit/delete users. Earlier, we worked through some
code to do this. My combo box of users does NOT display this semi-admin
user at all so regular users cannot 'accidentally' make modifications.

Thanks for all your time!!! =)

No problem.

 

[Joan Wild]

I actually made a SuperGroup so to speak and put my SuperUser
in that SuperGroup and in the Admins group. I then removed all
permissions for the Admins group.

Why?

 

[Jeff Conrad]

Hi Joan,

I do understand exactly why you are asking "Why?"

That was the first time I ever set up User Level Security.
I was following Jack Macdonald's steps pretty closely
so I would not miss anything. He specifically talks about
this on Page 9 and Page 11 of his security document:

http://www.geocities.com/jacksonmacd/AJMAccessSecurity.pdf

I was just following directions.

 

[Lynn Trapp]

Well, I'm afraid I'm asking the same "Why?" that Joan asked. I just looked
through Jack's document again and don't see anything about removing
permissions from the Admins group.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm

Hi Joan,

I do understand exactly why you are asking "Why?"

That was the first time I ever set up User Level Security.
I was following Jack Macdonald's steps pretty closely
so I would not miss anything. He specifically talks about
this on Page 9 and Page 11 of his security document:

http://www.geocities.com/jacksonmacd/AJMAccessSecurity.pdf

I was just following directions.

 

[Justin To via AccessMonster.com]

Just one more question...

development.mdw
-Groups: Admins, Users, Employees, Project Managers
-Users: Admin(Default) in Users,
SuperAdmin in Admins, Users

production.mdw
-Groups: Admins, Users, Employees, Project Managers
-Users: SuperAdmin in Admins, Users
SemiAdmin in Admins, Users
User1 in Employees, Users
User2 in Employees, Users

Now, all my User#'s can log in fine, same with my SuperAdmin in both my
mdw's...however, my SemiAdmin can't even log on...I've tried putting him in
the Employees group, then he can go on....is it because the "Admins" group
are different in both mdw's and he can only log on if he is in another
group (namely Employees)?

Also I enabled all permissions on all objects in the development.mdw for
Admins group. Employees, Users are created on both mdw with the same
GroupID...is there anything I did wrong?

 

[Joan Wild]

Well, I'm afraid I'm asking the same "Why?" that Joan asked. I just
looked through Jack's document again and don't see anything about
removing permissions from the Admins group.

I didn't find it either. No harm in doing so, but...

 

[Joan Wild]

Just one more question...

development.mdw
-Groups: Admins, Users, Employees, Project Managers
-Users: Admin(Default) in Users,
SuperAdmin in Admins, Users

production.mdw
-Groups: Admins, Users, Employees, Project Managers
-Users: SuperAdmin in Admins, Users
SemiAdmin in Admins, Users
User1 in Employees, Users
User2 in Employees, Users

SuperAdmin does not need to be in this workgroup

Now, all my User#'s can log in fine, same with my SuperAdmin in both
my mdw's...however, my SemiAdmin can't even log on...I've tried
putting him in the Employees group, then he can go on....is it
because the "Admins" group are different in both mdw's and he can
only log on if he is in another group (namely Employees)?

Yes, but you don't have to put SemiAdmin in any other group. Yes that user
can't open the database, but they don't need to open the database to manage
user accounts. They'll get the message about 'no permission to open
database'; they can click on OK and then go to Tools, Security and do what
they need to do.

Also I enabled all permissions on all objects in the development.mdw
for Admins group. Employees, Users are created on both mdw with the
same GroupID...is there anything I did wrong?

I'll assume you meant Project Managers and not Users. You would assign the
pertinent permissions on objects to the Employees and Project Managers
groups while logged in using the development mdw, as SuperAdmin.

 

[Jeff Conrad]

Two easy reasons:

1. It was my first time setting up security, remember?
Beginner's mistake.

2. Over-zealousness.
"Nobody gets anything, but me!"


Of course I know now that it is pretty pointless, but
everything worked (and is still) fine so I left it alone.

 

[Jeff Conrad]

Hi Justin,

development.mdw
-Groups: Admins, Users, Employees, Project Managers
-Users: Admin(Default) in Users,
SuperAdmin in Admins, Users

That is fine.

production.mdw
-Groups: Admins, Users, Employees, Project Managers
-Users: SuperAdmin in Admins, Users
SemiAdmin in Admins, Users
User1 in Employees, Users
User2 in Employees, Users

Red Alert, shields up!
The SuperAdmin should NOT be in the production MDW.
All your hard work would be for nothing then.
Remove the SuperAdmin from the production MDW.

Now, all my User#'s can log in fine, same with my SuperAdmin in both my
mdw's...however, my SemiAdmin can't even log on...I've tried putting him in
the Employees group, then he can go on....is it because the "Admins" group
are different in both mdw's and he can only log on if he is in another
group (namely Employees)?

As Joan pointed out, you do not *have* to put SemiAdmin in any other
group. You can still access the Security menu and add/edit/delete users.

Also I enabled all permissions on all objects in the development.mdw for
Admins group. Employees, Users are created on both mdw with the same
GroupID...is there anything I did wrong?

I'm hoping that was a simple typo. The Users Group should have zero permissions
on everything. Set the Employees and Project Managers Group permissions as
needed while you are logged in as the SuperAdmin.

Then begin to add users and assign them to the groups while you are using
the production MDW file and logged in as SemiAdmin. Keep lots of good
backups of both MDW files and the MDB.

 

[Lynn Trapp]

I didn't find it either. No harm in doing so, but...
I try to avoid doing "no harm in doing so" type of things because you never
know when they will bite you in the butt. ;-)

 

[Lynn Trapp]

Of course I know now that it is pretty pointless, but

everything worked (and is still) fine so I left it alone.

Yeah, I'm sure it's ok but, like I said to Joan, I try to avoid doing
pointless but harmless things because you never know when they will jump up
and haunt you. <g> I spent weeks writing some code here at work last spring
that, as it turns out, may contain one of those "harmless" things in it.
Well, now that we are about to move to a new database version, we are
finding out that it's not so "harmless" after all...LOL

 

[Justin To via AccessMonster.com]

Yes, I meant Employees and Project Managers ^^;;

This dual workgroup file is still a bit confusing to me...but just because
a user is part of Admins in the production.mdw, that doesn't mean he
actually have admin rights over the database, right? But he just has admin
rights to add/remove users? So that would also mean...the only reason why
anyone would be on Admins would be that the user just needs to add/remove
users...and nothing else..?

This is because I don't want to use the User and Group Accounts options by
MS Access. I created a form for it already and I have more control over
it...

If I want to make a group thats actually like the Admins in develop.mdw, I
would have to create a new group, and grant most of the permissions to
them, right? So for a Department Director, he would be in the Admins group,
and a....SuperAdmins group?

Man, this seems to get more confusing as I get closer to solving the
problem. Thanks in advance!

Justin

PS: how do quote from a previous post? like...

 

[Joan Wild]

Two easy reasons:

1. It was my first time setting up security, remember?
Beginner's mistake.

That's fine Jeff, but I just don't understand why you are now recommending
it, knowing that it's pointless?