Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
malachi151
Posting Yak Master
152 Posts |
 Posted - 2012-03-23 : 14:48:31
|
| Someone today made a claim about Oracle having a feature (Virtual Private Database) where supposedly you somehow can define security for users such that when a user writes a query against a table, it will automatically filter the results returned to them.The example was if you have a table with data for multiple customers and you expose it to customers, if they write SELECT * FROM SomeTable it would only return the records for that customer by automatically, behind the scenes, adding a WHERE clause something like WHERE CustomerId = 123, it buts transparent to the user and doesn't require any programming to setup...Or something along those lines. He was asking if SQL Server can do this.The closest thing I can think of to do this would be to setup parameterized Table Valued Functions.Any insight into this?--------------------------------------------------My blog: http://practicaltsql.net/Twitter: @RGPSoftware |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2012-03-23 : 14:57:11
|
| http://www.symantec.com/connect/articles/oracle-row-level-security-part-1I don't believe SQL Server has thatNot to say that it can't be imitatedYou can create your own rules and application security in SQL Server..based on what ever you deem...We have done this bunches of times for peoplesoft based on organizations and the PS Security file...and also using surrogates that can act on behalf of another groupReally no big dealA rose by any other name....Brett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxWant to help yourself?http://msdn.microsoft.com/en-us/library/ms130214.aspxhttp://weblogs.sqlteam.com/brettk/http://brettkaiser.blogspot.com/ |
 |
|
|
malachi151
Posting Yak Master
152 Posts |
Posted - 2012-03-23 : 15:30:25
|
| Yeah, I looked into this some more. There is an article on TechNet that basically describes how to do it:http://technet.microsoft.com/en-us/library/cc966395.aspxHowever, having looked at this and the Oracle implementation, I'm not sure either does what we need, because we aren't using actual database logins, we are have our own users table, so it looks like we would need to implement something along the lines of what's described in the SQL Server link, but joining to our user tables instead of the system ones. So it looks like the Oracle VPD wouldn't actually work either anyway... far as I can tell...Thanks--------------------------------------------------My blog: http://practicaltsql.net/Twitter: @RGPSoftware |
|
|
|
|
|
|
|
|
Row level security??