Restrict access of Switchboard item without manual user login...

[avincent61]

Long story short...Users do not want to manually login to databases, but some objects need to be limited to a handful of users.
I created double the amount of forms to ensure those without explicit access could only View data. In this particular DB, it was easier to break it up this way.

I have a "Personnel" table that includes "UserLogin" & "UserSecurity" fields.
I added hidden, unbound text boxes of the same name to my Switchboard and entered code in Form Load to capture the desired information.
This part works perfectly....

Private Sub Form_Load()

    gblWindowsLoginName = CreateObject("WScript.Network").UserName
    Call RibbonsNavPaneHide
   
    Dim User As String
    User = Environ("Username")
    Me.txtUserLogin = User
    Me.txtUserSecurity = DLookup("UserSecurity", "Personnel", "UserLogin='" & Forms!Switchboard!txtUserLogin & "'")
              
End Sub

Now, I need to use that captured information to Allow or Deny access to a specific menu of the Switchboard, called "Programs". This sub-Switchboard accesses forms that edit the table data, which only need to be accessed by 5 total users.
I want to put a Public If statement in my Module and simply reference that code to run on the "Programs" button click via the Switchboard Manager.
Below is as far as could get and I don't have any Error Handling added yet either.

Public Function ProgramsMenu()

    If txtUserSecurity = 2 Then
        '???????????

    Else
        MsgBox "Please use the Public Menu", vbOKOnly, "Access Denied"
                
End Function

 

[Gasman]

I restricted the items in the switchboard to relevant users.
You still need someway of identifying users though?

 

[avincent61]

I restricted the items in the switchboard to relevant users.
You still need someway of identifying users though?

I hope I'm understanding your question...

Users are identified in the "Personnel" table with either 1, 2, or 3 security level and the switchboard pulls the Global Username and the Security Level when it opens.
When a user clicks the "Programs" button in the Switchboard, I want it to check their security level and either allow access or display a MsgBox that tells them to go to a different menu.

 

[theDBguy]

Hi. How exactly are you identifying the users in the Personnel table? If you're using their "network login," then using your Environ("Username") code should allow you to check their permission levels and apply the appropriate security measures.

 

[Gasman]

I hope I'm understanding your question...

Users are identified in the "Personnel" table with either 1, 2, or 3 security level and the switchboard pulls the Global Username and the Security Level when it opens.
When a user clicks the "Programs" button in the Switchboard, I want it to check their security level and either allow access or display a MsgBox that tells them to go to a different menu.

That is my point. I did not even show any options that a user was not allowed to use.

 

[avincent61]

Hi. How exactly are you identifying the users in the Personnel table? If you're using their "network login," then using your Environ("Username") code should allow you to check their permission levels and apply the appropriate security measures.

Hi!
To answer your question: I have a "Personnel" table that includes "UserLogin" & "UserSecurity" fields.
I have no issues with the code that pulls the info when the database loads the Switchboard form.

My trouble is how to code the first part of the "If" action when the button is clicked....under: If txtUserSecurity = 2 Then ?????????

Public Function ProgramsMenu()

If txtUserSecurity = 2 Then
???????????
Else
MsgBox "Please use the Public Menu", vbOKOnly, "Access Denied"

End Function

I couldn't change the font when using the code box, so I just posted it straight up.

 

[avincent61]

That is my point. I did not even show any options that a user was not allowed to use.

Haha, yes, that's the code that I'm asking for help with.
I think an If statement is the easiest way to do it, but I don't know how to code the action if the user has Level 2 security.

If txtUserSecurity = 2 Then
**This is the line I don't know how to code.
Else (this code is fine)
MsgBox "Please use the Public Menu", vbOKOnly, "Access Denied" (this code is fine)

 

[Gasman]

Well, what do you want to do when the value is 2?

 

[avincent61]

Well, what do you want to do when the value is 2?

I want the user to access the "Programs" switchboard menu. If it was just opening a form or report, I'd have no problem, but i don't know how to code "let them access the menu item they clicked on" Lol

 

[Gasman]

Well I would likely do the opposite.
If not 2, then display a message, otherwise let the function run as it would normally.

 

[Pat Hartman]

Here's a custom switchboard that will give you a place to start. It includes basic security. It assumes everyone logs in, I can give you options for silent login that just gives people view only security. You can create a dummy user and if the user picks login without entering credentials, you silently log him in as the "dummy" user which will have only minimal view security and no update authorization.

