Thread: Protect the database
06-28-12, 04:49 #1Registered User
- Join Date
- May 2012
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.
06-29-12, 07:26 #2Registered User
- Join Date
- Jun 2011
- Inside your mind
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:
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
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:
DoCmd.NavigateTo "acNavigationCategoryObjectType" DoCmd****nCommand acCmdWindowUnhide
You would probably want to secure that, and although being far from 'secure', you could simply use a bit of code like:
'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
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:
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
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...