Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Show Right Column to Right User Expand / Collapse
Author
Message
Posted Monday, August 6, 2012 12:42 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, June 9, 2014 1:21 AM
Points: 58, 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)

Post #1340374
Posted Monday, August 6, 2012 7:39 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:36 AM
Points: 12,910, Visits: 32,029
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1340601
Posted Tuesday, August 7, 2012 5:15 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 12:36 AM
Points: 178, Visits: 574
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.
Post #1341155
Posted Tuesday, August 7, 2012 5:39 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:36 AM
Points: 12,910, Visits: 32,029
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1341168
Posted Wednesday, September 26, 2012 7:50 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Saturday, August 17, 2013 7:45 PM
Points: 65, Visits: 228
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.
Post #1365000
Posted Thursday, September 27, 2012 4:08 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:36 AM
Points: 12,910, Visits: 32,029
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1365127
Posted Wednesday, November 21, 2012 12:10 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 4:12 PM
Points: 958, Visits: 1,031
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.
Post #1387571
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse