Access database NTFS permissions to add records but not modify

Posted on 2010-03-26
Medium Priority
Last Modified: 2012-06-27
Don't know if this is possible, but here is what I am after.

One of my offices runs a number of Access databases. All staff have full permissions on them.  These work OK.  

The managers want to introduce a new application that they want ordinary staff to have permissions to add records to, but not to modify existing records.  Managers should have permission to change reocrds. Is this possible with NTFS permissions with two different security groups?  

Reluctant to start using WorkGroup Security as I have found it a pain in the neck!

Question by:rsclark
  • 3
  • 2
  • 2
  • +3
LVL 97

Accepted Solution

Lee W, MVP earned 100 total points
ID: 28661860
Nope, NTFS permissions will not work for this.  They are applicable to the file only. Adding a record is changing the file.  Modifying a record is changing a file - there is no difference as far as NTFS is concerned.  

I wouldn't be using Access, I'd be using SQL/SQL Express.  As for workgroup administrator - that's your best bet... I've worked BRIEFLY with a couple of databases that use it... but I try to avoid Access whenever possible.
LVL 70

Expert Comment

ID: 28662524
I agree on the first bit with LeeW, you can't use NTFS for this, because of the way that Access works then all users have to have R/W permissions.

However, It is possible to use the security within access to do what you want, I have written a number of access databasea that do just that. Security in access isn't really that bad - once you are used to it.
LVL 97

Expert Comment

by:Lee W, MVP
ID: 28662697
I never said it wasn't possible with workgroup administrator...  just that I avoid access whenever possible and would recommend using SQL instead (especially with SQL Express free and the SQL Management Studio available to manage it).
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.


Author Comment

ID: 28669827
So if I use Workgroup Administration, can I apply it to just one access database leaving the others untouched?
LVL 97

Expert Comment

by:Lee W, MVP
ID: 28670763
Yes - I know that's the case - but I haven't done it it... probably a different question... the original question is a flat out no, not possible.
LVL 77

Assisted Solution

peter57r earned 100 total points
ID: 28673390
Stepbystep insructions are here:


Don't try to miss out steps.
LVL 70

Assisted Solution

KCTS earned 100 total points
ID: 28673948
Depending on your security requirements and alternative may be to:

Use the Database Splitter to split the database into 2 parts the fontend and backend database, this will have all the data in the backend and the forms, reports, queries etc in the front end.

You can then make a copy of the frontend and modify the forms to make them read-only

All you then have to do is give access to one version of the fontend to some users and access to the other frontend to others.

All users will then see and use the same data (frm the backend), but only those with the correct forms in the frontend can modify the data.

Assisted Solution

OxonDev earned 100 total points
ID: 28675768
The short answer is yes, but only if you create and use a custom workgroup (mdw) file in a specific way.
1. Create your custom workgroup file, say custom.mdw, using the workgroup adminstrator.  Make sure that custom.mdw is on a network drive folder that all users have read/write permissions to.
2. Ignoring default Admins & Users group accounts, create groups which define your security roles such as "Managers" & "Ordinary Staff".  
3. Create user accounts and add them to the appropriate group.  Create an admininstrator user account, say 'CustomAdmin', which will have full permissions to all database objects and add that to the 'Admins' & 'Managers' group accounts.
4. Set the password for the default Admin account.  
5. Restart Access and logon using the CustomAdmin account leaving the password textbox blank.
6. Give CustomAdmin a password
7. Repeat 5 & 6 with all other user accounts if you want to change passwords

Once you've done this you can recreate your Access mdb, let's call that file secured.mdb, and assign permissions for all  database objects.
1. Allow CustomAdmin full access to all database objects.  This is the only user account that has specific permissions.  All other users should inherit their permissions from the group account(s) they belong to.
2. Remove permissions all database objects for default 'Admins' & 'Users' group accounts.
3. Assign specific permissions to all database objects to the 'Managers' group account based on the security role.
4. Repeat this for 'Ordinary Staff' group account.

Once you've done this use the workgroup adminstrator to rejoin the default workgroup file, system.mdw.  This step means that all Access mdb files are opened using system.mdw so unsecured mdb files won't be forced to open using the custom.mdw file on the development machine.  All other client machines can be left alone unless you joined a custom workgroup file.

Now we have a secured mdb file, secured.mdb, which can't be opened using the default workgroup information file, system.mdw.  So how do you open the secured.mdb file.  Well by shortcuts that use command line switches in the target line like this:
"C:\Programme Files\Office\Access.exe" "C:\secured.mdb" /wrkgrp "[Network drive Folder]\custom.mdw"

The downside to using jet security like this is maintenance:
1. Desktop shortcuts can be deleted although scripts could recreate them if they've gone.
2. Shortcuts in MOSS come up with warning prompt when you click on them (because an executable is inlcuded in the target line)
3. Workgroup user accounts are independant of active directory adding another task to Joiners and Leavers policies.  The same applies to resetting passwords.

If Jet security seems too much like hard work, you coould use a SQL Server backend, & assign table permissions to AD users in SQL Server.  You can use link tables in the Access front-end and the default interfaces will react to user persmissions accordingly, so with no logon prompt it's seemless from the users point of view.
LVL 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 100 total points
ID: 28677161
I would STRONGLY encourage you to follow the advice at Joan Wilde's site regarding implementing security. Using any other methods generally miss a step. Note also that ULS has been removed in newer versions of Access, but you can run your .mdb versions in 07 and 2010 and ULS will be in place.

I would second the suggestion by others to use SSE and manage permissions through the server. This is generally a much more secure scenario, and is fairly easy to setup.

The steps outlined above miss a few key points:

1) You never removed the Admin user from the Admin account. This means that ANY workgroup file can be using to open the account, since the Admin user is the same in all Workgroup files.

2) You cannot permanently remove permissions from the Admins group. If you do, any member of that group can simply reactivate those permissions. See #1 for why this is bad.

3) You failed to create a new database while logged in as the new "owner". Therefore, whoever created the current database (probably Admin) could take over that database and do what they wish with it, regardless of the workgroup file used.

Frank Rice wrote a good piced on securing Access using several methods:


Author Closing Comment

ID: 31707434
All very good and helpful - many thanks for all your contributions.

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Implementing simple internal controls in the Microsoft Access application.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

601 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question