ORA-01031: Insufficient Privileges while creating mview in different schema.

Recently I faced very interesting issue, when user was complaining that he is getting (SCHEMA:TEST1) ORA-01031: insufficient privileges while creating materialized view under different schema (SCHEMA:TEST2) though TEST1 has DBA role assigned.

For TEST1:

SQL> select * from dba_sys_privs where grantee='TEST1';
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
TEST1 UNLIMITED TABLESPACE NO
SQL> select * from dba_role_privs where grantee='TEST1';
GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
TEST1 DBA NO YES
TEST1 CONNECT NO YES
TEST1 RESOURCE NO YES

For TEST2:

SQL> select * from dba_sys_privs where grantee='TEST2';
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
TEST2 UNLIMITED TABLESPACE NO
SQL> select * from dba_role_privs where grantee='TEST2';
GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
TEST2 CONNECT NO YES
TEST2 RESOURCE NO YES

 

When user tried creating mview in TEST2 schema by connecting schema TEST1 (though having DBA role assigned) was getting ORA-01031: insufficient privileges. I also tried same using SYSTEM user but same error:

SQL> conn test1/test1
Connected.
SQL> create materialized view test2.mv_t as select * from test2.t;
create materialized view test2.mv_t as select * from test2.t
 *
ERROR at line 1:
ORA-01031: insufficient privileges
SQL> conn system/rmbman08
Connected.
SQL> create materialized view test2.mv_t as select * from test2.t;
create materialized view test2.mv_t as select * from test2.t
 *
ERROR at line 1:
ORA-01031: insufficient privileges

After searching on Metalink, I found very interesting note : ORA-01031: Insufficient Privileges When Create A Mview In Different Schema (Doc ID 749112.1). It clearly stats:

This is not a code bug; this is expected behavior. As explained above, the owner of the base table should be granted with the ‘create table’ privilege explicitly and not through a role. Granting the create table privilege explicitly to the owner of the base table allows us to create the materialized view in another schema.

After this I tried provided solution:

SQL> conn / as sysdba
Connected.
SQL> grant create table to test2;
Grant succeeded.
SQL> conn test1/test1
Connected.
SQL> create materialized view test2.mv_t as select * from test2.t;
Materialized view created.

Solved 🙂

Just to put some light on this “expected behavior“, Mview creation also creates one table under mview owner with same name.

SQL> @obj
Enter value for object_name: MV_T
old 3: where object_NAME = upper('&object_name')
new 3: where object_NAME = upper('MV_T')
OBJECT_NAME OWNER OBJECT_TYPE
-------------------------------------------------- ------------------------------ -------------------
MV_T TEST2 TABLE
MV_T TEST2 MATERIALIZED VIEW

 

This means TEST2 should have create table privilege which had been already assigned to TEST2 though role RESOURCE.

In metalink note : Role Restrictions (Doc ID 11740.1) u will find following information:

“USERS CANNOT PERFORM DDL BASED ON PRIVILEGES RECEIVED THROUGH A ROLE”:
This restriction is necessary because roles can be enabled or disabled dynamically, making security checking at runtime ambiguous. For example, if a user is granted SELECT on a table via a role, enables that role, creates a procedure to select from the table, and then disables that role,should the procedure be allowed to execute? The user still has the privileges, but they are not currently active.Roles are intended for use as a tool to administer privileges for end users; they are not intended for use by application developers.

So here we got solution with perfect explanation 🙂


Hope so u will find this post very useful 🙂

Cheers

Regards,

Adityanath

Advertisements

18 thoughts on “ORA-01031: Insufficient Privileges while creating mview in different schema.

  1. Hi to all, how is the whole thing, I think every one is getting more from this web site, and your views are good in support of new people.

  2. I simply want to tell you that I am newbie to blogging and truly liked you’re page. Probably I’m planning to bookmark your site . You actually come with superb writings. Thanks a lot for sharing your webpage.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s