Results 1 to 2 of 2
  1. #1
    Join Date
    May 2012
    Posts
    132

    Unanswered: Protect the database

    Is it possible in access 2010 to make the left panel password protected so that the user will not be able to view and open tables and queries?
    ,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
    I asked god to please give me everything to enjoy the life, God said: i gave you the life to enjoy everything.

  2. #2
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    Not particularly easily...

    You can setup Users, and request that the user logs in upon loading up the database.

    You can then create a Macro, and call it: AutoExec - This will automatically trigger whenever the database is loading. It will be the very first thing that happens after someone has logged in.

    Inside the AutoExec macro, place some code similar to:

    Code:
    Select Case CurrentUser
    
        'See if it's someone else logging in...
        Case "LIMITED ACCESS USERNAME GOES HERE"
        
            'Shows the navigation pane, if hidden.
            DoCmd.SelectObject acTable, , True
    
            'Opens the Admin form.
            DoCmd.OpenForm "WHATEVER FORM YOU WANT TO LOAD", acNormal
    
            'Hides the Navigation Pane.
            DoCmd.NavigateTo "acNavigationCategoryObjectType"
            DoCmd****nCommand acCmdWindowHide
    
            'Hides the Ribbon.
            DoCmd.ShowToolbar "Ribbon", acToolbarNo
    
            'Hides the status bar.
            DoCmd.ShowToolbar "Status Bar", acToolbarNo
                    
    
        'Otherwise, it must be you, so show everything!
        Case Else
    
            i = MaximizeApp()
            DoCmd.SelectObject acTable, , True
            DoCmd.SetDisplayedCategories True
            DoCmd.LockNavigationPane False
    
            'Shows the ribbon.
            DoCmd.ShowToolbar "Ribbon", acToolbarYes
    
            'Shows the status bar.
            DoCmd.ShowToolbar "Status Bar", acToolbarYes
    
    End Select
    That code might have some unnecessary stuff in it, I pieced it together from a more complicated script I use for handling logins, and trimmed out various users and bits that I know aren't necessary.


    Alternatively, you could use a modified version of the script above, without users involved to simply always hide the navigation pane upon opening your database and showing a main 'control' form.

    The 'control' form could have buttons for navigating to your various forms, and one button could be used to show the navigation bar, using:

    Code:
    DoCmd.NavigateTo "acNavigationCategoryObjectType"
    DoCmd****nCommand acCmdWindowUnhide
    So, when the user clicks the button, it'll show the sidebar with your tables, forms, etc on it.

    You would probably want to secure that, and although being far from 'secure', you could simply use a bit of code like:

    Code:
    'Request input from the user.
    i = InputBox("Please enter password:", "Password Required")
    
    'If the input matches the password specified here, then show the navigation sidebar.
    If (i = "YOUR CHOSEN PASSWORD GOES HERE - CASE SENSITIVE") Then
    
        DoCmd.NavigateTo "acNavigationCategoryObjectType"
        DoCmd****nCommand acCmdWindowUnhide
    
    'Otherwise, inform the user that the password is incorrect, and terminate the process.
    Else
    
        MsgBox "You have entered an incorrect password. Please try again.", vbExclamation, "Incorrect Password!"
    
    End If
    That will do the same thing, but won't require user logins. The reason it isn't really a secure method is because the password will be stored in plain text. Meaning, anyone using your database can push: ALT+F11, find the bit of code, and be able to see what password it's expecting, as it's not encrypted.

    If you're not too bothered about that and/or think the average Joe using the database probably won't do/know how to do that, then that's a much simpler solution to your problem.


    ASIDE: If you're worried about security, but want it to be more secure than simply pushing ALT+F11 and hunting around for the code snippet, you could store the password in a table, say called: sidebar_passwords, with just a single field Passwords. You could then adapt the above user input request code to something like:

    Code:
    Dim strSQL As String
    Dim rs As DAO.Recordset
    
    'Request input from the user.
    i = InputBox("Please enter password:", "Password Required")
    
    'Searches the database table for the password given.
    strSQL = "SELECT [Passwords] " & _
             "FROM sidebar_passwords " & _
             "WHERE [Passwords] = '" & i & "'"
             
    Set rs = CurrentDb.OpenRecordset(strSQL)
    
    'If that password is valid, then the navigation sidebar can be shown.
    If Not ((rs.BOF) And (rs.EOF)) Then
    
        DoCmd.NavigateTo "acNavigationCategoryObjectType"
        DoCmd****nCommand acCmdWindowUnhide
    
    'Otherwise, inform the user that the password is incorrect, before the process is terminated.
    Else
    
        MsgBox "You have entered an incorrect password. Please try again.", vbExclamation, "Incorrect Password!"
    
    End If
    
    'Tidy up
    rs.Close
    set rs = Nothing
    This is much more secure, as if when loading the database the navigational sidebar is hidden by default, then there is no way to see the contents of the sidebar_passwords table, where the password is stored, without using your button (which requires a password) to show the sidebar. As the code uses a lookup, simply using ALT+F4 will tell you where the password(s) is/are stored, but won't display what the password is or allow a user to access the table itself, and therefore the password(s) will be more tightly secured.


    If you wanted to go even more security mad, you could use some form of encryption or hashing or both - even utilising a salt, or possibly several salts for storing the password within the sidebar_passwords table. You could then use a lookup algorithm to generate a thousand or so possible passwords based on different salts (as you'd want the algorithm to use 'dummy' salts), and see if a match is found.

    I'm now beginning to talk about cryptography, cyphers and other security intensive features that are well beyond the scope of this question.


    There's three answers for you. Take your pick!


    Hope this helps!


    NOTE: Where the code is written as DoCmd****nCommand in the above examples, you need to replace it with DoCmd . Run Command without any spaces. The forums prevent that command from being written, as it's particularly powerful, and can be used for malicious uses. I assure you however, that nothing malicious is included in the code I've provided for you.
    Last edited by kez1304; 06-29-12 at 07:56.
    Looking for the perfect beer...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •