Access database NTFS permissions to add records but not modify

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!

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Lee W, MVPTechnology and Business Process AdvisorCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Brian PiercePhotographerCommented:
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.
Lee W, MVPTechnology and Business Process AdvisorCommented:
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).
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

rsclarkAuthor Commented:
So if I use Workgroup Administration, can I apply it to just one access database leaving the others untouched?
Lee W, MVPTechnology and Business Process AdvisorCommented:
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.
Stepbystep insructions are here:

Don't try to miss out steps.
Brian PiercePhotographerCommented:
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.
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.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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:

rsclarkAuthor Commented:
All very good and helpful - many thanks for all your contributions.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.