Is it possible to extend every select query which is executed by a db user?

e.g. the user will execute select * from mytable and in the background the following is executed: select * from mytable union all select * from different_schema.mytable

It is possible to restrict a query by a dynamic where clause using VPD functions.

return '1=1'

Can I extend queries with VPD to also select data from different schemas?

return '1=1 union all select * from different_schema.' || tab;

I am getting the following error with the above example:

ORA-28113: policy predicate has error

A workaround would be to create a bunch of dynamically generated views containing the unions, but I would like to use VPD or a similar approach if possible.

CodePudding user response：

No, you can not do this with VPD. VPD will add a predicate to each query, which is an extension to the where clause of the query. It can not be used to union with another table.

I would suggest your other approach: use views that contain the unions and grant the appropriate rights on these views instead of the base table(s).