My Oracle Support Banner

Dbms_mview.refresh Out_of_place Lose Some Grants After The Refresh On Database 19c (19.9) (Doc ID 2910150.1)

Last updated on JANUARY 05, 2023

Applies to:

Oracle Database - Enterprise Edition - Version 19.9.0.0.0 and later
Information in this document applies to any platform.

Symptoms

OUT_OF_PLACE MV refresh DOES NOT CARRY normal GRANTS if another user granted  
WITH GRANT OPTION

TEST CASE
==========
grant dba to m identified by m
conn m/m
create user TEST1 identified by TEST1;
grant connect,resource to TEST1;
create user TEST2 identified by TEST2;
grant connect,resource to TEST2;


drop materialized view TEST_MVIEW;
create materialized view TEST_MVIEW refresh complete on demand as select
'test' col1 from  dual;

revoke all on TEST_MVIEW from TEST1;
revoke all on TEST_MVIEW from TEST2;

grant delete on TEST_MVIEW to TEST1 with grant option;
grant select, insert, update, delete, alter on TEST_MVIEW to TEST2;

select count(*) from user_tab_privs where table_name = 'TEST_MVIEW';
6

execute dbms_mview.refresh('TEST_MVIEW',method =>'c', atomic_refresh =>
false, out_of_place => true);
select count(*) from user_tab_privs where table_name = 'TEST_MVIEW';
1 <=just one row



Changes

 

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Changes
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.