How to TRULY lock access to tables

[is49460]

Hello,

Does anyone know of a reliable way to prevent users to access tables?
Making an accde file, creating backend and linking to it through some "hidden" location, hiding table by setting attributes and etc are all good ways to partially prevent users to access data. But knowledgeable user will find a way to get to data. What I'm looking for is to truly lock access to the tables.
What I have done before was to hide all the database objects, panels, disable shortcut keys and disabled shift key function on start-up via vba. Then I had a button on one of the forms that would prompt for a hard coded password, and then unlock database objects if password matched (for admin purposes of course). That's the only way I found to prevent users to get to the tables. Does anyone have any other suggestions how to prevent users to view tables.

Thanks.

[evander]

Your requirement may push you toward a more advanced database management system. Good luck.

[Yance]

There is the way to Encrypting an Access Database, check out this link:

http://msdn.microsoft.com/en-us/libr...ffice.10).aspx

[is49460]

There is the way to Encrypting an Access Database, check out this link:

http://msdn.microsoft.com/en-us/library/aa140844(v=office.10).aspx

Thanks for the reference!! good to know

[pkstormy]

There's a simple way to prevent user's from getting behind the scenes to the tables.

See this example. Note that the mdb first opens a 'mainformbackground' which maximizes and then opens the popup main menu form. This prevents users from accessing the upper main menus to close any forms.

http://www.dbforums.com/6332819-post68.html

Although this doesn't prevent holding the shift key and opening the mdb/accdb to get behind the scenes (and I wouldn't recommend any coding to disable this), most user's don't know the trick of holding the shift key down while opening the mdb.

Also create an mde/accde versus using an mdb/accdb. This prevents user's from seeing the code should it bomb out anywhere or getting into design view.

[is49460]

Thanks for your suggestion!

There's a simple way to prevent user's from getting behind the scenes to the tables.

See this example. Note that the mdb first opens a 'mainformbackground' which maximizes and then opens the popup main menu form. This prevents users from accessing the upper main menus to close any forms.

http://www.dbforums.com/6332819-post68.html

Although this doesn't prevent holding the shift key and opening the mdb/accdb to get behind the scenes (and I wouldn't recommend any coding to disable this), most user's don't know the trick of holding the shift key down while opening the mdb.

Also create an mde/accde versus using an mdb/accdb. This prevents user's from seeing the code should it bomb out anywhere or getting into design view.

[TheShabz]

Although this doesn't prevent holding the shift key and opening the mdb/accdb to get behind the scenes (and I wouldn't recommend any coding to disable this), most user's don't know the trick of holding the shift key down while opening the mdb.

they do now =P

[wil]

depending on where you keep the linked tables, the connection that the link follows could use a "view-only" user (I did this to keep users from modifying tables linked from SQL Server).

[pkstormy]

they do now =P

Ha. Good one TheShabz (this made me chuckle). Most of my users don't browse the MSAccess forum help sites though.

[RuralGuy]

pkstormy,

Why do you suggest *not* disabling the shift bypass key?

[pkstormy]

I don't like disabling the shift bypass for a couple of reasons:

1. It's usually not upsizing friendly code.
2. I'm getting into about a dozen different mdb files throughout the day to make a fix here and there and need to do it quickly (I then create an mde). Disabling the bypass means you have to re-enable it, close out, and then get back in again. For me, this is a hassle. The setup I have where the mainbackground form maximizes and then I use popup/modal forms, works very well (I put an admin close button for me to easily close and get behind the scenes.) I can then make a change within a few seconds.
3. I don't like the risk of using this routine. I have had problems with it with different versions and I've never really had problems with any users getting behind the scenes or try to. I can also easily tell if a user get's behind the scenes as I write certain values to the data tables (such as the user name).
4. I also have a nice routine whereby users can open any table via a set of queries. By clicking 1 button, they can easily export that data to excel or any other format.

I also use SQL Server linked tables. This combined with the routine which maximizes the background form and then using popup/modal forms for all the other forms, allows me to control exactly what a user can and cannot do (I use a permissions system embedded into the user table.)

Mostly though it's the hassle of getting into the mdb and re-enabling the shift key. Doing this for a dozen different databases throughout the day to make updates really becomes a pain.

[pkstormy]

Here's a method similar to what I use (basic in nature). It works well for pretty much keeping users from closing behind the scenes (you'll notice that none of the MSAccess menu items can be clicked on).

Given that a user could theoretically hold the shift key down, they really wouldn't know what table to update or change since I usually have a fairly in-depth relational setup. I also have automatic alerts sent to me when data is not updated in the way it should be.

Combined with SQL Server and the ability to track who made what update and when, if someone does get behind the scenes, I can pretty much identify them and then they get a nice little chat from me. I've only had to chat with someone once though many years ago.

[Bubi]

Hi,

I came accross this thread and it looks like this is something that I was looking for .
My application is Access 2007 front-end with linked tables to SQL server. I was wondering how this setup could be combined with SQL server. I am new to this, I have read a lot about it, but still haven't figure it out.
My SQL server has SQL authentication. I want my application to have Login, and based on it I would allow or not allow different options. Would something like following be acceptable:
1. Create users in SQL server with their password. Have some kind of form for admin people to do this ( I assume is possible to be done this way with some stored procedures). Or just simply go to server and do this.
2. Add same users to dbo_AdminTable (created on SQL Server) with user level security
3. Create Log In form in access front-end. Accept user input, submit to connection string this info and try to connect to server. If it connects (valid user), then setup rest depending on their user security level. If it does not connect then give some kind of message.

Thanks a lot for any suggestions

[pkstormy]

Hi Bubi,

Here is the method I use: http://www.accessforums.net/code-rep...mple-7538.html

A couple of things to point out:
1. When I create an ODBC DSN to connect to SQL Server, I don't individualize it but instead connect using a Group user name/password or utilize the sa. Since I play the role of dba, developer, and manager, I don't have the time to add/delete users in SQL Server and mess around with their permissions.
2. The example shows an MSAccess 'user' type table which would instead just be a linked SQL Server table.
3. I never use a login form but instead grab the user's loginID automatically in code (see the example). I'm a strong believer that user's have enough logins/passwords to remember and any login forms I've created in the past where always a nuisance for users where they asked if there was a way to automate it so they didn't have to enter a login/password. In my startup code, I check to see if the returned LoginID matches a LoginID in the 'user' table. If it doesn't, it pops up a message saying they need to contact admin to have their name added to the 'user' table and then exits the mdb. I give certain users a security level which then lets them open the 'user' table (via a form) so they can add/delete users and thus, I never ever have to deal with add/deleting users. A big time-saver for me.
4. The example shows how you can enable/disable/make visible/invisible fields on the form based upon the user's security level. This is essentially a simple function to lookup the user's security level in the 'user' table. (ex: if retUserSecurityLevel(getUserLoginID()) = 1 then .... and retUserSecurityLevel is the function to lookup what that user's security level is in the 'user' table.)
5. In the Code Repository, there is also code to automatically create the ODBC DSN. I put this code in my startup routine. That way I don't have to go around to each computer and create an ODBC DSN for it (remember also, if you mis-spell the ODBC DSN in any way, you'll have problems with some users not being able to open the tables.) Doing this automatically in code not only saves you time, but also ensures that the ODBC DSN name is exactly the same for each computer.

If you only have to manage 1 application, you may not mind adding/deleting user's to SQL Server or manually creating ODBC DSN's on every computer. If you manage 25-30 applications like I do, you can easily get swamped doing this which can take valuable development time away from you.

[Bubi]

Thank you for such detailed answer. It looks like we have same roles in our projects
I will take a look and see how this could fit into my project.
I will post back on results.

Thanks