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: <[EMAIL PROTECTED]> 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. > > > > > [EMAIL PROTECTED] 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/[EMAIL PROTECTED] > > 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: <[EMAIL PROTECTED] INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