Switchboard with simple security

This is a custom switchboard. It expands on the Switchboard Items table to add four additional columns to define view/edit/add/delete security levels for switchboard items and forms/reports referenced by them. It includes tables to manage users and also to assign them security levels. The user...

www.access-programmers.co.uk

I strongly suggest that you remove all the duplicate forms you created and switch to a method like this one.

 

[avincent61]

Thanks for your reply Pat! Not sure why, but it wouldn't let me reply to your post.
I have other databases that run off of a "one form for everyone" type of method, but I really struggled with the coding when I had to make some forms read only for only some users.
I'm looking at the link you provided now, maybe it was the answer I needed in my other database. I'll definitely report back.

 

[avincent61]

Thanks again Pat!
After review, your set up is indeed what I am looking for, just more complex than I'd like Hahaha.
It will take me some time to implement it (translating is slow work) but I'm sure I'll get it.
Permission to come to you with any questions?

 

[Pat Hartman]

If you don't know how to code, every solution is complex but this one is about as simple as it gets.

There are four types of security - read, edit, add, delete. Each person is assigned four numeric values from 0-9. Each menu is assigned a read level, the other three default to 0 behind the scenes to keep things simple. Each form/report is assigned the four numeric values from 0-9 using the switchboard maintenance form.

In the forms that need to check security, you have an If statement in
The Current Event to control read
The BeforeUpdate Event to control edit
The BeforeInsert Event to control add
The BeforeDeleteConfirm event to control delete

To start defining security, divide people into groups and objects into groups. Otherwise you'll drive yourself crazy. Three groups will probably be enough for users no matter how many you have. And three groups for objects is also probably sufficient. That limits your matrix to 3x3 which is manageable. Use mid range values where possible so you can leave yourself room for higher/lower levels for special conditions.

 

[avincent61]

If you don't know how to code, every solution is complex but this one is about as simple as it gets.

There are four types of security - read, edit, add, delete. Each person is assigned four numeric values from 0-9. Each menu is assigned a read level, the other three default to 0 behind the scenes to keep things simple. Each form/report is assigned the four numeric values from 0-9 using the switchboard maintenance form.

In the forms that need to check security, you have an If statement in
The Current Event to control read
The BeforeUpdate Event to control edit
The BeforeInsert Event to control add
The BeforeDeleteConfirm event to control delete

To start defining security, divide people into groups and objects into groups. Otherwise you'll drive yourself crazy. Three groups will probably be enough for users no matter how many you have. And three groups for objects is also probably sufficient. That limits your matrix to 3x3 which is manageable. Use mid range values where possible so you can leave yourself room for higher/lower levels for special conditions.

I'm no expert, but I've been learning as I go for 3+ years (with still lots to learn). The current switchboard code I have was outsourced. I am able to modify one or two spots, but the rest I'm essentially afraid to mess with.

 

[avincent61]

If you don't know how to code, every solution is complex but this one is about as simple as it gets.

There are four types of security - read, edit, add, delete. Each person is assigned four numeric values from 0-9. Each menu is assigned a read level, the other three default to 0 behind the scenes to keep things simple. Each form/report is assigned the four numeric values from 0-9 using the switchboard maintenance form.

In the forms that need to check security, you have an If statement in
The Current Event to control read
The BeforeUpdate Event to control edit
The BeforeInsert Event to control add
The BeforeDeleteConfirm event to control delete

To start defining security, divide people into groups and objects into groups. Otherwise you'll drive yourself crazy. Three groups will probably be enough for users no matter how many you have. And three groups for objects is also probably sufficient. That limits your matrix to 3x3 which is manageable. Use mid range values where possible so you can leave yourself room for higher/lower levels for special conditions.

My code is below. I have tried to use 'Option Explicit' in this code and it basically kills the switchboard. Not sure why. I do have it on my other objects though.
Some items are the same as your code (albeit in different places), but a lot is set up differently. Would I need to mirror your code entirely for this to work out?

Option Compare Database

Private Sub Form_Load()

    gblWindowsLoginName = CreateObject("WScript.Network").UserName
    Call RibbonsNavPaneHide
   
End Sub

Private Sub Form_Open(Cancel As Integer)
' Minimize the database window and initialize the form.

    ' Move to the switchboard page that is marked as the default.
    Me.Filter = "[ItemNumber] = 0 AND [Argument] = 'Default' "
    Me.FilterOn = True

 
