SSRS Create Subscription Issue Using Web Services

Our implementation of SSRS into our Java web site is going very well! However, we ran into an issue trying to set up subscriptions thru the web services.

When trying to set the message body text (the Comment field), we got the error:

One of the extension parameters is not valid for the following reason: you do not have permission to modify the value for the "Comment" 

The users all have the browser role, which allows Manage Individual Subscriptions, but this is not enough. There is a restriction in that setting that disallows the user from setting the comment field.
What I had to do was give the browser role the ability to Manage All Subscriptions. The major caveat with this setting is that if the application calls ListSubscriptions(null, null), the user will see all subscriptions in the system. We had to setup code on the front end to ALWAYS pass the UserID to the ListSubscription method. (http://msdn.microsoft.com/en-us/library/reportservice2005.reportingservice2005.listsubscriptions)

Also, note that for the My Reports folder, the browser security role is not used, its the My Reports role. You will need to give the My Reports role the Manage All Subscriptions task as well.

peace
BobP

Data based, Row Level Security in Reporting

Overview

Last month I promised an article on data based security. Well, after I ignored it for a month, someone asked for it. So here is what I am currently implementing in a project for a client.

I import several transactional systems into a data mart of my own design. It is a star schema, and utilizes 4 fact tables and about 20 dimensions. One of the dimensions handles the primary focus of our reports. Let’s call it Orders. Each individual order is assigned an OrderKey that ties all of the fact tables together.

To implement security, I assign OrderKeys to report user groups. The assignment is based on rules setup thru a front end .NET web application, and can be used to filter orders to users based on any field in the data mart. Then the report SQL checks to make sure that every order is in that security table for the user pulling the report.

The Details

I have setup a data dictionary containing all of the metadata that I need to build a query for the fields. The admin application displays the fields to the admin user, and the admin user selects a field, an operator (=, <, >, <>, Like, etc) and then enters a value.

So for instance, the admin user would select the CostCenter field, select the = operator, and then type in 1234. Stored in the database is the line: CostCenter = 1234.

Once the admin user is done, they select a menu item to “build” the rules. Using the data dictionary, the application builds SQL statements to filter the OrderKeys. The statements look like this:

Insert into Security
Select Distinct a1.*
From
(Select 1 as ProfileID, f.OrderKey as KeyVal
From factOrderLine f (NOLOCK)
inner join dimProduct d (NOLOCK)
on f.ProductKey = d.ProductKey

Where d.ProductID <> '555666') a1
Inner Join
(Select 1 as ProfileID, f.OrderKey as KeyVal
From factOrderLine f (NOLOCK)
inner join dimMFG d (NOLOCK)
on f.MFGKey = d.MFGKey

Where d.MFGID = 'PG') a2
On a1.KeyVal = a2.KeyVal

Because I am doing inner joins, this acts as an AND clause. So in the above query, I would get all orders that have a product ID not equal to 555666 and everything where the manufacturer ID is PG.

The resultant list is a profile ID, and then order key itself.

I put this into the Security table.

When I code the SQL in the report, I add an EXISTS statement to the WHERE clause:

and OrderKey in (Select OrderKey from Security where ProfileID = @ProfileID)

The @ProfileID parameter is being passed in by the app calling the reports. It is set when the user logs into the web front end.

That in a nutshell is how I do data based row level security.

This is a huge topic, and I have only scratched the surface in this blog. As I work on it some more, and add new features, I will blog them as well.

In the meantime, if you have any questions or suggestions, please email me!

peace

Data Based Security in Reporting

I sometimes wonder if anyone actually reads these blogs :-P
Well, here is my chance to find out.

I am going to ask for feedback.

In my position as a Business Intelligence Consultant, I have been asked before how to implement data based security (DBS). By DBS I mean the ability for an admin user to restrict a row of data from a user based on a value in the database.

For example, I, as an end user of a report, can only see transactions that occurred within my region: Georgia; whereas my peer across the country can only see transactions in Colorado.

I could add a where clause to every report, but what if the admin wants the ability to filter by ANY field in the database.

I have a solution that works quite well, but I am interested to know if anyone else has run into this request, and how they have solved it.

I am going to post my solution later on this week.

Peace