I know this is a way-out question, and I'm not confident of a positive
answer, but I'd like to know whether it is possible to protect/secure a View
so that it can only be used as a TARGET in a join operation, as opposed to
being used for direct SELECT.

for example:

SELECT * FROM MyView -- would fail

but

SELECT * FROM Customer c
LEFT OUTER JOIN MyView v ( on v.Key = c.ViewKey ) -- would succeed

The question is predicated on an idea that sometimes data is permissable to
access when presenting it as "secondary data" because we are accessing
individual "points" of data, but we don't want people accessing the entire
data-set.

I know that the JOINed query above could be made into another View, bypass
MyView altogether (simply reference the underlying table), in which case
MyView becomes superfluous and the desired outcome is achieved.

Except that: depending on the combinations of JOINS, and if there are
multiple of them, I might end up with a large number of views to allow for
all the combinations of different joins that I want. For example, a table
with 6 foreign keys has 64 different combinations to cover the different
joins I might or might not want to make. A table with 10 foreign keys takes
that up to 1024 combinations etc.

Kevin Frey (kevin_g_frey@hotmail.com) writes:
> I know this is a way-out question, and I'm not confident of a positive
> answer, but I'd like to know whether it is possible to protect/secure a
> View so that it can only be used as a TARGET in a join operation, as
> opposed to being used for direct SELECT.
>
> for example:
>
> SELECT * FROM MyView -- would fail
>
> but
>
> SELECT * FROM Customer c
> LEFT OUTER JOIN MyView v ( on v.Key = c.ViewKey ) -- would succeed
>
> The question is predicated on an idea that sometimes data is permissable
> to access when presenting it as "secondary data" because we are
> accessing individual "points" of data, but we don't want people
> accessing the entire data-set.

Consider:

SELECT v.*
FROM MyView v
JOIN dummytbl d ON 1 = 1

And if dummytbl is a one-row table, you still get all rows from MyView.

I find it difficult to achieve what you are looking for without writing
custom query tool where the users only can build queries in some guided
way, and where you disallow certain constructs or impose a TOP clause
to the queries.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx