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.