ACC2000: How to Simulate Column-Level Security in Microsoft Access (304315)

SUMMARY

The Microsoft Jet database engine does not provide column-level security, as does Microsoft SQL Server, where you can assign permissions at the column level to a user or group account. For example, you may want some users/groups to have read-write permissions to some columns, whereas other users/groups have read-only permissions to the same columns. This article demonstrates how you can simulate column-level security in Microsoft Access 2000. This article assumes that you have a thorough understanding of how to create queries and relationships, and a thorough understanding of the pieces that make up the Microsoft Access security model: workgroups, accounts, ownership, and permissions.

MORE INFORMATION

You can simulate column-level security in Microsoft Access by following these steps:

Create a table (primary table) that contains a primary key field and any fields that should have the same level of permissions.
Set security on this table so that all users/groups have the same level of permissions (for example, read-write) on this table. Because you are setting permissions at the table level, all fields in the primary table will have the same level of permissions for your user/group accounts.
Create a separate table (secondary table) that will also contain the primary key field, and any fields where security settings will differ for your user/group accounts.
Set security on this table so that your user/group accounts will contain the same level of permissions on this table. The permissions should differ from those granted in the primary table and should be restricted (for example, read-only). Because of your security settings, all fields in the secondary table will have the same level of permissions that differ from the level of permissions granted to your users/groups for the primary table.
Create a one-to-one relationship between the two tables based on the primary key field.
Create a query that joins the two tables and selects the fields from both tables. This allows you to have all necessary data; however, the column permissions for your user/group accounts will differ, depending on the permissions that user/group is granted to the table from which the field is selected.

For example, if the fields in your primary table contain read-write permissions for your user/group accounts and the fields in your secondary table contain read-only permissions and you create a query containing these fields, you will see all data; however, you will not be able to edit/update the fields from the secondary table. Any fields selected from the secondary table will be read-only.

NOTE: If you have a field in the secondary table with read-only permissions but this same field is in the primary table and has read-write permissions, and you want to assign the read-only permissions, in your query, make sure to select the field from the secondary table.
You can use the query as the record source for a form. When using the query, you can retrieve all data; however some columns will be read-only for your users.

REFERENCES

For more information about how to secure a database in Microsoft Access, click Microsoft Access Help on the Help menu, type secure a Microsoft Access database in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.

For more information about setting user-level security, click Microsoft Access Help on the Help menu, type secure a database using the user-level security wizard in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.

For more information about securing a Microsoft Access database file, see the following article in the Microsoft Knowledge Base:

207793 ACC2000: Access Security FAQ Available in Download Center

254372 ACC2000: Overview of How to Secure a Microsoft Access Database