End Sub

Private Sub Form_Current()
' Update the caption and fill in the list of options.

    Me.Caption = Nz(Me![ItemText], "")
    FillOptions
   
End Sub

Private Sub FillOptions()
' Fill in the options for this switchboard page.

    ' The number of buttons on the form.
    Const conNumButtons = 8
   
    Dim con As Object
    Dim rs As Object
    Dim stSql As String
    Dim intOption As Integer
       
    ' Set the focus to the first button on the form,
    ' and then hide all of the buttons on the form
    ' but the first.  You can't hide the field with the focus.
    Me![Option1].SetFocus
    For intOption = 2 To conNumButtons
        Me("Option" & intOption).Visible = False
        Me("OptionLabel" & intOption).Visible = False
    Next intOption
   
    ' Open the table of Switchboard Items, and find
    ' the first item for this Switchboard Page.
    Set con = Application.CurrentProject.Connection
    stSql = "SELECT * FROM [Switchboard Items]"
    stSql = stSql & " WHERE [ItemNumber] > 0 AND [SwitchboardID]=" & Me![SwitchboardID]
    stSql = stSql & " ORDER BY [ItemNumber];"
    Set rs = CreateObject("ADODB.Recordset")
    rs.Open stSql, con, 1   ' 1 = adOpenKeyset
   
    ' If there are no options for this Switchboard Page,
    ' display a message.  Otherwise, fill the page with the items.
    If (rs.EOF) Then
        Me![OptionLabel1].Caption = "There are no items for this switchboard page"
    Else
        While (Not (rs.EOF))
            Me("Option" & rs![ItemNumber]).Visible = True
            Me("OptionLabel" & rs![ItemNumber]).Visible = True
            Me("OptionLabel" & rs![ItemNumber]).Caption = rs![ItemText]
            rs.MoveNext
        Wend
    End If

    ' Close the recordset and the database.
    rs.Close
    Set rs = Nothing
    Set con = Nothing

End Sub

Private Function HandleButtonClick(intBtn As Integer)
' This function is called when a button is clicked.
' intBtn indicates which button was clicked.

    ' Constants for the commands that can be executed.
    Const conCmdGotoSwitchboard = 1
    Const conCmdOpenFormAdd = 2
    Const conCmdOpenFormBrowse = 3
    Const conCmdOpenReport = 4
    Const conCmdCustomizeSwitchboard = 5
    Const conCmdExitApplication = 6
    Const conCmdRunMacro = 7
    Const conCmdRunCode = 8
    Const conCmdOpenPage = 9
    'Const conCmdPrograms = 10

    ' An error that is special cased.
    Const conErrDoCmdCancelled = 2501
   
    Dim con As Object
    Dim rs As Object
    Dim stSql As String
    Dim strPass As String

