By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,628 Members | 1,175 Online
Bytes IT Community
Submit an Article
Got Smarts?
Share your bits of IT knowledge by writing an article on Bytes.

How To Create User Permissions and Customized Menus in MS Access

twinnyfo
Expert Mod 2.5K+
P: 3,284
Seldom does a Database Designer build a Database for themselves and themselves alone. It’s usually built around the requirements of a Customer. Many times, the Database will have multiple Users, and these various Users may (or may not) perform different functions within the Database.

MS Access offers a bit of security with passwords that can encrypt the Database (both the Front and/or Back End) to limit who can open a Database. However, we are still faced with the issue that once a User has access to the Database, they have access, since the password encryption unlocks the entire Database, not just certain parts.

One of the options for managing multiple users is to have a Switchboard or menu of activities that can be performed within the Database, and the Users will only select those items that apply to them, based on their responsibilities in the work center. However, the primary challenge with such an option is that you open yourself to a user “exploring” places of the Database they shouldn’t be exploring. In most situations, this is not really a problem, because the average employee doesn’t want to sabotage their place of employment. However, when it comes to personal, confidential or even medical information, it behooves the Database Administrator to build into their Database certain safeguards to ensure that restricted information is not fallen upon casually or incidentally.

Another solution would be to create separate front ends for each type of User. When one User requires access to a particular set of Forms and Reports, those objects could be loaded into one Front End, linked to the Back End. Another User requires a different set of Forms and Reports, so the Database Administrator builds another Front End that suits. However, the obvious challenge with this solution is the constant expansion of different FEs. This can also cause a drastic duplication of effort, as some different Users (or different types of Users) may require the use of the same Objects. Then, every time one of these Objects is changed, all FEs containing that Object must also be updated.

I find myself in a situation like that in my work center. We only have 10 employees who use the Database on a regular basis, with sometimes 10 more who have a very limited access for short periods of time. However, those 20 employees have vastly different roles in the Database. Additionally, since nearly everything we touch is Privacy Act Information (I work for the Federal Government), we want to control access as much as possible.

Over time, I have developed a method for limiting access to the Database, based on the User. The method for doing this is rather simple:
  1. Create a Users Table
  2. Establish Public Constants and Variables
  3. Determine who has accessed the Database
  4. Determine what to do with that User
  5. Allow User-specific access to the Database

Creating a Users Table

Your Table may have different Fields, but my Table (tblUsers) has certain fields based on my requirements. Feel free to make your own list of requirements and build the Table appropriately. A description of my Table follows:

Expand|Select|Wrap|Line Numbers
  1. Field Name      Data Type
  2. UserKey         Long (AutoNumber)
  3. UserID          Text
  4. UserName        Text
  5. UserRights      Integer
  6. UserMachine     Text
  7. UserDirectory   Text
  8. UserSigBlock    Memo
The UserID is the value returned from the OS environment that identifies who is currently logged onto the machine. All our computers require Smart Card Access, so security is double-authentication throughout. Only people who are verified users can access the Database.

The UserRights will be explained below, but this is a value indicating what level of access (or areas of access) the User will have. This comes into play in the final steps.

Since our IT Department often requests a list of our Users’ machine names, I have included that in my list, and this, too, is easily obtainable from the OS environment.

Occasionally, our Users will download reports or spreadsheets from the Database, and having a User-established Directory helps to make sure their items are always in one place.

Our office frequently sends out e-mail messages from the Database, so I have also included a User-customizable E-Mail signature block. This allows the Database to quickly add a signature block to outgoing E-Mails. This is very convenient.

Now that your Users Table is created, we need to figure out how to use it!


Establishing Public Constants and Variables

I’ve created a Module that holds my lists of Public Variables and Public Functions, called modSystem. Here is a list of what I use to limit User Access:

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. 'User Rights Constants
  5. Public Const UserAdmin As Integer = 1
  6. Public Const UserOIC As Integer = 2
  7. Public Const UserPromo As Integer = 3
  8. Public Const UserRecords As Integer = 4
  9. Public Const UserSrRecorder As Integer = 5
  10. Public Const UserRecorder As Integer = 6
  11.  
  12. 'User Variables
  13. Public glngCurrentUser As Long
  14. Public gstrUserID As String
  15. Public gstrCurrentUser As String
  16. Public gintUserRights As Integer
  17. Public gstrUserDirectory As String
  18. Public gstrUserMachine As String
