Implementing Access application Security

[StuMunro]

I am just finishing off an application in Access 97. The application is split into a backend for the tables and the front end for the queries and forms etc with linked tables to the backend.

I want protect the application so ALL of the following are met.

1) No access to the table data expect through front end forms/reports. i.e. the data format is private to the application. all standard access menus/options have been removed from the users display.

2) No passwords to open the front end database, as if the user knows the open password then they can import/link the tables into another db using the password.

Can someone explain how to achieve this?

I have tried a Database password on the backend, and linking using this password from the front end, but all you have to do to get round this is to link to the tables in the front end in a new database.

Stu.

 

[scking]

You should have one single .mdw file set up with your usernames and passwords. Then set the permissions on the front end links so everyone had the modify permission. Then set the permissions on the back end with appropriate permissions for data access.

A most critical step is handling the default 'Admin' username which is used as the default username for all access to the database. Microsoft says to make it secure you need to remove all permissions for the 'User' group which is inherited by all users and build another group, maybe 'MyUsers' that has the permissions which would normally be assigned to the Users group. Each user would then have the 'MyUser' group permissions assigned.

For the most comprehensive discussion of Access security I have found, and the basis for this note, see:

http://support.microsoft.com/support/Access/Content/SECFAQ.asp

Steve King Growth follows a healthy professional curiosity

 

[Terpsfan]

Please note that Access security presents a possible significant bottleneck to your application. I have heard from other developers that once you get over 10 users, the system.mdw file tends to get corrupted. I personally don't use Access security - I implement my own. I create my user and password table and force everyone to log into the database with a USERID and password. I don't find Access security to be that useful, because what's to prevent a user from locating the file in Explorer and deleting it? But you can implement a lot of security by simply making the Database Window invisible to the user, which you have already done. I also find creating the front end as an .mde file is a further security measure. It prevents users from seeing any code and doesn't allow users to make any changes to forms and reports. But if you block out the Database Window they can't see that stuff anyway.

 

[scking]

From experience with a database we fielded for a multi-user environment and have been using succesfully for five+ years, we don't have problems with a corrupted .mdw file. If a user is really concerned about security, I believe a personally developed security system for anyone other than an expert to pose more problems and possible holes than are acceptable. Yes, if you use an .mde file on the front end you can prevent user's from seeing the design, and in this case may be adequate. I would recommend to avoid user security by other means if possible, but not necessarily personal security systems. Other issues associated with this discussion thread are:

1) No passwords to open the front end database, as if the user knows the open password then they can import/link the tables into another db using the password.

Note: this is not necessarily true. Merely removing the 'modify design' permission to any linked tables on the front end will prevent the user from linking to other data. They do not have the permissions needed to modify the connections property of the linked table.

2) What's to prevent a user from locating a file in Explorer and deleting it.

Note: Since we are talking multi-user it's safe to assume a network with network permissions assigned by an Administrator. There are a number of different mechanisms which could be employed to help with this issue. Some effective and some only slightly so. I would set up permissions for a group on the network to allow read and modify but not delete. If someone were to maliciously, or otherwise, try to delete the file they would need to be a member of the group that could read/modify to even see it, but then they couldn't delete. Of course any system administrator would be performing regular backups which could be recovered if someone were to delete. Others include (to prevent inadvertant deletion only):
1) Hide the file
2) Set the readonly property of the file

Steve King


Growth follows a healthy professional curiosity

 

[StuMunro]

Re: point number 1.

I dont think I explained myself clear enough here.

I basically want my application to have similar security to that of a VB/C++ program which uses a password protected database to store the data. All access to the database would use a hardcoded password in the VB application. There would be no password to run the VB app as there does not need to be one.

This means the user(customer or competitor running a demo version ) cannot alter the raw data directly or 'Steal' my table design my viewing the design. If they want to extract the data for some other perpose then they will have to pay me to provide this function.

Having a front end access application seems to leave all sorts of ways of viewing the tables if the customer has a full copy of Access available. ie he could create a new database and link to the linked tables in the front end which gives direct access to the back end tables.

Please help if you can, as I have spent hours experimenting with the security options.

stu.