Data hiding

Hi,
I wonder if anyone knows if it is possible to configure SQL Server 2000 so
that rows inserted in a table by user A are invisible for user B. User A and
B both insert and select into the same table but only sees their own rows. Is
there inbuild support for such "data hiding" or would I have to build myself?
//Magnushttp://vyaskn.tripod.com/row_level_security_in_sql_server_databases.htm
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Magnus" <Magnus@.discussions.microsoft.com> wrote in message
news:A3FC5F3B-225D-44FF-9698-9E014BADF780@.microsoft.com...
> Hi,
> I wonder if anyone knows if it is possible to configure SQL Server 2000 so
> that rows inserted in a table by user A are invisible for user B. User A and
> B both insert and select into the same table but only sees their own rows. Is
> there inbuild support for such "data hiding" or would I have to build myself?
> //Magnus|||Thanks!
So SQL Server does not directly support VPD, like Oracle apparently does.
What ever solution I choose, I'll be forced to make changes in my client. Is
there a reason to why SQL Server lacks this feature?
/Magnus
"Tibor Karaszi" wrote:
> http://vyaskn.tripod.com/row_level_security_in_sql_server_databases.htm
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Magnus" <Magnus@.discussions.microsoft.com> wrote in message
> news:A3FC5F3B-225D-44FF-9698-9E014BADF780@.microsoft.com...
> > Hi,
> > I wonder if anyone knows if it is possible to configure SQL Server 2000 so
> > that rows inserted in a table by user A are invisible for user B. User A and
> > B both insert and select into the same table but only sees their own rows. Is
> > there inbuild support for such "data hiding" or would I have to build myself?
> >
> > //Magnus
>
>|||We're doing this now, and no client changes are needed. We removed SELECT
from the underlying tables, and created Views based on suser_sname as
suggested.
Jeff
"Magnus Zotterman" <Magnus Zotterman@.discussions.microsoft.com> wrote in
message news:6F92D63D-EEE2-483A-8342-8E32A8AE1856@.microsoft.com...
> Thanks!
> So SQL Server does not directly support VPD, like Oracle apparently does.
> What ever solution I choose, I'll be forced to make changes in my client.
Is
> there a reason to why SQL Server lacks this feature?
> /Magnus
> "Tibor Karaszi" wrote:
> > http://vyaskn.tripod.com/row_level_security_in_sql_server_databases.htm
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "Magnus" <Magnus@.discussions.microsoft.com> wrote in message
> > news:A3FC5F3B-225D-44FF-9698-9E014BADF780@.microsoft.com...
> > > Hi,
> > > I wonder if anyone knows if it is possible to configure SQL Server
2000 so
> > > that rows inserted in a table by user A are invisible for user B. User
A and
> > > B both insert and select into the same table but only sees their own
rows. Is
> > > there inbuild support for such "data hiding" or would I have to build
myself?
> > >
> > > //Magnus
> >
> >
> >|||Hi Jeff!
Hmm, I don't really understand what you mean with removing SELECT from the
underlying tables. My problem is that my client doesn't do all db-access
through stored procedures. Have you been able to implement row level security
without forcing the client to use stored procedures? Then *please* tell me
how you do it!
/Magnus
"Jeff Dillon" wrote:
> We're doing this now, and no client changes are needed. We removed SELECT
> from the underlying tables, and created Views based on suser_sname as
> suggested.
> Jeff
> "Magnus Zotterman" <Magnus Zotterman@.discussions.microsoft.com> wrote in
> message news:6F92D63D-EEE2-483A-8342-8E32A8AE1856@.microsoft.com...
> > Thanks!
> >
> > So SQL Server does not directly support VPD, like Oracle apparently does.
> > What ever solution I choose, I'll be forced to make changes in my client.
> Is
> > there a reason to why SQL Server lacks this feature?
> >
> > /Magnus
> >
> > "Tibor Karaszi" wrote:
> >
> > > http://vyaskn.tripod.com/row_level_security_in_sql_server_databases.htm
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > http://www.karaszi.com/sqlserver/default.asp
> > > http://www.solidqualitylearning.com/
> > >
> > >
> > > "Magnus" <Magnus@.discussions.microsoft.com> wrote in message
> > > news:A3FC5F3B-225D-44FF-9698-9E014BADF780@.microsoft.com...
> > > > Hi,
> > > > I wonder if anyone knows if it is possible to configure SQL Server
> 2000 so
> > > > that rows inserted in a table by user A are invisible for user B. User
> A and
> > > > B both insert and select into the same table but only sees their own
> rows. Is
> > > > there inbuild support for such "data hiding" or would I have to build
> myself?
> > > >
> > > > //Magnus
> > >
> > >
> > >
>
>|||We're not using stored procedures either.You remove SELECT permissions from
the tables, then create Views that use suser_sname() in the Where clause,
and give SELECT permissions on the Views.
Jeff
"Magnus Zotterman" <Magnus Zotterman@.discussions.microsoft.com> wrote in
message news:B69BAD33-2BFF-489E-83F7-4BDC88AD66EA@.microsoft.com...
> Hi Jeff!
> Hmm, I don't really understand what you mean with removing SELECT from the
> underlying tables. My problem is that my client doesn't do all db-access
> through stored procedures. Have you been able to implement row level
security
> without forcing the client to use stored procedures? Then *please* tell me
> how you do it!
> /Magnus
> "Jeff Dillon" wrote:
> > We're doing this now, and no client changes are needed. We removed
SELECT
> > from the underlying tables, and created Views based on suser_sname as
> > suggested.
> >
> > Jeff
> > "Magnus Zotterman" <Magnus Zotterman@.discussions.microsoft.com> wrote in
> > message news:6F92D63D-EEE2-483A-8342-8E32A8AE1856@.microsoft.com...
> > > Thanks!
> > >
> > > So SQL Server does not directly support VPD, like Oracle apparently
does.
> > > What ever solution I choose, I'll be forced to make changes in my
client.
> > Is
> > > there a reason to why SQL Server lacks this feature?
> > >
> > > /Magnus
> > >
> > > "Tibor Karaszi" wrote:
> > >
> > > >
http://vyaskn.tripod.com/row_level_security_in_sql_server_databases.htm
> > > >
> > > > --
> > > > Tibor Karaszi, SQL Server MVP
> > > > http://www.karaszi.com/sqlserver/default.asp
> > > > http://www.solidqualitylearning.com/
> > > >
> > > >
> > > > "Magnus" <Magnus@.discussions.microsoft.com> wrote in message
> > > > news:A3FC5F3B-225D-44FF-9698-9E014BADF780@.microsoft.com...
> > > > > Hi,
> > > > > I wonder if anyone knows if it is possible to configure SQL Server
> > 2000 so
> > > > > that rows inserted in a table by user A are invisible for user B.
User
> > A and
> > > > > B both insert and select into the same table but only sees their
own
> > rows. Is
> > > > > there inbuild support for such "data hiding" or would I have to
build
> > myself?
> > > > >
> > > > > //Magnus
> > > >
> > > >
> > > >
> >
> >
> >