Share via

Row-Level Security in the Issue Tracking Sample Application

  • Article

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

The Issue Tracking sample application 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 can 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 added dynamically 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 predefined 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

When 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

To save the row-level permissions settings specified by the user, the ItemPermissions_Save routine is used. This routine 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

When the permissions have been dropped, the ItemPermission_Save routine checks 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 application, you must manually update the IssuesBaseView with those changes. The IssuesBaseView is not updated automatically with changes you make to the Issues or IssuesView tables.

See Also

Defining Table Security | Security Permissions Model | Issue Tracking Sample Application | The Web Pages in the Issue Tracking Sample Application | The Structure of the Issue Tracking Sample Application | Workflow Functionality in the Issue Tracking Sample Application