The regular RMTrack user interface provides a high level security in terms of which users can access which issues. When writing an SQL query for a custom report it is strongly recommended that the queries provide the same level of security. To help with this several macros have been defined. Whenever one of these macros is encountered in a query it is substituted with the appropriate SQL.
One of the macros [IssueRowLevelSecurity]* incorporates all the other macros and mimics the security of the regular RMTrack user interface. It is recommended that issue row level security should always be used when querying the Issues table. This ensures that a report does not accidentally disclose information to users that they could not normally access.
The following macros are supported:
|
Macro
|
Purpose
|
|
[CurrentUserProjects]
|
A parenthesized list of ProjectId’s the user executing the report belongs to. For example: (1, 2, 4). This macro is intended for use with SQL’s “IN” operator, to ensure that the query only returns data for projects the user belongs to. For example:
SELECT * FROM [Issues] WHERE Issues.ProjectId in [CurrentUserProjects]
The query actually executed will be:
SELECT * FROM Issues WHERE Issues.ProjectId in (1, 2, 4)
|
|
[IssuePreFilterAnd]
|
The SQL expression that is defined by the Site Option PreFilter, ending with an AND. This macro is intended to ensure that any issue prefilter conditions can also be applied. For example:
SELECT * FROM Issues WHERE [IssuePreFilterAnd] Issues.ProjectId in [CurrentUserProjects]
If a pre-filter has not been defined then this macro is an empty string.
|
|
[AndIssuePreFilter]
|
The SQL expression that is defined by the Site Option PreFilter, starting with an AND. This macro is intended to ensure that any issue prefilter conditions can also be applied. For example:
SELECT * FROM Issues WHERE Issues.ProjectId in [CurrentUserProjects] [AndIssuePreFilter]
If a pre-filter has not been defined then this macro is an empty string.
|
|
[IssuePreFilter]
|
The SQL expression that is defined by the Site Option PreFilter, without an AND at the start. This macro is less useful than the [AndIssuePreFilter] because to correctly use it the query author needs to know that a prefilter has been defined.
If a pre-filter has not been defined then this macro is an empty string.
|
|
[IssueRowLevelSecurity]*
|
An SQL expression that exerts row level security constraints against a query on the Issues table. See below for more information. *
|
RMTrack’s full issue level security consists of the following requirements:
-
Site administrators can access any issue
-
A user that is member of at least one group with unrestricted access can access any issue in a project they belong to
-
Further restricted by the issue pre-filter site option
-
A user that is not member of any group with unrestricted access can only access issues they have created, are currently assigned to, or have been assigned to at some point in the issue’s history
-
Further restricted by the issue pre-filter site option
* These requirements have been wrapped up in a macro called [IssueRowLevelSecurity] which evaluates to the following SQL expression:
----------------------------------------
(exists(select * from Users
where Users.UserId = @CurrentUserId
and Users.SiteAdministrator = 1)
or
(Issues.ProjectId in [CurrentUserProjects]
[AndIsusesPreFilter]
and (exists(select top 1
Groups.UnrestrictedIssueAccess
from UserGroups inner join Groups
on Groups.GroupCode = UserGroups.GroupCode
where UserGroups.UserId = @CurrentUserId
order by Groups.UnrestrictedIssueAccess desc)
or
(Issues.CreatedByUserId = @CurrentUserId
or Issues.AssignedToUser = @CurrentUserId
or exists(select *
from IssueHistory
where IssueHistory.IssueId = Issues.IssueId
and IssueHistory.OldAssignedToUser = @CurrentUserId)
)
)
)
)
----------------------------------------
The macro [IssueRowLevelSecurity] can be used in a SQL query for a custom report as follows:
----------------------------------------
SELECT
Issues.IssueId, Issues.Summary
FROM Issues
WHERE [IssueRowLevelSecurity]
AND Issues.StatusCode = ‘Open’
----------------------------------------
It is important to note that the [IssueRowLevelSecurity] macro only works with the Issues table - not an alias of that table.
It is recommended that issue row level security should always be used when querying the Issues table. This ensures that a report does not accidentally disclose information to users that they could not normally access.
NOTE: The RMTrack user interface also provides field level security – restricting which fields a user can see based on group membership. There is no macro provided to handle field level security. This should be controlled by what groups/users can access the report.
|
