I have a schema with its tablespace where are application's data are located. Now I need a separate schema which will have an access only to select the data. I wish to create a set of views for that schema.
My questions are:
is it possible to create schema at the same tablespace and limit its grants just to some views (without giving an access to the original tables) ?
is it possible to create separate tablespace for that schema and create views and grants that will work across both of them ?
if both are possible than which one should be used and why ?
Thank you in advance.
I created 3 schemas:
A is the one which has tables
grant select on FOO to B
B is to create the view
create or replace view BAR as select * from A.FOO
C is suppose to be able to
select * from B.BAR but if try to (as B):
grant select on BAR to C
I'm getting an error:
Error: ORA-01720: grant option does not exist for 'A.FOO'
Basicaly I wish to achieve 3 levels:
A (original table) < B (view definition) < C (select only from the view)
But it seems that it requires me to grant C direct access to read the original table which fails all the idea.