Posted by Kevin Frey on 10/10/07 08:09

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.

[Back to original message]