Allowing certain users to administer users via code, and other security questions

[google]

I have a few general questions. I am working on a new database to be
used within my company. I would like to give a couple of people,
particularly HR, the ability to add and delete Access users, and
add/remove them to groups, so as people join and leave the company,
they can be added/removed as database users at that time. However, I
don't want them to have to do it through the standard Access
users/groups interface, and I don't want any of the code or other
design details to be visible to them. I am aware of code (such as the
CreateUser command) to perform those functions via code, but I'm
assuming that the user that is logged in must have permissions to
administer users for the code to work. I've read the MS article 120911
about giving users ability to add users without viewing the design, but
I'm a bit confused by it. It states "Because Mary was not a member of
the Admins group in the system database in use when the database was
created, Mary will not have permissions on objects that already exist
in the database.". To me, this insinuates that Mary *WOULD* be able to
view the design of anything added to the database AFTER the point in
time she was added to the admins group. If I'm understanding this
correctly, this would mean that the user or group you wanted to be able
to administer users should not be given this capability until AFTER the
database is complete. Unfortunately, this is not practical for me, as
it will be in a constant state of development for a long time. I
expect, however, that I'm misunderstanding something.

What exactly gives a user or group permission to administer users? I
know how to assign various permission levels to various database
objects, but none of the options seem specific to administering users.
Is that an inherent ability to the built-in "Admins" group, regardless
of what permissions you give to the Admins group for various database
objects? If so, should I create a "developer" group, that actually has
permissions to everything, and strip the Admins group of permissions
related to design, and add those users that should be able to
administer users to the Admins group? Are there any other inherent
abilities invisibly given to the Admins group that would be a caveat to
adding non-developers to this group?

I also have a couple of other security related questions (I've not had
to worry about security too much in the past). I realize that Access
is not the most secure application. For the vast majority of our data,
the standard "good practices" should be sufficient. For standard
users, I plan on giving them user IDs that are the same as their
network log-in, and kicking them out of the database if someone tries
to log in to the database under a userID that doesn't match the account
they are logged in to the network under. But for more sensitive data,
I intend on creating a second back-end that will be located on a
security restricted network share, that only those that should have
access to the sensitive data can get to. Does this seem like a
reasonable approach? Are there any caveats to this?

TIA!

 

[google]

Ok, I THINK I understand how to separate the ability to administer
users with the ability to to administer other areas of the database...
I'd just like to confirm I'm understanding this correctly:

1. The "Admins" group is hard coded to contain permissions to
administer users. Anyone who needs to add/change/delete users or
groups, needs to be a part of the Admins group.

2. The "Admins" group's PID is determined when you create the
workgroup.

3. To have a "developer" ID that has admin capability that is stronger
than a user that can administer only users, you need to use two
separate workgroups: one for development use, and one for users to
use. You set the two workgroups up with different PIDs and names, so
in effect the "Admins" group of each workgroup are identified as
different groups by the database, and can therefore have separate
permissions. But user groups get set up in both workgroups with the
same names/PIDs, so they are seen as the same groups by the database
that is being opened. You create the secured database while logged in
via the "dev" workgroup as a user that is part of the Admins group,
giving full access/ownership to THAT user and admin account. Users
that are added to the Admins group of the "users" workgroup don't have
the same database privileges, because they are not seen as the database
as belonging to the same Admins group that it was created under, but
because they are admins of that workgroup, they can still administer
users of that workgroup.

Do I have a correct handle on this?

If so, I'd still appreciate feedback on my security intentions.

Thanks!

 

[Joan Wild]

Do I have a correct handle on this?

Yes you do. It's outlined in the Security FAQ - section 33.

If so, I'd still appreciate feedback on my security intentions.

I plan on giving them user IDs that are the same as their

network log-in, and kicking them out of the database if someone tries
to log in to the database under a userID that doesn't match the account
they are logged in to the network under.

That seems unnecessary to me. The workgroup file won't let them in with an
invalid Username.

But for more sensitive data,

I intend on creating a second back-end that will be located on a
security restricted network share, that only those that should have
access to the sensitive data can get to. Does this seem like a
reasonable approach? Are there any caveats to this?

'security restricted' - as long as users (those few) have create/delete
permissions on the folder, it's OK.

 

[google]

Thanks for the reply Joan!

"That seems unnecessary to me. The workgroup file won't let them in
with an
invalid Username."

The reasons behind this are two-fold. For one, I've read that the
workgroup files aren't all that secure... it's apparently fairly easy
for someone to get the necessary info out of a database file to
re-create a matching workgroup file, and/or hack a workgroup file to
get info out of it. Of course, there are other security weaknesses
that may be bigger than these, but may as well plug what I easily can.
But the biggest reason I want to do this is so I don't have to require
users to have passwords... we have enough passwords for various apps as
it is. This way, I can simply rely on the security of their network
login, which will be more convenient for the user, and likely more
secure than relying solely on the workgroup file.

 

[Larry Linson]

Anything other than Access' own security (which is not, itself, infallible)
is merely "Security Lite" and won't stop even a modestly-knowledgeable
intruder.

If your data is really important, use a true server database, with Access as
the client application, and rely on the server DB's security.

If your Access code is important, compiling to MDE is better protection than
"Security Lite", but we have recently seen reliable reports that someone has
managed to de-compile the MDE back into source (though many things used to
make the source easily understandable cannot be reconstructed).

On the other hand, few Access databases are so complicated and so un-obvious
that an experienced Access developer cannot watch them run and re-create
them in far less time than it took to develop them originally. After all,
the heavy lifting of applying database techiques to the business problem has
already been done, and can't be concealed.

Larry Linson
Microsoft Access MVP

 

[Tom Ellison]

Dear Larry:

A good answer, obviously.

Haven't seen you around these parts much. It's so great to see you're at
it, and haven't lost a step. I'll tell Betsy.

Feel free to write!

Tom Ellison

 

[google]

"Anything other than Access' own security (which is not, itself,
infallible)
is merely "Security Lite" and won't stop even a modestly-knowledgeable
intruder."

I'm not planning on replacing Access' own security, but augmenting it.
I will still have user level security, and I will still be compiling
the code database to an mde, but I will be using the Windows/AD logon
security to ensure they are using the user ID they SHOULD be using,
without necessarily requiring a password to confirm that (though of
course, anyone could use a password in conjunction with this if they
felt it was necessary, and I MAY require that for certain users who
have a little more access than everyone else). I don't understand how
that is any "liter" than just using Acess security on its own (though
I'm eager to learn if you would be so kind as to provide some
examples).

The ONLY situation that is immediately apparent to me as an example
where substituting network logon verification for a workgroup password
is that the "enhanced" security does nothing to help secure the
back-end database: since code (to verify the network ID matches) can't
be invoked if a user were to attempt to LINK or IMPORT data from the
back-end, anyone could use a known Access user ID with a blank password
to link or import. However, in my situation anyway, they could pretty
much do that anyway. The majority of our data needs to be accessible
to some extent to everyone, so all userIDs are going to need
permissions to most all of the tables anyway. So they could have
linked or imported with their own user ID... no need to use someone
else's, so the password does little to prevent that. What I'm doing is
making sure that the 99% of the users who don't really understand how
to connect to the back-end database are being clearly identified, so I
can apply my conditional (row/column) security in the front end, as
well as all the steps I'm taking to track WHO is changing what.

Back-end security is indeed a problem. I agree that a true server
based database (SQL, or whatever) is the way to go if security of the
data is paramount, but that's not really an option here. My next task
is to look at the benefits and pitfals of RWOP queries to help secure
the data in the backend, as well as other snooping hinderances such as
"hiding" the back-end on an uknown share, and other little things that
can help keep prying eyes out.

Security is a difficult thing to discuss, because I think anyone with
the knowledge, resources, and determination, can get past anything we
can implement. So it all becomes an issue of compromises... what are
the biggest REALISTIC threats, and how much time, money, and effort are
we willing to invest to fight them, and how much convenience and
usability are we willing to trade to get there? For my situation,
usabilty and speed is paramount... very little of the data is national
security type stuff, and what little there is that really is private I
intend on putting on a user restricted share. For the remainder, I
want to do what I can to keep the users honest, while keeping the data
as easy and fast as possible. So at this point, I'm just trying to
learn about the various pros/cons of different security techniques.

Thanks!