Sorting out security

[Charles Hudson]

Please pardon yet another post on the subject of Access and user-level
security. I am inexperienced and greatly appreciate any help I can
receive. I would like to ask a question about the internal organization of
MS Access (or, perhaps, Jet) security.

I am developing an application which will give multiple users access to
various tables and forms, and with various permissions for reading,
entering, updating and deleting data. Eventually this will be deployed on
the Internet. I have read and experiemented and thus far have successfully
created a new workgroup, added a password for the Admin role, added groups,
users, user IDs and passwords for users and allocated permissions on the
database objects. I have demonstrated that these "roles", to borrow an
Oracle term, are restricted as intended. Some questions remain about
andministering multiple users, multiple System.mdw files ("workgroups") and
remote logins to a secured database.

As I understand matters, the Microsoft Jet engine enforces Access security
rules, and security is always enforced when Jet runs, according to the
settings it reads. The System.mdw file contains the security settings for
all users. Upon installation, Access creates the System.mdw file as a
default, with the only user, Admin, as a member of the Admins and Users
groups, and places the .mdw file in the owner's documents and settings path.
Presumably, Access knows where to reference this file. As a member of
Admins, Admin is has all permissions for all objects and, by default, there
is no password for this user. When anyone logs on to the unsecured
database, they are logging on as Admin, all-powerful, no password required.

In order to secure the database it is necessary to at least create a
database password. Doing so will create a prompt for any (anonymous) user
to gain access to the database, but once admitted, they are once again
all-powerful Admin. In order to differentiate powers, as it were, it is
necessary to define "user-level security", which means, at a minimum,
creating a password for the Admin. Once Admin has a password created for
their account, the database can no longer be accessed without specifying a
"user name" and password, if any, both. In the previous case we assume no
database password has been created. I don't know what would happen if both
a database password and user-level security were enforced, or if they can be
at the same time.

The Admin can create other users and groups, and add users to groups. If a
user has been created, the Admin can also create a password for them, or can
leave this field blank. If the password is blank, the user can create their
own, using the same security tools the Admin has used, albeit with fewer
options, if they are not a member of the Admins group themselves. Once
users are individuated, permissions can be granted for various groups of
users: some, like the Admins, can set permissions for users and other groups
that Admins may have created. "User-level" (non-Admins) have their
permissions set for them. The Admins grant or deny read, write, update and
delete permissions for tables, forms, queries, reports, macros etc.

Here I am at the limits of my knowledge:, and the questions begin:

Permissions are attached to the object itself, say my references. I take
this to mean that if a table were imported into another database (by linking
or by copying, does it make a difference?) then the permissions would
accompany it. In practice, does this mean that a database that was
unsecured now requires a logon?

A workgroup is a collection of users, objects, groups, permissions, and
passwords, it seems. It is represented by a file with .mdw as its
extension. Is a workgroup file associated with Access itself, with Jet, or
with a database? Does Access keep track of where this file is, internally
with some pointer? Is it a Registry key? Does Access read this file each
time it starts up, or only when it opens an associated database - in other
words, is the pointer to the security .mdw file stored in the database
itself?

And what about the Jet engine? I created a secured database that required
user names and password access, then stored it in a webroot folder and
created ASP pages to query and post to the database. Despite being password
protected, no username or password was provided in the ADODB connection
string, yet the information was returned to my browser from a query. it's
possible that I received the contents of a browser cache instead of a
database hit, I realize now, but I don't think that's what happened.

If it is possible to have more than one workgroup, there must be more than
one .mdw file. Some references suggest making a backup of System.mdw and
renaming it something other than System.mdw. How does Access know that you
have done this? How does one switch between multiple .mdws, as one post
suggested earlier? The workgroup administrator offers the option of
creating a new workgroup or joining an existing one. Who or what is it that
"joins" a workgroup: the Access application, the current database or the
user?

In order for the .mdw file to be read by others on a network, it has to be
in a shared folder, say my references. If my database were in a shared
folder but my system.mdw folder were not, could others open the database in
question? If they were using a copy of Access on their own machine, would
they reference their local System.mdw file containing different settings,
and perhaps over-ride security?

As you can see, I'm confused. I have reference books but they are somewhat
sketchy. Sorry for the long-winded post, but if anyone has information on
these issues, please feel free to respond. Again, my apologies for going
over the same ground once again.

