Skip to Main Content
  • Questions
  • CREATE VIEW . . . BEQUEATH DEFINER vs. WITH GRANT OPTION

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Glenn.

Asked: November 15, 2022 - 2:31 pm UTC

Last updated: November 16, 2022 - 2:47 pm UTC

Version: 19c

Viewed 10K+ times! This question is

You Asked

I have a view, cust.viewname, that references a table in a different schema I'll call "data". According to Oracle documentation for CREATE VIEW, the optional clause BEQUEATH DEFINER causes the view to run using the permissions of the view owner. This is the default if not specified.

The view schema has SELECT privs to a table in the data schema, but not WITH GRANT OPTION. The view owner has access to the data table, but when I try to create the view, it throws ORA-01720: grant option does not exist for 'DATA.TABLENAME'

CREATE OR REPLACE VIEW cust.viewname BEQUEATH DEFINER
AS
SELECT 
  field1,
  field2,
  ...,
  fieldN
FROM data.tablename;


If the view operates as the owner, and the view owner has access to the referenced objects, then why should grants to objects in other schemas require WITH GRANT OPTION? Is there a way around WITH GRANT OPTION? Our cloud provider refuses to allow WITH GRANT OPTION, but we have an ongoing need to create views in CUST that reference objects in other schemas and will be used by users other than CUST.

Thanks in advance.

and Chris said...

Is it possible a view with that name already exists? And access to it has been granted to a third user?

grant unlimited tablespace 
  to u1
  identified by u1;
grant create session, create view 
  to u2
  identified by u2;
create user u3;

create table u1.t (
  c1 int
);

insert into u1.t values ( 1 );
commit;

grant select on u1.t to u2;

conn u2/u2

create or replace view u2.v as
  select * from dual;
  
grant select 
  on v to u3;  

create or replace view u2.v bequeath definer as
  select * from u1.t;
/*
ORA-01720: grant option does not exist for 'U1.T'
*/


If it's not this; I don't know what the issue is. Please provide sample DDL for the users, tables, and views in question. Ensure to include all grants for the users and objects.

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

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.