Re: ORA-01031 enable query rewrite MV

ok

I will have a look at dbms_sys_sql

Thanks!

On Tue, May 16, 2023 at 10:55 PM Maxim <mdemenko_at_gmail.com> wrote:

> 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