Page 1 of 2 12 LastLast
Results 1 to 15 of 21

How to TRULY lock access to tables

  1. #1
    is49460 is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Jan 2010
    Posts
    40

    How to TRULY lock access to tables

    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.
    Last edited by is49460; 06-19-2010 at 01:09 PM.

  2. #2
    evander is offline Competent Performer
    Windows 7 Access 2003
    Join Date
    Apr 2010
    Location
    Philippines
    Posts
    193
    Your requirement may push you toward a more advanced database management system. Good luck.

  3. #3
    Yance is offline Novice
    Windows XP Access 2003
    Join Date
    Aug 2009
    Posts
    22
    There is the way to Encrypting an Access Database, check out this link:

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

  4. #4
    is49460 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jan 2010
    Posts
    40
    Quote Originally Posted by Yance View Post
    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

  5. #5
    pkstormy's Avatar
    pkstormy is offline Access/SQL Server Expert
    Windows XP Access 2003
    Join Date
    Mar 2010
    Location
    Madison
    Posts
    643
    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.

  6. #6
    is49460 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jan 2010
    Posts
    40
    Thanks for your suggestion!

    Quote Originally Posted by pkstormy View Post
    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.

  7. #7
    TheShabz is offline Court Jester
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    1,370
    Quote Originally Posted by pkstormy View Post

    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

  8. #8
    wil
    wil is offline Novice
    Windows XP Access 2007
    Join Date
    Jul 2010
    Posts
    7
    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).

  9. #9
    pkstormy's Avatar
    pkstormy is offline Access/SQL Server Expert
    Windows XP Access 2003
    Join Date
    Mar 2010
    Location
    Madison
    Posts
    643

    :)

    Quote Originally Posted by TheShabz View Post
    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.

  10. #10
    RuralGuy's Avatar
    RuralGuy is online now Administrator
    Windows 7 Access 2007
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    10,914
    pkstormy,

    Why do you suggest *not* disabling the shift bypass key?
    (RG for short) aka Allan Bunch MS Access MVP - WinXP Pro, Win7 Pro - acXP, ac07, ac10, ac13
    If your issue is resolved...go ahead and use the Thread Tools at the top of the thread and mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  11. #11
    pkstormy's Avatar
    pkstormy is offline Access/SQL Server Expert
    Windows XP Access 2003
    Join Date
    Mar 2010
    Location
    Madison
    Posts
    643
    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.

  12. #12
    pkstormy's Avatar
    pkstormy is offline Access/SQL Server Expert
    Windows XP Access 2003
    Join Date
    Mar 2010
    Location
    Madison
    Posts
    643

    Method

    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.
    Attached Files Attached Files

  13. #13
    Bubi is offline Novice
    Windows XP Access 2007
    Join Date
    Dec 2010
    Posts
    8
    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

  14. #14
    pkstormy's Avatar
    pkstormy is offline Access/SQL Server Expert
    Windows XP Access 2003
    Join Date
    Mar 2010
    Location
    Madison
    Posts
    643
    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.

  15. #15
    Bubi is offline Novice
    Windows XP Access 2007
    Join Date
    Dec 2010
    Posts
    8
    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

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Exclusive access/DB lock
    By Boru in forum Access
    Replies: 18
    Last Post: 07-28-2014, 12:38 AM
  2. Automatically Lock Fields
    By Toolman in forum Database Design
    Replies: 2
    Last Post: 05-27-2010, 09:36 AM
  3. Replies: 0
    Last Post: 03-25-2007, 11:24 PM
  4. File sharing lock count exceeded
    By bullwinkle55423 in forum Access
    Replies: 0
    Last Post: 03-22-2007, 10:58 AM
  5. Can I lock out fields in a form by keying off...
    By swampdonkey in forum Access
    Replies: 2
    Last Post: 09-07-2006, 06:16 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums