A Sample Of The Database Users Security Wizard  (Access 2002)

The following sample is designed to give read-only access to any Access user and setup a database with user groups that provide admin, full data access and readonly access to data.   This sample is for a backend data only database and the security setup will apply to the frontend database as well.  Click on the small pictures to expand them.

0) Ask all users from the backend data only database and the front end database to exit the database.   

1) Make sure that your Access database options allow you to see all hidden objects.   Now make all hidden objects unhidden by opening the properties of the objects and unchecking the hidden option.  This is critical as the wizard will miss all hidden objects !!!!

2) Now open the database and choose Security Workgroup Administrator.  If you already have a workgroup file that you want to use, join it and login to the user that you want to own the database and objects.

 Click join and find the SecureIt.mdw file  that I sent you in the email

 Click OK and login as ivan01    pwd    [Leave blank]  and click OK

 Now its time to start up the user level security wizard

Choose modify my own workgroup file

 Make sure that all objects are selected  and click next

 
Select full data users and readonly data users.  Remember to write down the PID for for groups as this is important.

As we are allowing other users to see our data in read-only mode,  check  Yes and Open/Run as below.  This is the main deviation from the standard responses for the wizards.  Unless the customer has some real reasons to hide the data from everyone, it is better to allow any Access users to have read-only access to the data.  That way if the security mdw file is lost or corrupted, then at least the data can be viewed.  And the data is usually far more valuable than the software.  If you completely lock the database, you should add a utility to export information to text so that you can recover the data.

On tables, check    Read Data and Read Design   and click the Next button

On this screen, add 2 users  reader   and editor  and remember to write down  with  PID’s  of  reader   and editor   and do not add any passwords as yet.

Now we need to add the editor to the full data users group.  Click the group or users combo and choose Full Data Users and Editor as shown below. 

 Now choose Reader and Read-Only Users

Now select the name of your backup database very carefully.  This will store the database as it was before any security has been applied.   Click finish

Now an access report will be generated.  This must be printed out and the Snapshot of the report should be kept as well as it tells a lot about the security process.  You may even wish to convert the snapshot to PDF format for safe keeping.

Now all you have to do is test, test, test as your live access database will now need to be converted over before anyone adds any new data.   Maybe you could even use The Workbench to keep users out of your Access 2000/2002 database whilst you are doing this maintenance work.

 After Conversion Jobs

The conversion wizard encrypts your database and doesn't tell you about it.  This is not desirable as an encrypted database can not be compressed into a zip file and is slower to run.   Under the security options, you will find the encrypt/decrypt options. 

Access 2000  - Not Yet Secure

Now it is testing time and guess what, if you log into a different password file and access the database as ADMIN which will happen after a new installation of Access, the database is not secure.   In Access 2002/XP it is pretty secure using the above method (please test). 

Make sure that you are logged into one of your security accounts (not Admin) and choose the User and Group Permissions option as below

Select List Users and your administration account as shown below .   Now select all objects by clicking on New Tables/Queries and holding down the shift key until you get to your last table.     Now click in the  Administer check box.   Press Apply.

Now click in the Groups radio button and all the tables should still be highlighted.   Make sure that Admins is selected as the group.   

Check the read data box so that it looks like the following picture.   Now press Apply.

You may wish to do this for other objects in your database.  I think that the best thing to do now is test and see how the protection of tables goes before charging onto to protect other things in your database.

Other Pages That Maybe Of Interest

Security Concerns, Encryption and Database Passwords
How to set field level permissions

Warning:  Even this approach still seems to leave the Access 2000 database vulnerable to the ADMIN account having full privileges when using another security workgroup file.   Check ownerships of the database and maybe you will need to import all objects from the current database into a new one whilst using a member of the Admins group.   Garry Robinson

Links >>>  Home | Search | Workbench | Orders | Newsletter | Access Security | Access professionals