Why does Oracle's DELETE ANY TABLE Privilege make Materialized Views from other Schemas visible-CodePudding

On an Oracle 19.17 SE stand alone database, Materialized Views have been created in several different Schemas. A Schema (User) which has not been granted the Privilege DELETE ANY TABLE displays only its own Materialized Views in ALL_MVIEWS. When the same Schema has been granted Privilege DELETE ANY TABLE, Materialized Views belonging to all Schemas are returned.

DGSW@atest19> select privilege from user_sys_privs order by 1;  
  
PRIVILEGE  
----------------------------------------  
ALTER SESSION  
CREATE ANY TABLE  
CREATE CLUSTER  
CREATE DATABASE LINK  
CREATE INDEXTYPE  
CREATE MATERIALIZED VIEW  
CREATE OPERATOR  
CREATE PROCEDURE  
CREATE SEQUENCE  
CREATE SESSION  
CREATE SYNONYM  
CREATE TABLE  
CREATE TRIGGER  
CREATE TYPE  
CREATE VIEW  
  
15 rows selected.  
  
DGSW@atest19> select owner,count(*) from all_mviews group by owner order by 1;

no rows selected

SYSTEM@atest19> grant DELETE ANY TABLE to dgsw;

Grant succeeded.

DGSW@atest19> select privilege from user_sys_privs order by 1;  
  
PRIVILEGE  
----------------------------------------  
ALTER SESSION  
CREATE ANY TABLE  
CREATE CLUSTER  
CREATE DATABASE LINK  
CREATE INDEXTYPE  
CREATE MATERIALIZED VIEW  
CREATE OPERATOR  
CREATE PROCEDURE  
CREATE SEQUENCE  
CREATE SESSION  
CREATE SYNONYM  
CREATE TABLE  
CREATE TRIGGER  
CREATE TYPE  
CREATE VIEW  
DELETE ANY TABLE  
  
16 rows selected.  
  
DGSW@atest19> select owner,count(*) from all_mviews group by owner order by 1;  
  
OWNER                            COUNT(*)  
------------------------------ ----------  
REGRESSION_1_6                          7  
REGRESSION_1_7                          7  
REGRESSION_1_8                          9  
REGRESSION_1_9                          9  
TEST1                                  10  
TEST2                                  10  
TEST3                                  10  
TEST4                                  10  
UI_TEST                                10  
VALIDATION1                             9

CodePudding user response：

Because that's how that view is written. If you look at the source code with:

select text_vc from all_views view view_name = 'ALL_MVIEWS';

then you will see that it's based on DBA_MVIEWS - which obviously includes all materialized views - filtered on a couple of things like you being the owner or:

        or /* user has system privileges */
        exists ( select null from v$enabledprivs
                 where priv_number in (-45 /* LOCK ANY TABLE */,
                                       -47 /* SELECT ANY TABLE */,
                                       -397/* READ ANY TABLE */,
                                       -48 /* INSERT ANY TABLE */,
                                       -49 /* UPDATE ANY TABLE */,
                                       -50 /* DELETE ANY TABLE */)
               )

So it's explicitly saying to include all of them if you have any of those ANY privileges.

Why they chose to do that could only really be answered by Oracle themselves. But as materialized views are really tables with a specialised update mechanism, that doesn't seem unreasonable. SELECT ANY TABLE should let you query any materialized view, so it's not a big leap to say they should all let you see that a materialized view exists.