Background
A requirement arose recently from the manager of one of our busy teams to know what rights her individual staff had to edit data on the system they use. These particular rights were stored in a table and used thus by the application; they are not therefore database permissions as such: hence the quotes in the title. This was coupled with a request to be able to edit them since the throughput of staff was often high and roles could be changed ‘on the hoof’ as different workload priorities were identified. If the process for changing these rights is too involved, you can end up with staff retaining permissions which are inappropriate or them logging in as someone else just to get the job done.
This requirement was then followed by a request from one of our IT staff who visit our remote offices to be able to do the same when setting up new users on site and being able to check and amend their rights without having to call Head Office.
The traditional way of responding to requests like these would be to write a VB application; in the future they’ll be .NET apps, but we’re not there yet! But wait, we could write a .NET app right now, … using Reporting Services.
The Report
The table which contains the user rights has a column for the username followed by columns for each of the areas of the system where they can be allowed editing rights. In the example these areas are called p, q and r and the rights are therefore p_edit, … etc. A zero means no rights; a one indicates editing is allowed.
The report has two parameters, username and ‘perm’ (which is the column name in the table) and runs a stored procedure. It happily displays the table whenever you run it, the two parameters being set to NULL. However if you want to change a right you just have to click on the cell corresponding to that user’s username and the area of the system concerned. This activates a hyperlink which runs the same report again, only this time with the two parameters filled in. The underlying stored procedure catches the non-null parameters, switches the permissions, then returns the resultset as usual.
If you have security concerns, apart from bolting down the stored proc and the report itself, you can always run it with credentials specifying an SQL logon which has select and update permissions only for this table.
Conclusions
This approach blurs the traditional distinction between applications and reports, but there are several areas like the one above where users want to do a little more than just display the data, but that little extra is intimately bound up with the data being displayed.
Consider an exception report. The user lists off a number of data exceptions which are generally handled according to easily programmable rules. You build in a hyperlink which goes away and does the necessary processing before rerunning the report and displaying what is now a blank list. But before doing this processing, the user wants to check there are no 'oddities' for which the rules aren't valid. They deal with the oddities elsewhere, rerun your report and then process what's left. This is another real life example and I'm sure there will be many others.
Of course there's nothing to stop you building a search screen (parameters would be the search fields, report would display the resulting rows) and linking this to a record edit page, ... but maybe that's going too far?!
