Skip to Main Content
  • Questions
  • Grant select on a View with grant option does not work

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Jorge.

Asked: November 10, 2017 - 1:44 am UTC

Last updated: January 22, 2024 - 1:55 pm UTC

Version: 11g

Viewed 50K+ times! This question is

You Asked

Hi,

I have Schema_1 that owns table_1, table_2, table_3.
Schema_1 creates View_1 using table_1,
Schema_1 Creates View_2 using table_2,
Schema_1 Creates View_3 using table_3.

Schema_2 Creates View_4 using View_1, View_2 and View_3.

Then as SYSDBA give the following Grants to Schema_3

GRANT SELECT ON Schema_2.View_4 TO Schema_3 WITH GRANT OPTION;
GRANT SELECT ON Schema_1.View_1 TO Schema_3 WITH GRANT OPTION; 
GRANT SELECT ON Schema_1.View_2 TO Schema_3 WITH GRANT OPTION; 
GRANT SELECT ON Schema_1.View_3 TO Schema_3 WITH GRANT OPTION; 
GRANT SELECT ON Schema_1.table_1 TO Schema_3;
GRANT SELECT ON Schema_1.table_2 TO Schema_3; 
GRANT SELECT ON Schema_1.table_3 TO Schema_3;


Finally using Schema_3 do the following query:

Select * from Schema_2.View_4;

ERROR at line 1:
ORA-01031: insufficient privileges



Do you have any clue?

Thanks.

and Chris said...

Are you sure you've created the view in schema 2? That needs privileges to on the views/tables in schema 1 too in order to work...

grant create session, create table, create view, unlimited tablespace to s1 identified by s1;
grant create session, create view to s2 identified by s2;
grant create session to s3 identified by s3;

create table s1.t1 (
  x int
);
create table s1.t2 (
  x int
);
create table s1.t3 (
  x int
);
create or replace view s1.vw1 as 
  select * from s1.t1;
create or replace view s1.vw2 as 
  select * from s1.t2;
create or replace view s1.vw3 as 
  select * from s1.t3;
  
create or replace view s2.vw4 as 
  select 'x' dummy from s1.vw1, s1.vw2, s1.vw3;

ORA-00942: table or view does not exist

select * from s2.vw4;

SQL Error: ORA-00942: table or view does not exist

GRANT SELECT ON S1.t1 TO S2;
GRANT SELECT ON S1.t2 TO S2; 
GRANT SELECT ON S1.t3 TO S2;

GRANT SELECT ON S1.Vw1 TO S2 WITH GRANT OPTION; 
GRANT SELECT ON S1.Vw2 TO S2 WITH GRANT OPTION; 
GRANT SELECT ON S1.Vw3 TO S2 WITH GRANT OPTION; 

create or replace view s2.vw4 as 
  select 'x' dummy from s1.vw1, s1.vw2, s1.vw3;

select * from s2.vw4;

no rows selected
  
GRANT SELECT ON S2.Vw4 TO S3 WITH GRANT OPTION;
GRANT SELECT ON S1.Vw1 TO S3 WITH GRANT OPTION; 
GRANT SELECT ON S1.Vw2 TO S3 WITH GRANT OPTION; 
GRANT SELECT ON S1.Vw3 TO S3 WITH GRANT OPTION; 
GRANT SELECT ON S1.t1 TO S3;
GRANT SELECT ON S1.t2 TO S3; 
GRANT SELECT ON S1.t3 TO S3;

conn s3/s3

select * from s2.vw4;

no rows selected

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Schema_2 with grant option solves the problem

Jorge, November 12, 2017 - 12:16 am UTC

Hi,

Thank you, the problem was solved by giving the WITH GRANT OPTION on Schema_2 as you mention.

Thanks.

create view using dblink object

Felipe Dal Pizzol, January 18, 2024 - 8:46 pm UTC

This example is perfect. But if S1 is in another database?
Create a dblink to access is ok, but the grants?
Something like this:

grant select on s1.vw1@mydblink to S2 with grant user;

Can you help me about how could I make this work, please?
Thank you.
Chris Saxon
January 22, 2024 - 1:55 pm UTC

I'm not sure what you're trying to do here - please clarify.

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.