August 19, 2004 at 5:55 am
Hi,,
I read an article about Virtual private database by Oracle, a system to define right by ROW (each user has a where clauses added to each query).
Is it possible to do that with SQL SERVER ?
Tom
August 19, 2004 at 6:23 am
i've foud the easiest way to do this is create a user-row lookup table - where you match usernames and criteria.
then create a view in sql server and only present the data available for modification
alternatively you can create a trigger for update/delete on your tables that checks against this lookup table and commits or rolls back the transaction.
MVDBA
August 19, 2004 at 6:33 am
YEs but you have to do the same step for each tables !! But a good idea for a one shot !!
Tom
August 19, 2004 at 6:45 am
there's nothing native to sql2000 for this.
esentially it's going to be down to something you implement in each view or table.
MVDBA
August 19, 2004 at 6:58 am
A good starting point if you want to build your own solution:
MVP Vyas Kondreddi's article on row-level security in SQL Server
K. Brian Kelley
@kbriankelley
August 19, 2004 at 7:05 am
yeah - a bit limited though - your permissions are only for rows you created - what if you want to mod rows that belong to a user group - or with a mixed set of criteria
MVDBA
August 19, 2004 at 7:21 am
Extrapolate to a groups table or the like... in much the same way as many of the community message forums work behind the scenes nowadays. Ultimately it gives a starting point to understand how to architect a row-level solution... covers the basics. Obviously most row-level solutions are going to be specific to the application/organization.
K. Brian Kelley
@kbriankelley
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply