Security. Prevent table data access via Excel & other external sources 2

[Ranvier]

I have created a runtime version of Microsoft Access 2000 for distribution. I have denied access to database viewer and locked down my application. However if I go via Excel (for example) and run a Database Query and connect to my database I can access the tables and look at the data. I need to prevent end users from seeing the data and the tables. Can I do this without using a wrkgroup security file, as I don’t want users to enter passwords every time they open the database but I don’t want them to view some of the tables via a ‘back door’.

Thanks in advance for any help.

 

[Hap007]

Dear Carva,

Well, access mdb files are intended to be used in a friendly environment. So it is tough to secure code and even tougher to secure the data. You can do things to tighten up access to the data, but a knowledgeable, power user can still possibly break that. Even a password protected mdb can be opened with the after market utilities.

Still doing some general things will keep novice users out.

1) It sounds like you have your program code, Frontend (forms, reports etc) in the same mdb as your data (BackEnd).
So, I would split the mdb into a FrontEnd and Backend
2) You can then password protect the Backend data, and Link to those tables in the frontend. The Frontend will remember the password.
3) You could then compile the Frontend and only distribute the resulting Access 'mde' file. So code and passwords are hidden.
4) You can also change the properties of the tables and make/set them to 'Hidden'. Not fool proof, but might help.
5) You could move your app to the latest version of MS Access and use the MSDE engine instead of Jet, which is SQL like and has more security, but you would need the Developers edition inorder to distribute your app.
6) You could just move to MS SQL server.
7) Finally, just do not worry about it, but make sure to have good 'Copyright' notice on the opening screen.

Good Luck,
Hap



Access Developer

http://www.fcs-software.com/fcsaccessdev.html

Access based Add-on Solutions
Access Consultants forum

http://www.tek-tips.com/threadminder.cfm?pid=1121

 

[Ranvier]

Hi Hap,

Thanks for the advice I think this solution is what I am looking for but can you answer this for me....

I have re-split my application. I then put a password on my backend database.

How do I then re-link my front end tables to the back end. I get 'Not a valid password' when I use Link Table Manager from the front end and I cant see how I can relink the tables from the backend to the front end. Thanks!

 

[Hap007]

Dear Carva,

I assume that the links were already present and then you password protected the backend database.

If this is the case, you can do 2 things.
1) Could refresh the links using VBA code. I would not advise this now, maybe later.
2) Follow this:
a) Delete all the links in your FrontEnd
b) Makesure you set the password to the backend (Write the password down somewhere in your notes)
c: Now, in your FrontEnd, open the File>Get External Data > Link and select the 'Password protected' backend

d)Once you enter the password, select all the tables that you need and then the FrontEnd will remember the passowrd.


Note: If you are also going to set the table's 'Hidden' attribute to true, do not do that until after you do step c, link or you might not see the tables.

Good Luck,
Hap...

You could do the ref

Access Developer

http://www.fcs-software.com/fcsaccessdev.html

Access based Add-on Solutions
Access Consultants forum

http://www.tek-tips.com/threadminder.cfm?pid=1121

 

[Hap007]

FYI:

Ignore 'You could do the ref '

after my name, do not no what that is, must have happened from a cut and paste

Hap

Access Developer

http://www.fcs-software.com/fcsaccessdev.html

Access based Add-on Solutions
Access Consultants forum

http://www.tek-tips.com/threadminder.cfm?pid=1121

 

[Ranvier]

All works! Thanks very much for your help.

 

[Ranvier]

Hap,

I have another question about what I have just done.

I have done everything you suggested:
1/split my database
2/Password protected by B/E
3/ReLinked F/E tables to B/E
4/Hide Tables
5/Compile front end as MDE

If I open up Excel and create a 'New database Query' (Tools, Get External Data), my backend is fully protected as I need a password But I can still see the tables in my F/E and import the data into excel. How can I prevent someone from doing this - again without the user of the application entering a password everytime they open the application?

Thanks

 

[jsteph]

carva,
First you must know that if a user can read the data, he can import it.

If you want to prevent reading from certain users, you must go into permissions and remove all permissions from the selected tables.

You need to, in the wrkgrp file (the .mdw), have the admin user removed from the admins group. Then you must force all access to this db to be made via this wrkgroup file.

I do this in the following way:
The front/end should be opened via a shortcut referencing /wrkgrp "mysecured.mdw"

I often rename the mdw to something like "MSCommx.dll", so the command line is

"c:\progr..blah\blah\msaccess.exe /wrkgrp c:\winnt\system32\mscommx.dll /user goduser C:\somedb.mdb"

This is not effective against knowledgeable users, but it tends to confuse the wannabe Access hackers.

In a Module, have the relink code do (pseudo)
docmd.delete "linked table"
docmd.transferdatabase aclink,"linked table"

Any module code that references data can be prefixed via opening a Workspace referencing the .mdw file. I have gone to the extent of putting the .mdw file in a table as an ole blob, then having code GetChunk it out to the App.path, as some random named file, then referencing that in Workspaces.
Now you can see that with Access, you need to get very painfully detailed and involved to get things secure.

Anyway, then ALL tables should be read-restricted. Use queries with OWNER ACCESS option for form/report sources.

There are other little tricks you can do, but as has been stated here--nothing is completely un-hackable. It's even easier now (thanks, dubiously to Tek-tips use of advertising keywords) becuase looking at the above post the word: passwords <--has told us all where we can get these hacking tools.

But, the password hackers MUST have the .mdw for this to work, so renaming, hiding, etc, will help, but if the .mdw's location is known to the hacker, and is accessible, then the passwords can be hacked.
--Jim

 

[Hap007]

Dear Carva,

I know of 2 ways to handle this.

1) Would be to implement the security method that others have mentioned. (but you indicated in your first post that you wanted to stay away from security issues and passwords for the user, so this does not seem like what you want)

2) Second method:
a) Add a table to hold all of your linked table names
b) Write 2 VBA code functions. One to remove all linked tables and one to open/create the tables that are linked and re-establish all links.
c) When the program starts, call the program to set/establish all the links
d) When the program exits, the last thing you call is the function that deletes all the linked table entries.

I know that this method 2 works, because I had to do this for a client of mine six months ago.

Good Luck,
Hap...

Access Developer

http://www.fcs-software.com/fcsaccessdev.html

Access based Add-on Solutions
Access Consultants forum

http://www.tek-tips.com/threadminder.cfm?pid=1121

 

[Ranvier]

Hap,

Again, thanks for the advice - I have implemented your suggestion with VBA code and it works fine. I'm coming around to the fact that it is very difficult to make everything secure....With linking and unlinking the tables there is a slight hole in that once the appliction is running (and the links have been made) the database at this point is again available to external sources (I can go through excel and execute a 'get external data'). I don't know if you knew that as it would presumably leave a security hole in your clients application (even only if while the application was running) or you may have had other security features to prevent this?

Jim,

Thanks for your comments but am I correct in understanding with your method a user would have to enter a password to open the appliation?


......thanks

 

[Hap007]

Dear Jim,

Well, if it now all works, using VBA, except when running, others can remotely see the linked tables, then:
I assume that you are adding the links at start up and removing at exit.

So, if that is the case, then just set your application so that it will only run in exclusive mode, and that should solve your final problem.

Hope This Helps,
Hap...

Access Developer

http://www.fcs-software.com/fcsaccessdev.html

Access based Add-on Solutions
Access Consultants forum

http://www.tek-tips.com/threadminder.cfm?pid=1121

 

[Hap007]

Dear Jim,

Almost forgot, set to run in exclusive mode only
and only allow the routine that creates the links to run if the application is running in exclusive mode, otherwise, a little message and exit. Note: This is also done in VBA.

Also, Make sure you only send compiled mde version.

Later,
Hap...

Access Developer

http://www.fcs-software.com/fcsaccessdev.html

Access based Add-on Solutions
Access Consultants forum

http://www.tek-tips.com/threadminder.cfm?pid=1121