Re: system_privilege_map issue

From: DA Morgan <damorgan_at_psoug.org>
Date: Thu, 05 Jul 2007 17:05:07 -0700
Message-ID: <1183680306.413000@bubbleator.drizzle.com>

Terry Dykstra wrote:
> "DA Morgan" <damorgan_at_psoug.org> wrote in message
> news:1183657739.205053_at_bubbleator.drizzle.com...

>> Terry Dykstra wrote:
>>> I noticed that in the sys.system_privilege_map table there is no entry 
>>> for CREATE MATERIALIZED VIEW or CREATE ANY MATERIALIZED VIEW.  Is that a 
>>> bug or am I missing something here?
>>> I can grant a user those privileges and the user can create the 
>>> materialized view.
>>>
>>> I'm running Oracle 9207 SE on W2K
>> Not in 9.2.0.7 but:
>>
>> SQL> select privilege, name
>>   2  from system_privilege_map
>>   3  where name like '%MATERIAL%';
>>
>> PRIVILEGE NAME
>> --------- ----------------------------
>>      -175 DROP ANY MATERIALIZED VIEW
>>      -174 ALTER ANY MATERIALIZED VIEW
>>      -173 CREATE ANY MATERIALIZED VIEW
>>      -172 CREATE MATERIALIZED VIEW
>>
>> SQL>
>> -- 
>> Daniel A. Morgan
>> University of Washington
>> damorgan_at_x.washington.edu (replace x with u to respond)
>> Puget Sound Oracle Users Group
>> www.psoug.org

>
> Interesting. Those privilege numbers use the old SNAPSHOT terminilogy.
>
> 1 select privilege, name
> 2 from system_privilege_map
> 3* where name like '%MATERIAL%'
> SQL> /
>
> no rows selected
>
> 1 select privilege, name
> 2 from system_privilege_map
> 3* where privilege between -175 and -172
> SQL> /
>
> PRIVILEGE NAME
> ---------- ----------------------------------------
> -175 DROP ANY SNAPSHOT
> -174 ALTER ANY SNAPSHOT
> -173 CREATE ANY SNAPSHOT
> -172 CREATE SNAPSHOT
>
> SQL> grant create materialized view to atcordba;
>
> Grant succeeded.
>
> SQL> grant create snapshot to atcordba;
>
> Grant succeeded.

Oracle will bristle at what I am about to say but snapshots were renamed to Materialized Views ... well they were. <g>

Even in future versions I've no doubt you'll be able to type: SQL> desc dba_snapshots

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Thu Jul 05 2007 - 19:05:07 CDT