ORA-01031 on CREATE MATERIALIZED VIEW

ORA-01031 on CREATE MATERIALIZED VIEW

I had a weird problem  today,

 I tried to create an Materialized view on an application user from sys user and faced ORA-01031 error.

When issuing a CREATE MATERIALIZED VIEW statement for a different schema (as DBA), one might encounter the following error:

SQL> CREATE MATERIALIZED VIEW rshankar.test_mv AS SELECT * FROM dual;

CREATE MATERIALIZED VIEW rshankar.test_mv AS SELECT * FROM dual

                                                         *

ERROR at line 1:

ORA-01031: insufficient privileges

For our setup let’s assume we have two users:

·         User SYS (has the DBA privilege and is used to setup the DB server)

·         User RSHANKAR (schema owner)

We want to use the SYS user to create the objects for the schema RSHANKAR. This is a standard approach to seperate the deployment user from the actual schema owner. So let’s create these two users and grant the privileges (DBA for the deployment user, CONNECT and RESOURCE for the schema owner):

SQL> CREATE USER RSHANKAR IDENTIFIED BY test123;

User created.

SQL> GRANT RESOURCE, CONNECT TO rshankar;

Grant succeeded.

SQL> ALTER USER rshankar QUOTA 100M ON users;

User altered.

SQL> GRANT CREATE MATERIALIZED VIEW TO rshankar;

Grant succeeded.

So far, so good. The user SYS  has all the privileges that come with the DBA role and the user RSHANKAR has a limited set of privileges due to the RESOURCE and CONNECT roles. In addition to those roles, we want to grant CREATE MATERIALIZED VIEW to our schema owner.

Now let’s see what happens when we try to issue the statement as the deployment user (SYS):

SQL> connect sys as sysdba

Enter password:

Connected.

SQL> CREATE MATERIALIZED VIEW rshankar.test_mv AS SELECT * FROM dual;

CREATE MATERIALIZED VIEW rshankar.test_mv AS SELECT * FROM dual

                                                         *

ERROR at line 1:

ORA-01031: insufficient privileges

SYS have the DBA privileges, so the problem must be that RSHANKAR does not have the correct privileges! So let’s try with the user RSHANKAR:

SQL> connect rshankar

Enter password:

Connected.

SQL> CREATE MATERIALIZED VIEW test_mv AS SELECT * FROM dual;

Materialized view created.

Now the confusion is complete. Why can I create the materialized view while connected as RSHANKAR, but not create the same view when connected as SYS? The user SYS has higher privileges than the user RSHANKAR, so that should not happen! It turns out that this is expected behaviour (MOS Doc 749112.1).

To solve the mystery, grant the CREATE TABLE privilege directly to the schema owner:

SQL> GRANT CREATE TABLE TO rshankar;

Grant succeeded.

Now the CREATE MATERIALIZED VIEW statement works as expected:

SQL> connect sys as sysdba

Enter password:

Connected.

SQL> CREATE MATERIALIZED VIEW rshankar.test_mv AS SELECT * FROM dual;

Materialized view created.

This solve my problem J