Re: Privileges

In article <4akidj$bm0_at_bcrkh13.bnr.ca>, Loren Budd <Loren_Budd_at_nt.com> wrote:

>stauffer_at_swarthmore.edu (stauffer) wrote:
>>Running into a problem on a new, vendor-installed database and my mind has
>>gone blank...
>>
>>Here is the error I get when a user tries to create a view:
>>
>>ERROR at line 4:
>>ORA-01933: cannot create a stored object using privileges from a role
>>
>>The user has the dba role and was granted 'create view' explicitly.
>>
>>So, what am I missing?
>>
>
>Glenn,
>
> Privileges must be granted EXPLICITLY to the user who is creating the view.
>Although an account with DBA privileges may have implicit access to the table
>the view is based on via the DBA_ROLE, this is not enough. Simply grant SELECT
>on any necessary tables to the account that is creating the view and everything
>should work.
> Note: This is similar to the way stored procedures work - to reference a
>table in a stored procedure, the stored procedure owner must have been granted
>EXPLICIT access (ie. through explicitly executed GRANT commands) to the
>referenced tables. I'm not sure why this works this way.
>
>Loren

Thanks for the help, everyone.

I went searching through the manuals and figured out my problem. I think...

This is the full text of the view I am trying to create:

create view dd_cache
as select parameter, gets, getmisses, getmisses/gets*100 percent, count,usage from v$rowcache
where gets > 100 and getmisses > 0;

The problem is that v$rowcache is a synonym for v_$rowcache. So, once I granted select on v_$rowcache to the user, the user could create this view.

Interesting thing is that the v_$rowcache view selects from v$rowcache synonym for the v_$rowcache view. I think I have to go back and read up on my V$ tables.

Thanks again for the responses,

Glenn Stauffer
DBA
Swarthmore College

To get random signatures put text files into a folder called ³Random Signatures² into your Preferences folder. Received on Tue Dec 12 1995 - 00:00:00 CET