ORA-01031: insufficient privileges raised during materialized view creation on another schema

Scenario:

ORA-01031: insufficient privileges raised during the creation of materialized view (MVIEW) on another schema by the SYSTEM user.

CREATE MATERIALIZED VIEW kaysar.mview_test
BUILD IMMEDIATE
AS
(SELECT * from kaysar.test);

[Error] Execution (4: 26): ORA-01031: insufficient privileges

Investigation:

The SYSTEM already has the privilege of CREATE_MATERIALIZED_VIEW to create the MVIEW on another schema but failed to create the MVIEW. It is surprising that a super user can’t do this.

Solution:

Upon dig down deeper it is found that the target schema user KAYSAR need exclusive privilege of CREATE TABLE to be granted though the RESOURCE role contains the create table privilege.

GRANT CREATE TABLE TO KAYSAR;

Note: This scenario is true for any user has DBA role and tries to create MVIEW on another schema.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.