Securing ms-access database 1

[vbtest5]

Hi,

I have an app with VB as front-end and MS-Access as back-end. Now when I would deploy the app, I do not want anyone to open the database using Ms-access and view the table structure. So I decided to password-protect the database. But if the database is password-protected and I try to give user-id and password using VB's connection string, it gives me error saying either the database is opened in exclusive mode or use workgroup file.

I have following questions:
1. Is using system workgroup file the only way to secure my database?
2. If I create this file, I think I also need to distribute it. Am I correct?
3. If I distribute it and the client already has system workgroup file for some other app, then my file will overwrite the client's existing file. Any solution for this?
4. If I distribute my app using Installshield, can I just deploy the system workgroup file or does it need manual configuration?

Please advise.

Regards,
Vilas

 

[mrdenny]

Vilas,
I'd recommend not trying to secure the database. You are selling the application to the client. They are putting their data into the application and database. They have every right to view the data directly in the database, and query it as they desire. This goes for access, MS SQL, MSDE, Oracle, etc as well.

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[oradba101]

I believe he was referring to securing the database from outside of the app. For example, he wants users to only be able to view data from within his app (for auditing purposes?) and not be able to view it using Access. Am I correct?

 

[willir]

Well actually, I disagree. Security is an issue.
- Protect your intellectual property. You are selling an application. Some one could take your code and use it for themselves.
- Protect the integrity and confidentiality of the data. Only authorized people should be able to access the data.

Many organizations will implement some form of security for an application, especially if the data is important.

1. Is using system workgroup file the only way to secure my database?

No
- System security (Active Directory or Novell, etc)
- Group and user security
- MDW / MDE

You may wish to review
Gauging your security needs; alternatives to Access/JET security faq181-3893
Security Explaining MDW files faq181-3950
Security More Help for Setting up Access Security faq181-1131

2. If I create this file, I think I also need to distribute it. Am I correct?

Of course.

3. If I distribute it and the client already has system workgroup file for some other app, then my file will overwrite the client's existing file. Any solution for this?

Backup your database.

Create another workgroup. From the menu, "Tools" -> "Security" -> "Workgroup Administrator"

Use a short cut that points to the database and uses the workgroup option.

Use server \ share
"\AppPathTo\msaccess.exe" "\\ServerName\ShareName\YourDB.mdb" /wrkgrp "\\ServerName\ShareName\YourDB.mdw"

or Drive mapping
"\AppPathTo\msaccess.exe" "ServerDrive:\DirName\YourDB.mdb" /wrkgrp "ServerDrive\DirName\YourDB.mdw"

4. If I distribute my app using Installshield, can I just deploy the system workgroup file or does it need manual configuration?

I don't think it is a good idea to over-write the SYSTEM.mdw. You may cause chaos with your customers.

But you can create a script to automate the process.

I do not want anyone to open the database using Ms-access and view the table structure

Presumably, the front-end database is converted to an MDE. Although the design on the backend is important, the coding and design of the forms and reports is where the "smarts" are located. Converting to an MDE will protect your coding, etc.

There are a few tricks you can use such as hiding your tables.

either the database is opened in exclusive mode or use workgroup file

Remember to set the set the database to open in "Shared" mode (from the menu, "Tools" -> "Options" -> "Advanced" tab.)

Now for some food for thought...
Access is not meant to be a full featured secure database like Oracle or MS*SQL or Informix. Regardless of what you do, there will be some who can figure out a way around it.

Richard

 

[vbtest5]

Hi,

I agree to oradba101 (MIS). I want that users should view the data using my App only which is written in VB6. Users should not be able to open the database using MS-Access.

Regards,
Vilas