Home » SQL & PL/SQL » SQL & PL/SQL » Privileges for Materialized views (Oracle 10g)
Privileges for Materialized views [message #295334] Tue, 22 January 2008 02:45 Go to next message
hidnana
Messages: 87
Registered: November 2007
Location: chennai
Member
Do we need to give select privilege to Materialized views explicitly or is it enough if we give the privilege to the corresponding tables.
Also do we need to create synoyms for these as we do for the other schema objects ?
Re: Privileges for Materialized views [message #295336 is a reply to message #295334] Tue, 22 January 2008 02:49 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Quote:
Do we need to give select privilege to Materialized views explicitly or is it enough if we give the privilege to the corresponding tables.
This is a very easy test. Create a user, grant him select on the tables involved in the MV and see if the user can select from the mv. You might also want to go to the effort of reading the documentation.

Quote:
Also do we need to create synoyms for these as we do for the other schema objects ?
That's up to you. Just as creating synonyms is up to you for other schema objects too.
Re: Privileges for Materialized views [message #295599 is a reply to message #295336] Tue, 22 January 2008 20:07 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
It depends on how you want to use the MV

If you want to select directly from it, then you will need privs as you would for any table.

If you want the MV to be used 'under the covers' with Query Rewrite when you query the base tables, then you will need to enable Query Rewrite for the database or session, ensure that QR is available on the MV, and grant the QUERY REWRITE system priv to the user. You should not need privs on the MV directly.

Ross Leishman
Previous Topic: Using Instr and substring
Next Topic: want to print dynamic sql query
Goto Forum:
  


Current Time: Wed Dec 07 22:13:15 CST 2016

Total time taken to generate the page: 0.10153 seconds