locked
Group Based Item Level Security in Report Model RRS feed

  • Question

  • User-830595639 posted

    Hi All,

    I want to restrict the data based on the group for Report Model. I am able to do this for particular users by using GetUserID() function, but wondering if there is any function available so that we can define the security based on the group.

    Thanks in Advance.

    Nishant

    Wednesday, June 8, 2011 5:07 AM

Answers

  • User-830595639 posted

    Hi,

    First you need to make a class library project, which will read the Active directory and return you back all the groups that the logged in user belongs to, then you can use these values in query to filter out your result set.

    There are couple of stpes needs to be followed. I am giving all the steps and code here.

    1. Make one class library project.
    2. Rename the class with the appropriate name.
    3. Paste the following code.

     

    using System.DirectoryServices;

    using System.Security;

     

    public static string GetGroupName(string UserID)

            {

                string GroupName =  string.Empty;

               

                try

                {

                    // Query on each group for its official Active Directory name and recursively list its contained users

                        DirectoryEntry de = new DirectoryEntry("GC://dc=dell,dc=com");

                        DirectorySearcher ds = new DirectorySearcher(de);

                        de.Close();

                        de.Dispose();

     

                        ds.Filter = "(&(cn=" + UserID + ")(objectCategory=user)(objectClass=user))";

     

                        ds.PropertiesToLoad.Add("distinguishedName");

     

                        SearchResultCollection results = ds.FindAll();

                        ds.Dispose();

     

                        if (results != null && results.Count > 0)

                        {

                            foreach (SearchResult result in results)

                            {

                                GroupName = ListGroups((string)result.Properties["distinguishedName"][0]);

                            }

                        }

                    

                }

                catch (Exception e)

                {

                    string msg = e.Message;

                    throw e;

                }

               

                return GroupName;

            }

     

            /// <summary>

            /// Query on the name passed in.  If it's a group recurse and call the method again else write the user info

            /// to the collection of users to be exported.

            /// </summary>

            /// <param name="distinguishedName"></param>

            private static string ListGroups(string distinguishedName)

            {

                string GroupName = "";

                try

                {

                    DirectoryEntry de = new DirectoryEntry("LDAP://" + distinguishedName);

                    DirectorySearcher ds = new DirectorySearcher(de);

                    de.Close();

                    de.Dispose();

     

                    // Query for the owner of the group and its users, if this is actually a group.  It might be a user.

                    ds.Filter = "(objectClass=*)";

                    ds.PropertiesToLoad.Add("member");

                    ds.PropertiesToLoad.Add("managedBy");

     

                    SearchResult results = ds.FindOne();

                    ds.Dispose();

     

                    string[] segments = distinguishedName.Split(new char[1] { ',' });

                    string group = segments[0].Remove(0, 3); // pull the group name

     

     

                    if (results.Properties["memberof"] != null)

                    {

                        string member;

                        string category;

     

                        // Result of 0 means count exceeds server defined limit of 1000

                        // Must request a block of 1000 items at a time.

     

                        int rangeIncrement = 1000; // Server defined default limit.

                        int rangeStart = 0;

                        int rangeEnd = rangeIncrement - 1;

                        string[] memberRange = { "" };

     

                        // Bind directly to group.

                        DirectoryEntry tempDE = results.GetDirectoryEntry();

                        

     

                        do

                        {

                            // Build propertyNames with range filter.

                            memberRange[0] = string.Format("member;Range={0}-{1}", rangeStart, rangeEnd);

                            // Refresh the cache with the new entries

                            tempDE.RefreshCache(memberRange);

                            string UserName = tempDE.Name.ToString();

     

                            foreach (object obj in tempDE.Properties["memberof"])

                            {

                                member = obj.ToString();

     

                                DirectoryEntry entry = new DirectoryEntry("LDAP://" + member);

                                category = (string)entry.Properties["objectCategory"][0];

                                entry.Close();

                                entry.Dispose();

     

     

     

                                if (category.IndexOf("CN=Group,") == 0)

                                {

                                    //this.LogInformationMessage("Starting the listing members for : " + member);

                                    GroupName = GroupName + "'" + member.Substring(3, member.IndexOf(",") - 3).ToString() + "',";

                                   

                                }

                            }

     

                            // Increment the request range

                            rangeStart += rangeIncrement;

                            rangeEnd += rangeIncrement;

     

                        } while (tempDE.Properties["member"].Count == rangeIncrement);

                    }

                }

                catch (Exception e)

                {

                    GroupName = e.Message;

                    //Logger.Error(e);

     

                    throw e;

                }

                return GroupName.Substring(0,GroupName.LastIndexOf(","));

            }

     

    1. Build the project.
    2. It will create the dll of the class.
    3. Copy the dll file and paste it in the following two location

    C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer\bin

    C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies

    1. Back up and open the Report Server security configuration file rssrvpolicy.config. You can find it at
      C:\Program Files\Microsoft SQL
      Server\MSRS10.<InstanceName>\ReportingServices\ReportServer.
    2. Navigate to the node <CodeGroup> which class attribute is “UnionCodeGroup” and Name attribute is “Report_Expressions_Default_Permissions”. Then replace the PermissionSetName attribute From “Execution” to “FullTrust”.
    3. Navigate to the node <CodeGroup> which class attribute is “FirstMatchCodeGroup”. Also replace the PermissionSetName attribute From “Execution” to “FullTrust”.
    4. Save the configuration file.
    5. To test the custom code in Preview mode in Business Intelligence Studio, we also need make the same changes in the Report Designer security configuration file rspreviewpolicy.config. The file is stored at C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies.
    6. Then make a Report and reference the assembly.
    7. Call the method GetGroupName from the Assembly code with UserID and it will return you all the group names from which the given user belongs to.

     

     I hope this will help many people.

    Thanks

    Nishant Gauraw

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, June 17, 2011 3:25 AM

All replies

  • User1471008070 posted

    Hi,

    Based on your description, it seems you want to filter the data based on user's group rather than UserID filed, right? If so, I think we can achieve this requirement in reporting services, you should create a customer code to check if the user running the report is in a particular group, for the detail steps, please see tony's professional reply at this link http://social.technet.microsoft.com/Forums/en-US/sqlreportingservices/thread/094cd39c-5e06-4a41-ba4a-3d0bad2970e1

    Thanks,
    Challen Fu

    Tuesday, June 14, 2011 3:46 AM
  • User-830595639 posted

    Hi You-Hu

    Thanks for reply. The link given by you is some how doesn't worked for me, but I found the other way to solve my problem.

    Wednesday, June 15, 2011 7:02 AM
  • User1471008070 posted

    Hi,

    Since you find another way to solve your issue, could you please share you solution here, I will mark it as answer, then other communities will know the direction when encounter the same issue. Thanks.

    Regards,
    Challen Fu

    Wednesday, June 15, 2011 10:15 PM
  • User-830595639 posted

    Hi,

    First you need to make a class library project, which will read the Active directory and return you back all the groups that the logged in user belongs to, then you can use these values in query to filter out your result set.

    There are couple of stpes needs to be followed. I am giving all the steps and code here.

    1. Make one class library project.
    2. Rename the class with the appropriate name.
    3. Paste the following code.

     

    using System.DirectoryServices;

    using System.Security;

     

    public static string GetGroupName(string UserID)

            {

                string GroupName =  string.Empty;

               

                try

                {

                    // Query on each group for its official Active Directory name and recursively list its contained users

                        DirectoryEntry de = new DirectoryEntry("GC://dc=dell,dc=com");

                        DirectorySearcher ds = new DirectorySearcher(de);

                        de.Close();

                        de.Dispose();

     

                        ds.Filter = "(&(cn=" + UserID + ")(objectCategory=user)(objectClass=user))";

     

                        ds.PropertiesToLoad.Add("distinguishedName");

     

                        SearchResultCollection results = ds.FindAll();

                        ds.Dispose();

     

                        if (results != null && results.Count > 0)

                        {

                            foreach (SearchResult result in results)

                            {

                                GroupName = ListGroups((string)result.Properties["distinguishedName"][0]);

                            }

                        }

                    

                }

                catch (Exception e)

                {

                    string msg = e.Message;

                    throw e;

                }

               

                return GroupName;

            }

     

            /// <summary>

            /// Query on the name passed in.  If it's a group recurse and call the method again else write the user info

            /// to the collection of users to be exported.

            /// </summary>

            /// <param name="distinguishedName"></param>

            private static string ListGroups(string distinguishedName)

            {

                string GroupName = "";

                try

                {

                    DirectoryEntry de = new DirectoryEntry("LDAP://" + distinguishedName);

                    DirectorySearcher ds = new DirectorySearcher(de);

                    de.Close();

                    de.Dispose();

     

                    // Query for the owner of the group and its users, if this is actually a group.  It might be a user.

                    ds.Filter = "(objectClass=*)";

                    ds.PropertiesToLoad.Add("member");

                    ds.PropertiesToLoad.Add("managedBy");

     

                    SearchResult results = ds.FindOne();

                    ds.Dispose();

     

                    string[] segments = distinguishedName.Split(new char[1] { ',' });

                    string group = segments[0].Remove(0, 3); // pull the group name

     

     

                    if (results.Properties["memberof"] != null)

                    {

                        string member;

                        string category;

     

                        // Result of 0 means count exceeds server defined limit of 1000

                        // Must request a block of 1000 items at a time.

     

                        int rangeIncrement = 1000; // Server defined default limit.

                        int rangeStart = 0;

                        int rangeEnd = rangeIncrement - 1;

                        string[] memberRange = { "" };

     

                        // Bind directly to group.

                        DirectoryEntry tempDE = results.GetDirectoryEntry();

                        

     

                        do

                        {

                            // Build propertyNames with range filter.

                            memberRange[0] = string.Format("member;Range={0}-{1}", rangeStart, rangeEnd);

                            // Refresh the cache with the new entries

                            tempDE.RefreshCache(memberRange);

                            string UserName = tempDE.Name.ToString();

     

                            foreach (object obj in tempDE.Properties["memberof"])

                            {

                                member = obj.ToString();

     

                                DirectoryEntry entry = new DirectoryEntry("LDAP://" + member);

                                category = (string)entry.Properties["objectCategory"][0];

                                entry.Close();

                                entry.Dispose();

     

     

     

                                if (category.IndexOf("CN=Group,") == 0)

                                {

                                    //this.LogInformationMessage("Starting the listing members for : " + member);

                                    GroupName = GroupName + "'" + member.Substring(3, member.IndexOf(",") - 3).ToString() + "',";

                                   

                                }

                            }

     

                            // Increment the request range

                            rangeStart += rangeIncrement;

                            rangeEnd += rangeIncrement;

     

                        } while (tempDE.Properties["member"].Count == rangeIncrement);

                    }

                }

                catch (Exception e)

                {

                    GroupName = e.Message;

                    //Logger.Error(e);

     

                    throw e;

                }

                return GroupName.Substring(0,GroupName.LastIndexOf(","));

            }

     

    1. Build the project.
    2. It will create the dll of the class.
    3. Copy the dll file and paste it in the following two location

    C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer\bin

    C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies

    1. Back up and open the Report Server security configuration file rssrvpolicy.config. You can find it at
      C:\Program Files\Microsoft SQL
      Server\MSRS10.<InstanceName>\ReportingServices\ReportServer.
    2. Navigate to the node <CodeGroup> which class attribute is “UnionCodeGroup” and Name attribute is “Report_Expressions_Default_Permissions”. Then replace the PermissionSetName attribute From “Execution” to “FullTrust”.
    3. Navigate to the node <CodeGroup> which class attribute is “FirstMatchCodeGroup”. Also replace the PermissionSetName attribute From “Execution” to “FullTrust”.
    4. Save the configuration file.
    5. To test the custom code in Preview mode in Business Intelligence Studio, we also need make the same changes in the Report Designer security configuration file rspreviewpolicy.config. The file is stored at C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies.
    6. Then make a Report and reference the assembly.
    7. Call the method GetGroupName from the Assembly code with UserID and it will return you all the group names from which the given user belongs to.

     

     I hope this will help many people.

    Thanks

    Nishant Gauraw

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, June 17, 2011 3:25 AM
  • User1471008070 posted

    Thank you for sharing your solutions and experience here. It will be very beneficial for other community members who have similar questions.

    Friday, June 17, 2011 6:20 AM