How can I give permissions for some users to "update" an Access 2002 database (which is located in a folder on the Server), while limiting others to "read" only?

I have a Microsoft Access 2002 database located on the Server:  C:\AA\Vault\clients.mdb

Currently, all users have full access  (add / change / delete / read / write / modify) to C:\AA\Vault\clients.mdb

Our new office policy wants to limit access as follows:

     2 users (A and K) should be allowed to update (add / change / delete / read / write / modify) database records
     3 users (C, J and S) should "only" be allowed to "read/search" database records

How would I go about setting permissions, to enforce the new policy?

Two thoughts come to mind:

       Create two separate folders, one for updates and one for read only.  Then have a scheduled task copy clients.mdb from the "updates" folder to the "read" only folder.

       or

       Create two database access groups in Active Directory, assign the 2 users to Update_Group, and assigned the 3 users to a "read_only group", then assign those permissions to the database:  C:\AA\Vault\clients.mdb

However, I would prefer to check with you experts, who are more knowledgeable than myself.

Thanks for your help.
eemmpphAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

McKnifeCommented:
Did you try the most simple way, just give read permissions to the file for user A and write permissions for user B while removing read access to anybody else (but admins)?
eemmpphAuthor Commented:
Wow McKnife, thanks for the quick response.  I will try right now.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
You can't use a Access (JET) db in a multi-user situation without giving everyone full rights to the directory where the DB resides.

 Permissions like your trying to apply must be built into the app or since your using an older version, you could use ULS (User Level Security or "workgroup" security).

 However I would build it into the app.

Jim.

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
Cloud as a Security Delivery Platform for MSSPs

Every Managed Security Service Provider (MSSP) needs a platform to deliver effective and efficient security-as-a-service to their customers. Scale, elasticity and profitability are a few of the many features that a Cloud platform offers. View our on-demand webinar to learn more!

eemmpphAuthor Commented:
To McKnife, while that solution did work, it brought up additional issues:  For example, it said this to the "read only" user:  "This database is read only, to make design changes, save a copy of the database.  Save-As ..."  This kind of defeats the purpose for securing the original database file.

To Jim Dettman, I believe your solution would work for me, by allowing the app to control "access" to the database.
eemmpphAuthor Commented:
Thank you.  I appreciate your help in finding the solution which would work best for me.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
To help with that, you would use code in the app (similar to the code below) o determine the user.

Then based on that, you might enable/disable various menu options, or in opening a form, set AllowEdits to False for example.

Jim.

Private Declare Function GetComputerNameA Lib "kernel32" (ByVal lpBuffer As String, nSize As Long) As Long
Private Declare Function GetUserNameA Lib "advapi32.dll" (ByVal lpBuffer As String, nSize As Long) As Long


Public Function WhoAmI(bReturnUserName As Boolean) As String

        ' Function returns either user name or computer name

        Dim strName As String * 255

10      If bReturnUserName = True Then
20        GetUserNameA strName, Len(strName)
30      Else
40        GetComputerNameA strName, Len(strName)
50      End If

60      WhoAmI = left$(strName, InStr(strName, vbNullChar) - 1)

End Function

Open in new window

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
Databases

From novice to tech pro — start learning today.