How to secure Tables and Queries from those with know hows?

[Bobby1st]

How can I protect my tables and queries on a MDE database using shift + click? The users also knows how to unhidden the objects?

 

[pbaldy]

I think the safest answer is to simply say that there is really no way to stop a knowledgeable user. Access is not that secure an application. Your best bet is the built-in security, if you want to use it. There's code all over to disable the shift key when opening a db, but that's easy to get around.

If you have control over their environment, you could either only give them the runtime version of Access or force them to open the application in runtime mode, but neither of those is foolproof either.

 

[Dom DXecutioner]

Hello,

To add to what has been said, even if you do use the built-in tools provided in access, you still will not be able to deter a knowledgeable and determined would-be-hacker.

If security is a major issue, the next level of security is to use an SQL server for the data and continue using Access for the front end or interface.

 

[Bob Miller]

Of all the dbs I produced for my company, NO USER ever screwed with a table structure or a query design!
You asked how to keep users from messing with them. Do you really mean people. Users usually have a vested interest in the db working properly and won't screw with it.
Now, I have had people try to mess with a db. The problem occurred when our bright IT department changed permissions on a network drive where only users had permission.
You can prevent people other than users of the db by setting permissions on the location of the BE and having users follow computer security rules of your company. - logging off when they leave the computer, not giving others their username and password, etc.

 

[Bobby1st]

pbaldy: I think the safest answer is to simply say that there is really no way to stop a knowledgeable user.

Dom DXecutioner: If security is a major issue, the next level of security is to use an SQL server for the data and continue using Access for the front end or interface.

Bob Miller: If security is a major issue, the next level of security is to use an SQL server for the data and continue using Access for the front end or interface.

Thank to you all for you enlightenments. This database is an employee performance database, I accept what a knowledgeable user can do. The db is on a shared folder on a server. The Knowledgeable user (K-user) is a member user. The data table are on the BE but link to the FE thereby, the k-user can make changes on the data table. I have to grant k-user access to the BE folder if not the FE won't work for k-user (restricted). I have a security or password protection but k-user can easily look at the password table, any other way to block that?

At least, it's a MDE and the forms, reports, etc are secured.

Thank you.

 

[The_Doc_Man]

Actually, there IS an effective way to stop a knowledgeable user who also needs to have update permissions in dangerous places. It is highly effective when applied properly. But it is administrative, not programmatic.

First, if you use a switchboard form on startup, disable the "backdoor" entries into Access (SHIFT and F11 and other tricks) and NEVER allow anyone to directly access a table or updateable query, you have some hope of making this work. In that case, your action forms can support auditing.

Set up auditing (there is a thread you can read on AUDIT TRAILS) that should be enlightening. Tell the person who "owns" the data what you have done and verify the importance of that database to the company. If necessary, get an opinion in writing.

Watch your k-user. Monitor your audit logs. If you EVER EVEN ONCE catch the k-user modifying data improperly then you grab that scruffy mother by the neck and drag him/her kicking and screaming all the way to the boss for whom this database was created. Explain in no uncertain terms that you are watching and that interfering with the DB costs the company time and money to recover from the interference. Then have the boss explain again using words like "official warning in the personnel jacket" and "second occurrence is a firing offense" and "lose partial vesting in your retirement funds".

You'd be surprised at how quickly discussions that affect monetary security will rivet someone's attention.

If you are NOT working from a switchboard form but rather allow direct access to the underlying data, your problem is that your design is deficient in respect to its security requirements. Because anyone who has update rights on a table and can get to that table WILL update that table.

 

[pbaldy]

Doc Man, I'd agree with most of what you said, but methods of getting around the disabled shift-bypass key and such are so commonplace, I thought it prudent to say that you couldn't effectively stop the "k-user". Once I'm past those and at the database window, I'm also past your audit code.

 

[Dom DXecutioner]

The_Doc_Man, while i also agree with you regarding your technique... it will still not deter a 'k-user'.

The shift-bypass is nothing more than a public property that can be modified with another db or vbscript.

The built-in security, while effective, can also be bypassed. A user can easily obtain the data in the tables and not have the need to modify it, depending on his/her intent. If you have credit card numbers, social security numbers, email listings, etc... they have what they need with no further use for the database.

Implementing your technique would have to require a proactive approach, in some cases this may not be doable.

That's just my opinion of course

btw, i've read some of your discussions along with banana's and i find you gentlemen, very inspiring.

 

[The_Doc_Man]

Thank you, Dom. You'd be surprised how a shot in the wallet catches someone's attention when physical threats of violence won't. And while a really determined user can bypass a lot of stuff, you can at least take the approach of making it a challenge. With this warning embedded in your code.

Go ahead and use the switchboard. Have it set up something in the Forms_Open routine such that you have a context variable in a public module. Have every form and every report look at the context variable. The moment someone breaks in to your DB bypassing the switchboard, you have a case where that context variable isn't there any more. Use that to trap the unsuspecting k-user.

Now, if this is REALLY a valuable DB, talk to your network admin. Have that person place audit events on all who Open the database file. WINDOWS audit events. Then have your switchboard update a file outside of the database.

The trail will be that you will have Windows Open-file events that do NOT correspond to the external log file created outside the database.

Take THAT evidence to the boss and make it clear that k-user is costing the company time and money - and risking data loss.

After that, you will find out whether your DB or the k-user is more valuable. And if that leads to a career choice, so be it.

And if all else fails, I've got a Cajun cousin, Ol' Boudreaux, who keeps a lot of 'gators in his back yard. (Well...., his back swamp.) Boudreaux can make ANYONE disappear.

 

[Bob Miller]

Thank you, Dom. You'd be surprised how a shot in the wallet catches someone's attention when physical threats of violence won't. And while a really determined user can bypass a lot of stuff, you can at least take the approach of making it a challenge. With this warning embedded in your code.

Go ahead and use the switchboard. Have it set up something in the Forms_Open routine such that you have a context variable in a public module. Have every form and every report look at the context variable. The moment someone breaks in to your DB bypassing the switchboard, you have a case where that context variable isn't there any more. Use that to trap the unsuspecting k-user.

Now, if this is REALLY a valuable DB, talk to your network admin. Have that person place audit events on all who Open the database file. WINDOWS audit events. Then have your switchboard update a file outside of the database.

The trail will be that you will have Windows Open-file events that do NOT correspond to the external log file created outside the database.

Take THAT evidence to the boss and make it clear that k-user is costing the company time and money - and risking data loss.

After that, you will find out whether your DB or the k-user is more valuable. And if that leads to a career choice, so be it.

And if all else fails, I've got a Cajun cousin, Ol' Boudreaux, who keeps a lot of 'gators in his back yard. (Well...., his back swamp.) Boudreaux can make ANYONE disappear.

I used to work in LA (Lower Alabama, of course) and we had a number of coon a$$es that were related to Boudreaux. Some were cousins and others were nieces and nephews and they also talked about how people would disappear when he came around. As a matter of fact, I think that the reason we didn't have trouble (see above post) with users, even k-user, was that his rep was well known.

 

[The_Doc_Man]

Ol' Boudreaux, he know how to work with 'dem 'gators, yeah. You don't mess with Ol' Boudreaux.