Re: DBMS_RLS: Fine-Grained Access Control - Table name substitution

"Bepy" <ramengo_at_hotmail.com> wrote

> The real problem is this: we have a master environment connected with others
> subsystems via dblinks. An interface program is responsible for loading data
> from specific subsystem in a run passing subsystem name (dblink) as
> parameter.
> Since we don't want to duplicate queryies for every dblink, we built an
> "universal" view putting every base table from every dblink in union with
> others:
> select 1 db_name, a.* from a_at_dblink1 a
> union all
> select 2 db_name, a.* from a_at_dblink2 a
> union all
> ...

Personally, I hate these UNION ALL views for queries. That is what partitioning is there for.

But that will not solve your problem. If I was to build such an environment, I would likely have a separate (but identical) schema for each dblink environment. That provides for better control, administration and security.. and protect and isolate the environments from one another. Even the link name can be the same within the schema, but point to a different TNS alias.

On the development side, I would consider something like ALTER SESSION SET CURRENT_SCHEMA to process/query each dblink environment separately. The actual code (down to SQL dblink name) will be identical.

--
Billy