Securing A Database

All,

As I promised earlier, here is my suggestion to secure an MS-Access database. The explanation below is applicable for MS-Access 2000 and XP versions, I haven't tested it on Access 2003.

This way of securing a database is a bit different from the one I posted earlier, but more secure.

Disclaimer:
This explanation of securing a database is based on MY experineces. I mean I've been doing it in this way, and it worked OK for me.
I DO NOT say that there is no other method of securing a database.
I will NOT take responsibility for any problems caused by securing your databases either in this or in any other way.
Use this guide on your own risk.

Pre-reading notice:
Menu paths and commands may be different in the English version of MS-Access. I use Hungarian MS-Access, but I am doing my best to remember (or translate to) the English menus and commands.
Where I'm in doubt about the exact English menu path or command and there are more than one possible translations, I enclose the alternative between parentheses.
Menu paths are indicated like this: File -> Get external data -> Import...
Command buttons are indicated like this: <OK>

To secure a database:
1. close the database you want to secure.
BEFORE closing it:
- If a database password is set, then clear it.
- If the VB Project is protected then unprotect it. To unprotect the VB Project go to Tools -> [project_name] options... in VB Editor and click the Protection tab. Remove the tick from the 'Lock Project from viewing' check box and clear the password.
2. CREATE a new blank database;
3. Check and make a note of the path of the default Workgroup Information File (.mdw).
- to do this, go to Tools -> Security -> Workgroup Administrator, and read the file path indicated.
4. create a new .mdw file using the Workgroup Administrator, and stay joined to it.
- to create a new Workgroup Information File go to Tools -> Security -> Workgroup Administrator, and click <Create...>. Provide the required information, click <OK>, click <Browse...>, and type a name.
- I usually use the name of my database which I want to secure.
- Note: if you wish your .mdw file to be unique, then you need to provide a workgroup code.
- If you provide a workgroup code, it's strongly recommended to make a note of and keep it in a safe place. Should the the .mdw file be lost, you will need this information to create the "same" .mdw file.
5. create a User with your logon name and put this user to the group called 'Admins';
- to create a new user go to Tools -> Security -> User and Group accounts... Click <New...> on the Users tab. Type a logon name, and a PID.
- Note: users are identified by their PID, not by their logon name. Anytime you create a user, it's strongly recommended to make a note of the PID and keep it in a safe place. Should the the .mdw file be lost, you will need the PIDs to create the same users in a new .mdw file.
- It's enough to create a user account for yourself. You can create the other users' after the whole process.
- Don't forget to put yourself into the 'Admins' group.
6. Create a password for the default Admin user (which you are at the moment).
- To enforce users to provide a logon name and a password, you need to set a password for the default Admin user. If the default Admin user has a blank password (in other words: no password set for it), then Access automatically starts with the default Admin user, and does not require user authentication.
- To create a password for the default Admin user, go to Tools -> Security -> User and Group accounts... and select the Change Password tab. Leave the Current Password blank, then type & confirm the new password.
- The default Admin user does not need a difficult password, because this user will not have any permission on the secured database at the end. We need the password only to enforce user authentication.
7. Close the database.
8. CREATE an OTHER new blank database.
- If a password has been set for the default Admin user, it will prompt for a password.
- Delete the "Admin" and type the user name you just created. NO PASSWORD REQUIRED for this user, so just click <OK>.
- From now on, you are logged on as the user that you've just created. Note: if you haven't put this user into the Admins group, you won't be able to continue.
- IMPORTANT NOTICE: the reason of creating an other blank database instead of just doing the whole process WITHIN the database to be secured, is that the only way to take the ownership of a 'Database' object is to create it as YOU, and not as the default Admin user. As I experineced, 'Database' ownership CANNOT BE TAKEN AWAY from the creator of the 'Database' object.
9. Go to Tools -> Security -> User and Group accounts... and REMOVE the user 'Admin' from the 'Admins' group.
- Clck Users tab, and select 'Admin' from the drop-down list. Select 'Admins' in the list box 'Member' ('Member of'), and click <Remove>.
- If you haven't added the new user to the Admins group, then you won't be able to remove the default Admin user from the Admins group because Access will not let you to have no users in the Admins group. At least one user must be in Admins group.
- If you can remove the default Admin user from the Admins group, then, from now on, you are the only user with administrative permissions for this database and in this .mdw file.
10. Set a password for yourself.
- How? See point 6.
- Memorize your password, because it can only be retrieved by using certain Password Recovery processes and softwares. As far as I know, there is NO WAY to retrieve a password via VBA or MS-Access for a normal user, it needs someone with deep hacking knowledge...
11. Import all objects from your database that you want to secure.
- Go to File -> Get external data -> Import..., and browse to your database.
- Select ALL objects: tables, queries, forms, reports, macros and modules, and click <Import>.
- If you haven't unprotect the VB Project, it WILL NOT import any VB code even behind the forms.
- From now on, the owner of the 'Database' object and all tables, queries etc. is YOU.
12. Run User Level Security wizard and make the permission settings.
- It's recommended to select all objects and grant only data modification rights to the 'Users' group.
- After running the wizard, it's recommended to restrict all rights of the default Admin user manually. This will ensure that if your database is opened with the default system.mdw file (which is automatically created when MS-Access is being installed), then the default Admin user will not have any permission to any data or object.
13. Join to the default Workgroup Information File, and close the database.
- to do this, go to Tools -> Security -> Workgroup Administrator, and click <Join...>, click <Browse...> and navigate to the default .mdw file you've hopefully made a note of.

After this process, if you just open your secured database then you will open it as the default Admin user. It will not require a password, because in the default .mdw file, the Admin user does not have a password. And, if you've restricted all permissions of the deafault Admin user, you won't be able to make any data or design modifications, and even open any objects.
The reason of it is that the default Admin user's PID is the same in all .mdw files. The default Admin user is automatically created when you create a new .mdw file. The default Admin user also CANNOT BE deleted. The default Admin user uses the same PID everywhere, so regardless of how many .mdw files you have on your system, Admin user logically is the same for all databases.

So then how to open your secured database?
You need to use the .mdw file you created. So open your database with the /wrkgrp switcher. Create a shortcut and use this in the Target field:

[drive]:[database_path][database_file] /wrkgrp [drive]:[mdw_file_path][mdw_file]

Example:

"C:MyDatabasesMyDatabase.mdb" /wrkgrp "C:MyDatabasesMyDatabase.mdw"

If you open your secured database in this way, then it will require a password (of course, because the default Admin user in your .mdw file has a password). Log on as yourself, and you now can add other users.

NOTE VERY CAREFULLY:
1. BEFORE starting the whole process ALWAYS make at least one backup copy of your unsecured database.
2. NEVER delete this backup copy UNLESS you're ABSOLUTELY SURE that everything is approved and tested and IS WORKING OK in your secured database.
3. DO NOT LET anyone to make any changes in data or design in your backup copy until you're absolutely sure that everything is working OK. Otherwise your backup copy will not be anymore a clone of your database.

Other notice:
I've been blocked out from my own databases many times while I was searching for a right way of securing. Unfortunately, HELP provides a likely poor aid.

If you encounter problems, I'll do my best to help you as my time allows me to.

I kindly ask the members that feel themselves more or less experienced to make a test and post any notes/suggestions/bugs/mistakes to this thread, PLEASE.

REMEMBER: IF YOU HAVE A BACKUP COPY YOU WON'T GET TROUBLE.