Home » SQL & PL/SQL » SQL & PL/SQL » Create Materialized View throws "ORA-01031: insufficient privileges" (Oracle Database 19c Enterprise Edition Release 19.0.0.0.0)
Create Materialized View throws "ORA-01031: insufficient privileges" [message #679962] Tue, 14 April 2020 14:21 Go to next message
wtolentino
Messages: 303
Registered: March 2005
Senior Member
when attempting to create a materialized view for the same schema/user account that owns the table it throws an error "ORA-01031: insufficient privileges"

the user account ssp_test has these system privileges but still will not create the materialized view.
SQL> select substr(grantee,1,20) grantee,
  2         substr(privilege,1,30) privilege
  3    from dba_sys_privs
  4   where grantee = 'SSP_TEST';

GRANTEE              PRIVILEGE
-------------------- ------------------------------
SSP_TEST             CREATE ANY MATERIALIZED VIEW
SSP_TEST             CREATE MATERIALIZED VIEW
SSP_TEST             CREATE SESSION

SQL>

SQL> create user ssp_test  identified by "********"
  2  default tablespace users
  3  temporary tablespace temp
  4  quota unlimited on users
  5  profile default
  6  account unlock;

User created.

SQL> grant create session to ssp_test;

Grant succeeded.

SQL> grant create materialized view to ssp_test;

Grant succeeded.

SQL> grant create any materialized view to ssp_test;

Grant succeeded.

SQL> create table ssp_test.test_tab1
  2  (col1 varchar2(20), col2 varchar2(20));

Table created.

SQL> alter table ssp_test.test_tab1 add constraints TEST_TAB1_PK primary key (col1);

Table altered

SQL> CREATE MATERIALIZED VIEW LOG ON ssp_test.test_tab1 WITH PRIMARY KEY INCLUDING NEW VALUES;

Materialized view log created.

SQL> create materialized view ssp_test.test_tab1_mv
  2  nologging
  3  cache
  4  build immediate
  5  refresh fast on commit as
  6  select * from ssp_test.test_tab1;
select * from ssp_test.test_tab1
                       *
ERROR at line 6:
ORA-01031: insufficient privileges


SQL>
please advise. i think i might be missing something.

thank you,
warren
Re: Create Materialized View throws "ORA-01031: insufficient privileges" [message #679964 is a reply to message #679962] Tue, 14 April 2020 14:45 Go to previous messageGo to next message
wtolentino
Messages: 303
Registered: March 2005
Senior Member
grant "CREATE TABLE" privileges solved the problem.
Re: Create Materialized View throws "ORA-01031: insufficient privileges" [message #679975 is a reply to message #679964] Wed, 15 April 2020 07:23 Go to previous message
Michel Cadot
Messages: 67237
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

CREATE MATERIALIZED VIEW


Prerequisites
The privileges required to create a materialized view should be granted directly rather than through a role.
...
To create a materialized view in another user's schema:
  • You must have the CREATE ANY MATERIALIZED VIEW system privilege.
  • The owner of the materialized view must have the CREATE TABLE system privilege.
...

[Updated on: Wed, 15 April 2020 07:25]

Report message to a moderator

Previous Topic: Fine Grain Access control disabled but still prevents to create Materialized View
Next Topic: Function call in ref cursor
Goto Forum:
  


Current Time: Fri Jul 10 02:53:54 CDT 2020