Friday, February 28, 2014

Permissions Required to Create a Materialized View

The idea for this post came from a problem, which I saw on Javier Morales Carreras' blog here.

This example was tested on Oracle 11.2. It shows the permissions required to create a materialized view. First I created a user:
 
SQL> conn / as sysdba
Connected.
SQL> create user andrew identified by reid
  2  /
 
User created.
 
SQL> grant create session to andrew
  2  /
 
Grant succeeded.
 
SQL>
 
I logged in as the user and tried to create a materialized view. This failed (obviously):
 
SQL> conn andrew/reid
Connected.
SQL> create materialized view mv1 as select * from dual
  2  /
create materialized view mv1 as select * from dual
                                              *
ERROR at line 1:
ORA-01031: insufficient privileges
 
SQL>
 
I granted create materialized view to the user:
 
SQL> conn / as sysdba
Connected.
SQL> grant create materialized view to andrew
  2  /
 
Grant succeeded.
 
SQL>
 
…but when I logged in as the user and tried to create a materialized view again, this failed too:
 
SQL> conn andrew/reid
Connected.
SQL> create materialized view mv1 as select * from dual
  2  /
create materialized view mv1 as select * from dual
                                              *
ERROR at line 1:
ORA-01031: insufficient privileges
 
SQL>
 
This is because you also need the create table privilege before you can create a materialized view:
 
SQL> conn / as sysdba
Connected.
SQL> grant create table to andrew
  2  /
 
Grant succeeded.
 
SQL>
 
When I tried to create a materialized view this time, I saw a different error:
 
SQL> conn andrew/reid
Connected.
SQL> create materialized view mv1 as select * from dual
  2  /
create materialized view mv1 as select * from dual
                                              *
ERROR at line 1:
ORA-01950: no privileges on tablespace 'USERS'
 
SQL>
 
I attended to the ORA-01950 and the user was then able to create a materialized view:
 
SQL> conn / as sysdba
Connected.
SQL> alter user andrew quota unlimited on users
  2  /
 
User altered.
 
SQL> conn andrew/reid
Connected.
SQL> create materialized view mv1 as select * from dual
  2  /
 
Materialized view created.
 
SQL>
 
You need to be able to create tables because a materialized view has an underlying table itself:
 
SQL> select object_name, object_type from user_objects
  2  /
 
OBJECT_NAME     OBJECT_TYPE
--------------- --------------------
MV1             TABLE
MV1             MATERIALIZED VIEW
 
SQL>
 
Oracle does not allow you to drop the table as this would stop the materialized view working:
 
SQL> drop table mv1
  2  /
drop table mv1
           *
ERROR at line 1:
ORA-12083: must use DROP MATERIALIZED VIEW to drop
"ANDREW"."MV1"
 
SQL>
 
Conversely, if you drop a materialized view, the associated table disappears too:
 
SQL> drop materialized view mv1
  2  /
 
Materialized view dropped.
 
SQL> select object_name, object_type from user_objects
  2  /
 
no rows selected
 
SQL>

No comments: