Go Back   Access World Forums > Microsoft Access Discussion > General

Thread Tools Rating: Thread Rating: 4 votes, 5.00 average. Display Modes
Old 03-26-2008, 11:09 AM   #1
Registered User
Join Date: Nov 2007
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
joyful15 is on a distinguished road
User Level Security

How do you set up user level security for your database?

joyful15 is offline   Reply With Quote
Old 03-26-2008, 11:15 AM   #2
Newly Registered User
statsman's Avatar
Join Date: Aug 2004
Location: Toronto ON
Posts: 2,074
Thanks: 4
Thanked 3 Times in 3 Posts
statsman will become famous soon enough
Almost everything in Access can be learned through trial and error, except security. Do not enter this Twilight Zone without a detailed users guide.

I would recommend:

download the first file – Security Paper by Jack Macdonald. It’s a little long but it tells you everything you need to know.

The 10 Commandments of Group Level Security

I - Don’t dismiss the Database Password.
Many databases only require a database password, not the Group or User Level Security function. This is especially true of stand-alone databases (only on one computer). Although the database password function may seem tame, many times it’s all that’s required.
While you may use both the Database Password function AND Group Level Security, you should really select one or the other. The two functions (Database Password and Group Level Security) are separate and distinct from one another. By using both, you are not adding to security, you are merely introducing a redundant step.

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.
Almost everything in Access can be learned by trial and error EXCEPT SECURITY.
Check the posts on security, then 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 or User 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.
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. This will avoid having to assign individual permissions to every user.

Some User Groups may have a dozen Users, others my have only one.

V - The User “Admin” is no one and everyone.
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”.
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.
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. Don’t forget to delete your .mdw file as well (see IX).

VII - The Owner
When you create a database, Access assigns an owner. The owner is the person who was logged on at the time the database was created. As “Admin” is the default User (See V) “Admin” is normally the database owner.
Regardless of what restrictions you may subsequently place on “Admin”, “Admin” can’t be restricted – after all, he owns the database.
The Final Step in Group Level Security is to make your User name the owner and to delete “Admin”.

VIII – The Administrator
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 an Administrator who has permissions for everything. 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 any changes”.

IX - Create a new .mdw file.
Security settings are saved in a Workgroup file which has the extension .mdw The default is System.mdw which is usually located in C:Windows/Settings of your computer.
Each secured database should have its own .mdw file which should be saved in the same folder as the database. When creating it, give it a name other than System.mdw as this may cause problems down the line.
You must include this new .mdw file in the path on the shortcut to the secured database. Anyone trying to open the database will receive a password challenge.

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. This way you’ll always have somewhere to go if a disaster occurs.
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.
Dart players always have a point.

Last edited by statsman; 03-26-2008 at 11:22 AM.
statsman is offline   Reply With Quote
The Following User Says Thank You to statsman For This Useful Post:
yus2aces (07-25-2012)
Old 03-27-2008, 01:22 AM   #3
Registered User
Join Date: Mar 2008
Posts: 24
Thanks: 0
Thanked 0 Times in 0 Posts
joepele is on a distinguished road
Sorry to hijack this thread but I have another question to add on.

If I have a Database where it contains the sales / client records of the 10 sales people.
When there is a new client the manager will randomly assign the client to one of this sales people to follow up.

How can I "secure" the database where the sales person can only view / update their own client?

joepele is offline   Reply With Quote

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Access 2007 User level Security Articles KeithG General 5 06-27-2011 04:12 AM
user level security wizard help please stanat20 General 0 10-07-2007 05:18 AM
abour user level security onurakkus General 5 01-22-2007 07:12 AM
Multi User / Level Security bodvoc General 2 02-15-2005 08:08 AM
User Level Security wjoc1 General 2 02-26-2004 06:28 AM

All times are GMT -8. The time now is 12:51 PM.

Microsoft Access Help
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post

Sponsored Links

Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World