Show Right Column to Right User

Question

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 (Design of a data warehouse with more than one fact tables)

Answer 1

SQL Server does have the ability to assign column permissions (http://msdn.microsoft.com/en-us/library/ms180341%28v=sql.105%29.aspx). You can set the specific permissions as you like, by treating each column as an object with its own security.

Managing column level security is likely to be cumbersome, because you have to remember to update the security every time the table changes and new users are added.

You consider a different approach. Define a separate view for each of the different groups. Only the manager would have access to the "manager" view; only the salesman (and the manager perhaps) would have access to salesman view and so on. Then build the application for each group based on those views.

Finally, managing multiple views might be a bit cumbersome. Instead, you can also have a table-valued function that wraps all the views into a single function. The function would check the permissions for each user and choose the appropriate data to return.

The advantage of user defined functions is that only the user who created the function needs to have access to the underlying tables. That is, the users only have permissions for the function; otherwise, they cannot see the underlying tables. The function would control what they can see.