Show Right Column to Right User
-
If I have three different user with different occupation (manager, salesman, accounting)
The main question is to how display right column to right person based on star schema and requirement below in SQL server?
The fact and dim are using regular table inside of data mart.
Background information:
The manager is authorized to see all column in factTransaction
The salesman is not allowed to see TaxAmount, TotalAmount and ProductBusinessKey.
The Accounting is note allowed to see Product Quantity, ProductPrice and GeographyFullname.
In windows, the they have their own user account.
The picture is take from the address (http://stackoverflow.com/questions/3308647/design-of-a-data-warehouse-with-more-than-one-fact-tables)
-
include all the columns, but blank them out based on permissions is what i would recommned.
something like this?
SELECT
SomeColumns,
CASE
WHEN IsSalesman = 1
THEN null
ELSE TaxAmount
END AS TaxAmount,
CASE
WHEN IsSalesman = 1
THEN null
ELSE TotalAmount
END AS TotalAmount
FROM factTransaction
akirajt (8/6/2012)
If I have three different user with different occupation (manager, salesman, accounting)The main question is to how display right column to right person based on star schema and requirement below in SQL server?
The fact and dim are using regular table inside of data mart.
Background information:
The manager is authorized to see all column in factTransaction
The salesman is not allowed to see TaxAmount, TotalAmount and ProductBusinessKey.
The Accounting is note allowed to see Product Quantity, ProductPrice and GeographyFullname.
In windows, the they have their own user account.
The picture is take from the address (http://stackoverflow.com/questions/3308647/design-of-a-data-warehouse-with-more-than-one-fact-tables)
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Here is a link on column level permissions
This assumes that your users log dierctly into SQL Server to run their queries. If you have some kind presentation layer that might be a better place to implement this.
-
ahh, but with column level permissions, you get errors that says user does not have permission to object:column, instead of empty values.
it depends on what the OP wants in that case.
For me, the column level permossions eliminate the ability to use the same report for all users...you need something that changes the report based on the user in that case.
if security is via database permissions,
i would use the IS_MEMBER('RoleName') = 1 function in the case statement example above, filter visibility based on the role the calling user is in;
if he has his own table within the application wwhich determines who is in which role, it's a basic modification of the same principal.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Sorry in advance for my novice question, but how will a CASE WHEN ISMEMBER() THEN clause be useful if the user is tasked with writing his own query in the first place? Couldn't he/she simply use a SELECT * to circumvent this? Or are you inserting this into a stored procedure and limiting the users to only being able to run the stored procedures? Again, apologies for the novice question.
-
Well. Remember that the sql security model is deny by default;
Its easy to forget that as a developer since you test as sa/sysadmin.
That means you can create a view and grant SELECT permissions to Bob, and he has no access at all to the table(s) the view uses.
But you are correct, if he had permissions to the table he could query it diectly.
The solution using ISMEMBER would require some tighter permissions
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
I can think of several ways to do this.
The first is to reflect the design into SSAS, and let the cube handle the security. While I'm only an SSAS novice, this is one of the things that it does well from memory. Users should only see what they are permitted to see.
The second is to create schemas for the users and in each schema, create views that filter out the data they are not permitted to see. Grant select rights on the views, not on the underlying tables.
The third is a variation on the second, and I think is better in terms of management. That is to create "subject areas" (again I would recommend schemas), create the views within the subject areas, assign the necessary permissions to the subject areas, and finally assign the users to the subject areas.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply