Role based security issues

I am attempting to set up a new user that has only the ability to run reports in the report manager.

I have created a new ActiveDirectory entry for DOMAIN\ReportUser. I have a created a new folder with the reports, and have set this user as a Browser role on this directory, and all reports in the directory.

I have made it throught the security maze to gain access to these reports as this user, but I cannot dynamically run the reports. As the BUILTIN\Administrator (content manager), I get a grayed background on the parameters and can run the reports. As this DOMAIN\ReportUser, I get what looks like HTML parameter items, and cannot run a dynamic report. Even if I change the role of this user to Content Manager, I still cannot run a dynamic report.

How do you properly set up a user to be able to dynamically run, and only run, a report in Report Manager, and have this user only see reports in a single folder?

I have been fighting the security issues of creating a RS site and properly setting up access, and have yet to find a single site or person explaining the entire process in any coherant method.

Mark

Hello Mark,

I'll tell you how I have mine setup for users only being able to run certain reports.

First off, I created a new item-level role (Site Settings -> Configure item-level role definitions) called Report Viewer and assigned it the 'View reports' & 'View Folders' task. I created my own role just to give the basic amount of permission needed. Then, go to the folder with the reports in it and hit Properties -> Security. Select 'New Role Assignment', check the box for 'Report Viewer', and type the name of your user in the 'Group or user name:' box.

Now, the specific user/group can view the folder and run the reports underneath.

You could also change the security on specific reports instead of the folder if you only want the user(s) to run certain ones.

Hope this helps.

Jarret

|||

Jarret,

This is along the lines as to what I have tried.

Here is a little more about what I am experiencing:

I have a RS management site (e.g. report1.myurl.com/reports) the when I log in (i.e. I get the windows "Connect to report1.myurl.com" login form) with the builtin\administrator account I get full access to all report folders and functionality to manage the site.

I have a ActiveDirectory user Domain\ReportUser1, who is configured in RS as a 'Report Viewer' as you have described in your previous post. However, when I login with this User (i.e. I get the windows "Connect to report1.myurl.com" login form) I enter my Domain\ReportUser1 credentials, I have the same access to the RS management evironment as the builtin\administrator. It does not limit this user to only the folder I have given permissions to. I have checked the entire site, every configuration form, and through SQL2005 Management Studio for RS, and as far as I can tell, this user is set to only see the reports in this directory and does not have Content Manager status.

As an alternative, I have created a parallel named instance of reporting services (e.g report2.myurl.com/reports) and have set up the Directory Security Authentication on the Reports and ReportServer virtual directories to log in as the Domain\ReportUser1. These virtual sites point to the same SQL2005 instance of reporting services as the Default web site virtual directories, and I have access to the report uploaded there.

When I log into RS manager for this alternative site, I am now limited to view only the report folder that was configured for this user, however, I still have the ability to upload files, create folders and database connections (Which I should not, based on the role-based permissions set for this user). In addition, when attempting to run the reports, I do not have access to run them. I.e. Normally the parameters have are in the ReportViewer ActiveX-based control, along with the export, print, refresh, etc. (this has your report parameters in a gray background). When I run for this alternative site, the parameters are visible as what looks like HTML controls and any attempt to run the report returns message:

The selected report is not ready for viewing. The report is still being rendered or a report snapshot is not available. (rsReportNotReady)

The security involved in Reporting services is extremely frustrating to set up properly, as you not only have the windows, sql and IIS security stars to align to even gain access to the RS site, you then need to configure these Roles, which do not seem to be filtering a ActiveDirectory user's access to these reports as I would expect.

What am I missing?

|||I'm having the same issue. Can you keep me in the loop....sql

Revisiting the locking issues with Sql2000

ok, about a few months ago I brought up the issues we were having with deadlocks. My company is running a .net data entry application hitting against a sql 2000 box. Forget hardware since we have more then enough hardware to handle the load.

The issue we are seeing is we get a process that blocks a table, and the bulding up of block process that cause timeouts/deadlocks etc. A couple of things we see when this happens is that .net on the web server seems to open up more and more connections(it seems to double or triple the connections as the blocking begins), we start to see deadlocks and we see alot of timeouts in the app due to the blocking.

The blocking aways happen on an insert of an order or a customer. One thing I keep wondering about is how we handle the unique id for our tables. Our DBA had us put in the following:

Begin Trans
select max(id) + 1
insert...
commit trans

I've heard from a few here and in other forums this is the wrong way to go. I'm still get a gut feeling that this could start causing issues like what we are seeing. My problem is I'm not knowledgable enough to make a strong enough case with it.

