Setting Security for Two Access 2003 databases (front-end and back-end) RRS feed

  • Question

  • Looks like MS Access 2003 security has a lot of workarounds and issues. For example, I have two databases (a front-end and a back-end linked to the front-end). I wanted to secure both of them. For example setting password (this is database password and not .mdw) to back-end (BE) blocks the starting up of front-end (FE) database. Joining .mdw in both the databases (FE and BE) also doesn’t work which means it causes other databases to also prompt for credentials. So a combination of setting password to front-end and joining back-end to secure .mdw has worked. Now the databases are secure and can be opened by credentials (.mdw creds for back-end and .mdw creds + database password for front-end). Does someone have an idea why this happens?
    Wednesday, January 5, 2011 12:11 PM


  • Hello Prasad.

    Setting a database password in't a best practice for securing a database. Although you could protect the backend database using a password, this password will be stored in the frontend database and can be read by sophisticated users. If the backend tables are already linked in the frontend when specifying a password for the backend database, one must delete all linked tables from the frontend and re-link them (specifying the password).
    User-level security is a complex topic and I will not explain all of it in detail, here you will find more about it: http://support.microsoft.com/kb/165009.

    Simply "joining" a workgroup using the Access menu commands does not have an effect on the database opened at all. Specifying a workgroup here means telling Access to open that other mdw file (instead of the default system.mdw from your user profile) when starting Access in the future. Configuring security in the database right now will be quite useless and will not secure your database. To see that I am right, start Access and join the default workgroup again and re-open the backend database. This should not be possible if your database was secure.
    The reason why "other databases prompt for credentials" is that you set a password for the Admin user in the workgroup file that  Access uses when starting (your new workgroup). When the Admin user in the workgroup used has a password, the user is prompted (and if not, the user is automatically logged on as user Admin). To solve this issue, re-join the default workgroup. This should always be done after creating a new workgroup file. To tell Access to use your new workgroup when opening your database, use a shortcut to Access that contains command line parameters: "DatabasePathAndFile" /wrkgrp "WorkgroupPathAndFile"
    Users and groups are stored in the mdw file, permissions are stored in the database.

    Here are the essential steps for securing an unsecured database.

    1. Create and join a new workgroup file using the Workgroup Administrator.
    2. Close and restart Access.
    3. Create users and groups in the new workgroup, configure group membership. Don't forget to add a user account for you that belongs to the Adminis group. Afterwards, remove the Admin user from the Admins group.
    4. Define a password for the Admin user (you are logged on as Admin)
    5. Close and restart Acces.
    6. Create a new blank database and log on using your username when promted to (no password yet).
    7. Define your password, if you wnat to (and you definitely should).
    8. If you want, specify the default permissions for new objects in your database.
    9. Import all objects from the unsecured database to the new database (including custom menues and import/export specifications, if present).
    10. Configure/verify permissions. The Users group and the Admin user should have no persmissions at all, don't forget to select the object "Database" in the permissions dialog.
    11. Create a shortcut that starts Access, opens your database and uses the new workgroup file
    12. Re-join the default system.mdw file to make Access use that one by default again.

    In steps 1 and 3, don't forget to write down (or take screenshots of) the workgroup information shown and the username/groupname and PID. This information must be available if case that your workgroup file has to be re-created for whatever reason. (The PID is only displayd while entering it. After creating the user/group, it can never be displayed again.)

    Following these steps for frontend and backend databases will make them secure.

    Kind regards,

    • Proposed as answer by Bruce Song Monday, January 10, 2011 9:11 AM
    • Marked as answer by Bruce Song Thursday, January 13, 2011 2:51 AM
    Thursday, January 6, 2011 10:17 AM