Solved Security for buttons in switchboard

[oxicottin]

Hello, I have a switchboard I been building and im now stuck at its security and where to put the code to check for security. Well I know where its just not working and only runs through Case 1. What I want is each page has a security of 1, 2 or 3 and if your security is a 1 then you can only see SecurityLevel 1 and if your a 2 then you can see SecurityLevel 1 and 2 and if your a 3 then you can see SecurityLevel 1, 2 and 3.

I tried adding the code below to the [frm_Switchboard] Private Sub FillOptions() in place of and it just runs through Case 1.

    'Assign a security level number to variable Security
    SecurityLevel = DLookup("UserSecurityType", "tbl_LoginUser", "[fOSUserLoginName] = '" & fOSUserName() & "'")

   Dim SecurityLevel As Integer
    Select Case SecurityLevel

    Case 1    'Show buttons only for SecurityLevel 1
            Me("Option" & rs![MenuSequentialNumber]).Visible = True
            Me("OptionLabel" & rs![MenuSequentialNumber]).Visible = True
            Me("OptionLabel" & rs![MenuSequentialNumber]).Caption = rs![MenuPageTitle]
            rs.MoveNext
    Case 1, 2    'Show buttons only for SecurityLevel 1, 2
            Me("Option" & rs![MenuSequentialNumber]).Visible = True
            Me("OptionLabel" & rs![MenuSequentialNumber]).Visible = True
            Me("OptionLabel" & rs![MenuSequentialNumber]).Caption = rs![MenuPageTitle]
            rs.MoveNext
    Case 1, 2, 3  'Show buttons only for SecurityLevel 1, 2, 3
            Me("Option" & rs![MenuSequentialNumber]).Visible = True
            Me("OptionLabel" & rs![MenuSequentialNumber]).Visible = True
            Me("OptionLabel" & rs![MenuSequentialNumber]).Caption = rs![MenuPageTitle]
            rs.MoveNext
            
    Case Else
         'If you dont have a security level then make it same as Case 1
    End Select
    Wend
    End If

 

[isladogs]

The normal approach would be three separate sections for case 1, case 2 & case 3.
As your code is identical for each section, there is no point having separate sections.

You need to think this through again.

 

[oxicottin]

@isladogs are you saying not only use the code in each case use its own Sql that would give me the results im looking for?

 

[isladogs]

Sorry. Don't understand your question.

 

[Pat Hartman]

   'Assign a security level number to variable Security
    SecurityLevel = DLookup("UserSecurityType", "tbl_LoginUser", "[fOSUserLoginName] = '" & fOSUserName() & "'")

   Dim SecurityLevel As Integer
    Select Case SecurityLevel

    Case 1    'Show buttons only for SecurityLevel 1
            Me("Option" & rs![MenuSequentialNumber]).Visible = True
            Me("OptionLabel" & rs![MenuSequentialNumber]).Visible = True
            Me("OptionLabel" & rs![MenuSequentialNumber]).Caption = rs![MenuPageTitle]
            rs.MoveNext
    Case 2    'Show buttons only for SecurityLevel 1, 2
            Me("Option" & rs![MenuSequentialNumber]).Visible = True
            Me("OptionLabel" & rs![MenuSequentialNumber]).Visible = True
            Me("OptionLabel" & rs![MenuSequentialNumber]).Caption = rs![MenuPageTitle]
            rs.MoveNext
    Case 3  'Show buttons only for SecurityLevel 1, 2, 3
            Me("Option" & rs![MenuSequentialNumber]).Visible = True
            Me("OptionLabel" & rs![MenuSequentialNumber]).Visible = True
            Me("OptionLabel" & rs![MenuSequentialNumber]).Caption = rs![MenuPageTitle]
            rs.MoveNext
            
    Case Else
         'If you dont have a security level then make it same as Case 1
    End Select
    Wend
    End If

 

[The_Doc_Man]

Let's do this...

You have a list of buttons that will be visible for security levels 1, or 1 & 2, or 1 & 2 & 3. You have an input of some kind that gives you the security level in force at the moment (presumably via lookup from a user table).

Dim Lv1 as Boolean, Lv2 as Boolean, Lv3 as Boolean

...
Lv1 = SecurityLevel < 2
Lv2 = SecurityLevel < 3
Lv3 = True
...
Me.Option1.Visible = Lv1
Me.Option1Label.Visible = Lv1
Me.Option2.Visible = Lv2
Me.Option2Label.Visible = Lv2
Me.Option3.Visible = Lv3
Me.Option3Label.Visible = Lv3

This code makes level 1 items visible when at security level 1 and security level 0. Level 2 items are visible when at security levels 0, 1, and 2. When at level 3, all items are visible all of the time.

