Hi, I know Fine-Grained Access Control permits adding a predicate to manage
access to objects.
The predicate is in the form of WHERE clause so you only can add filter to
controlled object.

Is there a way to substitute the object name ?

I need to deviate table selection into another table based on user access...

TIA.

On Wed, 9 Jul 2003 19:15:57 +0200, "Bepy" <ramengo (AT) hotmail (DOT) com> wrote:

No, You don't need to that.
Rethink your design and don't try to be 'smart'.
You should set up your system so you can use the pseudo function user

Why do you think when it isn't in the docs, it is still possible?




Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address

"Bepy" <ramengo (AT) hotmail (DOT) com> wrote


Yes.

Not directly, no. Can be simulated with some tricky SQL
but I wouldn't bother, quite frankly. Read on.

What's wrong with each user having its own synonym pointing
to appropriate table?
Fine-grained access is intended to control security across rows
of a table. Not for controlling security across multiple tables.
It won't work easy for what you want. May I suggest you look at synonyms
or some other way? Perheaps INSTEAD OF triggers or something like that?


Cheers
Nuno Souto
wizofoz2k (AT) yahoo (DOT) com.au.nospam

Thanks for previous answers.
Maybe I'm in the wrong path with FGAC...

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@dblink1 a
union all
select 2 db_name, a.* from a@dblink2 a
union all
....

That way is good as long as *all* dblink are up&running, but if one of these
become invalid (for example because Oracle goes down) the whole view become
unusable (even if still valid).

Can anybody help ?

"Bepy" <ramengo (AT) hotmail (DOT) com> wrote

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