I have established constants for the various types of Users because it is easier to remember the Type of User than it is to remember the value of their User Rights. Additionally, if I use these Global Constants throughout the Database, and need to make any changes to the actual values of these Constants, I don’t need to find every instance of the User Rights. The Global Constant will take care of that.

To explain my Public Variables, I like to allow the Database to have certain data always at its finger tips. For example, the gstrUserID is the System User ID, returned from the Operating System. The variable gstrCurrentUser is the actual name of the User (e.g. “Egbert Schmuckatelli”). This value is established when the Database Administrator sets up the User’s Account. The variable glngCurrentUser is the Primary Key of tblUsers. The other variables are self-explanatory.

I use these variables throughout the Database, whenever we want to identify a User as accomplishing a certain task or when User-specific information must be provided.


Determining Who Has Accessed the Database

This is the easiest step. When the Database opens, I use a Splash Form which automatically determines who logged into the Database, as well as what the Database will do with that User. In the OnOpen Event of the Form, I have the following:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Open(Cancel As Integer)
  2. On Error GoTo EH
  3.     Dim db As Database
  4.     Dim rst As Recordset
  5.     Dim strSQL As String
  6.     gstrUserID = Environ("USERNAME")
  7.     Set db = CurrentDb()
  8.     strSQL = "SELECT * FROM tblUsers " & _
  9.         "WHERE UserID = '" & gstrUserID & "';"
  10.     Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
  11.     If Not rst.EOF Then
  12.         With rst
  13.             .MoveFirst
  14.             glngCurrentUser = !UserKey
  15.             gstrCurrentUser = !UserName
  16.             gintUserRights = !UserRights
  17.             gstrUserMachine = !UserMachine
  18.             gstrUserDirectory = !UserDirectory
  19.         End With
  20.     Else
  21.         'This User does not exist in the Users Table
  22.         'Determine what to do--whether to Quit or set up new account
  23.     End If
  24.     rst.Close
  25.     db.Close
  26.     Set rst = Nothing
  27.     Set db = Nothing
  28.     Exit Sub
  29. EH:
  30.     MsgBox "There was an error initializing the Form!  " & _
  31.         "Please contact your Database Administrator.", vbCritical, "Error!"
  32.     Exit Sub
  33. End Sub
So, at this point, the Database knows the User’s rights within the Database, but we just have to make the system direct them appropriately.


Determining What to do With a User

Since my Splash Form has no actionable objects on it, after it completes doing several other things in the background (driven by the OnTimer Event), the Code simply tells the Form to Close. In the OnClose Event, I have placed the following:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Close()
  2. On Error GoTo EH
  3.     Select Case gintUserRights
  4.         Case UserAdmin
  5.             DoCmd.OpenForm "frmAdministrator"
  6.         Case UserPromo, UserOIC, UserSrRecorder
  7.             DoCmd.OpenForm "frmSwitchboard"
  8.         Case UserRecords
  9.             DoCmd.OpenForm "frmCommandRecords"
  10.         Case UserRecorder
  11.             DoCmd.OpenForm "frmPRFReview"
  12.     End Select
  13.     Exit Sub
  14. EH:
  15.     MsgBox "There was an error Closing the Form!  " & _
  16.         "Please contact your Database Administrator.", vbCritical, "Error!"
  17.     Exit Sub
  18. End Sub
You will notice that, based on the Rights of the User, a different Form opens. This is completely transparent to the User, as our “Records” staff only ever uses the Command Records Form. They don’t know any different. A Recorder never has need to access our Switchboard, but only performs duties on the PRF Review Form.

However, you may notice that three different types of Users (userPromo, UserOIC and UserSrRecorder) all open the Switchboard Form. This doesn’t seem to jibe with this entire Article! If you have established different levels of User Rights, how can they all access the same Form? Won’t they all have access to the same information?

Here is where I have made a small twist on a classic capability of MS Access.


Allowing User-Specific Access to the Database

Many years ago, Microsoft Access began introducing the Switchboard Manager. I have used that standard format as the basis for my Switchboard, but have made some changes to provide User-specific access to the Menu Items. Keep in mind that if you currently use MS Access’ built in Switchboard manager, this method will NOT work—nor will you be able to edit the Switchboards using the built in Switchboard Manager. This is an overhaul to the Tables, as well as the VBA code that runs the Switchboard, so you may want to start from scratch.

First, I’ve made some modifications to the underlying Table for the Switchboard. I have renamed my Table “tblSwitchboards” (note the plural, as this Table does, in fact, hold data for multiple Switchboards). Here is my list of Fields:

Expand|Select|Wrap|Line Numbers
  1. Field Name      Data Type
  2. User            Integer
  3. SwitchboardID   Integer
  4. ItemNumber      Integer
  5. ItemText        Text
  6. Command         Integer
  7. Argument        Text
Aside from the first Field (“User”) I believe this Table is identical to the MS Acces Switchboard Table generated automatically. One thing I have done is Create a related Table, tblSwitchboard Commands, which is used as a Row Source for the Command Field. Its values are as follows:

Expand|Select|Wrap|Line Numbers
  1. ID   Command
  2. 0    Page Title
  3. 1    Switch Page
  4. 3    Open Form
  5. 4    Open Report
  6. 6    Quit
This just makes the Table more intuitive when looking at it in Datasheet View.

Additionally, I have modified the Argument Field to be a Lookup Field, in which the Row Source is the following Query:

Expand|Select|Wrap|Line Numbers
  1. SELECT MSysObjects.Name
  2. FROM MSysObjects
  3. WHERE (MSysObjects.Type=-32768 OR MSysObjects.Type=-32764)
  4. ORDER BY MSysObjects.Name;
MSysObjects is a System Table within MS Access, so don’t go around trying to change it! This Query only returns the Names of Forms and Reports, which are the only objects my Users have access to (PERIOD!). With a little research you can expand this query to include any other objects you desire.

You may also notice that this Query will return SubForms and SubReports (as well as the Parent Forms and Reports). If you have a standard Naming Convention (as I know ALL of you do...) you can limit the Query further by changing the Where clause to:

Expand|Select|Wrap|Line Numbers
  1. WHERE (MSysObjects.Name Like "frm*" AND MSysObjects.Type=-32768) OR (MSysObjects.Name Like "rpt*" AND MSysObjects.Type=-32764)
You can experiment as much as necessary to get this Query to return the proper list of values.

It is very important that you set the “Limit To List” property of this Field to “No”. The reason for this will become apparent shortly.

Now that we have created this Switchboards Table, let’s populate it. Excuse the length of this list, but I wanted to illustrate more fully. Keep in mind that this list has been pared down significantly, but is used only for illustration (and please excuse the whacked columns, but I did not see a point in converting my tabs to spaces):

