databasedev.co.uk - database solutions and downloads for microsoft access

Microsoft Access Database Security - Security Permissions

Permissions

After you run the Security Wizard, you can manually change database and object permissions for user and group accounts in a workgroup. It's advisable to assign permissions to groups, not to users, because each user inherits the permissions assigned to the group.

Permission Types

Each user has access to nine types of permission for data or objects in a database. The following table describes the nine types of permission, and what each type enables a user to do. To read more about these permissions, search Microsoft Access Help for permissions, display the topic Work With Permissions, and select Types Of Permissions.

Permission Allows a user to Objects involved
Open/Run Open a database, form, or report. Run a macro. Databases, forms, reports, and macros.
Open Exclusive Open a database on a network, while ensuring that others cannot open the database while the first user has it open. Tables, queries, forms, reports, macros, and modules.
Read Design View the design of objects. No changes to the design are allowed. Tables, queries, forms, reports, macros, and modules.
Modify Design Change the design of objects and delete objects. Tables, queries, forms, reports, macros, and modules.
Administer Set database passwords, replicate databases, and change startup properties. Have full access to objects and data, and assign permissions for objects. Databases, tables, queries, forms, reports, macros, and modules.
Read Data View data, but not table designs or query designs. Tables and queries.
Update Data View and edit data, but not insert or delete data. Tables and queries.
Insert Data View and insert data, but not change or delete data. Tables and queries.
Delete Data View and delete data, but not change or insert data. Tables and queries.

Before starting the following exercise you should have already completed the previous tutorials within this security section; details of each can be found at the bottom of this page.

Task A-7: Granting permissions to a database and its objects

  1. Choose Tools, Security, User And Group Permissions to display the User And Group Permissions dialog box.
  2. Select the Change Owner tab. Select different object types and notice that you are the current owner of the database and all its objects

    Showing the current owner of the database and all the objects
  3. Select the Permissions tab. Let's take a look at the permissions assigned to the users and groups, starting with the groups.
  4. From the List options, select Groups. The Admins group is selected in the User/Group Name list.

    Listing the User/Group Permission names.
  5. Explore the permissions assigned to various object types and group names. Notice that the Order Entry, Sales Managers and Users groups have no permissions assigned to them. For the Users group, you want to leave it set that way.
  6. From the User/Group Name list, select Order Entry. You want to assign permissions to this group.
  7. From the Object Type drop-down list, select Database. In the Permissions section, check Open/Run

    Setting the Order Entry group to have permissions to Open/Run the current database.
  8. Click Apply. All users in the Order Entry group have permission to open and run the current database.
  9. Now let's set the Order Entry group's permissions for the table objects in the current database. From the Object Type drop-down list, select Table. From the Object Name list, select all the table names.
  10. In the Permissions section, check Update Data and Insert Data. Uncheck Delete Data. Notice that the options Read Design and Read Data are also checked by default with these options. Three options should be unchecked: Modify Design, Administer, and Delete Data.

    The Permissions granted to the Order Entry group on the Tables objects.
  11. Click Apply.
  12. Apply database and object permissions to groups according to the specifications in the following table (select all object names for each object type):

    Group Object Type Permissions
    Order Entry Query Read Design, Read Data, Update Data, Insert Data, Delete Data - all Queries
    Form Open/Run, Read Design - all Forms
    Report Open/Run, Read Design - all Reports
    Macro Open/Run - all Macros
     
    Sales Managers Database Open/Run
    Table Read Data, Read Design - all Tables
    Query Read Data, Read Design - all Queries
    Form Open/Run - all Forms
    Report Open/Run - all Reports
    Macro Open/Run - all Macros
  13. Click OK to close the dialog box.

Now that you have assigned your security for the groups and users, you will want to Test Your Microsoft Access Security.

Go to page:

  1. Steps to Securing an Access Database by Using User-level Security
  2. Setting Logon Procedures
  3. Group Accounts
  4. User Accounts
  5. Changing a Password
  6. The Security Wizard
  7. Permissions
  8. Testing Security
  9. Documenting Database Security
  10. Previewing Permissions
  11. Securing a Database with a Database Password
  12. Distributing the Secured Application

Recommended Reading:

If you are serious about your Microsoft Access security, then you should check out Garry Robinson's book Real World Microsoft Access Database Protection and Security

About the book:

Real World Microsoft 
            Access Database Protection and Security

Microsoft Access is the most popular desktop database in the world today and its very popularity means that its security measures can be easily compromised. Real World Microsoft Access Database Protection and Security takes a different approach than all the other Access books in the market by focusing from the start on all the issues that will help protect your database. It approaches protection and security from a task-by-task perspective and provides details that when put together will make your database more secure.

This book will help you to keep your staff from looking at your salary tables, stop your customers from looking at the design of your software that you distribute, and help you decide which security options are worth doing and which are generally a waste of your time.

Garry writes from a very experienced developer's point of view and he discusses in detail how to program all types of security issues including hiding tables as system tables, producing databases that password cracker software cannot easily crack, backing-up databases, menus, queries, and even user surveillance.