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

Last updated on APRIL 17, 2023

Applies to:

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

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