Browse DevX
Sign up for e-mail newsletters from DevX


Harden MS Reporting Services Using Custom Extensions, Part 2 : Page 2

In Part 1, you learned to create a custom security model for Microsoft Reporting Services. Now, tighten the screws by adding role membership authentication and stave off problems by troubleshooting and debugging your custom extensions ahead of time.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

Implementing Database Schema for Role Membership
As it stands, the Adventure Works database schema doesn’t include support for role membership (at least not for customers). For this reason, you'll need to add two additional tables, CustomerRole and Role, as shown in Figure 1.

Figure 1. Two New Tables: The Adventure Works database schema now supports role membership.

You may find this role membership data model familiar. The table Role holds the application roles, while the table CustomerRole defines to which roles a given customer belongs. Remember, a customer may belong to more than one role.

There are two scripts in the Database folder of the code download for this article. The ddl.sql script creates the new tables and stored procedures, while the data.sql script populates the new tables with some sample data. Once again, please note that the Report Server doesn't impose any restrictions on the role membership schema, so please feel free to enhance the schema to meet your requirements, like supporting nested roles, subsets of permissions, etc.

Implementing Role Authentication
There are two steps to updating the code extension to support role membership. First, you need to change the IAuthenticationExtension.IsValidPrincipalName. The second step is to enhance IAuthenticationExtension.LogonUser.

1) Validating the Principal Name
As you may recall, the Report Server calls IsValidPrincipalName each time a change is made to a security policy—for example, when you open the Report Manager, navigate to a folder, click on the New Role Assignments button, and assign a user to a given role(s). Before the new security policy is created, the Report Server calls IsValidPrincipalName in your custom extension to validate the principal name. However, the Report Server doesn't validate the semantics of the principal name (role or individual user). In fact, the Report Server itself has no provisions to handle role assignments. This custom security extension is responsible for enforcing the role membership authentication and authorization rules. Once the security extension acknowledges that the principal name is valid by returning true from the IsValidPrincipalName call, the Report Server simply proceeds by recording the changes to the security policy in the report catalog.

Therefore, to validate the principal name, change the uspIsValidPrincipalName to handle both possibilities—the principal name as an individual user or as an application-defined role. This requires a simple change to the uspIsValidPrincipalName stored procedure:

CREATE PROCEDURE uspIsValidPrincipalName ( @PrincipalName nvarchar(50) ) AS IF ISNUMERIC(@principalName) = 1 SELECT CustomerID FROM Individual WHERE CustomerID = CAST(@PrincipalName AS INT) ELSE SELECT RoleID FROM Role WHERE Name = @PrincipalName

In the Adventure Works Web portal, the passed principle name could be either the customer identifier (individual policy change) or role (role policy change), you need to query the appropriate table. If a match is found, IAuthenticationExtension.IsValidPrincipalName returns true and the Report Server happily records the policy change.

Interestingly, even if a single policy is changed (created, modified, or deleted) the Report Server validates all security policies assigned for this item. This is because the Report Server rebuilds the entire security descriptor for every item that is changed. This works in the same way as the RS Windows security extension does. For example, if the suppose you create a new policy assignment to grant browser rights to a new principal for a given folder/ In this case, the Report Server calls IsValidPrincipalName as many times as policies have been assigned for this folder passing the principal name.

Now, suppose you’ve granted browser rights to all members of the Gold role. To view reports in the FormsAuthentication folder, Figure 2 shows what the Report Manager Security tab would look like.

Figure 2. The Report Manager Security Tab: Use the Report Manager to maintain both user and role security policies.

Granted, once you implement custom role-membership features in the custom security extension, you can remove the individual policies (say, customers 11003, 25863, and 28389) altogether. Figure 2 merely demonstrates that both individual and role security policies are supported.

2) Authenticating the User
Once the Web application collects the user's credentials, it calls the LogonUser SOAP API and passes them to the IAuthenticationExtension.LogonUser method in the custom security extension.

You'll need to decide upfront at what point your custom security extension retrieves the user roles. Of course, this has to happen before the user request is authorized. One approach is to retrieve the roles in each CheckAccess overload. However, this can generate excessive database traffic since a single action against the report catalog, for instance, requesting a report, may result in several CheckAccess calls. For this reason, it's more efficient to retrieve the user roles in the IAuthenticationExtension.LogonUser and cache them in the ASP.NET cache object:

public bool LogonUser(string userName, string password, string authority) { string[] roles = null; if ((0==String.Compare(userName, m_adminUserName, true, CultureInfo.CurrentCulture)) && (password == m_adminPassword)) return true; if (!AuthenticationUtilities.IsValidUser(userName, password, m_connectionstring)) return false; // user is authenticated, now get user roles DataSet dsRoles = Util.GetUserRoles(userName); if (dsRoles.Tables[0].Rows.Count > 0) { roles = new string[dsRoles.Tables[0].Rows.Count]; for (int i=0; i < dsRoles.Tables[0].Rows.Count; i++){ roles[i] = dsRoles.Tables[0].Rows[i][0].ToString(); } // cache the user roles HttpContext.Current.Cache.Insert(userName, roles); } // if (dsRoles…. return true; }

Once the user is authenticated successfully, retrieve the user roles by calling the GetUserRoles helper function. Next, load the roles into a string array, which is cached in the ASP.NET cache object. Since the security extension will always be loaded in the Report Server application domain, you know for certain that HttpContext.Current provides access to the ASP.NET cache object.

Author's Note: Because this example does not specify explicit cache expiration, the cached roles will remain in the ASP.NET Cache object for the lifetime of the Report Server application domain. If your application serves many concurrent users and you are concerned with excessive memory consumption, you may want to consider implementing a cache expiration policy.

Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date