-CH-

 

[Lynn Trapp]

Charles,
First of all, have you read the Security FAQ? If not, you need to do so.
There is a link to it on the Security page of my website.

See my other comments inline below.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html

Please pardon yet another post on the subject of Access and user-level
security. I am inexperienced and greatly appreciate any help I can
receive. I would like to ask a question about the internal organization
of MS Access (or, perhaps, Jet) security.

I am developing an application which will give multiple users access to
various tables and forms, and with various permissions for reading,
entering, updating and deleting data. Eventually this will be deployed on
the Internet. I have read and experiemented and thus far have
successfully created a new workgroup, added a password for the Admin
role, added groups, users, user IDs and passwords for users and allocated
permissions on the database objects. I have demonstrated that these
"roles", to borrow an Oracle term, are restricted as intended. Some
questions remain about andministering multiple users, multiple System.mdw
files ("workgroups") and remote logins to a secured database.

Did you use the Security Wizard to do this or did you do it manually? You
should always name the .mdw file you use for securing your databases
something other than System.mdw. That is the default that is created when
you install Access. You will be happier with life if you always have a
different name for the secure workgroup file.

As I understand matters, the Microsoft Jet engine enforces Access security
rules, and security is always enforced when Jet runs, according to the
settings it reads.

Yes, this is true. If the Admin user has no password then every user is
logged in "silently" as Admin.

[snip]

In order to secure the database it is necessary to at least create a
database password. Doing so will create a prompt for any (anonymous) user
to gain access to the database, but once admitted, they are once again
all-powerful Admin.

NO, NO, a thousand times NO. Do NOT create a database password. It is next
to useless. Hackers can crack it in just a few seconds, if not quicker.

In order to differentiate powers, as it were, it is necessary to define
"user-level security", which means, at a minimum, creating a password for
the Admin. Once Admin has a password created for their account, the
database can no longer be accessed without specifying a "user name" and
password, if any, both. In the previous case we assume no database
password has been created. I don't know what would happen if both a
database password and user-level security were enforced, or if they can be
at the same time.

If you give Admin a password AND create a database password, you will be
prompted for both the database password AND the user name/password
combination.

The Admin can create other users and groups, and add users to groups. If
a user has been created, the Admin can also create a password for them, or
can leave this field blank.

Except through VBA, the Admin user cannot create a password for users but
can clear a user's password. However, if you secure your database properly,
you should remove the Admin user from the Admins group and remove all
permissions to all objects for the Users Group so that Admin has no ability
to do anything. This is how you prevent unauthorized entry to the database.

[snip]

Permissions are attached to the object itself, say my references.

That depends on what you mean, but the permissions for given objects are
stored in the database file.

I take this to mean that if a table were imported into another database
(by linking or by copying, does it make a difference?) then the
permissions would accompany it.

If you link to a table that has been secured, then the permissions on that
table will adhere, if that's what you mean.

In practice, does this mean that a database that was unsecured now
requires a logon?

The logon requirement is based purely on the presence or absence of an Admin
user password. If the Admin user of the .mdw file has a password then a
logon prompt will appear.

A workgroup is a collection of users, objects, groups, permissions, and
passwords, it seems. It is represented by a file with .mdw as its
extension. Is a workgroup file associated with Access itself, with Jet,
or with a database?

A workgroup file is, itself, a Jet database.

Does Access keep track of where this file is, internally with some
pointer? Is it a Registry key? Does Access read this file each time it
starts up, or only when it opens an associated database - in other words,
is the pointer to the security .mdw file stored in the database itself?

The specific workgroup file being used is based on each session and it
depends on how you open the database. If you open the database by double
clicking on it from Windows explorer, then it will use the workgroup file
that the computer (it is a by machine situation) is joined to. If, however,
you create a shortcut (as recommended in the Security FAQ) with the /wrkgrp
switch, then that workgroup will be used for as long as that session of
Access remains open. The general syntax for creating such a shortcut is:

"FullPathToMSAccess.exe" "FullPathToDatabase.mdb" /wrkgrp
"FullPathToSecure.mdw"

