Re: Jailing a schema from PUBLIC

  • From: "Rich Jesse" <rjoralist@xxxxxxxxxxxxxxxxxxxxx>
  • To: "rjamya" <rjamya@xxxxxxxxx>, oracle-l@xxxxxxxxxxxxx
  • Date: Tue, 13 Feb 2007 23:44:34 -0600 (CST)

Well, I tried it out.  I even expanded it a bit.  I ran the output from:

select distinct 'create or replace view rejlink.'||dv.view_name||
        ' as select * from sys.'||dv.view_name||' where owner != ''CVDTA'';'
from dba_views dv, dba_tab_columns dtc
where dv.owner = 'SYS'
and dv.view_name not like 'USER%'
and dv.view_name = dtc.table_name
and dtc.column_name = 'OWNER'
order by 1;

I then logged on to REMOTESCHEMA, but it can successfully read tables in
schema VENDOR_A.  I then added a private view for CATALOG, but the tables
can still be accessed.

But perhaps you're on the right track.  I wonder if there's an event that
can be set at the session level to trace what objects Oracle uses to
determine access to an object.  Something to investigate...


> Hey rjamya,
> Do you mean that for this statement:
> SELECT * FROM schema_a.table_1@remote_db;
> ...that the ALL_* views are needed in the "remote_db" schema pointed to by
> the dblink in order to gain access to that table?
> There's a lot of ALL_* views to modify to try this out, but I'll give it a
> shot.
> Hopefully your somewhat near Wisconsin (aka "The Beer State"), USA if this
> works!  :)
> Rich
>> wild idea,
>> since public access is visible through ALL_* views, you can create local
>> ALL_* views in schema B. These would be essentially same as regular views
>> but you'd filter out owner A.
>> like I said, it is a wild idea. if it works, I'll take a beer.
>> rjamya


Other related posts: