SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Show Right Column to Right User


Show Right Column to Right User

Author
Message
akirajt
akirajt
SSC-Enthusiastic
SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)

Group: General Forum Members
Points: 150 Visits: 191
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
Lowell
SSC Guru
SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)

Group: General Forum Members
Points: 74991 Visits: 40985
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!
nick.mcdermaid
nick.mcdermaid
SSC Eights!
SSC Eights! (864 reputation)SSC Eights! (864 reputation)SSC Eights! (864 reputation)SSC Eights! (864 reputation)SSC Eights! (864 reputation)SSC Eights! (864 reputation)SSC Eights! (864 reputation)SSC Eights! (864 reputation)

Group: General Forum Members
Points: 864 Visits: 782
Here is a link on column level permissions

http://www.mssqltips.com/sqlservertip/2124/filtering-sql-server-columns-using-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.
Lowell
Lowell
SSC Guru
SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)

Group: General Forum Members
Points: 74991 Visits: 40985
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!
dj1202
dj1202
Old Hand
Old Hand (375 reputation)Old Hand (375 reputation)Old Hand (375 reputation)Old Hand (375 reputation)Old Hand (375 reputation)Old Hand (375 reputation)Old Hand (375 reputation)Old Hand (375 reputation)

Group: General Forum Members
Points: 375 Visits: 231
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.
Lowell
Lowell
SSC Guru
SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)

Group: General Forum Members
Points: 74991 Visits: 40985
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!
Bruce W Cassidy
Bruce W Cassidy
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3189 Visits: 1033
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search