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
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.
Is it possible a view with that name already exists? And access to it has been granted to a third user?
grant unlimited tablespace
identified by u1;
grant create session, create view
identified by u2;
create user u3;
create table u1.t (
insert into u1.t values ( 1 );
grant select on u1.t to u2;
create or replace view u2.v as
select * from dual;
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.