CODEWITHSUNDEEP
securityreporting-services
Reza
Reza

Reputation: 834

Control Report Permission Based on Parameters in Reporting Services

Assume we have a report called SalesSummary for a large department. This department has many smaller teams for each product. People should be able to see information about their own product, not other teams' products. We also have one domain group for each of these teams.

Copying SalesSummary report for each team and setting the permission is not the best option since we have many products. I was thinking to use a code similar to below on RS, but it doesn't work. Apparently, System.Security.Principal.WindowsPrincipal is disabled by default on RS.

Public Function isPermitted() As Boolean
   Dim Principal As New System.Security.Principal.WindowsPrincipal(System.Security.Principal.WindowsIdentity.GetCurrent())
   If (Principal.IsInRole("group_prod")) Then
      Return true
   Else
      Return false
   End If
End Function

I also thought I can send the userID from RS to SQL server, and inside my SP I can use a code similar to below to query active directory. This also doesn't work due to security restriction. 

SELECT
* 
FROM OPENQUERY(ADSI,'SELECT cn, ADsPath FROM ''LDAP://DC=Fabricam,DC=com'' WHERE objectCategory=''group''')

Is there any easier way to achieve this goal?

Thanks for the help!

Upvotes: 4

Views: 1204

Answers (2)

Reza
Reza

Reputation: 834

So I ended up with this code:

        PrincipalContext domain = new PrincipalContext(ContextType.Domain, "AD");
        UserPrincipal user = UserPrincipal.FindByIdentity(domain, identityName);
        //// if found - grab its groups
        if (user != null)
        {
            PrincipalSearchResult<Principal> _groups = null;
            int tries = 0;
           //We have this while because GetGroups sometimes fails! Specially if you don't
           // mention the domain in PrincipalContext
            while (true)
            {
                try
                {
                    _groups = user.GetGroups();
                    break;

                }
                catch (Exception ex)
                {
                    logger.Debug("get groups failed", ex);
                    if (tries > 5) throw;
                    tries++;
                }
            }

            // iterate over all groups, just gets groups related to this app
            foreach (Principal p in _groups)
            {
                // make sure to add only group principals
                if (p is GroupPrincipal)
                {
                    if (p.Name.StartsWith(GROUP_IDENTIFIER))
                    {
                        this.groups.Add((GroupPrincipal)p);
                        this.groupNames.Add(p.Name);
                    }
                }
            }
          }

Now, that you have a list of related group you can check the list to authorize the user!

Upvotes: 1

Jamie F
Jamie F

Reputation: 23809

The first option you suggested (using embedded code to identify the executing user) will not be reliable. SSRS code is not necessarily executed as the user accessing the report, and may not have access to that users credentials, such as when running a subscription.

Your second approach will work, but requires the appropriate permissions for your SQL server service account to query Active Directory.

Another approach is to maintain a copy of the group membership or user permissions in a SQL table. This table can be updated by hand or with an automated process. Then you can easily incorporate this into both available parameters and core data queries.

Upvotes: 3

Related Questions