May 21, 2008 at 8:36 am
hi,
Does somebody know if I can hide data in a table based on a columnvalue? (like Oracle)
For example, we have a salesorder table with a column companyid. Employees are only able to see rows from their own company. The solution must be based on security posibilities, not views.
Datababase is 2005.
Wilfred
The best things in life are the simple things
May 21, 2008 at 10:13 am
I don't think there's a built-in way to do this. This is probably best handled in code in stored procedures. How are employees accessing the data? Do they use an application or do they have direct access to the tables via SSMS?
Greg
May 22, 2008 at 4:31 am
data is accessed directly
Wilfred
The best things in life are the simple things
May 22, 2008 at 6:26 am
The only option to do this is to create either a view, give your users read permission on the view and deny read permission on the table.
If you want to filter dynamically depending on the user you need to use a stoed procedure or function instead of the view.
[font="Verdana"]Markus Bohse[/font]
May 23, 2008 at 8:14 am
Having worked extensively with Oracle, that feature, along with a lot of other features, does not exist in SQL Server.
Your only solution is to use a view. You can probably use a function in the WHERE clause definition of the view to filter the rows based upon some criteria.
E.g., (just off the top of my head) as the customer logs in, the customer number is placed into a table. Then the view definition performs a join to this table, thus filtering the rows. Or a mapping table which maps user (SQL Server login) to customer id. You somehow need to exploit who is logged in as part of the mapping.
May 27, 2008 at 4:25 am
I found this article at Microsoft which comes pretty close:
http://www.microsoft.com/technet/prodtechnol/sql/2005/multisec.mspx
Wilfred
The best things in life are the simple things
May 28, 2008 at 12:29 am
Yes, as earlier posts says, use views.
Apart from that you can use encryption/decryption to hide values and allow to show table column.
May 28, 2008 at 10:37 am
The approach is commonly referred to as "row-level security" when dealing with SQL Server and unfortunately, unlike Oracle, it requires a home grown solution.
The standard way is to create a VIEW which in the WHERE clause can filter the data in the table based on the identity of the user. For instance, one way is to check the Windows groups, another way is to have every user login mapped to a companyID in another table and then doing the appropriate join.
Since SQL Server has the concept of ownership chaining, you can grant access to the VIEW but not to the base table so long as the schemas (I'm assuming SQL Server 2005) the view and table are part of have the same owner. Obviously, if both are in the same schema, then they do have the same owner.
K. Brian Kelley
@kbriankelley
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply