Any tips on how to secure data in a separate accdb (1 Viewer)

[wbulckens]

Hi folks,

I'm building an application in MS Access 2010.
This application must be able to read from a big table in a separate accdb.

This separate accdb contains info that should NOT be available for all my users to copy, as it is valuable competitive information that could be abused by people with bad intensions. So, a linked table solution is not an option.

However, it should be possible to run parameterized queries that do simple look-ups that return only small/relevant portions of the database.

Please send me your ideas!

Kind regards
Wim

 

[CJ_London]

depends on what you need to do with the data - is it just used for lookups in comboboxes or recordsets? Or is the user updating data? If the former, you can use vba to execute a sql string to populate them using a dsnless connection - so there is no linked table and no indication where the db is.

Also depends on what you do with your front end - is it compiled into a .accde? If not, there is little you can do to protect from someone who has a modicum of access skills - if they can read the above code, they can find the location and password

Alternatively you can still link to the db if your users do not need access to the navigation window. A simple thing to do is to hide the navigation window, or even the whole access window (all forms/reports would then need to be popup and modal) and disable the bypass keys

 

[CJ_London]

goes without saying but the other db should be password protected - although there are password crackers out there

 

[ButtonMoon]

This separate accdb contains info that should NOT be available for all my users to copy, as it is valuable competitive information that could be abused by people with bad intensions. So, a linked table solution is not an option.

You cannot stop people copying and using the data in an ACCDB file if they have read permissions on that file via the file system. It's imporant that the owner of the data realises that and agrees that it's an acceptable risk. If that's not acceptable then you need to use a different solution. Use a SQL DBMS (e.g. SQL Server or Oracle) with server-side security and don't give end users direct access to the database.

 

[gemma-the-husky]

one way is not maintain the link to the accdb permanently, but create it, extract the data, and destroy it each time.

you can encode a password to the remote accdb within your own accde/mde and users would then not be able to get at that data in the remote accdb.

clearly your users would be able to find the remote accdb (outside of access), but would not be able to open it directly. Having said that, in a complex file system, they may struggle to find it. A lot of this depends how tight/disciplined your company is.

 

[CJ_London]

You cannot stop people copying and using the data in an ACCDB file if they have read permissions on that file via the file system

Agreed, but you can do things to make it more difficult - for example, hide the file (although users can change settings to view hidden files), place is a none obvious directory, provide the file with a different filetype to make it less obvious it is a db (you can still access the file through linking or whatever), although the .laccdb file will still be visible when the db is being read by a user.

It just depends on the 'level' of security required

 

[ButtonMoon]

Agreed, but you can do things to make it more difficult - for example, hide the file (although users can change settings to view hidden files), place is a none obvious directory, provide the file with a different filetype to make it less obvious it is a db (you can still access the file through linking or whatever), although the .laccdb file will still be visible when the db is being read by a user.

It just depends on the 'level' of security required

Which is why I said that what really matters is to validate that with the person who owns or is responsible for the data. Demonstrate to that person the actual steps needed to circumvent whatever method you use to "hide" the data. If he/she is happy to live with that then go right ahead. I could be wrong, but since it only takes a couple of clicks to view hidden files in Explorer I suspect that the owner/customer may not be entirely confident in that as a method of "hiding" valuable data.