No IF ladders or CASE ladders required. What you want to do with that caption doesn't make sense as it was originally written, but if you make something invisible, it doesn't really matter what its caption happens to be.

 

[oxicottin]

@The_Doc_Man I think I see what your saying for the Boolean but Im not getting how the 3rd section "Me.Option1.Visible = Lv1 ect." would work, I would think I would hav to do that for every Option and OptionLabel for each Lv1,2 and 3. Why wouldn't the below work using some of your example?

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

'The number of buttons on the frm_Switchboard form.
    Const conNumButtons = 9

    Dim con As Object
    Dim rs As Object
    Dim stSql As String
    Dim intOption As Integer
    Dim Lv1 As Boolean, Lv2 As Boolean, Lv3 As Boolean
    Dim SecurityLevel As Integer
   
     'Assign a security level number to variable Security
    SecurityLevel = DLookup("UserSecurityType", "tbl_LoginUser", "[fOSUserLoginName] = '" & fOSUserName() & "'")
   
    Lv1 = SecurityLevel < 2
    Lv2 = SecurityLevel < 3
    Lv3 = True

    'Set the focus then hide all of the buttons on the form _
     but the first.  You can't hide the field with the focus.

    AutoFit Me.txtMenuPageHeading    'Expands Page Heading control to fit text
    Me.cmdDummy.SetFocus    'Set focus to hidden btn

    For intOption = 2 To conNumButtons
        Me("Option" & intOption).Visible = False
        Me("OptionLabel" & intOption).Visible = False
    Next intOption

    'Open tbl_MenuItems and find the first item for this Menu Page.
    Set con = Application.CurrentProject.Connection
    stSql = "SELECT * FROM [tbl_MenuItems]"
    stSql = stSql & " WHERE [MenuSequentialNumber] > 0 AND [MenuID]=" & Me![MenuID]
    stSql = stSql & " ORDER BY [MenuSequentialNumber];"
    Set rs = CreateObject("ADODB.Recordset")
    rs.Open stSql, con, 1   ' 1 = adOpenKeyset

    'If there are no options for this Menu Page then _
     display a message. Otherwise, fill the page with the menu items.
    If (rs.EOF) Then
        Me![OptionLabel1].Caption = "There are no items for this Menu page"
    Else
        While (Not (rs.EOF))
             Me("Option" & rs![MenuSequentialNumber]).Visible = Lv1
             Me("Option" & rs![MenuSequentialNumber]).Visible = Lv2
             Me("Option" & rs![MenuSequentialNumber]).Visible = Lv3
             Me("OptionLabel" & rs![MenuSequentialNumber]).Visible = Lv1
             Me("OptionLabel" & rs![MenuSequentialNumber]).Visible = Lv2
             Me("OptionLabel" & rs![MenuSequentialNumber]).Visible = Lv3
             Me("OptionLabel" & rs![MenuSequentialNumber]).Caption = rs![MenuPageTitle]
            rs.MoveNext
        Wend
    End If

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

End Sub

 

[isladogs]

Another possible solution is to use the Tag property of each control to determine whether visible or not.
I use this idea extensively in most of my apps.

Have a look at my example app Set Controls to see the idea in action.
It includes a boolean function ShowControls a(s well as EnableControls & LockControls) to manage the state of a group of controls together

In your case, all controls visible to users on level 1 could be tagged "1", those for level 2 managers tagged "2" and the rest for admins only tagged "3"

Then use code similar to this in the Form_Load event.

Private Sub Form_Load

'hide all tagged controls by default at form load'
ShowControls False "1","2","3"

'now show only those controls for each user level
Select Case SecurityLevel

Case 1
ShowControls True, "1"

Case 2
ShowControls True, "1", "2"

Case 3
ShowControls True "1","2","3"

Case Else
ShowControls True, "1"

End Select

End Sub

No other code is needed. Hope that helps

 

[arnelgp]

make it simple.
modify the Structure of tbl_MenuItems, add another Column (example the fieldname is MnuAccess, short text).
put the SecurityID that can access a particular menu item

example:
on main menu, "Administration" can only be accessed by the Administrator (SecurityID=3), so you put 3 on MnuAccess field on
that record. If you allow SuperUser to access that menu item, you add 2 (the content now of the field is "3, 2").

you also modify the SQL on the Form's code to include in the Filter MnuAccess Column:

example, there is code on the Opening of the form, so the new code will be:

