Microsoft KB Archive/304315

= ACC2000: How to Simulate Column-Level Security in Microsoft Access =

Article ID: 304315

Article Last Modified on 6/25/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q304315



Advanced: Requires expert coding, interoperability, and multiuser skills.

This article applies only to a Microsoft Access database (.mdb).



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:
 * 1) Create a table (primary table) that contains a primary key field and any fields that should have the same level of permissions.
 * 2) 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.
 * 3) 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.
 * 4) 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.
 * 5) Create a one-to-one relationship between the two tables based on the primary key field.
 * 6) 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.
 * 1) 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.

