3

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?

5

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

4
  • 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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