Private Sub Form_Open(Cancel As Integer)
    'Display users login name in border
    Dim id As Long
    Me.Caption = "User: " & fOSUserName()
    
    id = Nz(Dlookup("UserSecurity", "tbl_LoginUser", "fOSUserLoginName='" & Me.Caption & "'"), 1)
    'Set focus to dummy button
    Me.cmdDummy.SetFocus

    'Move to the Menu page that is marked as the default.
    Me.Filter = "[MenuSequentialNumber] = 0 AND [Argument] = 'Default' And MnuAccess Like '*" & id & "*'"
    Me.FilterOn = True

End Sub

therefore you minimized the Thinking part and you upgrade the code to be more generic.

 

[oxicottin]

Ok I'm going to step back and try and figure out what you guys are trying to explain because I think I entered the correct code but im still getting the same data. I don't understand the On_Open OR On_Load event being used because that's not running through the strSQL in the FillOptions Sub to show what buttons You allowed for the SecurityLevel you are. I'm confused....

 

[isladogs]

If you use my approach, you don't need your FillOptions sub except for the line to obtain the SecurityLevel
Move that to a function in a standard module

Public Function SecurityLevel()

    'Assign a security level number to variable Security
    SecurityLevel = DLookup("UserSecurityType", "tbl_LoginUser", "[fOSUserLoginName] = '" & fOSUserName() & "'")

End Function

Once you have done that, the SecurityLevel is available in any database object so my tag code should work as written..
The only other thing needed is the ShowControls procedure which is part of modControlState in my example app.

Hope that clarifies a few things

 

[arnelgp]

yes, i think you should step back.
first you need to find a way to make SecurityLevel on tbl_MenuItems into a Short Text.
why short text? to allow multiple SecurityLevel to access that particular menu item.

example:

if you are going to allow "All" users to access a Menu Item (the menu item is visible), SecurityLevel content is "1, 2, 3"
if only the Administrator has access (therefore visible only to him), SecurityLevel content is only "3".
if both Admin and SuperUser has access, SecurityLevel content "2, 3".

now, with this technique you Do Not need any Condition on Any Code (If SecurityLevel = 3... or Case SecurityLevel 1,2, etc).
all of the code in the Switchboard uses Filtered Recordset to display the menu.
therefore, you only Add to this Filter your SecurityLevel Column.

example:
(code on FillOptions() sub)

stSql = "SELECT * FROM [tbl_MenuItems]"
stSql = stSql & " WHERE [MenuSequentialNumber] > 0 AND [MenuID]=" & Me![MenuID]
stSql = stSql & " ORDER BY [MenuSequentialNumber];"

you change it to:

Dim lngID As Long
lngID = Nz(Dlookup("UserSecurity", "tbl_LoginUser", "fOSUserLoginName='" & Me.Caption & "'"), 1)

stSql = "SELECT * FROM [tbl_MenuItems]"
stSql = stSql & " WHERE [MenuSequentialNumber] > 0 AND [MenuID]=" & Me![MenuID] & " AND Instr(1, SecurityLevel, '" & lngID & "') > 0"
stSql = stSql & " ORDER BY [MenuSequentialNumber];"

----
it's is simple.
there is no If SecurityLevel = ...
or Case SecurityLevel ..

 

[The_Doc_Man]

Im not getting how the 3rd section "Me.Option1.Visible = Lv1 ect." would work, I would think I would hav to do that for every Option and OptionLabel for each Lv1,2 and 3.

Yes, you would. My method didn't involve an IF/THEN or CASE because the logic was based on defining three flags that had truth values that were in turn based on the current security level (i.e. the input value of overall security level).

My method is that you have your flags refer to the flag corresponding to the level of the control. So if Option1 is governed by security level 1, you ALWAYS test against the level 1 flag. If Option 2 is governed by security level 2, you always test against the level 2 flag. Similarly for the level 3 flag and Option 3. The point is that those flag values contain all the logic you need and the rest of the operation is essentially fixed. Once you get the flags set and have decided where each option belongs (security 1, 2, or 3), no more decisions. Everything can be static rather than you trying to synthesize names to figure out what you are looking at.

You have at least three different viewpoints here, but a little hint: EVERY ONE OF THEM is a viable method. The people proposing them (including me and my suggestion) have done this kind of thing before. You get to decide which approach appeals to you. Mine might not be what you like, and that's OK. All I hope to do is give you and other readers some ideas. And the others who are offering ideas have the same goal. So take your pick.

 

[Uncle Gizmo]

Another possible solution is to use the Tag property

I also use the Tag property, however it can get a bit messy if you're using the Tag property for more than one thing. As an alternative, and also a method that can be used in conjunction with the Tag property, I use what I call a "Container"... I quite like the "Container" idea because it provides a "Graphical Method" of identifying a bunch of controls. You can see it working in this video here:-