And what about the Jet engine? I created a secured database that required
user names and password access, then stored it in a webroot folder and
created ASP pages to query and post to the database. Despite being
password protected, no username or password was provided in the ADODB
connection string, yet the information was returned to my browser from a
query. it's possible that I received the contents of a browser cache
instead of a database hit, I realize now, but I don't think that's what
happened.

I can't give you any advice here

If it is possible to have more than one workgroup, there must be more than
one .mdw file. Some references suggest making a backup of System.mdw and
renaming it something other than System.mdw. How does Access know that
you have done this? How does one switch between multiple .mdws, as one
post suggested earlier? The workgroup administrator offers the option of
creating a new workgroup or joining an existing one. Who or what is it
that "joins" a workgroup: the Access application, the current database or
the user?

The COMPUTER you are using is what joins the workgroup.

In order for the .mdw file to be read by others on a network, it has to be
in a shared folder, say my references. If my database were in a shared
folder but my system.mdw folder were not, could others open the database
in question? If they were using a copy of Access on their own machine,
would they reference their local System.mdw file containing different
settings, and perhaps over-ride security?

If you properly secure your database, they should only be able to open it by
using (either via a shortcut or by joining) the workgroup file that you used
to secure the database.

 

[Joan Wild]

NO, NO, a thousand times NO. Do NOT create a database password. It is next
to useless. Hackers can crack it in just a few seconds, if not quicker.

Agreed, but to answer your question, yes once you are admitted, you are in
as the Admin user.

I can't give you any advice here

I suspect that it wasn't secured properly (like Admin still owns
everything). Therefore Admin user (silently logged in) had permission to
retrieve the information.

 

[TC]

Please pardon yet another post on the subject of Access and user-level
security. I am inexperienced and greatly appreciate any help I can
receive. I would like to ask a question about the internal organization of
MS Access (or, perhaps, Jet) security.

That's what we're here for! And congrats for making the distinction you
made. At some point, it becomes important to understand that
distinction. Some things come from Jet, & will apply to any Jet db, no
matter how you access it; but other things come from Access, & will not
apply uif you access the db using VB (for example).

I am developing an application which will give multiple users access to
various tables and forms, and with various permissions for reading,
entering, updating and deleting data. Eventually this will be deployed on
the Internet.

I'm tempted to ask, how are you going to deploy it? But that's a
question for another day

I have read and experiemented and thus far have successfully
created a new workgroup, added a password for the Admin role, added groups,
users, user IDs and passwords for users and allocated permissions on the
database objects. I have demonstrated that these "roles", to borrow an
Oracle term, are restricted as intended.

I suggest you avoid the Oracle term. It may contaminate your thinking
as to how things work with Jet & Access. For example, changing a user's
Oracle roles at runtime, is aok; but changing a Jet user's groups at
runtime - the equivalent operation in Jet - can be problematic.

Some questions remain about
andministering multiple users, multiple System.mdw files ("workgroups") and
remote logins to a secured database.

As I understand matters, the Microsoft Jet engine enforces Access security
rules, and security is always enforced when Jet runs, according to the
settings it reads.
Yes.


The System.mdw file contains the security settings for all users.

For all users /defined in that workgroup file/. But you could have
other users, defined in other workgroup files. A user of the same name
(eg. "Fred") could be defined in many workgroup files. Those different
Fred's, might or /might not/ be considered to be the same user, by Jet.
See the Personal Identifier (PID) property of the DAO User object.

Upon installation, Access creates the System.mdw file as a
default, with the only user, Admin, as a member of the Admins and Users
groups, and places the .mdw file in the owner's documents and settings path.
Presumably, Access knows where to reference this file.

The location of the current (default) workgroup file is recorded in the
registry. When Access starts, it gets that location from the registry,
locates the specified workgroup file, and attempts to log-on silently
as user "Admin", password blank. If that succeeds, you are now logged
in as user Admin. If if /fails/, that's when Access displays the logon
box, for an overt logon.

As a member of
Admins, Admin is has all permissions for all objects and, by default, there
is no password for this user. When anyone logs on to the unsecured
database, they are logging on as Admin, all-powerful, no password

required.

"Admin" is a terrible name for that user. As you correctly discern, the
Admin user has no "built in" priviliges, at all. He obtains his
priviliges from his default ownership of various objects, and his
default membership of various groups. You can easily construct a
database in which the Admin user does not own any objects, and is not a
member of any groups. That Admin user would have /no/ priviliges to any
objects in that database. In that regard, a far better name for the
Admin user, would be "Guest".

In order to secure the database it is necessary to at least create a
database password. Doing so will create a prompt for any (anonymous) user
to gain access to the database, but once admitted, they are once again
all-powerful Admin.

I've never tried a database password in conjunction with user level
security - but I imagine they should work independently. So, if the db
had a db password, /and/ the Admin user had a user-level password,
Access should prompt for /both/ passwords. Try it & see. Of course, if
you have user level security, there'd be no value in having a db
password as well.

In order to differentiate powers, as it were, it is
necessary to define "user-level security", which means, at a minimum,

creating a password for the Admin. Once Admin has a password created for
their account, the database can no longer be accessed without specifying a
"user name" and password, if any, both.
Yes.

In the previous case we assume no
database password has been created. I don't know what would happen if both
a database password and user-level security were enforced, or if they can be
at the same time.

Try it & tell us!

The Admin can create other users and groups, and add users to groups.

This is where I offer you a big, fat warning /DO NOT/ use terms
like "the Admin". Only use defined terms such as "the Admin user", or
"the Admins group". And when you refer to the Admins group, you really
mean: "the Admins group of the workgroup file which was in effect when
the database was created". Only the Admins group of /that specific
workgroup file/, has any special privilges to that database. The Admins
groups of /other/ workgroup files, do not have any special privilges to
that database.

If a
user has been created, the Admin can also create a password for them, or can
leave this field blank. If the password is blank, the user can create their
own, using the same security tools the Admin has used, albeit with fewer
options, if they are not a member of the Admins group themselves.

By "the Admin", above", you really mean "any member of the Admins group
of the workgroup file which was in effect when the database was
created"; tight?

Once
users are individuated, permissions can be granted for various groups of
users: some, like the Admins, can set permissions for users and other groups
that Admins may have created. "User-level" (non-Admins) have their
permissions set for them. The Admins grant or deny read, write, update and
delete permissions for tables, forms, queries, reports, macros etc.

Here I am at the limits of my knowledge:, and the questions begin:

Permissions are attached to the object itself, say my references.

Permissions only make sense in regard to:
- a specified object in a specified database, and
- a specified user (or group) in a specified workgroup file.

So, you would talk about the permissions of user Tom in workgroup file
1.WGF to table Blah in database A.MDB. Or, the permissions of group
GebneralUsers in workgroup file 2.WGF to table Salary in database
B.MDB. For more information on this, look for old posts from me in this
newsgroup containing the term SID (security identifier).

I take
this to mean that if a table were imported into another database (by linking
or by copying, does it make a difference?) then the permissions would

accompany it.

Sure does, and No. If you lik, you are accessing the actual table in
the other database. You, by which I mean, the currently logged-on user,
might or might not have permission to do that. But when you import a
table - assuming you have permission to do so, a new table /owned by
you/, is created, in the current database, and you, as owner of that
table, will have full permissions to it, thereafter.

A workgroup is a collection of users, objects, groups, permissions, and
passwords, it seems. It is represented by a file with .mdw as its
extension.

NO. A workgroup file containes only user names, group names, user/group
relationships, and a unique identifier called the Security Identifier
(SID) for each user & group. It /does not/ contain, or know about, the
database objects. The /database/ contains the database objects, and a
system table saying which SIDs have what access to which objects in
that database. This is the key to understanding Jet security.

Is a workgroup file associated with Access itself, with Jet, or
with a database?

There are only two ways in which any of those things are "associated".
1. Access knows the location & name of the current default workgroup
file (by looking in the registry), and
2. Every Jet database knows the SID of the Admins group of the
workgroup file that was in affect when that database was created. This
is how Jet can distinguish a member of the Admins group "of the
workgroup file that was in affect when the db was crewated", from, a
member of some other Admins group of some other workgroup file.

Does Access keep track of where this file is, internally
with some pointer? Is it a Registry key? Does Access read this file each
time it starts up,

Yes, as above.

or only when it opens an associated database - in other
words, is the pointer to the security .mdw file stored in the database
itself?

