Re: system_privilege_map issue

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

-- 
Terry Dykstra