June 22, 2012 at 8:15 am
I am looking for the best way to seperate out data stored in a table based on a users security role. Each users has a login that indicates his or her security role (e.g. a field called SecRole would have one of many entries Sales, Admin, HR, Manager, etc.) We want to write reports and depending on a users Security role to present the data via views of the underlying tables. So a Sales person would see one set of data from a table and HR would see another set of data from the same table. Each of the underlying tables we want to compartmentalize has a field that indicates what security level the rows is associated with. Therefore each view is created doing a simple Select * where SecRole = xxx
The only issue with this scenario is for every report I have to write a stored procedure and have a bunch of if statements testing which Security role is being used so I can determine which view to run the query against. This would be fine for one or two roles but as we add more roles this becomes a little unwieldy...
If @Secrole = Sales
Select * from Salesview
else if @Secrole = HR
Select * from HRview
Else if ...
I have over 200 reports that have to be modified to now begin to use views rather than query against the table itself. If I were able to use a Variable on the "From" clause this would be a piece of cake. I could use Dynamic queries but some of our "Select" statements are pretty complicated and quoting strings gets difficult to maintain.
So my question is: is there a way other than views and IF/Else statements to compartmentalize data in a table and present the data via reports so that protected data is never presented to unauthorized users.
Thanks in Advance for any suggestions...
June 22, 2012 at 8:19 am
You can create inline table valued functions and use those as parameterized views, passing in the appropriate values to obtain the desired result set.
June 22, 2012 at 12:40 pm
Lynn Thanks for the quick response...
I have been experimenting with this approach. I do have one question.
If I had a table that had 10 million rows and I only wanted to see data from the sales department (say 5 million rows and counting) would the IN-line function first return all the sales rows and then evaluate the rest of the query based on the where clause???
Each report can use different filters based on data required so writing a different in-line function for each report filter would seem to be required. I can set this up on another server but it will take me some time to test but I would think the response will be dependent somewhat if the inline function only filters on Secrole and not additional filters???
e.g.
Create FUNCTION [dbo].[udf_GetMyTableDataBySecRole]
(
-- Add the parameters for the function here
@SecRole VarChar(20)
)
RETURNS TABLE
AS
RETURN
(
-- Add the SELECT statement with parameter references here
SELECT* fromMyTable Where SecRole = 'Sales'
)
Select a.saleItem, a.salequanity, a.saleprice, b.Storename
From dbo.udf_GetMyTableDataBySecRole ('Sales') a -- Do I get x rows of data then filter on where clause
join store b On a.storecode= b.storecode
Where a.saledate between @BegDate and @EndDate
And a.salesperson = 'Bill'
June 22, 2012 at 12:52 pm
Reg_Mayfield (6/22/2012)
Lynn Thanks for the quick response...I have been experimenting with this approach. I do have one question.
If I had a table that had 10 million rows and I only wanted to see data from the sales department (say 5 million rows and counting) would the IN-line function first return all the sales rows and then evaluate the rest of the query based on the where clause???
Each report can use different filters based on data required so writing a different in-line function for each report filter would seem to be required. I can set this up on another server but it will take me some time to test but I would think the response will be dependent somewhat if the inline function only filters on Secrole and not additional filters???
e.g.
Create FUNCTION [dbo].[udf_GetMyTableDataBySecRole]
(
-- Add the parameters for the function here
@SecRole VarChar(20)
)
RETURNS TABLE
AS
RETURN
(
-- Add the SELECT statement with parameter references here
SELECT* fromMyTable Where SecRole = 'Sales'
)
Select a.saleItem, a.salequanity, a.saleprice, b.Storename
From dbo.udf_GetMyTableDataBySecRole ('Sales') a -- Do I get x rows of data then filter on where clause
join store b On a.storecode= b.storecode
Where a.saledate between @BegDate and @EndDate
And a.salesperson = 'Bill'
An inline table valued function gets expanded at run time.
Changing your example a bit:
Create FUNCTION [dbo].[udf_GetMyTableDataBySecRole]
(
-- Add the parameters for the function here
@SecRole VarChar(20)
)
RETURNS TABLE
AS
RETURN
(
-- Add the SELECT statement with parameter references here
SELECT* fromMyTable Where SecRole = @SecRole
)
Now using this, you could do this:
select * from [dbo].[udf_GetMyTableDataBySecRole]('Sales');
select * from [dbo].[udf_GetMyTableDataBySecRole]('Sales') where EffectiveDate >= '20120101' and EffectiveDate < '20120401';
You basically use the iTVF as a table or view, but it allows you to pass parameters that change what is returned.
About the best I do in a short post.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply