ownership chains, users and views

We have a design using views to partition visibility of table rows by sql us
er. Say we have two salesmen Fred and Barney, we wanted to create 2 views:
CREATE VIEW Fred.SALES_LEADS AS SELECT * FROM dbo.ALL_SALES_LEADS WHERE SALE
S_PERSON='Fred'
and
CREATE VIEW Barney.SALES_LEADS AS SELECT * FROM dbo.ALL_SALES_LEADS WHERE SA
LES_PERSON='Barney'
Neither salesman should be able to select from ALL_SALES_LEADS and each, bei
ng amazingly SQL savvy salesmen, can log on to sql and execute SELECT * FROM
SALES_LEADS WHERE STATUS='New'.
Problem is that neither because neither Fred nor Barney owns the ALL_SALES_L
EADS table, they can't select off the view with any combination of GRANT/DEN
Y/REVOKE statements that we can find because the ownership chains are broken
.
Our best fallback is to name the views differently but leave them owned by d
bo. Not as neat and tidy. ie
CREATE VIEW dbo.Fred_SALES_LEADS AS SELECT * FROM dbo.ALL_SALES_LEADS WHERE
SALES_PERSON='Fred'
GRANT SELECT ON dbo.Fred_SALES_LEADS TO Fred
Is there any way around our original problem, SQL gurus? I think this would
be a very good use of user-owned views in a number of realistic scenarios.> Our best fallback is to name the views differently but leave them owned by
dbo.
The table and view need to have the same owner in order to use views as a
security mechanism. If each salesperson accesses the database with their
own userid, you might consider filtering using database userid instead of a
hard-coded constant. This way, you only need one view. For example:
CREATE VIEW dbo.SALES_LEADS AS
SELECT *
FROM dbo.ALL_SALES_LEADS
WHERE SALES_PERSON = CURRENT_USER
GO
GRANT SELECT ON dbo.SALES_LEADS TO SalesRole
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Dave Cattermole" <anonymous@.discussions.microsoft.com> wrote in message
news:726B0928-E93C-4233-A5FE-051E8ADA5684@.microsoft.com...
> We have a design using views to partition visibility of table rows by sql
user. Say we have two salesmen Fred and Barney, we wanted to create 2 views:
> CREATE VIEW Fred.SALES_LEADS AS SELECT * FROM dbo.ALL_SALES_LEADS WHERE
SALES_PERSON='Fred'
> and
> CREATE VIEW Barney.SALES_LEADS AS SELECT * FROM dbo.ALL_SALES_LEADS WHERE
SALES_PERSON='Barney'
> Neither salesman should be able to select from ALL_SALES_LEADS and each,
being amazingly SQL savvy salesmen, can log on to sql and execute SELECT *
FROM SALES_LEADS WHERE STATUS='New'.
> Problem is that neither because neither Fred nor Barney owns the
ALL_SALES_LEADS table, they can't select off the view with any combination
of GRANT/DENY/REVOKE statements that we can find because the ownership
chains are broken .
> Our best fallback is to name the views differently but leave them owned by
dbo. Not as neat and tidy. ie
> CREATE VIEW dbo.Fred_SALES_LEADS AS SELECT * FROM dbo.ALL_SALES_LEADS
WHERE SALES_PERSON='Fred'
> GRANT SELECT ON dbo.Fred_SALES_LEADS TO Fred
> Is there any way around our original problem, SQL gurus? I think this
would be a very good use of user-owned views in a number of realistic
scenarios.