Re: Refresh materialized view by other user then owner

Hi,

Storing database objects into the SYS schema is unusual and unadvised... The SYS schema is protected and having SELECT ANY TABLE for a user doesn't affect the accesibility against the SYS objects. Of course there are a lot of other privileges which might be needed in order to perform a materialized view refresh. For example, because your sys.my_table has no materialized view log the Oracle server simply chooses to perform a complet refresh which implies, first of all, a truncate against the mv object therefore a drop any table privilege might be needed. However, trying the same scenario using SYSTEM instead SYS works without problems. If your business logic dictates that the materialized view should be contained by the SYS schema then the key for solving your problem is the O7_DICTIONARY_ACCESSIBILITY. By default this is set to false and, for security reasons, should have this value for almost all databases. Pay atention to the fact that this parameter is static and will imply an instance restart. After changing it you will be able to perform the refresh from the user2 user without any problems.

alec. Received on Tue May 02 2006 - 02:43:06 CDT