On Error GoTo HandleButtonClick_Err

    ' Find the item in the Switchboard Items table
    ' that corresponds to the button that was clicked.
    Set con = Application.CurrentProject.Connection
    Set rs = CreateObject("ADODB.Recordset")
    stSql = "SELECT * FROM [Switchboard Items] "
    stSql = stSql & "WHERE [SwitchboardID]=" & Me![SwitchboardID] & " AND [ItemNumber]=" & intBtn
    rs.Open stSql, con, 1    ' 1 = adOpenKeyset
   
    ' If no item matches, report the error and exit the function.
    If (rs.EOF) Then
        MsgBox "There was an error reading the Switchboard Items table."
        rs.Close
        Set rs = Nothing
        Set con = Nothing
        Exit Function
    End If
   
    Select Case rs![Command]
       
        ' Go to another switchboard.
        Case conCmdGotoSwitchboard
            'If there's a password specified for the switchboard, prompt the user for it and verify.
            strPass = Nz(DLookup("Auth", "Switchboard Items", "[SwitchboardID] = " & rs![Argument] & " AND [Command] = 0"), "")

            If Len(strPass) > 0 Then
            'If the entry is incorrect, notify the user and exit.
                If InputBox("Please enter the password for this option.", "Password required ...") <> strPass Then
                    MsgBox "That password is incorrect. Please verify the password and try again.", vbOKOnly
                    Exit Function
                End If
            End If
           
            Me.Filter = "[ItemNumber] = 0 AND [SwitchboardID]=" & rs![Argument]
           
        ' Open a form in Add mode.
        Case conCmdOpenFormAdd
            DoCmd.OpenForm rs![Argument], , , , acAdd

        ' Open a form.
        Case conCmdOpenFormBrowse
            DoCmd.OpenForm rs![Argument]

        ' Open a report.
        Case conCmdOpenReport
            DoCmd.OpenReport rs![Argument], acPreview

        ' Customize the Switchboard.
        Case conCmdCustomizeSwitchboard
            ' Handle the case where the Switchboard Manager
            ' is not installed (e.g. Minimal Install).
            On Error Resume Next
            Application.Run "ACWZMAIN.sbm_Entry"
            If (Err <> 0) Then MsgBox "Command not available."
            On Error GoTo 0
            ' Update the form.
            Me.Filter = "[ItemNumber] = 0 AND [Argument] = 'Default' "
            Me.Caption = Nz(Me![ItemText], "")
            FillOptions

        ' Exit the application.
        Case conCmdExitApplication
           
            If InStr("avincent|lrifici|kmullen", gblWindowsLoginName) > 0 Then
                DoCmd.ShowToolbar "Ribbon", acToolbarYes     'To show the ribbon
                'DoCmd.SelectObject acTable, , True
                DoCmd.SelectObject acTable, "tblBPMI", True  ' replace with the name of one of your tables
                DoCmd.RunCommand acCmdWindowShow
                DoCmd.Close
                Exit Function
            Else
                CloseCurrentDatabase
            End If
       
        ' Run a macro.
        Case conCmdRunMacro
            DoCmd.RunMacro rs![Argument]

        ' Run code.
        Case conCmdRunCode
            Application.Run rs![Argument]

        ' Open a Data Access Page
        Case conCmdOpenPage
            DoCmd.OpenDataAccessPage rs![Argument]

        ' Any other command is unrecognized.
        Case Else
            MsgBox "Unknown option."
   
    End Select

    ' Close the recordset and the database.
    rs.Close
   
HandleButtonClick_Exit:
On Error Resume Next
    Set rs = Nothing
    Set con = Nothing
    Exit Function

HandleButtonClick_Err:
    ' If the action was cancelled by the user for
    ' some reason, don't display an error message.
    ' Instead, resume on the next line.
    If (Err = conErrDoCmdCancelled) Then
        Resume Next
    Else
        MsgBox "There was an error executing the command.", vbCritical
        Resume HandleButtonClick_Exit
    End If
   
End Function

 

[Gasman]

Coding without Option Explicit is like walking a tightrope over the Grand Canyon, blindfolded.

You have to know what you are doing.

 

[Pat Hartman]

If you can't add option explicit without error, then the code is bad. You will need to define the undefined variables to get the code to compile. If you keep the switchboard, you should just do that anyway.

"my" switchboard does not have a fixed number of items on each page which means that it can take advantage of the security settings and only display menu items that a person is authorized to see. But the important security that controls who can update what is handled by the individual forms, not by the switchboard so no, you don't need to use my switchboard if you are OK with showing ever person, every option available from the switchboard.

 

[avincent61]

If you can't add option explicit without error, then the code is bad. You will need to define the undefined variables to get the code to compile. If you keep the switchboard, you should just do that anyway.

"my" switchboard does not have a fixed number of items on each page which means that it can take advantage of the security settings and only display menu items that a person is authorized to see. But the important security that controls who can update what is handled by the individual forms, not by the switchboard so no, you don't need to use my switchboard if you are OK with showing ever person, every option available from the switchboard.

Here's the error:

If I click the exit command, the ribbon and navpane unhide.
For anyone else, it exits the application.

I replaced with 'acCmdWindowUnhide' and get this error on execution, though it still performs the desired function. *starts pulling out hair*

 

[avincent61]

If you can't add option explicit without error, then the code is bad. You will need to define the undefined variables to get the code to compile. If you keep the switchboard, you should just do that anyway.

"my" switchboard does not have a fixed number of items on each page which means that it can take advantage of the security settings and only display menu items that a person is authorized to see. But the important security that controls who can update what is handled by the individual forms, not by the switchboard so no, you don't need to use my switchboard if you are OK with showing ever person, every option available from the switchboard.

I changed it to 'acCmdWindowHide = False' in that line and it resolved
As I said... Learning as I go.