Password Protected Linked Tables (1 Viewer)

kyoch

Registered User.
Local time
Today, 11:43
Joined
Mar 5, 2014
Messages
58
All,

I have a split database, the back end is password protected but the front end is not. I protect the front end by disabling the shift + open option, disabling F11, removing the ribbon, all menu buttons, and there's no right click function. Basically the users only see the forms I've created.

Since the back end is password protected, if some trickster were to try and import the tables from the back end to their own table a password prompt would display and they couldn't continue without the password. But if the trickster tries to import the table from the front end of the database, which is not password protected, then it doesn't require a password to view the tables!

How do I stop this??
 

CJ_London

Super Moderator
Staff member
Local time
Today, 16:43
Joined
Feb 19, 2013
Messages
16,553
by not having linked tables. You could create them/destroy them as required, but a cany trickster will simply have the program open so some tables are linked and do as you suggest. They only need one to discover the backend password.

google 'dsn less connections' to see if you want to go down that route. The other thing to ensure is that a) your front ends are .accde and b) passwords are not hard coded, but calculated in some way - hardcoded values can be easily found if you view .accde in notepad.
 

kyoch

Registered User.
Local time
Today, 11:43
Joined
Mar 5, 2014
Messages
58
I'm currently using .accdb

What's the advantage of using .accde?

Also, I don't think the dsn less route is the way I want to go. Ideally, I'd like Access to prompt for a password when a user tries to import a table from the front end, or just not allow it at all.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 16:43
Joined
Feb 19, 2013
Messages
16,553
.accde protects forms, reports and code from changes (not sure about macros). This is as much for the benefit for you the developer as anything else. You don't want users changing things, forgetting what they have done, or even that they made a change and then coming to you and saying 'its stopped working'

If users have access to the ribbon they can still add, edit and delete queries and delete linked tables plus edit/delete/add records without going through forms. If tables are local (i.e. in the front end) they can also edit and delete the table design. But there are things you can do to hide the ribbon and the navigation pane

I'd like Access to prompt for a password when a user tries to import a table from the front end, or just not allow it at all.
not possible unless you password protect the front end as well - but then genuine users will need to know the password so not much benefit.

there are things that can be done, but nothing is foolproof. The best you can do is deter the casual hacker - and data is more important than code.
 

kyoch

Registered User.
Local time
Today, 11:43
Joined
Mar 5, 2014
Messages
58
Ok, I have the ribbon and navigation panes hidden and vba editor is password protected so the user can't enter design mode or view any code to make changes.

I've found another thread with a similar issue but it's old. Looks like they hid the tables but didn't provide the code to hide the queries. Thoughts on this approach?

http://www.access-programmers.co.uk/forums/showthread.php?p=1485888#post1485888
 

kyoch

Registered User.
Local time
Today, 11:43
Joined
Mar 5, 2014
Messages
58
Yeah that wouldn't be good.

However, I was able to manually hide the tables through Table Properties and they were not deleted after compact and repair. Also, when I tried to import the tables from a separate database they were not visible so this solves my problem!

Unless someone can think of a way to get around that...
 

kyoch

Registered User.
Local time
Today, 11:43
Joined
Mar 5, 2014
Messages
58
Actually, all you have to do to get around that is select "Options" in the "Import Objects" window and they show up again. So that doesn't work.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 16:43
Joined
Feb 19, 2013
Messages
16,553
The question(s) you are asking has been asked many many times before and there are many possible solutions, although none guarantee complete security.

The first things you should be asking yourself is who are you protecting the db from, what in particular and why.

You can hide things - which will deter some people but not others who know how to unhide things.

you can compile code - to decompile is difficult, but there are services out there which will do it for a price. Compiling won't protect against a 'who' code competitor because they either have the resources or the budget to decompile. But it will protect against a 'who' user who likes to meddle. Also, compiled code is much less likely to become corrupted over time.

Data is the most valuable (in my opinion) - so consider protecting against employees who might like to take a copy of the db or of certain data to one of your competitors. This involves not just preventing them having direct access to the tables but also not providing them with the ability to export everything to excel or to a printer. (or tweaking a form to give them that ability - Note, password protecting the code does not prevent changes to the form or report so a user could change the recordsource by removing the criteria to see all records for example, a quick copy paste and your data is copied to another file). You might also want to consider using SQL server as a back end which provides higher levels of data security.

You can disable things - but what can be disabled can be undisabled.

You will also want to consider guarding against sql injection - a canny user can exploit this to destroy the database, copy all the tables, etc.

So before you start fiddling around the edges for an easy solution (there isn't one) ask yourself who are you protecting the db from, what in particular and why. Then you can start to develop a strategy for what really matters
 

kyoch

Registered User.
Local time
Today, 11:43
Joined
Mar 5, 2014
Messages
58
So I found that if your tables are hidden AND you do not have the "Show Hidden Objects" checked in Navigation Options then a user cannot import any hidden objects.

If you have a table that is hidden but you select the "Show Hidden Objects" button then you can still see the object when trying to import.

The sql injection is interesting because although I've hidden everything in my database there's nowhere I've seen where I can disable anyone from importing something into my database. Any thoughts on how to overcome that?
 

CJ_London

Super Moderator
Staff member
Local time
Today, 16:43
Joined
Feb 19, 2013
Messages
16,553
google sql injection to find out the implication - it does not mean importing tables - it means running unauthorised (injected) code
 

speakers_86

Registered User.
Local time
Today, 12:43
Joined
May 17, 2007
Messages
1,919
Access is not made to be secure. If security is a must, consider a different technology. In Access, you can mostly only prevent people from snooping around. On the plus side, few people really know Access. I've found that even people who claim to use it use it in the same fashion as Excel, which is not correct, so those people can be kept out easily. But if I wanted to snoop around in your project, I probably can. Making the mde or accde is enough to keep me out of the vba code, but if I have access to the backend, I can get to the data, and there is nothing you can do to stop me from that.
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 16:43
Joined
Jul 9, 2003
Messages
16,244
The point is MS Access itself doesn't need to be secure because it is just as secure as your Excel and Word Documents. Why people keep on about access and security, it never makes any sense to me.

Sent from my SM-G925F using Tapatalk
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 16:43
Joined
Sep 12, 2006
Messages
15,613
I doubt if a user COULD copy a table from the front end.

All he would get is the linked table (not the data), and he would still need the password. (He may even need the password to copy the link. Try it and see.

Mind you the password is clearly visible, easily enough, so if a user can see the table, or even write code in a module he can determine the password.
 

Users who are viewing this thread

Top Bottom