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
Adityanath's Database & Cloud Blog 
Leave a reply to Adityanath Dewoolkar Cancel reply