1

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:

  1. 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) ?

  2. is it possible to create separate tablespace for that schema and create views and grants that will work across both of them ?

  3. if both are possible than which one should be used and why ?

Thank you in advance.

update:

I created 3 schemas:

A is the one which has tables FOO

 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.

1

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) ?

Yes. And if your concern is limited to just read-only access to those tables then you don't even need to create separate views, you can simply create new schema and grant select on those tables.

is it possible to create separate tablespace for that schema and create views and grants that will work across both of them ?

Yes. If you just create views on this schema then you even don't need to worry to manage separate tablespace because views(is a named and validated SQL query) are stored in the Oracle data dictionary.

if both are possible than which one should be used and why?

It's up to you. In my opinion, if you place schema in to the separate tablespace then you can transport it to another database with ease. Also it makes possible to perform point-in time recovery. Additionally, we can place such tablespace to different disks as per the IO performance requirements.

Update

You have to have granted select on FOO with grant option to user B in order for user 'B' to grant select on view to user 'C' as demonstrated below.

SQL> conn a/a
Connected.

SQL> grant select on t1 to b with grant option;

Grant succeeded.

SQL> conn b/b
Connected.

SQL> create or replace view v_t1 as select * from a.t1;

View created.

SQL> grant select on v_t1 to c;

Grant succeeded.

SQL> conn c/c
Connected.
SQL> select * from b.v_t1;

    ID
----------
     1
  • I put an update to the original question. Please take a look – pawel7318 Jan 11 '17 at 17:13
  • @pawel7318: Answer is updated to address your issue.. – JSapkota Jan 12 '17 at 4:06
  • Now it perfectly works ! grant ... with grant option did the trick – pawel7318 Jan 12 '17 at 9:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.