table security - direct user access & linked access via forms

[arpeggione]

If I put my tables in a "backend" database on the network - and secure the directory (Active Directory), I understand that users won't be able to directly open that directory.

However, I am wondering - would they still be able to open the tables via an Access front end (given that I allowed them to using coding filters based on user group)?

thanks for any wisdom anyone can offer.

Karen

 

[lameid]

In a word no.

The windows accounts need read permission to the data file to see the data. This is separate than built in Access security.

Additionally, users need permission to change the folder so that when the locking file is created, Access can managae locking for the user otherwise whichever type of user opens first (read only / modify) will block the other out with an error... I think File already in use. The Catch-22 is in Windows when you create a new file, it inherents permissions from the folder and everytime you compact and repair you technically create a new file and delete the original, thereby changing in file specific permissions.

 

[arpeggione]

Thank you....I thought as much, but just wanted to check....regards, Karen

 

[arpeggione]

Well...let me pose an additional question - same type of subject. What I've settled on for protecting the tables is having the program default to open to a form. The button on the form will check the user and let them in or not - depending on who it is. In addition (after making a copy) I'll disable the shift key - so only those that know the password can actually get to the tables.

How does this sound to you? Could you think of any immediate holes? I want to make my tables secure, but not TOO secure. SQL Server is too much for this small application.

thanks again for your time and thoughts,

Karen

 

[lameid]

It all depends on if you are just trying to prevent mistakes or malicious people with knowledge out.

On the latter extreme you should not use Access for your data.

On the former extreme that is probably enough.

However there is a world of in between.

While I don't have any cracking tools, I could still in short order make a front end I can get to the objects under your model simply by importing the objects.

To protect your tables from edits from those that shouldn't... the best I know to do is to use workgroup security that was used through at least Access 2003 (still behind the times).

Rather than using your application type security, you would use workgroup files....

A model I came up with before and used was to create a Master workgroup file containing my developer user (modify permissions to design) and a user for each real world group I wanted to use documenting the PID.

Then for each group I created an additional workgroup file with only the additional PID for a particular group. I then put that workgroup folder in a folder with permissions set so only the appropriate group of users has access to it.

Then I would have each group of users start access with command line switches to open the specific workgroup file with the appropriate user (/workgroup /user). Note there is a password switch but is not necessary if the password is blank.

Lastly I set the permissions I wanted using my master workgroup file. The key thing to know is that the PID's control access and the workgroup file only has authentication that can change. Also the Users group and Admin user are common to all workgroup files so if you give either of those permissions, that is the permission that anyone not using a specific workgroup witll have (I've used this by design as well). The weaknesses in this model is that workgroup security can be broken (or so I've seen rumored), anyone that you let in can by definition delete the file and anyone that gains access to a user's session can copy the workgroup file. Because the password is stored in the workgroup file, if you use them, all I have to do is have get a copy of the workgroup when I know the password. Your later password changes won't do anything because I have a user/password combination that lets me use the PID and get to the data.

At least with the workgroup file scenario, you can keep the default user from changing objects... If you decide to go down that path, also check out Run with owner permission queries RWOP. I didn't use them under the model described above... I'm guessing they would still work under this model (the owner PID not in the workgroup file).

I hope that helps.

 

[arpeggione]

lamied....yes...this is really helpful. Thanks so much for your in-depth reply....regards, karen