Simon Krenger

Ok, so here is a problem that a developer brought up. I thought that this problem is quite interesting and also a bit confusing. Obviously, according to Oracle, this is not a bug – it’s a feature!

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

dba@KDB01:SQL> CREATE MATERIALIZED VIEW simon.simon_mv AS SELECT * FROM dual;
CREATE MATERIALIZED VIEW simon.simon_mv AS SELECT * FROM dual
                                                         *
ERROR at line 1:
ORA-01031: insufficient privileges

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

• User SIMONDBA (has the DBA privilege and is used to setup the DB server)
• User SIMON (schema owner)

We want to use the SIMONDBA user to create the objects for the schema SIMON. 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):

masterdba@KDB01:SQL> CREATE USER simondba IDENTIFIED BY tiger;

User created.

masterdba@KDB01:SQL> GRANT DBA TO simondba;

Grant succeeded.

masterdba@KDB01:SQL> CREATE USER simon IDENTIFIED BY tiger;

User created.

masterdba@KDB01:SQL> GRANT RESOURCE, CONNECT TO simon;

Grant succeeded.

masterdba@KDB01:SQL> ALTER USER simon QUOTA 100M ON users;

User altered.

masterdba@KDB01:SQL> GRANT CREATE MATERIALIZED VIEW TO simon;

Grant succeeded.

So far, so good. The user SIMONDBA has all the privileges that come with the DBA role and the user SIMON 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 (SIMONDBA):

masterdba@KDB01:SQL> connect simondba@KDB01
Enter password:
Connected.
simondba@KDB01:SQL> CREATE MATERIALIZED VIEW simon.simon_mv AS SELECT * FROM dual;
CREATE MATERIALIZED VIEW simon.simon_mv AS SELECT * FROM dual
                                                         *
ERROR at line 1:
ORA-01031: insufficient privileges

Err, what? I have the DBA privileges, so the problem must be that SIMON does not have the correct privileges! So let’s try with the user SIMON:

simondba@KDB01:SQL> connect simon@KDB01
Enter password:
Connected.
simon@KDB01:SQL> CREATE MATERIALIZED VIEW simon_mv AS SELECT * FROM dual;

Materialized view created.

Now the confusion is complete. Why can I create the materialized view while connected as SIMON, but not create the same view when connected as SIMONDBA? The user SIMONDBA has higher privileges than the user SIMON, 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:

masterdba@KDB01:SQL> GRANT CREATE TABLE TO simon;

Grant succeeded.

Now the CREATE MATERIALIZED VIEW statement works as expected:

simondba@KDB01:SQL> CREATE MATERIALIZED VIEW simon.simon_mv AS SELECT * FROM dual;

Materialized view created.

Now you might say: “But the RESOURCE role contains the CREATE TABLE privilege, why do I have to grant that privilege explicitly?”. That is correct and I have no answer for that…