Expand|Select|Wrap|Line Numbers
  1. User    ID    No.    Item    Cmd    Argument
  2. Administrator    1    0    ACC Officer Promotions    Page Title    Default
  3. Administrator    1    1    Task Management    Switch Page    2
  4. Administrator    1    2    Administrative Tasks    Switch Page    6
  5. Administrator    1    3    Quit    Quit    
  6. Administrator    2    0    Task Management Menu    Page Title    
  7. Administrator    2    1    Review Open Tasks    Open Form    frmTasks
  8. Administrator    2    2    Overdue Tasks Report    Open Report    rptOverdueTasks
  9. Administrator    2    3    Upcoming Tasks Report    Open Report    rptUpcomingTasks
  10. Administrator    2    4    Task Template    Open Form    frmTaskTemplate
  11. Administrator    2    5    Print Task Template List    Open Report    rptTaskTemplateListing
  12. Administrator    2    6    Print Continuity Book    Open Report    rptContinuityBook
  13. Administrator    2    7    Manage Projects    Open Form    frmProjects
  14. Administrator    2    8    Return to Main Menu    Switch Page    1
  15. Administrator    6    0    Administrative Tasks    Page Title    
  16. Administrator    6    1    Command Records    Switch Page    10
  17. Administrator    6    2    Release Actions    Open Form    frmPromotionRelease
  18. Administrator    6    3    Recorders    Switch Page    11
  19. Administrator    6    4    Evaluation Timeliness    Open Form    frmEvaluations
  20. Administrator    6    5    STEP Quotas    Open Form    frmSTEPQuotas
  21. Administrator    6    6    Manage Users    Open Form    frmUsers
  22. Administrator    6    7    Administrator Functions    Open Form    afrmAdministrator
  23. Administrator    6    8    Return to Main Menu    Switch Page    1
  24. Administrator    10    0    Command Records Menu    Page Title    
  25. Administrator    10    1    Screening Progress    Open Form    frmRecordScreening
  26. Administrator    10    2    Send Missing Evals    Open Form    frmSendMissingEvals
  27. Administrator    10    3    Command Records Database    Open Form    frmCommandRecords
  28. Administrator    10    4    Return to Previous Menu    Switch Page    6
  29. Administrator    11    0    Recorder Actions    Page Title    
  30. Administrator    11    1    Recorder Listing    Open Form    frmRecorders
  31. Administrator    11    2    Recorder Scorecard    Open Form    frmRecorderScorecard
  32. Administrator    11    3    Return to Previous Menu    Switch Page    6
  33. OIC    1    0    ACC Officer Promotions    Page Title    Default
  34. OIC    1    1    Task Management    Switch Page    2
  35. OIC    1    2    Administrative Tasks    Switch Page    6
  36. OIC    1    3    Quit    Quit    
  37. OIC    2    0    Task Management Menu    Page Title    
  38. OIC    2    1    Review Open Tasks    Open Form    frmTasks
  39. OIC    2    2    Overdue Tasks Report    Open Report    rptOverdueTasks
  40. OIC    2    3    Upcoming Tasks Report    Open Report    rptUpcomingTasks
  41. OIC    2    4    Task Template    Open Form    frmTaskTemplate
  42. OIC    2    5    Print Task Template List    Open Report    rptTaskTemplateListing
  43. OIC    2    6    Return to Main Menu    Switch Page    1
  44. OIC    6    0    Administrative Tasks    Page Title    
  45. OIC    6    1    Command Records    Switch Page    10
  46. OIC    6    2    Recorders    Switch Page    11
  47. OIC    6    3    Evaluation Timeliness    Open Form    frmEvaluations
  48. OIC    6    4    Return to Main Menu    Switch Page    1
  49. OIC    10    0    Command Records Menu    Page Title    
  50. OIC    10    1    Screening Progress    Open Form    frmRecordScreening
  51. OIC    10    2    Send Missing Evals    Open Form    frmSendMissingEvals
  52. OIC    10    3    Command Records Database    Open Form    frmCommandRecords
  53. OIC    10    4    Return to Previous Menu    Switch Page    6
  54. OIC    11    0    Recorder Actions    Page Title    
  55. OIC    11    1    Recorder Listing    Open Form    frmRecorders
  56. OIC    11    2    Recorder Scorecard    Open Form    frmRecorderScorecard
  57. OIC    11    3    Return to Previous Menu    Switch Page    6
  58. Officer Promotions    1    0    ACC Officer Promotions    Page Title    Default
  59. Officer Promotions    1    1    Task Management    Switch Page    2
  60. Officer Promotions    1    2    Quit    Quit    
  61. Officer Promotions    2    0    Task Management Menu    Page Title    
  62. Officer Promotions    2    1    Review Open Tasks    Open Form    frmTasks
  63. Officer Promotions    2    2    Overdue Tasks Report    Open Report    rptOverdueTasks
  64. Officer Promotions    2    3    Return to Main Menu    Switch Page    1
  65. Senior Recorder    1    0    Senior Recorder Actions    Page Title    Default
  66. Senior Recorder    1    1    PRF Review    Open Form    frmPRFReview
  67. Senior Recorder    1    2    Recorder Scorecard    Open Form    frmRecorderScorecard
  68. Senior Recorder    1    3    Quit    Quit
NB: The Argument Field can have one of four types of entries:
  1. “Default” – this indicates that this is the Main Switchboard (Home page)
  2. Blank – indicating that this is a Switchboard Page Title (also indicated by the Command Field) or indicates to Quit the Database
  3. A number (this is saved as Text!!) and indicates the Switchboard page to which the User will be directed
  4. The name of a Form or Report
