Re: Sorting out security
- From: "TC" <aatcbbtccctc@xxxxxxxxx>
- Date: 12 May 2005 00:04:17 -0700
Charles Hudson wrote:
> 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
.
- Follow-Ups:
- Re: Sorting out security
- From: TC
- Re: Sorting out security
- References:
- Sorting out security
- From: Charles Hudson
- Sorting out security
- Prev by Date: Re: Access security
- Next by Date: Re: Auto Signon and Joining MDW Group
- Previous by thread: Re: Sorting out security
- Next by thread: Re: Sorting out security
- Index(es):
Relevant Pages
|
|
