query rewrite system privilege

List, I finally got an answer to the problem I was facing with materialized views and the 'query rewrite' system privilege . Thanks for all your help especially mladen and thomas.

• Original Message ----- To: <bulbultyagi_at_now-india.net.in> Sent: Saturday, September 20, 2003 00:42

> Hello,
>
>
> The answer to your question is that the query rewrite system privilege
> has been deprecated in Oracle 9i. So the behavior you see is correct.
> I think we need to fix the documentation.
>
> Also, the user does not need special privileges to enable query rewrite
> in the session or for queries to get rewritten with materialized views.
> All privilege checks are done only when creating the materialized view
> (similar to an index).
>
> Regards,
> Shilpa Lawande.
>
>
>
>
> bulbultyagi_at_now-india.net.in wrote:
>
> >
> > I am using oracle 9.2.0.1.0 enterprise edition on windows
> >
> > A user in my database is able to create materialized views with query
> > rewrite enabled.
> > But I have not given him 'query rewrite' system privilege.
> >
> > Isn't it required to create a mat. view with query rewrite enabled ?
> >
> > I quote from Oracle9i SQL Reference docs :
> > "To create a materialized view in another user's schema You must have
the
> > CREATE ANY MATERIALIZED VIEW system privilege.
> > ...To create the materialized view with query rewrite enabled, in
addition
> > to the
> > preceding privileges The owner of the master tables must have the QUERY
> > REWRITE system privilege. If you are not the owner of the master tables,
you
> > must have the GLOBAL QUERY REWRITE system privilege or the QUERY REWRITE
> > object privilege on each table outside your schema."
> >
> > SQL> sho user
> > USER is "SYS"
> >
> > SQL> create user test identified by test
> > 2 default tablespace users
> > 3 temporary tablespace temp
> > 4 quota unlimited on users;
> > User created.
> >
> > SQL> grant create session, create table, create materialized view to
test ;
> > Grant succeeded.
> >
> > SQL> connect test/test@***
> > Connected.
> >
> > SQL> create table master as select * from user_tables ;
> > Table created.
> >
> > create materialized view mymatview
> > tablespace users
> > build immediate
> > refresh on demand
> > enable query rewrite
> > as select table_name, count(*) from master group by table_name;
> >
> > Materialized view created.
> >
> > How was this user able to create this materialized view with query
rewrite
> > enabled ?
> >
> > Not only this , I find that this user is also able to enable query
rewrite
> > in his session also, as I show below :
> >
> >
> > SQL> alter session set optimizer_mode=choose;
> > Session altered.
> > SQL> alter session set query_rewrite_enabled=true;
> > Session altered.
> > SQL> alter session set query_rewrite_integrity=enforced;
> > Session altered.
> >
> > SQL> @?\rdbms\admin\utlxplan
> > Table created.
> >
> > SQL> set autotrace traceonly explain
> > SQL> analyze table master compute statistics;
> > Table analyzed.
> >
> >
> > SQL> select table_name, count(*) from master group by table_name;
> >
> > Execution Plan
> > ----------------------------------------------------------
> > 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=204 Bytes=612
> >
> > 0)
> >
> > 1 0 TABLE ACCESS (FULL) OF 'MYMATVIEW' (Cost=3 Card=204 Bytes=
> >
> > 6120)
> >
> > Any ideas as to how all this was possible without 'query rewrite' or
'global
> > query rewrite' ?
> > I would be very grateful if you could explain what I am doing wrong . I
> > fear this might be an RTFM type of mistake on my part.
> >
> > ...........................
> >
>
>
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: <bulbultyagi_at_now-india.net.in
  INET: bulbultyagi_at_now-india.net.in

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).