Notice, also, how the Switchboard options are clearly different for each User Type. Now we just have to set up our Form to work with this Table.



(Image 1)


As you may not be able to see from the picture attached, my Switchboard has ten Command Buttons and Labels. These Command Buttons and Labels follow a specific Naming Convention (because I know you ALL follow the same principles!) of “cmdOption1”, “cmdOption2”, etc. and “lblOption1”, “lblOption2”, etc. You can ignore some of the stuff on this Form, but you can see that it’s really just a modification of the old MS Access standard Switchboard. When we look under the hood, this is what we find:

The Form itself has the Record Source of tblSwitchboards. the Form's Properties of Allow Additions and Allow Deletions are both set to No. As the User navigates the Menu, the Form Filters the specific Record that it needs (one record and one record only is displayed). There are several procedures we have to put in place for this Form to Work with the Table:

NB: Some of my code is not displayed as it is irrelevant for this purpose.

Expand|Select|Wrap|Line Numbers
  1. Option Explicit
  2. Option Compare Database
  3.  
  4. Private Const intButtons = 10
  5.  
  6. Private Sub Form_Open(Cancel As Integer)
  7. On Error GoTo EH
  8.     Me.Filter = "User = " & gintUserRights & _
  9.     " AND ItemNumber = 0 AND Argument = 'Default'"
  10.     Me.FilterOn = True
  11.     Exit Sub
  12. EH:
  13.     MsgBox "There was an error initializing the Form!  " & _
  14.         "Please contact your Database Administrator.", vbCritical, "Error!"
  15.     Exit Sub
  16. End Sub
We establish the Constant intButtons to indicate how many buttons this Form uses. It’s nice to know we can always expand if we need to.

Then, when the Form opens, we filter by the “Default” Switchboard Page for that User. Very simple, so far, right?

When we choose another Switchboard Page to move to, the Code will filter the Form by the Page Title Selected (according to the appropriate User), so when that Filter is complete, we need to show the Switchboard options for this Page:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2. On Error GoTo EH
  3.     FillOptions
  4.     Exit Sub
  5. EH:
  6.     Exit Sub
  7.     MsgBox "There was an error moving to the current Record!  " & _
  8.         "Please contact your Database Administrator.", vbCritical, "Error!"
  9. End Sub
This must go in the OnCurrent Event of the Form, otherwise the initial Page will not populate properly.

Then, the associated Procedure to fill the Options for the Form:

Expand|Select|Wrap|Line Numbers
  1. Private Sub FillOptions()
  2. On Error GoTo EH
  3.     Dim dbOptions As Database
  4.     Dim rstOptions As Recordset
  5.     Dim strSQL As String
  6.     Dim intOption As Integer
  7.     Me.cmdOption1.SetFocus
  8.     For intOption = 2 To intButtons
  9.         Me("cmdOption" & intOption).Visible = False
  10.         Me("lblOption" & intOption).Visible = False
  11.     Next intOption
  12.     Set dbOptions = CurrentDb()
  13.     strSQL = "SELECT * FROM tblSwitchboards" & _
  14.         " WHERE User = " & gintUserRights & _
  15.         " AND ItemNumber > 0 AND SwitchboardID = " & Me.SwitchboardID & _
  16.         " ORDER BY ItemNumber;"
  17.     Set rstOptions = dbOptions.OpenRecordset(strSQL, dbOpenDynaset)
  18.     If rstOptions.EOF Then
  19.         Me.lblOption1.Caption = "There are no items for this Switchboard page"
  20.     Else
  21.         While Not rstOptions.EOF
  22.             Me("cmdOption" & rstOptions!ItemNumber).Visible = True
  23.             Me("lblOption" & rstOptions!ItemNumber).Visible = True
  24.             Me("lblOption" & rstOptions!ItemNumber).Caption = rstOptions!ItemText
  25.             rstOptions.MoveNext
  26.         Wend
  27.     End If
  28.     rstOptions.Close
  29.     dbOptions.Close
  30.     Set rstOptions = Nothing
  31.     Set dbOptions = Nothing
  32.     Exit Sub
  33. EH:
  34.     MsgBox "There was an error listing the Options on the Form!  " & _
  35.         "Please contact your Database Administrator.", vbOKOnly, "WARNING!"
  36.     Exit Sub
  37. End Sub
