User Level Security Malfunction

[mikeycan]

Hello!

I have an Access 2003 database, and I ran the User Level Security Wizard on
the database, and it all seemed to work fine. I get prompted to log in and
the world is good. Later, I asked my other users to open the database, and
it let them all in, without prompting for a UserID/Password. I did a little
research and it suggested I might not have removed my Admin user from the
Admins group and/or not assigned a password to the Admin. But when I checked
the Users/Group Accounts and Users/Group Permissions, its shows that I
“successfully†made myself the owner of the database (different than the
Admin user), assign the Admin a password, and that the Admin user is not in
the Admins group. What could have gone wrong and how to I fix it? I have
done this before on an Access2000 database had had no issues.

The same articles also recommended that I create a new blank database with a
new workgroup (manually) and import the old database objects. I got to the
point of trying to import, then I got the error that I don’t have permissions
from the old database. I logged into the old database (hoping if I had both
open it would work) and it still gives me the same error on import.

 

[Olduke]

It sounds as though you used the wizard's suggestion when it came to a file
to store your security setting.

When you set up security in Access, the security settings (User IDs, User
Groups, Passwords and Permissions) are saved by default in a file called
System.mdw

System.mdw normally resides in the Windows/System folder on your computer.

The System.mdw on my computer is different than the System.mdw on your
computer. Why? I have different software, settings and set up than you do.

As a result, if your database is copied onto my computer and my System.mdw
has no Access security settings, it lets me right in with no challenge.

The way to go is to create a new .mdw file (different name than “Systemâ€)
which is in the same folder as the database. This file will contain your
security settings for that specific database. You have to place this new
..mdw in the path on your shortcut to the database so that anyone who opens
the database will have to enter their UserID and Password.

 

[mikeycan]

As part of the running the User Level Security Wizard, I did create a new
..mdw file (called Security.mdw), and it was used to add the users. I did not
add to the system.mdw. From my research, I understand how the system.mdw
has the Admin in the Admins group, and it will open any database that also
has the Admin in the Admins group. However, since my Admin is not in the
Admins group, any of the users System.mdw should not override entry into my
database. But it does.

 

[Rick Brandt]

As part of the running the User Level Security Wizard, I did create a
new .mdw file (called Security.mdw), and it was used to add the
users. I did not add to the system.mdw. From my research, I
understand how the system.mdw has the Admin in the Admins group, and
it will open any database that also has the Admin in the Admins
group. However, since my Admin is not in the Admins group, any of
the users System.mdw should not override entry into my database. But
it does.

When your file can be opened with any old mdw file then it is not secured
properly. When anyone opens your file with a workgroup file that does not
require a login they are *absolutely* doing so as the user 'Admin' member of
group 'Users'.

Admin's membership in the 'Admins' group is not usually an issue because the
'Admins' group in any user-created mdw file is not the same as the Admins group
in any other mdw file unless the same identifiers were used to create them.
However; the group 'Users' is the same in ALL workgroup files.

So...if they can open your file then it must be the case that either 'Admin' or
the group 'Users' has permissions or ownership that they should not have. In
most cases it is because 'Admin' still owns the database. That will let anyone
in regardless of the permissions you have taken away from Admin because Owners
have built in permissions that cannot be taken away.

 

[mikeycan]

That is just my dilemma. According to the User and Group Permissions menu,
the Users group has no rights to open the database, and it also shows that my
personal User ID is the owner of the database (as well as, all the objects
individually). So again, I don’t understand why the default system.mdw will
allow the database to be opened. But bottom line, can I fix it?

 

[BruceM]

The way I understand it the database owner can not be reassigned. The
person who logged on to Access when the database was created is the owner.
Click Tools > Security > User and Group Permissions. Click the Change Owner
tab, and select Database as the Object Type. If you are listed as the
owner, you created the database while logged on as yourself, not as Admin.
I know you said you are the owner, but I'm just trying to verify the
details.
If you double click the database file itself in My Computer, can you open
it? If so, there is still a security hole. Are you opening the database by
way of a shortcut that includes the path to the Security.mdw file, or have
you changed the default mdw file on your installation of Access? In order
for the security to work on other computers, those computers must have the
same Security.mdw file you have (it can and probably should be in a shared
location if there are several users), and the shortcut on those computers
must include the path the the Access executable, the database file, and the
Security.mdw file. For instance:
"C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE"
"D:\Folder\YourDatabase.mdb" /wrkgrp "D:\Folder\Security.mdw"

I think the quotes are necessary only if there are spaces in the path.

You may have done these things, but you were not specific about some of
them, so I can't be sure.

 

[Rick Brandt]

That is just my dilemma. According to the User and Group Permissions
menu, the Users group has no rights to open the database, and it also
shows that my personal User ID is the owner of the database (as well
as, all the objects individually). So again, I don't understand why
the default system.mdw will allow the database to be opened. But
bottom line, can I fix it?

I can only suggest finding one of the on-line explicit step by step instructions
for securing a file and starting over with one of those. It is too easy to miss
a step otherwise. On those rare occassions where I create a secured file I do
it the sort of the other way around. I first create a blank secured file that I
verify cannot be opened except with my workgroup file and THEN I build my app
with it. I just did this excercise last night and it worked for me.

Create workgroup and join it.
(close and re-open Access (use any file you like)

Create new user, and place in Admins group
Remove Admin from Admins group
Give Admin a password
(close and re-open Access as new user)

Create new blank file
(it will be owned by my new user since he just created it).

Give new user a password
Remove all permissions to database object from Admin
Remove all permissions to "new" objects (every type) from Admin
Remove all permissions to database object from Users group
Remove all permissions to "new" objects (every type) from Users group
(notice you only have to do this for the "new" object of each type since no
objects exist yet)

I should now find that if I close and reopen as Admin that I cannot open the
blank file. I should also find that if I rejoin System.mdw that I cannot open
the blank file.

Now that I have a properly secured file I can create objects in it. Since Admin
and Users have no permissions to "new" objects, anything I add will
automatically be an object that they have no permissions to. This also applies
to objects that are imported so I can now import all of the objects from an
existing file into this new secured file and all of those objects should be
locked down with respect to Admin and Users. Then I should be able to create
more users and groups and assign permissions as desired.

What I like about this procedure is that I don't waste time creating groups and
users and assigning permissions until after I have established that my file is
correctly secured. Another thing you can do with this method is take the
secured blank file and before you put any objects in it you can make a copy and
call that your secured template file. Any time you want another secured file
that will use the same workgroup you just copy the template file and off you go.

 

[Joan Wild]

Are your users opening the same database as you? If your database is split, then you secured your copy of the frontend, but your users are still using their old (unsecured) frontend. You need to redistribute the new frontend.

If this isn't the case, then you missed a step in securing it, as Rick said.

 

[mikeycan]

Hello All,

I will try to list all the facts as I know them:

System is Access 2003. The database and Security.mdw files are on a shared
network drive, where all 10 of the users has access. Under “User and Group
Permissionsâ€, my personal ID is listed as the owner for all the Objects
(Database, Tables, Queries etc). Under “User and Group Accounts†the Admin
is not in the Admins Group. I do not have path to the Security.mdw file in
my short cut. Instead for my installation of Access I set it to point to the
Security.mdw file. However, when I change back to the default system.mdw, it
does allow me to open the database without ID/password. Once the other users
point to the Security.mdw file they are prompted to log on, but if they don’t
point to it, their default System.mdw lets them open the database.

I was slow and methodical while running the User Level Security Wizard, so I
am pretty sure I did not miss any steps. And some how I think the database
is partially secured (hence I think this is a malfunction). Like I mentioned
earlier, after reading a few of the on-line explicit step by step
instructions, I attempted to create a new secured database (both .mdb and
..mdw files) and import the old database’s objects, but the old database gives
me the “You don’t have permissions†error. I tried opening the old with the
Security.mdw, and the new with its own SecuredRev.mdw at the same time to see
if it then would allow me to import, still the “You don’t have permissionsâ€
error. Then I tried to just cut and paste some of the old database’s objects
into the new, again “You don’t have permissions†error. I need to have some
security on this database, but I don’t want to re-write it as a new database.
Is re-writing my only choice?

 

[Joan Wild]

Hello All,

I will try to list all the facts as I know them:

System is Access 2003. The database and Security.mdw files are on a shared
network drive, where all 10 of the users has access. Under “User and Group
Permissionsâ€, my personal ID is listed as the owner for all the Objects
(Database, Tables, Queries etc). Under “User and Group Accounts†the Admin
is not in the Admins Group.

Under User and Group Permissions, what permissions does the Users Group have - (ensure you check all objects)?

If you open the mdb using system.mdw, what does it show re Users Group permissions and ownership?

 

[mikeycan]

Hello Joan,

I opened the database with my “Security.mdw†file and viewed the permissions
for the users group, then closed it and opened it again but this time with
the “System.mdwâ€, and here is what I found:

Security.mdw
Database None mmontoya
Table None mmontoya
Query None mmontoya
Form None mmontoya
Report None mmontoya
Macro None mmontoya


System.mdw
Database none <<UNKNOWN>>
Table none <<UNKNOWN>>
Query none <<UNKNOWN>>
Form none <<UNKNOWN>>
Report none <<UNKNOWN>>
Macro none <<UNKNOWN>>

 

[Joan Wild]

Well that doesn't make sense i.e. when you open using system.mdw, you are doing so as the user 'Admin' which is a member of the Users Group. And yet you found that the Users Group doesn't have permission on the Database object. So I don't understand how you are able to even open the mdb without that permission.

 

[mikeycan]

My understanding is that the Admin, in the system.mdw is part of the Admins
group as well, which is why i can open any unsecured database. So given all
this, is there any way to fix my database?

 

[Rick Brandt]

My understanding is that the Admin, in the system.mdw is part of the
Admins group as well, which is why i can open any unsecured database.
So given all this, is there any way to fix my database?

But the Admins group in System.MDW is not the same Admins group as in your
Secure.MDW so that should not be relevant. My understanding with regard to the
internal ID (this is what matters, not the name) is...

The user Admin is universally the same in all MDW files.

The group Users is universally the same in all MDW files.

The group Admins is the same in all System.MDW files that are created by
installing Access or if created by Access when it cannot find a copy.

The group Admins is unique in every user-created MDW file except in cases where
you provide the exact same identifiers when creating the workgroup.

So when System.MDW is used in an attempt to open a properly secured file, Access
will see that Admin is a member of Admins, but it won't be the *correct* Admins
group that it recognizes to have administrative rights in the file and that is
why the user will be denied entry.

 

[BruceM]

You stated earlier that you ran the security wizard, and are now the owner
of the database and all objects in it. If you are the database owner, you
logged on as yourself before creating the database. However, I have the
impression that you ran the security wizard on the existing database, so I
don't see how you could be the owner of the database itself (for which
ownership cannot be reassigned).
You state that you opened the database with one mdw file, then another. Did
you do that by changing the default mdw file, then reopening the mdb file?
If so, I suggest that you use a shortcut as I described in an earlier post.
Do not join the security mdw file (except when first creating it), but
rather use a shortcut to join it for an Access session. You could add a
user switch to force Access to ask for a password:
"C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE"
"D:\Folder\YourDatabase.mdb" /user "YourName" /wrkgrp
"D:\Folder\Security.mdw"
As I recall you can use an empty string for the user, which will bring up a
blank logon prompt.

