Re: ORA-01031 enable query rewrite MV

it works equally with sys, maybe you can automate the whole process with DBMS_SYS_SQL.PARSE_AS_USER for this additional grant (to avoid reconnect), if you need to reconnect as the target user - the whole thing doesn't make any sense, with all given grants mview creation works out of the box (i mean - in your case as lsc user).

Regards

Maxim

On Tue, May 16, 2023 at 10:43 PM Ls Cheng <exriscer_at_gmail.com> wrote:

> Hi
>
> oh yes now it works, the only difference is that after granting the select
> from another user lsc session the DBA session did not reconnect (in your
> test you reconnected), if I reconnect the MV gets created
>
> Thanks!
>
> On Tue, May 16, 2023 at 10:33 PM Maxim <mdemenko_at_gmail.com> wrote:
>
>> Well i did it with another dba user, but in my environment it works
>>
>> ❯ sqlplus scott/tiger
>>
>> SQL*Plus: Release 19.0.0.0.0 - Production on Tue May 16 22:31:00 2023
>> Version 19.14.0.0.0
>>
>> Copyright (c) 1982, 2021, Oracle. All rights reserved.
>>
>> Last Successful login time: Tue May 16 2023 22:30:19 +02:00
>>
>> Connected to:
>> Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
>> Version 19.14.0.0.0
>>
>> SQL> show user
>> USER is "SCOTT"
>> SQL> select * from session_roles where role='DBA';
>>
>> ROLE
>>
>> --------------------------------------------------------------------------------
>> DBA
>>
>> SQL> _at_t
>> SQL> -- connected as SYS
>> SQL> create user lsc identified by lsc;
>>
>> User created.
>>
>> SQL> grant connect, resource, unlimited tablespace to lsc;
>>
>> Grant succeeded.
>>
>> SQL> grant create TABLE to lsc;
>>
>> Grant succeeded.
>>
>> SQL> grant create MATERIALIZED view to lsc;
>>
>> Grant succeeded.
>>
>> SQL> grant on commit refresh to lsc;
>>
>> Grant succeeded.
>>
>> SQL> grant global query rewrite to lsc;
>>
>> Grant succeeded.
>>
>> SQL>
>> SQL> create table lsc.t1
>> 2 as
>> 3 select sysdate mydate, a.*
>> 4 from dba_objects a;
>>
>> Table created.
>>
>> SQL>
>> SQL> CREATE MATERIALIZED VIEW LOG ON lsc.t1 with rowid, SEQUENCE (
>> 2 MYDATE ,
>> 3 OWNER ,
>> 4 OBJECT_NAME ,
>> 5 SUBOBJECT_NAME ,
>> 6 OBJECT_ID ,
>> 7 DATA_OBJECT_ID ,
>> 8 OBJECT_TYPE ,
>> 9 CREATED ,
>> 10 LAST_DDL_TIME ,
>> 11 TIMESTAMP ,
>> 12 STATUS ,
>> 13 TEMPORARY ,
>> 14 GENERATED ,
>> 15 SECONDARY ,
>> 16 NAMESPACE ,
>> 17 EDITION_NAME ,
>> 18 SHARING ,
>> 19 EDITIONABLE ,
>> 20 ORACLE_MAINTAINED ,
>> 21 APPLICATION ,
>> 22 DEFAULT_COLLATION ,
>> 23 DUPLICATED ,
>> 24 SHARDED ,
>> 25 CREATED_APPID ,
>> 26 CREATED_VSNID ,
>> 27 MODIFIED_APPID ,
>> 28 MODIFIED_VSNID
>> 29 )
>> 30 including new values;
>>
>> Materialized view log created.
>>
>> SQL>
>> SQL>
>> SQL> create table lsc.t1_mv
>> 2 as
>> 3 select owner, count(*) cnt, sum(object_id) sum_object_id
>> 4 from lsc.t1
>> 5 group by owner;
>>
>> Table created.
>>
>> SQL>
>> SQL> create materialized view lsc.t1_mv
>> 2 ON PREBUILT TABLE WITHOUT REDUCED PRECISION
>> 3 USING INDEX
>> 4 REFRESH FAST ON COMMIT
>> 5 USING DEFAULT LOCAL ROLLBACK SEGMENT
>> 6 USING ENFORCED CONSTRAINTS DISABLE ON QUERY COMPUTATION
>> 7 ENABLE QUERY REWRITE
>> 8 as
>> 9 (
>> 10 select owner, count(*) cnt, sum(object_id) sum_object_id
>> 11 from lsc.t1
>> 12 group by owner
>> 13 );
>> create materialized view lsc.t1_mv
>> *
>> ERROR at line 1:
>> ORA-01031: insufficient privileges
>>
>>
>> SQL> connect lsc/lsc
>> Connected.
>> SQL> grant select on lsc.t1_mv to scott with grant option;
>>
>> Grant succeeded.
>>
>> SQL> connect scott/tiger
>> Connected.
>> SQL> _at_a
>> SQL> set echo on
>> SQL> create materialized view lsc.t1_mv
>> 2 ON PREBUILT TABLE WITHOUT REDUCED PRECISION
>> 3 USING INDEX
>> 4 REFRESH FAST ON COMMIT
>> 5 USING DEFAULT LOCAL ROLLBACK SEGMENT
>> 6 USING ENFORCED CONSTRAINTS DISABLE ON QUERY COMPUTATION
>> 7 ENABLE QUERY REWRITE
>> 8 as
>> 9 (
>> 10 select owner, count(*) cnt, sum(object_id) sum_object_id
>> 11 from lsc.t1
>> 12 group by owner
>> 13 );
>>
>> Materialized view created.
>>
>> SQL>
>>
>>
>> Regards
>>
>> Maxim
>>
>> On Tue, May 16, 2023 at 10:26 PM Ls Cheng <exriscer_at_gmail.com> wrote:
>>
>>> Hi
>>>
>>> I also did grant select on lsc.t1 to sys with grant option; with no luck
>>>
>>> On Tue, May 16, 2023 at 10:19 PM Maxim <mdemenko_at_gmail.com> wrote:
>>>
>>>> You need grant select on lsc.t1_mv to sys with grant option (see the
>>>> doc. 1326117.1
>>>>
>>>> Regards
>>>>
>>>> Maxim
>>>>
>>>>
>>>> On Tue, May 16, 2023 at 10:13 PM Ls Cheng <exriscer_at_gmail.com> wrote:
>>>>
>>>>> Hi
>>>>>
>>>>> I also use other dba users and fail the same.
>>>>>
>>>>> mview log is owned by the user
>>>>>
>>>>> select log_owner, master, log_table from dba_mview_logs
>>>>>
>>>>> LOG_OWNER MASTER LOG_TABLE
>>>>> ------------------------------ --------------------
>>>>> --------------------
>>>>> LSC T1 MLOG$_T1
>>>>>
>>>>>
>>>>> Thanks
>>>>>
>>>>>
>>>>>
>>>>> On Tue, May 16, 2023 at 10:03 PM Dominic Brooks <dombrooks_at_hotmail.com>
>>>>> wrote:
>>>>>
>>>>>> Why are you using sys? Dodgy practice if you ask me.
>>>>>>
>>>>>> First thing that stands out is the mview log is not owned by the user.
>>>>>>
>>>>>> Sent from my iPhone
>>>>>>
>>>>>> > On 16 May 2023, at 20:26, Ls Cheng <exriscer_at_gmail.com> wrote:
>>>>>> >
>>>>>> > 
>>>>>> > Hi
>>>>>> >
>>>>>> > I am trying to create a MV on a prebuilt table in Oracle 19c,
>>>>>> running the create statements in SYS for user lsc, at the bottom are the
>>>>>> DDL's. The problem is the MV create statement fails with ORA-01031 because
>>>>>> of the ENABLE QUERY REWRITE clause in the create statement however the user
>>>>>> already has the required privileges. Anyone know if I am missing any
>>>>>> privilege?
>>>>>> >
>>>>>> > Thanks
>>>>>> >
>>>>>> >
>>>>>> > -- connected as SYS
>>>>>> > create user lsc identified by lsc;
>>>>>> > grant connect, resource, unlimited tablespace to lsc;
>>>>>> > grant create TABLE to lsc;
>>>>>> > grant create MATERIALIZED view to lsc;
>>>>>> > grant on commit refresh to lsc;
>>>>>> > grant global query rewrite to lsc;
>>>>>> >
>>>>>> > create table lsc.t1
>>>>>> > as
>>>>>> > select sysdate mydate, a.*
>>>>>> > from dba_objects a;
>>>>>> >
>>>>>> > CREATE MATERIALIZED VIEW LOG ON lsc.t1 with rowid, SEQUENCE (
>>>>>> > MYDATE ,
>>>>>> > OWNER ,
>>>>>> > OBJECT_NAME ,
>>>>>> > SUBOBJECT_NAME ,
>>>>>> > OBJECT_ID ,
>>>>>> > DATA_OBJECT_ID ,
>>>>>> > OBJECT_TYPE ,
>>>>>> > CREATED ,
>>>>>> > LAST_DDL_TIME ,
>>>>>> > TIMESTAMP ,
>>>>>> > STATUS ,
>>>>>> > TEMPORARY ,
>>>>>> > GENERATED ,
>>>>>> > SECONDARY ,
>>>>>> > NAMESPACE ,
>>>>>> > EDITION_NAME ,
>>>>>> > SHARING ,
>>>>>> > EDITIONABLE ,
>>>>>> > ORACLE_MAINTAINED ,
>>>>>> > APPLICATION ,
>>>>>> > DEFAULT_COLLATION ,
>>>>>> > DUPLICATED ,
>>>>>> > SHARDED ,
>>>>>> > CREATED_APPID ,
>>>>>> > CREATED_VSNID ,
>>>>>> > MODIFIED_APPID ,
>>>>>> > MODIFIED_VSNID
>>>>>> > )
>>>>>> > including new values;
>>>>>> >
>>>>>> >
>>>>>> > create table lsc.t1_mv
>>>>>> > as
>>>>>> > select owner, count(*) cnt, sum(object_id) sum_object_id
>>>>>> > from lsc.t1
>>>>>> > group by owner;
>>>>>> >
>>>>>> > create materialized view lsc.t1_mv
>>>>>> > ON PREBUILT TABLE WITHOUT REDUCED PRECISION
>>>>>> > USING INDEX
>>>>>> > REFRESH FAST ON COMMIT
>>>>>> > USING DEFAULT LOCAL ROLLBACK SEGMENT
>>>>>> > USING ENFORCED CONSTRAINTS DISABLE ON QUERY COMPUTATION
>>>>>> > ENABLE QUERY REWRITE
>>>>>> > as
>>>>>> > (
>>>>>> > select owner, count(*) cnt, sum(object_id) sum_object_id
>>>>>> > from lsc.t1
>>>>>> > group by owner
>>>>>> > );
>>>>>> >
>>>>>> >
>>>>>>
>>>>>

--
http://www.freelists.org/webpage/oracle-l