Hide some menu items as per user rights?

[savedlema]

Hi everyone!

I'm making an application which requires users with different permissions/previleges to login and use the application.For example, if the user is "admin", all menu items in the menu bar are shown and enabled. If the user is say "user_group1", some of the menu items are to be hidden. The same for "user_group2", "user_group3", "user_group4" and so on.

I could put a code for each specific user in each form but that's a great deal since users and their user groups may be added after application deployment. So it has to be something flexible.

I want the user "Administrator" to be able to assign/change permissions to others users of the system.

How do I do this?
I have been thinking of creating a table that will include user_groups and permisions, say like a row: user_group (user_group1, user_group2,user_group3) and permission (File,Edit,Backup,Manage Users (these being menu items)) and then for each user I will be setting either "Yes" or "No" under each permission/menu item indicating that the menu item is to be hidden of shown.
But, I can read these permission to a dataset but I can't see how to link them with the menu items.

How should I accomplish this please?

[cgeier]

Store all of the menu names that will require access permissions in a table.

Create table: Menu
Column: name (PK)

or

Column: id (PK)
Column: name

Create table: Group
Column: name (PK)
Column: menuPermissions (PK); FK references Menu.name

Create table: UserPermission
Column: id (PK); FK references User.id
Column: groupName (PK); FK references Group.name

Create table: User
Column: id (PK)
Column: firstName
Column: lastName
Column: password

Add a permission for each menu item--some of the items you may be able to group together and some menu items may be dependent on others. If someone doesn't have access to the file menu, they shouldn't have access to the edit menu under file menu. The following is not tested:

Dim FileMenuAccess as Boolean = False
Dim FileEditMenuAccess as Boolean = False
Dim FileBackupMenuAccess as Boolean = False
          ...

If FileMenuAccess = True Then
   'enable file menu

   If FileEditMenuAccess = True Then
      'enable file-edit menu
   Else
      'disable file-edit menu
   End If
Else
   'disable file menu
End If

Private Sub GetMenuPermissions()
   'get menu permissions for user from database

End Sub

Or

You could use a dictionary:

Dim menuDict as New Dictionary(Of String, Boolean)

If menuDict.Contains("FileMenuAccess") Then
   'enable file menu

   If menuDict.Contains("FileEditMenuAccess") Then
      'enable file-edit menu

   Else
      'disable file-edit menu

   End If
Else
   'disable file menu

End If

Private Sub GetMenuPermissions()
   'get menu permissions for user from database
   'add each menu permission to menuDict

End Sub

[cgeier]

My previous post used some reserved words. Also, I've worked on the db structure a little more.

The table names are as follows:

• AppPermission
• AppGroup
• AppUser
• AppGroupPermission
• AppUserGroup

AppPermission:

Version 1:

CREATE TABLE AppPermission (name nvarchar(50) NOT NULL
CONSTRAINT PK_AppPermission_name PRIMARY KEY,
description nvarchar(100))

AppGroup:

CREATE TABLE AppGroup (name nvarchar(50) NOT NULL
CONSTRAINT PK_AppGroup_name PRIMARY KEY,
description nvarchar(100))

AppUser:

CREATE TABLE AppUser (id nvarchar(25) NOT NULL
CONSTRAINT PK_AppUser_id PRIMARY KEY,
firstName nvarchar(50) NOT NULL,
lastName nvarchar(50) NOT NULL,
password nvarchar(25))

AppGroupPermission:

CREATE TABLE AppGroupPermission (groupName nvarchar(50) NOT NULL,
permissionName nvarchar(50) NOT NULL,
CONSTRAINT PK_groupName_permissionName
PRIMARY KEY(groupName, permissionName), 
CONSTRAINT FK_AppGroupPermission_AppGroup_groupName 
FOREIGN KEY (groupName) 
REFERENCES AppGroup(name) 
ON DELETE CASCADE ON UPDATE CASCADE, 
CONSTRAINT FK_AppGroupPermission_AppPermission_permissionName 
FOREIGN KEY (permissionName) 
REFERENCES AppPermission(name) 
ON DELETE CASCADE ON UPDATE CASCADE)

AppUserGroup:

CREATE TABLE AppUserGroup (userId nvarchar(25) NOT NULL,
groupName nvarchar(50) NOT NULL,
CONSTRAINT PK_userId_groupName
PRIMARY KEY(userId, groupName), 
CONSTRAINT FK_AppUserGroup_AppGroup_groupName 
FOREIGN KEY (groupName) 
REFERENCES AppGroup(name) 
ON DELETE CASCADE ON UPDATE CASCADE, 
CONSTRAINT FK_AppUserGroup_AppUser_userId 
FOREIGN KEY (userId) 
REFERENCES AppUser(id) 
ON DELETE CASCADE ON UPDATE CASCADE)

[cgeier]