Another option is to open Access, but no mdb file, while joined to the
Security file:
"C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE"
/user "YourName"
/wrkgrp "D:\Folder\Security.mdw"
Use the File > Open dialog to open a particular mdb file.

This will log you on as yourself while joined to the Security.mdw file. You
will be the owner of any new database you create. If you are the owner of
the one that is giving you trouble, and if in that database you have full
permissions, including membership in the Admins grouip, you should be able
to import objects from the old database to the new one.

It may be worth copying a system.mdw file from another computer that has the
same version of Access. Set System.mdw as the default workgroup file.
Double clicking on a secured mdb file will silently attempt to log you on as
the Admin user, but if the Admin user has no permissions or ownership you
will receive a "lack of permissions" message.

I wrestled with user-level security for quite a while, but thanks to some
Help documents and some helpful replies in this forum by Joan, among others,
I finally got a handle on it. I do not claim any great expertise, but I do
know that there is a gremlin somewhere in your set-up. As Joan said, you
should not have been able, using the default, unchanged System.mdw file, to
open a mdb file in which the Users group has no permissions.

The mdw file identifies you, put the permissions are in the database file.
If your default mdw file is System.mdw, you should be able to open a
database in which Admin has full permissions.

My understanding is that the Admin, in the system.mdw is part of the
Admins
group as well, which is why i can open any unsecured database. So given
all
this, is there any way to fix my database?

 

[Olduke]

FOR FUTURE REFERENCE.

The 10 Commandments of Group Level Security

I - Don’t dismiss the Database Password.
Many of the secured databases I deal with only require a database password,
not the Group Level Security function. This is especially true of
stand-alone databases (only on one computer). Although the database password
function may seem tame, most of the time it’s all that’s required.
Don’t use the Database Password function AND Group Level Security. Select
one or the other.

II - Security is always on.
You can’t turn it off. Even on an “unsecured†database, there is one
default user in the security system. (see V)

III - Do not enter the deep waters of Access security without a guide.
Check the posts in this section, download the suggested articles and read
them until you understand them. Pay special attention to Users, User Groups
and Permissions; how to use them and how to create them. I still have to
refer to my guide for assistance each time I use User or Group Level Security.

IV - Turn off your computer.
Now that you have made the decision to proceed with Group Level security,
turn off your computer. Sit down with a paper and pencil. Figure out before
you start what your User Groups are going to be and what permissions you need
to assign to them. This is done by identifying the permissions people need
to do their jobs.
Examples:
The clerks enter or update data and only require permissions to use the
forms that let them do this. They don’t need permissions for anything else.
The president of the company on the other hand is given permissions to view
or print all forms and reports.
You must then decide the permissions to be given to everyone in between.
The company’s Accounting dept. needs permissions for receivable and payable
forms and reports but they don’t require permissions for the clerk’s forms.
Create your User Groups first. Assign permissions to each User Group based
on what the group does. You then create individual users and assign them to
the appropriate User Group.
Some User Groups may have a dozen Users, others my have only one.

V - The user “Admin†is everybody.
The person who decided to use that name should be severely chastised. When
you start security you will notice that the user Admin is already there.
First time around, most people think they’re OK because they assign
themselves as Admin based on the false impression that a user named Admin
must have special rights and permissions. Admin is only the “Default Userâ€.
Admin has no special status unless you specifically assign it.
By the way, don’t confuse the user Admin with the User Group Admins (notice
the “s†in the User Group).

VI - Make a copy of your database and use that.
If you are like me, you will probably mess up the security at least once.
While you can undo or make corrections to your secured database, if its’
totally fouled up (which it will be the first few times you try Security),
it’s easier to just delete the database and start again on another copy.

VII - The Administrator
Create a database Administrator. The Administrator (most likely you) is
the ONLY person to whom you assign permissions for everything. The
Administrator is the only one you assign permissions to make programming
changes and changes to the designs of tables, queries, reports, macros and
modules. The Administrator is the only one you assign permission to move a
User to a new User Group, delete old Users, create new Users and to change
User Group permissions. The Administrator is the only one you assign
permission to upload new functions into the database.
How many people spend their entire career with the same company anymore?
When you leave the company you are currently with, it’s likely they will
still be using your database. Create a second Administrator. Place the User
Name and Password in a sealed envelope and leave it with your supervisor. If
the supervisor leaves the company, change the User Name and Password of your
“back door†and give the new information to the new supervisor. This will
avoid one of the most common Access Security questions which begins “I
inherited this secured database but I can’t make changesâ€.


VIII - Create a new .mdw file.
The .mdw file is where your all of your security settings (User Groups,
Permissions, User names and passwords) are stored. By default, they are
saved in System.mdw which is located in C:Windows/Settings of your computer.
If you store them there, the database is secured on your computer but not
on anyone else’s. Why? The System.mdw on my computer is not the same as the
one on your computer. My computer doesn’t have the security settings so I
can get right into the database with no password challenge. In addition, if
you save your security to System.mdw, all of your formerly unsecured
databases now demand a password and User Name before you can use them.
Create a new .mdw file (don’t name it System) which should be saved in the
same folder as your database. You must include this new .mdw file in the
path on the shortcut to the secured database when it’s opened. Anyone trying
to use the database will receive a password challenge.

IX - Make it fail.
Now that you’ve checked all the User Names and passwords to ensure they
work and they only have the permissions they need, run some tests. Come up
with the most obscure scenarios to make your security fail or be bypassed.
Be creative. Pretend you’re a spy trying to hack the CIA’s computers. When
you’re satisfied everything is covered, you’ve finished the job.

X - Never make changes to the original database.
One of the reasons we used a copy of the database to install security was
to leave an intact original or master copy which is kept in a secure area.
When changes are required, make them to a copy of your master. When
everything in the copy is working properly, make the copy your new master.
You’ll also have somewhere to go if you mess up your changes.
When backing up, you only need to copy the data that’s in the tables. All
the forms, queries, reports, macros etc. are already backed up in your master.

 

[Joan Wild]

All very good advice, but comments inline....

--
Joan Wild
Microsoft Access MVP

FOR FUTURE REFERENCE.

I - Don’t dismiss the Database Password.
Don’t use the Database Password function AND Group Level Security. Select
one or the other.

Why? There's no problem using both if you want to.

III - Do not enter the deep waters of Access security without a guide.
I still have to
refer to my guide for assistance each time I use User or Group Level Security.

Me too.

VI - Make a copy of your database and use that.
If you are like me, you will probably mess up the security at least once.

I'd wager that's 'if you are like anybody'

While you can undo or make corrections to your secured database, if its’
totally fouled up (which it will be the first few times you try Security),
it’s easier to just delete the database and start again on another copy.

And also delete any mdw you created.

VIII - Create a new .mdw file.
The .mdw file is where your all of your security settings (User Groups,
Permissions, User names and passwords) are stored.

Nope - the permissions are not stored in the mdw. They are stored in the mdb file.

By default, they are
saved in System.mdw which is located in C:Windows/Settings of your computer.
If you store them there, the database is secured on your computer but not
on anyone else’s. Why? The System.mdw on my computer is not the same as the
one on your computer.

Yes it is. That's why it isn't secure if you use system.mdw.

My computer doesn’t have the security settings so I
can get right into the database with no password challenge.

Yes, but that's because the system.mdw files *are* the same. I think you meant the leave the 'not' out of your sentence above.

 

[Lincoln]

Are your users using the shortcut that the Security Wizard created. If not
Access is going to use the System.mdw file.