What permissions are required for complex cross-schema materialized view in Oracle?

Question

I have a complex cross-schema query that I'd like to create as a materialized view in Oracle 19c (although the database has been ported forward from numerous historical versions of Oracle). This includes joining a subquery to 5 additional tables across three total schemas, and then unioning to another copy of the query against 3 different schemas. The account creating the view has DBA and CONNECT roles, and has been granted GLOBAL QUERY REWRITE, UNLIMITED TABLESPACE, CREATE PROCEDURE, CREATE MATERIALIZED VIEW, and CREATE TABLE system privileges. It also has explicit SELECT permissions on all of the tables in the query.

I can run the query itself without issue. However, when I add the setup for a materialized view:

CREATE MATERIALIZED VIEW <account_schema>.<materialized_view_name>
BUILD IMMEDIATE
REFRESH FORCE
ON COMMIT
AS
<SELECT...>

I get ORA-01031, or "insufficient privileges". What am I missing? Is there a way to query for a more detailed report of what's missing? It points to a specific location in the query (the table in the last join), but I don't see any difference between it and the other tables.

Answer 1

The issue appears to be the "ON COMMIT" portion of your DDL. From the documentation:

"To create a refresh-on-commit materialized view (REFRESH ON COMMIT clause), in addition to the preceding privileges, you must have the ON COMMIT REFRESH object privilege on any master tables that you do not own or you must have the ON COMMIT REFRESH system privilege."

So either go with "ON DEMAND" or grant the additional privilege for each source table.