Securing a Front-End/Back-End Application

There are additional factors to take into account when establishing user-level security for an application that has been split into a back-end database containing only tables, and a front-end database containing the remaining objects and links to the tables in the back-end database. You may not know the name of the specific network location for the back-end database in advance, or you may want to allow a database administrator to move the back-end database. In either situation, users must be able to relink to the tables in the back-end database.

Û To establish user-level security for a front-end/back-end application

1. For both databases, follow the procedures described earlier in this section for establishing security with the User-Level Security Wizard.
   
   
2. Assign permissions to the appropriate groups to read, update, insert, or delete data in the back-end database. To do so, either assign permissions directly for the tables in the back-end database, or remove all permissions for those tables and create queries in the front-end database that have the RunPermissions property set to Owner’s, and that use the tables in the back-end database.

   See Also For more information about the RunPermissions property, see “Using the Microsoft Access RunPermissions Property with User-Level Security” later in this chapter.

3. Assign Open/Run permission for the Database object in the back-end database to all groups you worked with in Step 2. This is required even if you are using queries with the RunPermissions property set to Owner’s.
   
   
4. In the front-end database, grant users Modify Design permission for the linked tables. Granting users Modify Design permission on linked tables in the front-end database doesn’t grant them the same rights to the tables in the back-end database.

   By default, after you run the User-Level Security Wizard, your database users have permission to create new tables and queries in the front-end database. If Database Access Objects (DAO) code has been used to remove this permission, you must use DAO code and the dbSecCreate constant to reassign this permission.

   See Also For more information about using DAO code to assign permissions, see “Securing Objects” later in this chapter.

5. When the users first install your application, have them run the Linked Table Manager (Tools menu, Add-Ins submenu) from the front-end database to refresh the links to the tables in the back-end database in its new location.

   Tip You can also write code that prompts users during startup to refresh table links. For sample code that does this, see the RelinkTables and RefreshLinks functions in the RefreshTableLinks module of the Orders sample application, which is included with Microsoft Access.

Because users have Modify Design permission for the linked tables in the front-end database, they can reset the links to the back-end tables if the location of the back-end database changes. They can’t make any modifications to the design of the actual tables in the back-end database.