horizontal data restriction

[pp]

I have 2 installations:
1. W2k with MS SQL 2000 sp2.
2. W2003 with MS SQL sp3.

There are two databases in all instalations: "maindb" and "userdb". The
table with data is in maindb.

In the 1'st installation I restict user access to all data in table in this
way:
- create user's login and user in databases maindb and userdb.
- create view in userdb with "where" clause as dbo (dbo is owner of this
view). This clause restict access to data. (create view userdb.dbo.tabl e as
select * from maindb.dbo.tabl e where ...)
- add user to group "Public" in maindb.
- add role "data reader" to user in userdb.
- effect: user can access data only by view, can not access any data in
maindb.

I do the same in secound installation:
- effect: user can not access data by view - message like this: "user
have not permission to select on maindb.dbo.tabl e"

Is this bug in sp3 or in sp2 ?

Is there another way to horizontal restrict access data in tables?

In Sybase ASE this method (restict by view) works ok. And there is new
property of ASE 12.5.1 - administrator can define context of login - the
"where" clause will be added automatically to any select.

Please help me. Thank You for any advice.

[Greg D. Moore \(Strider\)]

Re: horizontal data restriction


"pp" <pp@oih.ko> wrote in message news:bt177s$ar6 $1@galaxy.uci.a gh.edu.pl...[color=blue]
> I have 2 installations:
> 1. W2k with MS SQL 2000 sp2.
> 2. W2003 with MS SQL sp3.
>
> There are two databases in all instalations: "maindb" and "userdb". The
> table with data is in maindb.
>
> In the 1'st installation I restict user access to all data in table in[/color]
this[color=blue]
> way:
> - create user's login and user in databases maindb and userdb.
> - create view in userdb with "where" clause as dbo (dbo is owner of[/color]
this[color=blue]
> view). This clause restict access to data. (create view userdb.dbo.tabl e[/color]
as[color=blue]
> select * from maindb.dbo.tabl e where ...)
> - add user to group "Public" in maindb.
> - add role "data reader" to user in userdb.
> - effect: user can access data only by view, can not access any data[/color]
in[color=blue]
> maindb.
>
> I do the same in secound installation:
> - effect: user can not access data by view - message like this: "user
> have not permission to select on maindb.dbo.tabl e"
>
> Is this bug in sp3 or in sp2 ?[/color]

Might be an affect of cross-database ownership chaining which is disabled as
a default in SP3.

[color=blue]
>
> Is there another way to horizontal restrict access data in tables?
>
> In Sybase ASE this method (restict by view) works ok. And there is new
> property of ASE 12.5.1 - administrator can define context of login - the
> "where" clause will be added automatically to any select.
>
> Please help me. Thank You for any advice.
>
>
>
>[/color]

[Dan Guzman]

Re: horizontal data restriction

To add to Greg's response, both maindb and userdb need to have the same
owner in order for the ownership chain to be unbroken. If necessary, you
can change the database owners to a common login with sp_changedbowne r.

--
Happy Holidays

Dan Guzman
SQL Server MVP


"pp" <pp@oih.ko> wrote in message news:bt177s$ar6 $1@galaxy.uci.a gh.edu.pl...[color=blue]
> I have 2 installations:
> 1. W2k with MS SQL 2000 sp2.
> 2. W2003 with MS SQL sp3.
>
> There are two databases in all instalations: "maindb" and "userdb". The
> table with data is in maindb.
>
> In the 1'st installation I restict user access to all data in table in[/color]
this[color=blue]
> way:
> - create user's login and user in databases maindb and userdb.
> - create view in userdb with "where" clause as dbo (dbo is owner of[/color]
this[color=blue]
> view). This clause restict access to data. (create view userdb.dbo.tabl e[/color]
as[color=blue]
> select * from maindb.dbo.tabl e where ...)
> - add user to group "Public" in maindb.
> - add role "data reader" to user in userdb.
> - effect: user can access data only by view, can not access any data[/color]
in[color=blue]
> maindb.
>
> I do the same in secound installation:
> - effect: user can not access data by view - message like this: "user
> have not permission to select on maindb.dbo.tabl e"
>
> Is this bug in sp3 or in sp2 ?
>
> Is there another way to horizontal restrict access data in tables?
>
> In Sybase ASE this method (restict by view) works ok. And there is new
> property of ASE 12.5.1 - administrator can define context of login - the
> "where" clause will be added automatically to any select.
>
> Please help me. Thank You for any advice.
>
>
>
>[/color]