You may have noticed that the key clause for this code to work lies here:

Expand|Select|Wrap|Line Numbers
  1. " WHERE User = " & gintUserRights
Only the Switchboard Items that apply to that specific User will show up.

One last thing. How do we determine what to do when a Menu Item is clicked?

First, each Command Button and Option Label must have in its OnClick EvenT the following:

Expand|Select|Wrap|Line Numbers
  1. =SelectOption(x)
Where “x” is the specific number of the Command Button or Option Label (1-10). That will execute the following Function:

Expand|Select|Wrap|Line Numbers
  1. Private Function SelectOption(intOption As Integer)
  2. On Error GoTo EH
  3.     RestoreForm Me.Form.Name
  4.     Const optSwitchboard = 1
  5.     Const optFormAdd = 2
  6.     Const optFormBrowse = 3
  7.     Const optOpenReport = 4
  8.     Const optExit = 6
  9.     Const ErrCancelled = 2501
  10.     Dim dbOption As Database
  11.     Dim rstOption As Recordset
  12.     Dim strSQL As String
  13.     Set dbOption = CurrentDb()
  14.     strSQL = "SELECT * FROM tblSwitchboards" & _
  15.         " WHERE User = " & gintUserRights & _
  16.         " AND SwitchboardID = " & Me.SwitchboardID & _
  17.         "  AND ItemNumber=" & intOption & ";"
  18.     Set rstOption = dbOption.OpenRecordset(strSQL, dbOpenDynaset)
  19.     If Not rstOption.EOF Then
  20.         Select Case rstOption!Command
  21.             Case optSwitchboard
  22.                 Me.Filter = "User = " & gintUserRights & _
  23.                     " AND ItemNumber = 0" & _
  24.                     " AND SwitchboardID = " & rstOption!Argument
  25.                 Me.FilterOn = True
  26.             Case optFormAdd
  27.                 DoCmd.Close acForm, Me.Form.Name
  28.                 DoCmd.OpenForm rstOption!Argument, , , , acAdd
  29.                 GoTo SelectOption_Exit
  30.             Case optFormBrowse
  31.                 DoCmd.Close acForm, Me.Form.Name
  32.                 DoCmd.OpenForm rstOption!Argument
  33.                 GoTo SelectOption_Exit
  34.             Case optOpenReport
  35.                 DoCmd.OpenReport rstOption!Argument, acPreview
  36.                 GoTo SelectOption_Exit
  37.             Case optExit
  38.                 DoCmd.Quit
  39.             Case Else
  40.                 MsgBox "Unknown option."
  41.         End Select
  42.     Else
  43.         MsgBox "There was an error reading the Switchboards Table."
  44.         GoTo SelectOption_Exit
  45.     End If
  46. SelectOption_Exit:
  47. On Error Resume Next
  48.     rstOption.Close
  49.     dbOption.Close
  50.     Set rstOption = Nothing
  51.     Set dbOption = Nothing
  52.     Exit Function
  53. EH:
  54.     If (Err = ErrCancelled) Then
  55.         Resume Next
  56.     Else
  57.         MsgBox "There was an error executing the command.  " & _
  58.             "Please contact your Database Administrator", vbCritical
  59.         Resume SelectOption_Exit
  60.     End If
  61. End Function
If you have your Switchboard's Table set up properly, you should never experience any errors. But, just in case, there is error handling included (because I know you ALL include error handling in ALL your code, right?)

And that’s it! It’s really not too complicated, but perhaps some of you may have thought that it was too daunting a task to tackle. Now you have some options.

I’d also appreciate any feedback on better ways to execute this code. I’m always willing to learn!

Hope this hepps!
Attached Images
File Type: png Main Menu.png (65.6 KB, 33032 views)
Jun 12 '14 #1
Share this Article
Share on Google+
17 Comments


zmbd
Expert Mod 5K+
P: 5,397
Thank You Twinnyfo... this is certainly alot to read thru/

A few things about Access:
It is not a secure database in the same aspects as enterprise level RDMS such as Oracle, SQLServer, MySQL, etc..

Anything done at the Access interface level can be undone/bypassed by someone like myself, or for that fact, any reasonably advanced Access user (I would suspect that Twinnyfo could do so with his eyes shut (^_^) )

Thus, passwords and other sensitive information (SSN,TaxID,etc...) should never ever be stored in plan text within the database tables... even if the database is using encryption, one should encrypt the field value too!

As a supplement to this article I would recommend reading at least the following:

Public Variables:
If you are using Access 2010 or later, I suggest that one transition to the TempVars collection.
(TempVars Object
Unlike the public variable, these will hold their values in case the code errors (often the public/global variables will dump their value in an error condition) usually only losing the set value upon an explicit command to delete, reset, or the program is shutdown. Another advantage is that the Tempvar collection values can be directly referenced by queries and other ms-access-database objects.

And for those that would like to add a PIN or Passphrase to the user:
I for one, actually use SHA2 or MD5 along with a salt and user input to hash the User name in my table:
SHA2(username&password&(SHA2(rndSalt)) ==> stored value
I then Store the SHA2(username) and the salt value in one table so that the random salt can be determined later.
Thus, neither the actual user name nor their passwords are ever actually in the database.

I also never store the user name and the group privilege level in plan text. I'll use the API call to retrieve the current PC user information and SHA2 that for storage along with the digest of the User+Group. Otherwise it is all too easy for a user to simply elevate privilege in the user table by simply changing the record values.
Jun 14 '14 #2

twinnyfo
Expert Mod 2.5K+
P: 3,284
Z is absolutely correct concerning security in MS Access: There is none!

However, our security is managed at the macro level. No user can even get into any of our systems without secure authentication using Smart Cards. Second, when an authorized user does get into our Network, their permissions are restricted concerning which locations they can access. If they are allowed access to our particular network location, then I control which type of access they have to our DB.

Granted, as Z states, just about anyone with any Access experience can hack into our back ends if they really wanted to. But, again, we are not dealing with hostile or malicious employees, just overworked ones....

This is not a truly "secure" solution, but a practical one. My article was focusing on the capability to code in custom user permissions that are not built into Access. In our world, we have a lot of people with Access databases which are truly "open", so one does not even need Access experience to edit data--or even delete a table "by accident".

I'm just trying to prevent the accidents.

But, if you want a secure DB, Z is correct, go with Oracle or one of the others (and have a big budget!).
Jun 16 '14 #3

NeoPa
Expert Mod 15k+
P: 31,495
I'm not sure you're quite getting what Z is trying to say there Twinny. Many of the ideas that he's suggesting are highly recommended for use in Access-only situations. He's already provided code for handling the basic encryption for you (and other readers of this excellent article). I've changed my systems to use some of his code and it wasn't difficult. All discoverable from Bytes.com.

Storing user names or account IDs in plain text is not a great idea, but not as critical as storing the passwords. As Z says, you should only ever store the encrypted values for passwords and when you check them you do a lookup using the return value from the encryption function itself with the parameter value of whatever the user enters onto the form.

Why is that so important?
Beyond the obvious point of making it much more secure with very little effort, you should also bear in mind that when people use passwords they often use the same ones for various different systems. This means that the responsibility for keeping them obscured goes well beyond the sometimes limited security requirements of the current system.

PS. I just looked again (as I couldn't see it first time through) to realise you're not handling security with a basic password system but using AD to integrate into your project. A good approach I use myself when I can.

However, when using this approach it makes good sense to use Function to Return UserName (NT Login) of Current User rather than the Environment variable as that is so easily hackable - without even any Access understanding.

As I say, this option isn't universally appropriate, but when it is it makes very good sense to utilise it.
Jun 26 '14 #4

twinnyfo
Expert Mod 2.5K+
P: 3,284
Good advice from all....

I will work to incorporate the alternate method for getting the UserName--although I doubt any of our systems would work if one were to log in with their smart Card and then change system's UserName to something else--but I guess it might be possible for them to do that and still operate their system. Why anyone here would want to do that so see a bunch of lists about Air Force Promotions is beyond me....

I should also clarify a satement I made in Post #3:

In our world, we have a lot of people with Access databases which are truly "open", so one does not even need Access experience to edit data--or even delete a table "by accident".

I'm just trying to prevent the accidents.
Those "wide-open" databases that people are using in our world... none of them were created nor maintained by me. I have yet to make a DB that is fully Gorilla-proof, but I've graduated from chimpanzee to orangutan!

My (very small) list of users who have network access to my DB, know that they can only do what I allow them to do. And I know them all well enough that they don't have the skills to crack encryption codes and hashes and mess with back ends. They don't even know what those words mean!

But, again, good info and advice. I am always working to improve what I have, and this is at least one improvement. Many thanks!
Jun 26 '14 #5

NeoPa
Expert Mod 15k+
P: 31,495
My advice was less about your own situation Twinny, than about the very large number of situations found by the very large number of people that will come here for help getting their systems to work. This is now, whether you realise it yet or not, a place where many many people will come looking for help in this area. Congratulations on a job well-done!
Jun 26 '14 #6

P: 1
Hi Twinnfo, awesome example, is it possible for you to send me or post a link to download the database example used in this so i can visually see it to help me in my project

thanks
Feb 16 '15 #7

NeoPa
Expert Mod 15k+
P: 31,495
That would be accomplished by attaching a ZIPped database to the thread. We'll see if that's something Twinny is happy to do for you.
Feb 16 '15 #8

twinnyfo
Expert Mod 2.5K+
P: 3,284
NeoPa and nytewing,

I'll see if I can do that once I get back to work. Just popped on to the site for a moment to see what was going on. Currently enjoying some much needed Island Time in Hawai'i...
Feb 19 '15 #9

twinnyfo
Expert Mod 2.5K+
P: 3,284
nytewing,

Attached you will see a compressed zip file of the requested DB. Please note that this DB will not work as is. You must first add an entry in the Table tblUsers for yourself. This is required for every user you want to add to the DB.

Additionally, you must manually update any changes in the Table tblSwitchboards, as I have not yet created a Switchboard manager (like the old MS version). However, since my menus very seldom change, I haven't put the effort into it.

I've also left a few bells and whistles in, but took out most other bells and whistles, as they made the DB too complex for an intro to Users and Permissions.

Hope this file is valuable for you. If anything breaks or you can't understand anything within, just let me know and I'll update and re-post.

Hope this hepps!
Attached Files
File Type: zip UserPermissions.zip (85.0 KB, 1345 views)
Feb 25 '15 #10

P: 2
Hello,

I've got a question about the database.
I don't understand how the users need to login, cause there isn't a login form or anything like that.

Kind regards,
Patrick
Sep 28 '15 #11

twinnyfo
Expert Mod 2.5K+
P: 3,284
Patrick,

The login is based on the Windows Userid. When you have networked systems, all your users will have unique usernames, so that permissions may be set for various network resources. The database determines the username. The DBA would set the permissions for those wishing to access the database.

Hope this hepps.
Sep 28 '15 #12

P: 2
Copy that, at the moment we have a local network with only Local user accounts, however we're planning to go on controled domain. For now I'll just make a login form and check every form according you're user tables :)
Sep 29 '15 #13

P: 3
The code works great for controlling access, I have one question. Something is causing the database to minimize every time I click on a control on the switchboard. Any idea what is causing this?
Nov 24 '15 #14

twinnyfo
Expert Mod 2.5K+
P: 3,284
Blade8639,

This is intentional. The DB should always appear as "floating" forms on top of other applications. As long as your forms are modal and pop up, this should not be a problem.
Nov 25 '15 #15

P: 3
I tried putting everything in pop up mode and modal and the forms work fine but it is causing issues with some of the reports. I plan for other securities on the database including putting it on an sql server and restricting who can see it. The navigation bar won't be visible and no one will have access to the back end.

How do I turn off? I will play with the reports some more to see if I can get them to work with those options but I would like to know what is causing it.
Nov 25 '15 #16

twinnyfo
Expert Mod 2.5K+
P: 3,284
Blade,

In the frmSwitchboard, in the SelectOption sub, it calls the following line:

Expand|Select|Wrap|Line Numbers
  1. RestoreForm Me.Form.Name
simply comment out that line and you should be good to go. You can also comment out other occurrences of that code throughout the DB.

Hope this hepps!
Nov 25 '15 #17

P: 3
Everything is working now. Thank you very much.
Nov 25 '15 #18