User Level Security in 2003

[Deborahp]

It has been awhile since I used the wizard to set security. Actually, I have never applied it in 2003.
Can someone give me some simple steps on what to do first??
Do I create a copy of the .mdw file and join??
Is there a way to capture their network login and then have them create a password??

Need some help

Thanks,
Deborah

[HansV]

The easiest way is to select Tools | Security | User-Level Security Wizard.
The first step will ask you whether you want to create a new workgroup information file (.mdw); if you're already using a custom .mdw you can choose to use that.

x64.png

In the next step, you can provide the data needed for the new .mdw.

You can use VBA code to get the Windows username:

Dim strUser As User
strUser = Environ("username")

but the Access user and Windows user are completely unrelated, they aren't linked in any way.

[Deborahp]

OK. Let me ask this because I remember having this issue a few years ago.
When I create the new .mdw and add the users, how do I make it specific for this database only?
I seem to remember, I locked everyone out of access a few years ago :(

[HansV]

The second step of the wizard will ask you whether you want to make the new .mdw your default one, or whether you want to create a shortcut that opens the database with the new .mdw. You should choose the latter.

x249.png

[Deborahp]

OK. Went through the wizard, made the shortcut and life seemed GOOD. However, this morning, I went to the folder and doubled click on the icon for the database and it opened. I did not use the shortcut.
So, why???
And what can I do to keep this from happening again?

[HansV]

If you open the database directly, you use the workgroup information file of which you are a member. If this is the original System.mdw, there is probably no password for the Admin user. In that case, you are not prompted for a username/password and you are automatically logged in as Admin. It then depends on the permissions that Admin has. In a well-secured database, all permissions have been taken away from the Admin user so that you can't do anything useful when logged in as Admin.
See Access Security FAQ for heaps of useful information about user-level security.

[Deborahp]

This folder only contains my new workgroup file. I thought since it was connected to this workgroup then it would not open up without asking for a username and password.
Hmmm...I think I am missing something.

[HansV]

A database is not associated with any workgroup information file.

Your Windows user has a default workgroup information file. When you install Access, it is System.mdw; you can join another workgroup information file through Tools | Security | Workgroup Administrator...

If you open a database directly from Windows Explorer, or start Access and open a database, you're using your default workgroup information file.

The Security Wizard lets you create a new workgroup information file, and gives you the option to join it (make it your default one) or not. If the latter, the only way to open the database together with the special workgroup information file is by using a command line or a shortcut that runs that command line.

[Deborahp]

Hans,
I appreciate all your information. I am just having a block here...I just don;t understand.
I created a new workgroup. I have a short cut and when I run from the shortcut, it prompts for login. If i run from windows explorer....no login.
I went to tool/security and JOINED it again. This time all databases are asking for a login in.
I only want this one database assocaited with this new workgroup and no matter how it is accessed, the user has to log in.
Can this be done???

[HansV]

As you indicate, you don't want to be prompted for a username and password in ALL databases. So you should select Tools | Security | Workgroup Administrator... and join the original System.mdw again (it is located in C:\Documents and Settings\<username>\Application Data\Microsoft\Access or in C:\Users\<username>\AppData\Roaming\Microsoft\Access depending on your version of Windows), or create and join an new .mdw file.

Before continuing, I strongly urge you to read first two sections (1. What are the steps to help protect a database? and 2. In a nutshell, how does Microsoft Access security work?) in the Access Security FAQ carefully, and let it sink in.

Next, open the database using the shortcut, and log in using a username/password that gives you administrate permissions. This should NOT be the default Admin user, but a "new" user who is member of the Admins group. If you don't have such a user yet, login as Admin, create a new user (Tools | Security | User and Group Accounts...) and make it a member of the Admins group, then close the database, then reopen it and log in as the new user.

Now make sure that the Admin user is not a member of the Admins group, and that the Users group has few or no permissions (Tools | Security | User and Group Permissions...)

When you have done this, users won't be able to do anything in the database when they open it without using the shortcut.

[Wendell]

In addition to what Hans suggests you read, you might find our tutorial The Secrets of Security useful. Also you might want to read Jack MacDonald's "How I Use Microsoft Access User Level Security" which is no longer available on his web page, but can be found attached to this thread on dbforums.

[Deborahp]

I have read and re-read all the material. If I understand correctly, the user must access the database by the short to invoke the security login not from windows explorer. This will work for most users since they are not very computer literate and think that windows explorer is internet explorer however I do have some IT people using it and well, they just double click on the icon in the folder and do not have to log in. I thought in the past that I had several databases with security that no matter where I activated it that it required a log in. Am I dreaming??

[HansV]

If the Admin user in the workgroup information file you're a member of has a password, you'll have to log into every database.

If the IT people insist on opening the database directly, they shouldn't be able to do anything if you have removed all permissions from the Admin user. They'll learn to use the shortcut soon enough if that is the only way they can do anything useful.

[Wendell]

As Hans noted, but let me emphasize - if you remove all permissions from the Admin user, then people trying to open it using the Admin account will be blocked.

[Deborahp]

I do understand that. I am the only one in that workgroup who has admins rights and it works great from the shortcut. I have removed all persmissions from the admin user.
When they open it from explorer, it uses the system.mdw file and gives them full access.

[HansV]

I'm sorry, but you must have done something wrong - if you have removed all permissions from Admin, they shouldn't be able to do anything when they use system.mdw.

[Deborahp]

Ok. I fully agree with you!
SO, I need to open my secured database using the shortcut.
Go into Tool/Security/persmissons and make sure all the persmission for ADmin are removed.
or do I just open access and do this??
I thought if I open access and do this, then I will have to login for every database.

[Wendell]

Permissions are on a database by database basis, so if you remove all the Admin priviledges in one database, it will still have permissions in other databases. And you do it with Tools/Security/Permissions.

[Deborahp]

I understand that. Really, I do. LOL
I just need to know which way to open the database to make the changes.
With the new .mdw file from the shortcut or just inside of access.
Wait...if I change it in my database just in access, that system.mdw will only be applicable to that database and not to every other one??? is that what you are telling me??

[HansV]

You have to distinguish between the general user and group settings on the one hand, and the permissions they have in a specific database on the other hand.

User and group settings, such as passwords and group membership, are stored in the workgroup information file (.mdw).
Permissions of users and groups on database objects are stored in the database.

You need to open the database using the shortcut, and log in as a user that is a member of the Admins group, but not Admin itself. Then select Tools | Security | User and Group Permissions... and make sure that you are the owner of all database objects, and that Admin has no permissions on any database object. You can do this because you are logged in as a member of the Admins group other than Admin (you wouldn't have been able to do this if you had used System.mdw because there, Admin is the only member of the Admins group, in fact the only user).

If you then quit Access and reopen the database directly from Windows Explorer, you'll be logged in as Admin automatically (since it is the only user and since it doesn't have a password in System.mdw), and therefore you shouldn't have any permissions.