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...
Thanks!
Rich
> 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
>>
>
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
