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