What I need help is getting enough information to either point to how we manage ID's or at the very least rule them out. I've done searchs in google and some other search engines but there doesnt seem to be any good articles on how best to handle unique ids and show a good case for it. I do know I can use sql identity, or even guid with the newid() function but I need solid proof before my boss will move to one of these methods. Any links you guys can give or even your own write up will really help my case and hopefully help my headache.

Thanks Ahead of timeI hope at least that you have an index on the id column. I do know that the spike in connections is caused by the blocking, so you are right in thinking that solving the blocking problem will solve the connection problem.

How long does the insert take? If it is a row or two to a couple of nice normalized tables, they shouldn't cause so much trouble. If you are inserting into 100 column tables, then you have some redesigning ahead of you.

I am going to guess that the DBA wants you to save off the max(id) + 1 as your ID. This is a fairly common need, but can be accomplished better through stored procedures. Are you supporting SQL 2000 as well as another RDBMS? If so, then you might not be able to get around this, as you have to play to the lowest common denominator. I am not sure if Oracle, DB2 or any of the others have a new_id () - like function.|||Yeah indexing is in place and from what I can tell, done correctly. We update statistics every night and reindex maybe once a week or so now. The inserts is one at a time but we have about 100-150 users at any given time inserting orders not to mention data we pull in from the websites(10 different sites pulling in around 5000-6000 orders a day). The ugly part of the websites is that it is a cursor that does maybe 50 to 60 orders one at a time(we run the webimports once an hr for each site). We just can't seem to narrow it down and its driving us crazy.|||Yeah, IDENTITY would have made your life much easier, and you probably would not have had those blocking issues. You can and probably should add a table hint to the table where MAX id is stored, but...

The problem with this volume and concurrency is that using:

BEGIN TRAN
select @.id=max(id)+1 from tbl (HOLDLOCK)
INSERT...
...
COMMIT TRAN

would ensure consistency of data, but the duration of each transaction would also ensure that you'll continue having timeouts.

...And before certain "someone" jumps in to promote GUID, I'd say I'm all for it, but try to convince your developers and especially users that typing 5AA729BB-466C-40E8-A487-1C375CFB965B is easier than any sequencial number...|||Cursor that is run once per hour? Hmm. This could be a part of the problem. First thing is going to be finding out who is blocking whom. Try this out, and modify it to find out who is doing what:

select spid, cpu, physical_io, memusage, blocked, waittype, lastwaittype, waitresource
from master..sysprocesses
where blocked > 0
union
select spid, cpu, physical_io, memusage, blocked, waittype, lastwaittype, waitresource
from master..sysprocesses
where spid in (select blocked from master..sysprocesses)

You will probably want to add either program_name, hostname, or loginame to the above queries, depending on how you can tell the various processes apart.

If the ID column is indexed, then the select max(id) + 1 should take out just a rowlock on that one row. The cursor, on the other hand, could be doing all sorts of things to the back of your database. Let us know who is doing all the blocking. Then we can figure out what to do about it.|||Actually who is blocking who I can answer. Its actually across the board. If it was one particular process that would cause it we would have hunted that down. Sometimes it locks when the web imports comes in. When that happens, sometimes its the cursor, sometimes its somone out on the floor taking the order over the phone. Sometimes it happens when the web import is not occuring. This is the frustrating part...there really isn't a partern to it...|||Since no one process is to blame, have you narrowed it down to this one table with the ID column?

How long does it take to do the insert? If the insert goes over about 2 seconds (6000 inserts/8 hour shift comes out to one insert per 5 seconds, so taking gusts into account..), then you should be OK. Otherwise, blocking on the select is almost guaranteed.

As a wild-a** idea, you could build off both ends of the table, and have some processes add orders with select min(id) - 1 for their IDs. That may only get you so far, though.

I think the consensus is going to be try to eliminate the select part altogether, and get to an identity or new_id() way of doing things. It sounds like you do not have to worry about cross-platform or migration issues.|||You may be able to put a band-aid of sorts on this system. After thinking it over, this may be the second system I have seen in my career that could benefit from the (nolock) hint. If you use the (nolock) hint on the select max(id) + 1 query, then it will not be blocked by the insert queries. HOWEVER, you will run a slightly higher risk of inserting (or trying to insert) duplicate IDs into the table. I think this is already a minor problem in the design, because two connections could select the same max(id) if they are run close enough together. The increase in risk is that this time period will now expand by a few milliseconds, to wit, the amount of time between when the insert query sets up all of its locks, and when the insert actually happens. I doubt it is a long period of time (or at least I hope it is not), so you may get away with it. Just remember, (nolock) is not to be used lightly. If you do a search on (nolock) in this forum, you will probably find a number of posts saying do not use it. As was pointed out before, using either an identity column, or using new_id() would be a much better fix for this situation. This would only ever be a band-aid buying you a little time to implement a better solution. Good luck.