Row-level Security Support in the Issue Tracking Solution

See Also

The Issue Tracking solution makes it possible for users to specify row-level security settings for each issue defined in the database. By clicking the Item Permissions button on the toolbar, the user can specify which roles may view, modify, or delete the issue.

Setting up the Item Permissions Dialog Box

The Item Permissions dialog box, implemented in the ItemPermissions.htm file, provides the user interface for specifying row-level security for a specific issue. The dialog box displays a list of all of the roles defined for the database along with the valid permissions that can be defined per role.

The roles are read from the database when the Web page is first opened and dynamically added to the user interface using DHTML. This is done in the 

Sub ItemPermissions_Setup
routine, which creates a recordset that contains a list of all of the roles defined for the database.

Set oRolesRS = CreateObject("ADODB.RecordSet")
    
oRolesRS.Source="SELECT name AS RoleName, uid AS Role_Id FROM sysusers WHERE (sid IS NULL) AND (issqlrole = 1) AND (NOT (name LIKE 'db_%'))"
On Error Resume Next
Set oRolesRS.ActiveConnection=oConnection
oRolesRS.Open

Then, for each role returned, a new row in a pre-defined table on the Web page is created to hold the role name and the combo box used to select the security options for that role. A combo box is created using the <SELECT> tag, and a SELECT CASE structure is used to add the appropriate options to the combo box based on the underlying table permissions for the role.

strHTML = "<SELECT id=Permissions" & oRolesRS.Fields("RoleName").value & ">"
        
strNoAccess = "<OPTION VALUE=0>" & L_NoAccess_DialogItem
strBrowse = "<OPTION VALUE=1>" & L_BrowseOnly_DialogItem
strEdit = "<OPTION VALUE=7>" & L_BrowseAndEdit_DialogItem
strDelete = "<OPTION VALUE=15>" & L_BrowseEditDelete_DialogItem
strPermissions = "<OPTION VALUE=47>" & L_BrowseEditDeletePermissions_DialogItem
        
Select Case GetRolePermissions(oRolesRS.Fields("RoleName").value)
    Case 47
        strHTML = strHTML & strNoAccess & strBrowse & strEdit & strDelete & strPermissions
        intSelected = 4
    Case 15
        strHTML = strHTML & strNoAccess & strBrowse & strEdit
        intSelected = 2
    Case 1
        strHTML = strHTML & strNoAccess & strBrowse
        intSelected = 1
    Case 0
        strHTML = strHTML & strNoAccess
        intSelected = 0
    Case Else
        strHTML = strHTML & strNoAccess & strBrowse & strEdit & strDelete & strPermissions
        intSelected = 4
End Select
strHTML = strHTML & "</SELECT>"
oCell.InnerHTML = strHTML

Once the set of roles and available permissions has been displayed, the routine 

Sub ItemPermissions_Load
is called, so, if there are existing row-level security settings set for the issue, the combo box is set to reflect the current settings.

Row-level security settings are saved in the modPermissions table of the database. This table contains one or more rows for each issue in the database that has row-level security enabled. Each row in the modPermissions table represents the security settings for a particular role.

To load the settings, a recordset is generated that contains all the rows from the modPermissions table that match the ID of the current issue.

set oPermissions = CreateObject("ADODB.Recordset")
oPermissions.Source = "SELECT * FROM modPermissions WHERE RowId = " + cStr(intItemID) + " AND TableID = " + cStr(GetTableID("Issues"))
set oPermissions.ActiveConnection = oConnection
oPermissions.Open

If this recordset returns no results, row-level security has been enabled for this issue. Otherwise, each combo box associated with each role must be updated with the value currently stored in the modPermissions table.

If there are results, the type column of the modPermissions table defines what permissions have been granted to each role. For each row returned from the modPermissions table, the matching combo box associated with the role is updated with the type value from modPermissions.

While Not oPermissions.EOF
    intPermissions = oPermissions.Fields("Type").value
    ' Check to see if permissions are set to a combination not supported by dialog. If so, set to 0
    If intPermissions <> 0 And intPermissions <> intReadPermissions And intPermissions <> (intReadPermissions + intWritePermissions) And intPermissions <> (intReadPermissions + intWritePermissions + intGrantPermissions ) Then
        intPermissions = 0
    End If
    For intRow = tblItemPermissions.rows.length-1 to 0 Step -1
        If tblItemPermissions.rows(intRow).parentElement.tagName = "TBODY" Then
            If tblItemPermissions.rows(introw).children(1).children(0).id = "Permissions"& oPermissions.Fields("RoleName").value Then
                tblItemPermissions.rows(introw).children(1).children(0).value = intPermissions
            End If
        End If
    Next
    oPermissions.MoveNext
WEnd

Saving the Row-Level Permissions Settings

In order to save the row-level permissions settings specified by the user, the ItemPermissions_Save routine is used. This routine first removes all existing permissions by calling the DropPerms routine. DropPerms uses the modDropRowPermissions stored procedure. If no specific role is specified when the routine is called, all permissions are dropped from the current item, otherwise the routine removes only those permissions associated with the specified role.

Function DropPerms(txtRole)
    Dim rsExec
    Set rsExec = CreateObject("ADODB.RecordSet")
    If txtRole <> "" Then
        rsExec.Source="EXEC modDropRowPermissions 'Issues', " & intItemID & ", '" & txtRole & "'"
    Else
        rsExec.Source="EXEC modDropRowPermissions 'Issues', " & intItemID
    End If
    On Error Resume Next
    Set rsExec.ActiveConnection=oConnection
    rsExec.Open
    If Err.number <> 0 Then
        HandleDBErrors L_UnableToAccess_Message
        DropPerms = False
        Exit Function
    End If
    DropPerms = True
End Function

Once the permissions have been dropped, the ItemPermission_Save routine walks through each of the combo boxes that contain the security settings for each role and grants the specified permissions for each role using the GrantPerms routine. This routine uses the modGrantRowPermissions stored procedure to assign the permissions

Function GrantPerms(txtRole, txtPerms)


    Set rsExec = CreateObject("ADODB.RecordSet")    
    rsExec.Source="EXEC modGrantRowPermissions 'Issues', " & intItemID & ", '" & txtRole & "', '" & txtPerms & "'"
    
    On Error Resume Next
    Set rsExec.ActiveConnection=oConnection
    rsExec.Open
    If Err.number <> 0 Then
        HandleDBErrors L_UnableToAccess_Message
        GrantPerms = False
        Exit Function
    End If
    
End Function

Note   If you make any changes to table-level permissions to the Issue Tracking solution, you must manually update the IssuesBaseView with those changes. The IssuesBaseView is not automatically updated with changes you make to the Issues or IssuesView tables.