FE / BE and Security

[MajP]

I have built split databases (front-end / back-end), and have limited knowledge on implementing security, but have never dealt with security issues on split databases. Currently the user has a database (Access 2k) that is on a network drive in a folder that no one else has permission to access. They want to open it up so that other users can have limited use to information in a few tables. Some tables/fields the general user is not allowed to see, some he can read only, and others he can have full edit priveledges. There will probably be 20 users, and 5 Administrators. I have split the database, and plan to make seperate Administer and General User front ends and put the front ends on individual machines. In the past I have done some very limited security using a combination of .mde files, user groups and permissions, pop-up forms, hiding the data base window, hiding menus, etc; but I do not how to tie this together. I am hoping that I can get some general strategy or references to accomplish the following or answer the following:

1) I would like the General Users to only be able to link to certain tables. If the link needs to be restored they can only restore these certain tables. There are things in certain tables that I do not want them to see.
2) Of the tables that they can link, they can only view data in certain tables and will have add/edit/delete in others.
3) Users can not delete or copy the back end.
4) Not everyone has the network drive mapped the same way. Some people may for example map it as "Q:\Drive Name" while other may have it as "S:\ Drive Name". How do I distribute the front end and manage the links in this case? I have read to use the UNC path name instead of the physical name, but I do not know how to specify the UNC.
5) I would like the features of an .mde front end, but I could not secure an .mde. Do you secure a .mdb then turn it in to a secure .mde?

I hope this makes sense. Any help to point me in the right direction is appreciated.

 

[lupins46]

With workgroup security you can control access to each object in your application, so as long as you have your two groups clearly defined youcan allocate the relevantpermissions to those groups and then add users to one or both of the groups. I don't think it is worth getting into which tables they can link to. That should be taken care of by which forms etc they can use.

Switching to UNC is about the route you follow to the backend from the Linked Tables MAnager - if you go via computer names/share names rather than locally mapped drives you will create the UNC path.

I don't believe you will find a way to prevent someone copying or deleting the backend because every user must have full permissions on that folder. You can make it difficult by hiding things but AFAIK that is it.

References:
Best introduction to security:

http://www.geocities.com/jacksonmacd/AJMAccessSecurity.pdf

MS_official docs:

http://support.microsoft.com/default.aspx?scid=/support/access/content/secfaq.asp

The Book:
'Real World Microsoft Access Database Protection and Security' Garry Robinson. APress.

 

[LittleSmudge]

the important thing to be aware of when using MSAccess user/group security ( that the help files don't seem to make explicit ) is that :-

The User Names, Group Names and which user is in which group is held in the .MDW file

The PERMISSIONS that a particular user or Group has with regard to any object in Access is held IN THE DATABASE CONTAINING THE OBJECT.


So, therefore you can create an Admnin FE and allow only people who are members of the Administration Group to open those forms
and you can create a JoeBloggs FE for the great-unwashed to use.

You can THEN open up the BE and on a table by table basis you can assign Read, Update, Add, Delete permissions to all tables for members of the Administrators group and you can add or take away permissions for members of the JoeBloggs group so that they cannot even read from your more sensitive tables.

You must also remember to take away ALL permisions for the Admins user and strctly limit the powers of members of the Admin group otherwise anyone can come along with the default System.mdw and get in the back door.



'ope-that-'elps.



G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[MajP]

Lupins46 and LittleSmudge,
Thanks for the info. The FAQs sheet on the MS site was excellent. I think I got it except a clean way to handle the new .mdw file. I am guessing that you put the .mdw file on the network (maybe in the same folder as the BE). Then you put on the local machines both the FE and a shortcut. The user must use the shortcut since it opens the front end using the specified .mdw as a startup parameter. The problem I see doing that is the user moves the FE the shortcut will no longer be any good. Is there a better way?

 

[LittleSmudge]

IF the user moves the FE then you take them outside and rub them down with a house brick.


I find they don't do it twice !




G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.