Hello all,
I have the following problem to resolve. I have a lot of tables and views in
the database. I can't grant user access to the separate tables, because what
may be written to the tables has to be 'filtered' by the views that are on
top of them. More, the database can be accessed over ODBC, and granting
access to the tables can violate the integrity checking (I can't use
standard rule checking neither, believe me).
So every table's data change is "overruled" by views (and rules on them).
There are other (dynamically changing) rights in the database, and it is
based on these that the view's rules will check whether yes or no a separate
data field in a table will be changeable.
If a 'DO INSTEAD SELECT .....'- clause is executed in such a rule, it is
executed with the rights of the VIEW itself (with the VIEW having access
rights on the table that's still invisible and inaccessible to the users
themselves).
But I have a problem : if I make such a 'DO INSTEAD'- clause, everything has
to fit in 1 select statement. BUT, checking access rights this way, on
several different fields, performing several different actions (/if-
statements) on the separate fields, is very hard, if not at all impossible
in 1 single select statement. So I thought to do this in a _function_ called
from the INSTEAD- clause : ".... DO INSTEAD SELECT my_func(...);"
BUT (and here comes the problem !!!), when that is done this way, the
function no longer executes with the right of the VIEW, but with the rights
of the user himself... making it again impossible to change separate fields
on the table on which the action is taken...
So my question is : can I do these things without having to accept (too)
many compromises on security, or should I adapt another way of working to do
what I want to do ?
Thanks, and kind regards,
Philippe Bertin.
Avionics Software Development Engineer.
P.S. I want by all means to keep the separate tables invisible to the users
(e.g. when they connect over ODBC).