Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I need to access the user_mviews or all_mviews table to get meta information about materialized view in my app. Which grants are necessary to read the user_mviews table?

share|improve this question
add comment

1 Answer

up vote 4 down vote accepted

by default all users can read the user_mviews and all_mviews views since the SELECT right is granted to public:

SQL> SELECT grantee, privilege, table_name
  2    FROM dba_tab_privs
  3   WHERE table_name IN ('USER_MVIEWS', 'ALL_MVIEWS');

GRANTEE  PRIVILEGE  TABLE_NAME
-------- ---------- ------------
PUBLIC   SELECT     USER_MVIEWS
PUBLIC   SELECT     ALL_MVIEWS

If for some reason this grant has been removed, you would need to regrant the rights to either public or a specific user (with a DBA account):

GRANT SELECT ON user_mviews TO <user>;

Edit: By definition, USER_MVIEWS describes materialized views owned by the current user while ALL_MVIEWS describes the materialized views accessible to the current user (the user needs to be granted SELECT on the mview either directly or through a role).

share|improve this answer
    
the problem that I have is that I'm able to do a select on this table but without any result rows. –  paweloque May 9 '11 at 8:51
3  
If you're connecting as a different user than the owner of the mview, it won't appear in USER_MVIEWS, and will only appear in ALL_MVIEWS if you have permissions against the mview itself. Make sure select on the mview is granted to the user you're connecting as. –  Alex Poole May 9 '11 at 8:53
    
hi alex, this solved my problem! please write you answer as an answer so that I can upvote it and accept as final answer. –  paweloque May 9 '11 at 9:43
2  
@lewap: Vincent's answered your original question, I've just addressed the follow-up. Unless you plan to edit the question I'd go with this as the answer. Glad I could help though. –  Alex Poole May 9 '11 at 10:10
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.