Nifty Container - Nifty Access 105 views 10 Jan 2020

There's more about it on my website here:-

Nifty Container

Nifty Container - Nifty Access

Nifty Container The "Nifty Container" offers you a graphical way of managing the controls on your form. All you do is surround a group of controls with a box, choose one of the coding examples, or write your own, and there you go, you have a simple and easy-to-use method, which avoids using your...

www.niftyaccess.com

Drop me a line and I will explain how you can get a Copy for free!

 

[oxicottin]

@arnelgp I tried your last post and it works BUT It only shows pages that have a 1 and the buttons are separated with a gap. I attached v2 that allows you to create multi select and save as 1,2,3 ect. and with arnelgps example but again if I am security 3 and on the first menu of pages it should show all of them BUT it only displays pages that have a security with 1,2,3 or 1,3 just as ling as it has a 1 in it.

Thanks,

 

[arnelgp]

you are using customized Switchboard (not dynamic as created by the wizard).
the wizard create a Continuous form, while you have a single form.

 

[oxicottin]

@arnelgp I tried using a continuous form with same results. Also, Why is it only displaying controls that have 1 in the SecurityLevel even though my UserSecurityType is a 3 and should show all pages.

Example 1 Image: If in the image below with the results for the security level and me being UserSecurityType is a 1 it only displayed "Production Data Enty Form" I don't know why it doesn't even have a 1 for its security level BUT its sequential number is a 1 and "Reports, Graphs an Forms" does include a 1.

Example 2 Image: If in the image below with the results for the security level and me being UserSecurityType is a 3 it should show everything but it only displayed "Production Data Enty Form" I don't know why it doesn't even have a 1 for its security level BUT its sequential number is a 1 and "Advanced records search" does include a 1 and "Administration" does include a 1.

Last,

you are using customized Switchboard (not dynamic as created by the wizard).
the wizard create a Continuous form, while you have a single form.

If I changed from a single to a continuous form I get the exact same gaps between button as I did with the single form.

 

[oxicottin]

Ok got a lot done... I got it working the way I wanted messing with FillOptions a lot.... The only thing I cant now figure out is that gap between buttons, How the heck do I get that gap to go away?

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

'The number of buttons on the frm_Switchboard form.
    Const conNumButtons = 9
    
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim strSQL As String
    Dim intOption As Integer
    
    AutoFit Me.txtMenuPageHeading    'Expands Page Heading control to fit text
    Me.cmdDummy.SetFocus    'Set focus to hidden btn

    For intOption = 1 To conNumButtons
        Me("Option" & intOption).Visible = False
        Me("OptionLabel" & intOption).Visible = False
    Next intOption
    
    Set dbs = CurrentDb()
'-------------------------------------------------------------------------------------------
    Dim lngID As Long
    lngID = Nz(DLookup("UserSecurityType", "tbl_LoginUser", "[fOSUserLoginName] = '" & fOSUserName() & "'"), 1)
    
    strSQL = "SELECT * FROM [tbl_MenuItems]"
    strSQL = strSQL & " WHERE [MenuSequentialNumber] > 0 AND [MenuID]=" & Me![MenuID] & " AND [SecurityLevel]<=" & lngID & "" 'If text then '" & lngID & "'"
    strSQL = strSQL & " ORDER BY [MenuSequentialNumber];"
'-------------------------------------------------------------------------------------------
    Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)
    
    If rst.EOF Then
        Me![OptionLabel1].Caption = "There are no items for this switchboard page"
    Else
        intOption = 1
        With rst
        Do Until .EOF
            Me("Option" & rst![MenuSequentialNumber]).Visible = True
            Me("OptionLabel" & rst![MenuSequentialNumber]).Visible = True
            Me("OptionLabel" & rst![MenuSequentialNumber]).Caption = rst![MenuPageTitle]
            rst.MoveNext
         intOption = intOption + 1
      Loop
   End With
   rst.Close
   dbs.Close
 
   Set rst = Nothing
   Set dbs = Nothing
   End If
End Sub

 

[isladogs]

If you are hiding controls, they are still in place but not visible.
Moving visible controls up to fill the gaps may require a lot of additional code.
That would have been true whether you had used my approach or Doc's or anyone else's from this thread.
For that reason, it is often better to disable the controls but leave them visible

But I have to say, what you have now may work to your satisfaction (apart from the gaps) but it is far more complicated than it needed to have been.
Good luck with your project

 

[oxicottin]

@isladogs its easy for you guys because you have done it and know what your doing, me im just pecking away and taken advise given. I like doing it to help make things easier for me at work... I just wanted to thank everyone for the advice, suggestions and examples given! Im going to try and tackle a different approach to the space,