Re: Jailing a schema from PUBLIC

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...

Thanks!
Rich

> Hey rjamya,
>
> Do you mean that for this statement:
>
> SELECT * FROM schema_a.table_1_at_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
>>
>
>

--
http://www.freelists.org/webpage/oracle-l