By on ( 42 Comments )

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

Categories: Administration, ORA errors

Tagged as: , , , ,

42 replies ›

  8. 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.

    Reply

  9. 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.

    Reply

  11. It’s awesome to visit this site and reading the views
    of all friends regarding this article, while I am also eager of getting know-how.

    Reply

  14. Howdy! This post could not be written much better!
    Going through this post reminds me of my previous roommate!
    He always kept talking about this. I’ll forward this information to him.
    Fairly certain he’s going to have a good read.
    I appreciate you for sharing!

    Reply

  20. You’ve made some really good points there. I looked on the net to learn more about the issue and found most people will go along with your views on this website.

    Reply

  21. I like the helpful info you provide in your articles. I’ll
    bookmark your weblog and check again here regularly. I am quite sure I’ll learn many
    new stuff right here! Good luck for the next!

    Reply

  22. Like!! I blog frequently and I really thank you for your content. The article has truly peaked my interest.

    Reply

  24. I blog often and I seriously appreciate your information. The article has truly peaked my interest.
    I am going to bookmark your website and keep checking for new information about once a week.
    I opted in for your RSS feed as well.

    Reply

  26. Spot on with this write-up, I absolutely believe this web site needs much more attention. I’ll probably be returning to read more,
    thanks for the info!

    Reply

  27. Just want to say your article is as amazing. The clearness in your submit is just great and i
    could suppose you’re knowledgeable in this subject.

    Fine along with your permission allow me to clutch your feed to stay up to date with imminent post.
    Thanks a million and please continue the rewarding work.

    Reply

  28. Generally I don’t learn article on blogs, but I would like to say that this
    write-up very compelled me to check out and do it!
    Your writing style has been amazed me. Thank you, quite great article.

    Reply

  29. Greetings! This is my 1st comment here so I just wanted to give a quick shout out and say I genuinely enjoy reading through your blog posts. Can you suggest any other blogs/websites/forums that cover the same topics? Thank you!

    Reply

  30. I like what you guys are up too. Such clever work and reporting! Carry on the superb works guys I抳e incorporated you guys to my blogroll. I think it’ll improve the value of my web site 🙂

    Reply

Leave a Reply

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

Gravatar
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 )

Cancel

Connecting to %s