The db knows the SID of the Adnins group of the right workgroup file,
but it does not know the name or path of that file; hence it can not
open it automagically.

And what about the Jet engine? I created a secured database that required
user names and password access, then stored it in a webroot folder and
created ASP pages to query and post to the database. Despite being password
protected, no username or password was provided in the ADODB connection
string, yet the information was returned to my browser from a query. it's
possible that I received the contents of a browser cache instead of a

database hit, I realize now, but I don't think that's what happened.

Don't know ADOB - fingers getting tired - will pass on that one!

If it is possible to have more than one workgroup, there must be more than
one .mdw file.

Yes. Seldom done in practice, but eminently possible if required.

Some references suggest making a backup of System.mdw and
renaming it something other than System.mdw.

That would just be for the purpose of copying it back if the original
became corrupted. Unnecessary, IMO, because I believe (but am not 100%
sure) that Access will create a new default workgroup file, if you
delete the old one. Far more important to keep a backup of any /new/
workgroup files that you create for the purpose of securing a database!
And you'll doubtless deduce, from that, that youshould /not/ use the
default wgf (system.mdw) to secure a database. That leaves a great big
hole. You should start by creating a /new/ wgf & giving it unique
creation details. All good guides should mention that.

How does one switch between multiple .mdws, as one post
suggested earlier? The workgroup administrator offers the option of
creating a new workgroup or joining an existing one. Who or what is it that
"joins" a workgroup: the Access application, the current database or the
user?

"Joining" a wg just means, using the wg administrator program to change
the default wgf name & location in the registry. Then Access looks
there, for ther wgf. A bad idea, cos this will affect /all/ databases
on that PC. Better to leave the registry poiunting at the default wgf,
so normal (unsecured) db's do not ask for a username/password. Then,
when you create a new wgf & secure a database against it, use the
/wrkgrp switch of a shortcut target to tell Access to use that
particular wgf when it opens that particular database. Google is your
friend again.

In order for the .mdw file to be read by others on a network, it has to be
in a shared folder, say my references.

Or, have a seperate copy on each PC. But then, of course, you have the
logistical problem of keeping them all up to date.

If my database were in a shared
folder but my system.mdw folder were not, could others open the database in
question?

Before you worry about that: do you realize that the db should be split
into a so-called "front end/back end" structure? And each user should
have their own copy of the FE? Google is your friend again.

If they were using a copy of Access on their own machine, would
they reference their local System.mdw file containing different settings,
and perhaps over-ride security?

People often say: I used a different wgf, & it let me straight in!!
This just means they missed a step in the securing process.

As you can see, I'm confused. I have reference books but they are somewhat
sketchy. Sorry for the long-winded post, but if anyone has information on
these issues, please feel free to respond. Again, my apologies for going
over the same ground once again.

It's a very tough capability to understand clearly. Hope I've helped,
good luck!

TC

 

[TC]

For all users /defined in that workgroup file/.

Oops, no. As stated later, the workgroup files does /not/ contain the
object permissions. Those are in the database file. The rest that I
said in that par, was correct.

TC

 

[Guest]

I have followed your this and processes in response to Charles Hudson's
questions.

Right now I am set up with a functional clean database, with a fully defined
mdw file. The default "admin" user is not the owner of any part of the
database and has no permissions whatsoever. The database has been split and
will be operating off of a server. The front end is ready for operation and
installation on the respective computers of the people in the department.

So I am down to this one issue. I need to set up the shortcut for the
secured database, so that the secured database does not interfere with the
nonsecured databases. I have looked at the target for the shortcuts and can
see what it says, I just do not know what to put in the shortcut to get it to
open up the correct mdw as it opens the secured database.

I just need to know how to point to the proper mdw while opening the secured
database.

Thank you in advance.

 

[TC]

Right-click the shortcut and choose Properties. Then you will see a box
for the shortcut "target". In that box, you should type:

"xxxx" "yyyy" /wrkgrp "zzzz"

replacing xxx with the full parth & name of your copy of msaccess.exe;
yyy with the full path & name of your database; and zzz with the full
path & name of the correct workgroup file for that database.

For example:

"c:\program files\office\msaccess.exe" "c:\stuff\my database.mdb"
/wrkgrp "c:\stuff\securing.mdw"

HTH,
TC