Some of the sample data didn't get posted in the proper place. The 30 minute cut-off didn't allow me to edit it further. Here's the sample data with it's table:

AppPermission:

AppGroup:

AppUser:

AppGroupPermission:

AppUserGroup:

Sample SQL Queries:

To get permissions for "user2":

SELECT AppGroupPermission.groupName, AppGroupPermission.permissionName 
FROM AppGroupPermission
INNER JOIN AppUserGroup
ON AppGroupPermission.groupName = AppUserGroup.groupName
WHERE AppUserGroup.userId = 'user2'

To get permissions for "user2" along with the description of the permissions:

SELECT t2.groupName, t2.permissionName, AppPermission.description 
FROM AppPermission
INNER JOIN
(SELECT AppGroupPermission.groupName, AppGroupPermission.permissionName 
FROM AppGroupPermission
INNER JOIN AppUserGroup 
ON AppGroupPermission.groupName = AppUserGroup.groupName
WHERE AppUserGroup.userId = 'user2') t2 
ON t2.permissionName = AppPermission.name

Here's how to create a table programmatically:

Need to add Imports System.Data.SqlClient

    Public Sub AppPermissionTbl()

        Try

            Using cn As New SqlConnection(connectStr)
                Dim sqlText As String = String.Empty

                sqlText = "CREATE TABLE AppPermission (name nvarchar(50) NOT NULL "
                sqlText += "CONSTRAINT PK_AppPermission_name PRIMARY KEY, "
                sqlText += "description nvarchar(100))"

                'open connection
                cn.Open()

                'create new SqlCommand
                Using sqlCmd As New SqlCommand(sqlText, cn)

                    'execute
                    sqlCmd.ExecuteNonQuery()
                End Using

                System.Windows.Forms.MessageBox.Show("Table created: AppPermission", "Table Created.", MessageBoxButtons.OK, MessageBoxIcon.Information)

            End Using

        Catch ex As SqlClient.SqlException
            System.Windows.Forms.MessageBox.Show("Error:: AppPermissionTbl: " & ex.Message, "Error - Create Table", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Catch ex As Exception
            System.Windows.Forms.MessageBox.Show("Error:: AppPermissionTbl: " & ex.Message, "Error - Create Table", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Sub

There are documents attached below (in rich text format) that contain the information that was posted above as images.

[cgeier]

The code above in AppPermissionTbl is how to do it for SQL Server and SQL Server Express.

[savedlema]

cgeier,
First, thank you very much for sharing your precious time and knowledge with me and others here. I really appreciate that.

I use mysql instead of SQL, but I can change your code accordingly. I will try to do all that and will come back here for feedback.

Many thanks again.

[cgeier]

To get a list of menus that exist in the MenuStrip (ex: MenuStrip1):

I use a class called "MenuInfo" to store the data.

MenuInfo.vb

Public Class MenuInfo
    Public Property name As String
    Public Property depth As Integer
    Public Property parent As String

    'constructor
    Public Sub New()
    End Sub

    'constructor
    Public Sub New(ByVal name As String, ByVal depth As Integer, ByVal parent As String)
        Me.name = name
        Me.depth = depth
        Me.parent = parent
    End Sub
End Class

The following recursive method will get all of the sub-menus (DropDownItems). The initial call to "getToolStripChildMenuNames" is in "getToolStripMenuNames".

getToolStripChildMenuNames:

    Private Sub getToolStripChildMenuNames(ByVal myToolStripMenuItem As ToolStripMenuItem, ByRef menuList As List(Of MenuInfo), ByRef depth As Integer)

        'increment depth
        depth += 1

        For Each child As ToolStripMenuItem In myToolStripMenuItem.DropDownItems

            'add menu name to list
            menuList.Add(New MenuInfo(child.Name, depth, myToolStripMenuItem.Name))

            'recursivesly call function to get all children
            getToolStripChildMenuNames(child, menuList, depth)
        Next

        'decrement depth
        depth -= 1

    End Sub

The following method will get the menu names on the MenuStrip and call "getToolStripChildMenuNames" to get the sub-menus:

getToolStripMenuNames:

    Private Sub getToolStripMenuNames(ByVal myMenuStrip As MenuStrip)

        Dim output As String = String.Empty

        'hold menu info
        Dim menuList As New List(Of MenuInfo)

        'keeps track of depth
        Dim depth As Integer = 0

        'get top level menus - File, Tools, etc...
        For Each item As ToolStripMenuItem In myMenuStrip.Items

            'add menu to list
            menuList.Add(New MenuInfo(item.Name, depth, ""))

            'get all child menu items - DropDownMenus
            getToolStripChildMenuNames(item, menuList, depth)
        Next

        output = "List of Menus:" + System.Environment.NewLine + System.Environment.NewLine

        For Each item In menuList
            output += "Name: " & item.name & " Depth: " & item.depth & System.Environment.NewLine
            output += "      Parent: " + item.parent + System.Environment.NewLine + System.Environment.NewLine
        Next

        MessageBox.Show(output)
        'Console.WriteLine(output)
    End Sub

Usage:
getToolStripMenuNames(MenuStrip1)

Resource:
Adapted from here.

[cgeier]

You may consider storing the MenuStrip (ToolStripMenuItem) variable names in the database.

ex:

• FileToolStripMenuItem
• BackupToolStripMenuItem
• ExitToolStripMenuItem
• ToolsToolStripMenuItem
• AccountingToolStripMenuItem
• EmployeeToolStripMenuItem

The database structure will stay the same. The sample data in two of the tables will change as follows:

AppPermission:

AppGroupPermission:

[savedlema]

Thank you again.
I have been trying to follow your tables and codes..how then do I effect it by code when it comes to loading and disabling menu items based on the result of a database query? Can we use what is in the dataset to decided which menu items to enable/disable?

Thanks again for your good time and help.

[cgeier]

The following will show how to hide/show a menu based on user rights/permissions using a database that uses the structure in my post(s) above. There are most likely other ways of doing this, but the way I decided to do it was by making use of recursion. I use Dictionary and List in the process.

Ensure that you have SQLExpress or mySQL installed.

In this example, I use a database named "UserApp". So, create a new database named "UserApp". And create the file structure as previously described. Do not enter the sample data yet as I have made some slight modifications that will eliminate the need for some of the data. I require that only the youngest ToolStripMenuItem be entered in the database. It will be assumed that a user will also need access to all of the menus ancestors (parents).

Create a form named "Form1.vb" and add the following to it:

• Add a ComboBox named "ComboBox1" to the form.
• Add a Button named "Button1" to the form.
• Add a StatusStrip named "StatusStrip1" to the form. Then add a StatusLabel named ToolStripStatusLabel1" to "StatusStrip1".
• Add a MenuStrip named "MenuStrip1" to the form. Then create the following structure in MenuStrip1:

File menu:

• File => Backup
• File => Exit

Tools menu:

• Tools => Accounting
• Tools => Employee => EmployeeSubMenu1
• Tools => Employee => EmployeeSubMenu2
• Tools => Order Entry
• Tools => Refresh

Create a module named "AppPermissionsModule.vb".

For SQLExpress, add the following Imports statement: Imports System.Data.SqlClient

For mySQL, ensure that the Connector/Net software is installed. Then add a reference to MySQL.Data:

• Project
• Add Reference
• .NET
• MySQL.Data

Then add the following Imports statement: Imports MySql.Data

Create the following public/private variables shown below:

Imports System.Data.SqlClient
Imports MySql.Data

Module AppPermissionsModule

    '--------------------------------------------
    ' ToDo:
    ' Change dbName to the name of your database
    '--------------------------------------------
    'database name
    Private dbName As String = "UserApp"

    '--------------------------------------------
    ' ToDo:
    ' Change dbUsername to your database username
    ' Change dbPassword to your database password
    '--------------------------------------------
    Private dbUsername As String = "username"
    Private dbPassword As String = "pass"

    'connection string for SQLExpress
    Public _connectStrSQLExpress = "Server=.\SQLExpress;Database=" & dbName & ";Trusted_Connection=Yes;"

    'connection string for mySQL
    Public _connectStrMySQL = "Database=UserApp;Data Source=localhost;User Id=" & dbUsername & ";Password=" & dbPassword

    'dictionary of permissions within the program
    'adds permissions for all ancestors
    Private _menuPermissionsDict As New Dictionary(Of String, Boolean)

    'dictionary of all menus and sub-menus that exist on form
    Private _menuDict As New Dictionary(Of String, MenuInfo)

    'this is our local copy of the ToolStripStatusLabel from the form
    Private _ToolStripStatusLabel1 As ToolStripStatusLabel = Nothing

    'this is our local copy of the StatusStrip from the form
    Private _StatusStrip1 As StatusStrip = Nothing

End Module

Now, lets get all of the menu names and dropdown menu names from our form. But before we do, let's create a structure to hold the menu information. Create a class named "MenuInfo.vb".

MenuInfo.vb

Public Class MenuInfo
    'name of ToolStripMenuItem
    Public Property name As String

    'depth of menu
    Public Property depth As Integer

    'name of parent
    Public Property parent As String

    'our copy a ToolStripMenuItem
    'this is used so we can change 
    '.Visible property for each
    'ToolStripMenuItem
    Public Property menu As ToolStripMenuItem

    'constructor
    Public Sub New()
    End Sub

    'constructor
    Public Sub New(ByVal menu As ToolStripMenuItem, ByVal name As String, ByVal depth As Integer, ByVal parent As String)
        Me.menu = menu
        Me.name = name
        Me.depth = depth
        Me.parent = parent

    End Sub

    'constructor
    Public Sub New(ByVal menu As ToolStripMenuItem, ByVal depth As Integer, ByVal parent As String)
        Me.menu = menu
        Me.depth = depth
        Me.parent = parent

    End Sub
End Class

First of all let's get the menu items that you see when you look at the form (such as "File", "Tools", etc) and add them to a List(Of MenuInfo):

getToolStripMenuNames:

    Private Sub getToolStripMenuNames(ByVal myMenuStrip As MenuStrip)

        'holds data for display
        Dim output As String = String.Empty

        'hold menu info
        Dim menuList As New List(Of MenuInfo)

        'keeps track of depth
        Dim depth As Integer = 0

        'get top level menus - File, Tools, etc...
        For Each tsItem As ToolStripMenuItem In myMenuStrip.Items

            'add menu to list
            'ToolStripMenuItem, menu name, depth, parent name
            menuList.Add(New MenuInfo(tsItem, tsItem.Name, depth, ""))

        Next

    End Sub

So now we have "File", and "Tools". How do we get the dropdown menus?

Let's create a sub to get the dropdown menus. I pass menuList and depth using "ByRef", so that we can modify the values. You could make "menuList" and "depth" private variables (at the top) and eliminate these parameters.

getToolStripChildMenuNames:

    Private Sub getToolStripChildMenuNames(ByVal myToolStripMenuItem As ToolStripMenuItem, ByRef menuList As List(Of MenuInfo), ByRef depth As Integer)

        'increment depth
        depth += 1

        'get DropDown menus
         For Each child As ToolStripMenuItem In myToolStripMenuItem.DropDownItems

             'add menu name to list
             'ToolStripMenuItem, menu name, depth, parent name
              menuList.Add(New MenuInfo(child, child.Name, depth, myToolStripMenuItem.Name))

        Next
    End Sub

This gets the DropDownItems for a single ToolStripMenuItem. What if the DropDown menu has a DropDownItem menu? We will use recursion to handle this situation. Then we can get all drop down menus:

Private Sub getToolStripChildMenuNames(ByVal myToolStripMenuItem As ToolStripMenuItem, ByRef menuList As List(Of MenuInfo), ByRef depth As Integer)

    'increment depth
    depth += 1

    'get DropDown menus
    For Each child As ToolStripMenuItem In myToolStripMenuItem.DropDownItems

        'add menu name to list
        'ToolStripMenuItem, menu name, depth, parent name
        menuList.Add(New MenuInfo(child, child.Name, depth, myToolStripMenuItem.Name))

        'add menu name to list
        'ToolStripMenuItem, depth, parent name
        'menuList.Add(New MenuInfo(child, depth, myToolStripMenuItem.Name))

        'recursivesly call function to get all children
        getToolStripChildMenuNames(child, menuList, depth)
    Next

    'decrement depth
    depth -= 1

End Sub

Let's modify "getToolStripMenuNames" to make use of our new Sub "getToolStripChildMenuNames".

getToolStripMenuNames:

Private Sub getToolStripMenuNames(ByVal myMenuStrip As MenuStrip)

    'holds data for display
    Dim output As String = String.Empty
    Dim errMessage As String = String.Empty


    'hold menu info
    Dim menuList As New List(Of MenuInfo)

    'keeps track of depth
    Dim depth As Integer = 0

    'get top level menus - File, Tools, etc...
    For Each tsItem As ToolStripMenuItem In myMenuStrip.Items


        'add menu to list
        'ToolStripMenuItem, menu name, depth, parent name
        menuList.Add(New MenuInfo(tsItem, tsItem.Name, depth, ""))

        'add menu to list
        'ToolStripMenuItem, depth, parent name
        'menuList.Add(New MenuInfo(tsItem, depth, ""))

        'get all child menu items - DropDownMenus
        getToolStripChildMenuNames(tsItem, menuList, depth)
    Next

    output = "List of Menus:" + System.Environment.NewLine + System.Environment.NewLine

    For Each item As MenuInfo In menuList
        output += "Name: " & item.name & " Depth: " & item.depth & System.Environment.NewLine
        output += "      Parent: " + item.parent + System.Environment.NewLine + System.Environment.NewLine

        'add info to _menuDict
        '_menuDict.Add(item.name, item)
         _menuDict.Add(item.menu.Name, item)
    Next

    'MessageBox.Show(output)
    'Console.WriteLine(output)

End Sub

Uncomment MessageBox.Show(output) in the above code to see a list of all the variable names for your ToolStripMenuItem's. This is the data that will go in the AppPermission table (in the "name" column). We will enter all of dropdown menus (ToolStripMenuItem) here except for top level menus like "File" and "Tools". Also, we don't need to add ToolStripMenuItem that we will grant to everyone like "File" => "Exit" (ExitToolStripMenuItem).

Table "AppGroupPermission" is where we will assign permissions to our groups. See the previous post for more info.

Enter the sample data from the previous post into the database at this time. You can eliminate the top most menus such as "File" (FileToolStripMenuItem) and "Tools" (ToolsToolStripMenuItem) as previouly mentioned.

Now we need a way to determine the ancestors of our child (DropDown) menus. We will use another recursive function named "getAncestors" for this purpose. Once again, you can probably eliminate the paremeters that I pass ByRef if you make them "Private" (at the top). This is where we add the permissions for all of a child's ancestors (parents):

getAncestors:

Private Sub getAncestors(ByVal menuName As String, ByVal _menuDict As Dictionary(Of String, MenuInfo), ByRef _menuPermissionsDict As Dictionary(Of String, Boolean))

    Dim parentMenuName As String = String.Empty

    'check if menu name exists in form
    'if so, get it's parent
    If _menuDict.ContainsKey(menuName) Then

        'add menu name to dictionary, if it doesn't already exist
        If Not _menuPermissionsDict.ContainsKey(menuName) Then
            _menuPermissionsDict.Add(menuName, True)
        End If

        'get parent menu name
        parentMenuName = _menuDict(menuName).parent

        'if parentMenuName exists, call getAncestors again
        If parentMenuName <> "" Then

            'add parent menu to menuPermissionDict, if it doesn't exist
            If Not _menuPermissionsDict.ContainsKey(parentMenuName) Then

                'add to dictionary
                _menuPermissionsDict.Add(parentMenuName, True)
            End If

            'get the parent menu's ancestor (parent)
            'by recursively calling "getAncestors"
            'we will get all parent menus until we
            'reach the top level menu
             getAncestors(parentMenuName, _menuDict, _menuPermissionsDict)
         End If

    End If
End Sub

Let's retrieve data for one of our users from the database now. We will use a Function so that we can return the data using a DataTable. We will process the data in a different Sub. In reality, I would use error handling "Try-Catch-Finally", here, but I've removed it here to make it easier to see what to do:

getPermissionsSQLExpress:(no error handling)

    Private Function getPermissionsSQLExpress(ByVal username As String) As DataTable
        'uses _ToolStripStatusLabel1 and _StatusStrip1 values set in
        'doTasks and/or getUsersSQLExpress

        Dim retDataTable As New DataTable

        'Create SQL Connection and get permissions
        Using cn As New SqlConnection(_connectStrSQLExpress)

            Dim sqlText As String = String.Empty
            sqlText = "Select AppGroupPermission.groupName, AppGroupPermission.permissionName "
            sqlText += "FROM AppGroupPermission "
            sqlText += "INNER JOIN AppUserGroup "
            sqlText += "ON AppGroupPermission.groupName = AppUserGroup.groupName "
            sqlText += "WHERE AppUserGroup.userId = '" & username & "'"

            ' Open Connection
            cn.Open()

            Using cmd As New SqlCommand(sqlText, cn)
                Dim dA As New SqlDataAdapter(sqlText, cn)


                dA.Fill(retDataTable)

            End Using

        End Using

        Return retDataTable
    End Function

getPermissionsSQLExpress:(with handling)

    Private Function getPermissionsSQLExpress(ByVal username As String) As DataTable
        'uses _ToolStripStatusLabel1 and _StatusStrip1 values set in
        'doTasks and/or getUsersSQLExpress

        Dim retDataTable As New DataTable
        Dim errMessage As String = String.Empty

        Try
            'update status
            _ToolStripStatusLabel1.Text = "Status: Getting User Permissions..."

            'refresh the StatusStrip
            'to ensure the ToolStripStatusLabel
            'text is updated
            _StatusStrip1.Refresh()

            'Create SQL Connection and get permissions
            Using cn As New SqlConnection(_connectStrSQLExpress)

                Dim sqlText As String = String.Empty
                sqlText = "Select AppGroupPermission.groupName, AppGroupPermission.permissionName "
                sqlText += "FROM AppGroupPermission "
                sqlText += "INNER JOIN AppUserGroup "
                sqlText += "ON AppGroupPermission.groupName = AppUserGroup.groupName "
                sqlText += "WHERE AppUserGroup.userId = '" & username & "'"

                Try
                    ' Open Connection
                    cn.Open()

                    Using cmd As New SqlCommand(sqlText, cn)
                        Dim dA As New SqlDataAdapter(sqlText, cn)

                        Try
                            dA.Fill(retDataTable)
                        Catch ex As Exception
                            errMessage = "getPermissionsSQLExpress (SqlDataAdapter): " & ex.Message
                            MessageBox.Show(errMessage)
                            _ToolStripStatusLabel1.Text = errMessage
                            _StatusStrip1.Refresh()
                        End Try
                    End Using

                Catch ex As SqlClient.SqlException
                    errMessage = "getPermissionsSQLExpress (error in connection - SqlException): " & ex.Message
                    MessageBox.Show(errMessage)
                    _ToolStripStatusLabel1.Text = errMessage
                    _StatusStrip1.Refresh()
                Catch ex As Exception
                    errMessage = "getPermissionsSQLExpress (error in connection): " & ex.Message
                    MessageBox.Show(errMessage)
                    _ToolStripStatusLabel1.Text = errMessage
                    _StatusStrip1.Refresh()
                End Try
            End Using

        Catch ex As SqlClient.SqlException
            errMessage = "getPermissionsSQLExpress (SqlException): " & ex.Message
            MessageBox.Show(errMessage)
            _ToolStripStatusLabel1.Text = errMessage
            _StatusStrip1.Refresh()
        Catch ex As Exception
            errMessage = "getPermissionsSQLExpress: " & ex.Message
            MessageBox.Show(errMessage)
            _ToolStripStatusLabel1.Text = errMessage
            _StatusStrip1.Refresh()
        End Try

        Return retDataTable
    End Function

Here is the "mySQL" version. This one is untested:
getPermissionsMySQL:

    Private Function getPermissionsMySQL(ByVal username As String) As DataTable
        'uses _ToolStripStatusLabel1 and _StatusStrip1 values set in
        'doTasks and/or getUsersSQLExpress

        Dim retDataTable As New DataTable
        Dim errMessage As String = String.Empty

        Try
            'update status
            _ToolStripStatusLabel1.Text = "Status: Getting User Permissions..."

            'refresh the StatusStrip
            'to ensure the ToolStripStatusLabel
            'text is updated
            _StatusStrip1.Refresh()

            'Create SQL Connection and get permissions
            Using cn As New MySqlClient.MySqlConnection(_connectStrMySQL)

                Dim sqlText As String = String.Empty
                sqlText = "Select AppGroupPermission.groupName, AppGroupPermission.permissionName "
                sqlText += "FROM AppGroupPermission "
                sqlText += "INNER JOIN AppUserGroup "
                sqlText += "ON AppGroupPermission.groupName = AppUserGroup.groupName "
                sqlText += "WHERE AppUserGroup.userId = '" & username & "'"

                Try
                    ' Open Connection
                    cn.Open()

                    Using cmd As New MySqlClient.MySqlCommand(sqlText, cn)
                        Dim dA As New MySqlClient.MySqlDataAdapter(sqlText, cn)

                        Try
                            dA.Fill(retDataTable)
                        Catch ex As Exception
                            errMessage = "getPermissionsMySQL (MySqlDataAdapter): " & ex.Message
                            MessageBox.Show(errMessage)
                            _ToolStripStatusLabel1.Text = errMessage
                            _StatusStrip1.Refresh()
                        End Try
                    End Using

                Catch ex As MySqlClient.MySqlException
                    errMessage = "getPermissionsMySQL (error in connection - MySqlException): " & ex.Message
                    MessageBox.Show(errMessage)
                    _ToolStripStatusLabel1.Text = errMessage
                    _StatusStrip1.Refresh()
                Catch ex As Exception
                    errMessage = "getPermissionsMySQL (error in connection): " & ex.Message
                    MessageBox.Show(errMessage)
                    _ToolStripStatusLabel1.Text = errMessage
                    _StatusStrip1.Refresh()
                End Try
            End Using

        Catch ex As MySqlClient.MySqlException
            errMessage = "getPermissionsMySQL (MySqlException): " & ex.Message
            MessageBox.Show(errMessage)
            _ToolStripStatusLabel1.Text = errMessage
            _StatusStrip1.Refresh()
        Catch ex As Exception
            errMessage = "getPermissionsMySQL: " & ex.Message
            MessageBox.Show(errMessage)
            _ToolStripStatusLabel1.Text = errMessage
            _StatusStrip1.Refresh()
        End Try

        Return retDataTable
    End Function

We will use a Sub called "doTasks" to store our data from "getPermissions".

Public Sub doTasks(ByVal username As String)

    Dim permissionsDt As DataTable = Nothing

    'get user permissions from db
    permissionsDt = getPermissionsSQLExpress(username)

    'get user permissions from db
    'permissionsDt = getPermissionsMySQL(username)


End Sub

Now that we can get the permissions from the database based on the user, and we have written code to get the names of the menus that exist in our form, let's use this data to make a menu visible/hidden based on a user's permissions. We will use a sub called "setPermissions" for this. I will use dictionary to make it easier. Although I don't use the value in some of the dictionary objects. We will need to pass the name of our MenuStrip to this Sub as we need to use it to retrieve the menu information from the form.

setPermissions:

Private Sub setPermissions(ByVal dt As DataTable, _
                               ByVal MenuStrip1 As MenuStrip)

    'holds the user's permissions from the database
    Dim dbPermissionsDict As New Dictionary(Of String, Boolean)

    'reset _menuPermissionsDict
    _menuPermissionsDict.Clear()

    'clear dictionary of menu info
    _menuDict.Clear()


    'retrieve menu info from form
    getToolStripMenuNames(MenuStrip1)

    For Each row As DataRow In dt.Rows
        Dim menuPermission As String = Nothing
        menuPermission = row("permissionName").ToString()
        'Console.WriteLine("dbMenuPermission: " & menuPermission)

        If menuPermission IsNot Nothing Then

            'add permission to dbPermissionDict
            dbPermissionsDict.Add(menuPermission, True)
        End If
    Next



    '--------------------------------------------
    'ToDo:
    '
    'Add any menus names you want all users
    'to have access to here.
    '
    'We add permissions to "dbPermissionDict"
    'so we can get all of the menu's ancestors
    '(parent) menus at the same time.
    '
    'It is only necessary to add the 
    'youngest child menu. All of it's ancestors
    'will be granted permission automatically.
    '
    'ex:
    'File menu has name "FileToolStripMenuItem1"
    'File => Edit menu has name "EditToolStripMenu1"
    '
    'You only need to add "EditToolStripMenu1".
    'Permissions for "FileToolStripMenuItem1"
    'will be added automatically.
    '-----------------------------------------------


    'grants all users access to File => Exit menu
    If Not dbPermissionsDict.ContainsKey("ExitToolStripMenuItem") Then
        dbPermissionsDict.Add("ExitToolStripMenuItem", True)
    End If

    'grants all users access to Tools => Refresh
    'RefreshToolStripMenuItem is the name for the 
    '"Refresh" menu (under Tools)
    If Not dbPermissionsDict.ContainsKey("RefreshToolStripMenuItem") Then
        dbPermissionsDict.Add("RefreshToolStripMenuItem", True)
    End If

    '------------------------------------
    'Add any menus you want all users to
    'have access to here
    '-------------------------------------

    For Each kvp1 As KeyValuePair(Of String, Boolean) In dbPermissionsDict
        'get permission name
        Dim permission As String = kvp1.Key

       'grants permissions for all ancestor (parent) menus
       'permissions are updated in menuPermissionDict
       getAncestors(permission, _menuDict, _menuPermissionsDict)
    Next

    For Each kvp As KeyValuePair(Of String, MenuInfo) In _menuDict

       If (_menuPermissionsDict.ContainsKey(kvp.Key)) Then

            'if user has permissions, make menu / drop-down menu visible
            _menuDict(kvp.Key).menu.Visible = True

            Console.WriteLine("permission: " & kvp.Key)
        Else
            'if user doesn't have permissions for menu,
            'hide the menu
            _menuDict(kvp.Key).menu.Visible = False
        End If

    Next

End Sub

Let's add a call to "setPermissions" inside of "doTasks". "setPermissions" needs our MenuStrip, so let's we'll create a parameter to pass our MenuStrip (MenuStrip1) to it:

Public Sub doTasks(ByVal username As String, ByVal MenuStrip1 as MenuStrip)

    Dim permissionsDt As DataTable = Nothing

    'get user permissions from db
    permissionsDt = getPermissionsSQLExpress(username)

    'get user permissions from db
    'permissionsDt = getPermissionsMySQL(username)

    If permissionsDt IsNot Nothing Then
        setPermissions(permissionsDt, MenuStrip1)
    End If


End Sub

Now all we need to do is use all the code:

Usage:

Dim username As String = String.Empty
username = "user5"

doTasks(username, MenuStrip1)

In case I mistyped / copied anything, I've attached the module. The attached file contains error-checking code. Some of the Sub/Functions may contain additional parameters.

Usage of the attached file:

Dim username As String = "user5"

doTasks(username, ToolStripStatusLabel1, StatusStrip1, MenuStrip1, "SQLExpress")

[cgeier]

Here's "doTasks" that is in the attached file above:

    Public Sub doTasks(ByVal username As String, _
                        ByVal ToolStripStatusLabel1 As ToolStripStatusLabel, _
                        ByVal StatusStrip1 As StatusStrip, _
                        ByVal MenuStrip1 As MenuStrip, _
                        ByVal databaseProduct As String)

        Dim errMessage As String = String.Empty
        Dim permissionsDt As DataTable = Nothing

        'set private variable values
        'used by other methods
        _ToolStripStatusLabel1 = ToolStripStatusLabel1
        _StatusStrip1 = StatusStrip1

        Try

            If databaseProduct = "SQLExpress" Then
                'get user permissions from db
                permissionsDt = getPermissionsSQLExpress(username)
            ElseIf databaseProduct = "mySQL" Then
                'get user permissions from db
                permissionsDt = getPermissionsMySQL(username)
            Else
                errMessage = "Error: (doTasks): " & " Database product not supported. "
                errMessage += "Must be 'SQLExpress' or 'mySQL'."
                MessageBox.Show(errMessage)
                ToolStripStatusLabel1.Text = errMessage
                _StatusStrip1.Refresh()
            End If


            If permissionsDt IsNot Nothing Then
                setPermissions(permissionsDt, MenuStrip1)
            End If

        Catch ex As Exception
            errMessage = "Error: (doTasks): " & ex.Message
            MessageBox.Show(errMessage)
            ToolStripStatusLabel1.Text = errMessage
            _StatusStrip1.Refresh()
        End Try
    End Sub

[savedlema]

Thank you again. I'll follow up and get back to you. (I wonder how you came to know all that!)

[savedlema]

Hi cgeier, I was working on your code this weekend.
There is one thing I seem to miss; how should I link the AppPermissionsModule and the Form1? How should I call this module into action?

Thanks again.

[cgeier]

See above where it says: usage of the attached file.

[tashee2007]

@ cgeier - I really liked your efford used to share your skill. Well i am in process of learning VB.NET & SQL 2008 application development. I came across this discussion and found out that the features which i need for my application is here. I have tried to follow your instruction provided out here but i am really confused on how to use.

I would appreciate if you could attached the working code of above sample so that i can see and use for my application. If you dont mind!!!

Thanks

[tashee2007]

Hope you dont mind.

[Lethugs]

You can create a table which contains UseGroup and column with datatype bit for Menu Names, so you will have

UserGroup Menu1 Menu2 Menu3 and so on

Then Create a Form (say module access) which contains checkboxes that corresponds to every Menu and maybe a combobox for UserGroup so you can specify which Group you're setting access

Every time you add a userGroup make sure to add this also to your table containing your access settings, you can set the Menu columns to true (since it has a bit datatype) as default then you can edit its setting on your module access form later.

Before you load your main form which contains your menus, call first the module access form Form_Load to check for 'Checked' and 'Unchecked' checkboxes
To make sure you are looking for the right settings, set your combobox containing your groupName same as user's groupName, so if you logged in as Administrator, set the combobox selected item or selected value to administrator.

If say menu1 is check then enable your menu on your main form

menu1.Enabled = True

You have to check all the checkboxes to set the access setting of your menus

[tashee2007]

Thanks for the response. I would appreciate if you can put some example code so that i can use?

[tashee2007]

@cgeier - I have used exactly the code which you have asked or shown in the attachment. I have call the functions in my FORM1 as below:

Imports System.Data.SqlClient
Public Class Form1
    Private Sub Form1_Load(sender As Object, e As System.EventArgs) Handles Me.Load
        Dim userList As List(Of String) = Nothing
        userList = getUsersSQLExpress(ToolStripStatusLabel1, StatusStrip1)
    End Sub

    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        doTasks("user1", ToolStripStatusLabel1, StatusStrip1, MenuStrip1, "SQLExpress")
    End Sub
End Class

When i load a form, my status bar shows "Getting Users..." and nothing happens.

When i run my BUTTON click events, the menu gets hide but, what ever user name i give in my code:

doTasks("user1", ToolStripStatusLabel1, StatusStrip1, MenuStrip1, "SQLExpress")

the permission seems to be same.

Please help me.

[Lethugs]

Say you have a main form and security form

After creating checkboxes and Combobox for the groupname and menu. Do the operation for updating the table for this menu's, (I used Updating since in adding username group, you have to insert this to your table for security like what I stated above, then just update it in security form).

On your security form load event, create select statement

Select * From Security where groupName = 'yourGroupName'

Set the check status of every checkbox you have:

Menu1.Checked = Datatable.Rows(0).Item("Menu1")
Menu2.Checked = Datatable.Rows(0).Item("Menu2")

where Datatable is the Datatable you used to store data from your select Statement
.Item("Menu1") - Table Column Name

On your Main Form, call security form laod event first to activate the form,

Call FrmSecurity.FrmSecurity_Load(sender,e)

Set the Combobox Selected Value to groupName of the user

FrmSecurity.Combobox.SelectedValue = 'UserGroupName'

Then Set the Status of your Menus:

If FrmSecurity.Menu1.Checked = True then
    FrmMain.Menu1.Enable = True
 Else
     FrmMain.Menu1.Enabled = False
 End If

Do all for your menus. This could take a lot of codes depending on your menu.

[savedlema]

tashee2007,
Try it again carefully, it seems to be working after clicking "Button1". If it does not change with users, try to run a querry that will show you what your application will fetch. Run this querry out of your application (eg. phpmyadmin)

[savedlema]

cgeier,

Thank you very much again for your wonderful great input to my question. Through your contribution I have been able to archieve what seemed impossible.
Now I'm well to go on.

Thanks, and the thread is solved.

[SushantKhot31]

Some error in

setPermissions funtion...see the attched file.

can you send me this peoject..!!??

[SushantKhot31]

This is my project. Please open my project and fix it error and send me..
its urjent .

[Reverend Jim]

What is urgent for you is not urgent for anyone else. Nobody here is going to do what you are asking.