RE: ORA-1031 when calling a procedure

From: Tefft, Michael J <Michael.J.Tefft_at_snapon.com>
Date: Wed, 19 Oct 2016 09:51:32 +0000
Message-ID: <8CA507E7F87805479C5C3DF54AA713A780C858E5_at_LISL-XMBS-14-PP.snaponglobal.com>

I suggest you trace the U1 session and see what, exactly, is tripping the ORA-1031.

What is the database version? There were a number of under-advertised security changes that came in with 12c.

Mike Tefft

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Remigiusz Sokolowski Sent: Wednesday, October 19, 2016 3:04 AM To: l.flatz_at_bluewin.ch
Cc: oracle-l_at_freelists.org
Subject: Re: ORA-1031 when calling a procedure

I read docs - simply thought that I can manage it with code pieces being owned by SYS and performed on definer rights. The plan was to give users ability to create or drop restore points without giving them other privileges. From what You say it seems to would have to switch to plan B, which is not so nice as PL/SQL

Regards
Remigiusz

W dniu 19.10.2016 o 08:46, l.flatz_at_bluewin.ch pisze:
> Hi Remigiusz,
>
> If I remeber that right you must connect as sydba.
> It is not good enough tthat the procedure is owned by sys.
> Was this your question?
>
> Regards
>
> Lothar
> ----Ursprüngliche Nachricht----
> Von : remigiusz.sokolowski_at_nordea.com
> Datum : 19/10/2016 - 08:37 (UTC)
> An : oracle-l_at_freelists.org
> Betreff : ORA-1031 when calling a procedure
>
> Hi,
>
> Here is a short example of the issue:
>
> conn / as sysdba
> drop restore point rp1;
> create procedure p1 as
> begin
> execute immediate 'create restore point rp1 guarantee flashback
> database'; end; / create user u1 identified by u101; grant connect to
> u1; grant execute on sys.p1 to u1; conn u1/u101 exec sys.p1;
>
> When it comes to calling sys.p1 as u1 I've got
> SQL> exec sys.p1;
> BEGIN sys.p1; END;
>
> *
> ERROR at line 1:
> ORA-01031: insufficient privileges
> ORA-06512: at "SYS.P1", line 3
> ORA-06512: at line 1
>
> Similar thing happens when granting select on v_$restore_point and
> then doing a select from another user
>
> I am aware that creating restore points especially guaranteed ones
> requires much of privileges, but assumed it might be covered with
> PL/SQL Is it possible at all - I mean creating restore point from
> PL/SQL not being SYS
>
> Best regards
> Remigiusz
>

--
Pole na kazi

---

Remigiusz Sokolowski <remigiusz.sokolowski_at_nordea.com>

pos   : Senior DBA at DIiUSI
comp  : Nordea AB division in Poland
addr  : Luzycka 6A st, 81-537 Gdynia, Poland phone : +48 58 667 17 43
mobile: +48 602 42 42 77
geo   : 54.4952N 18.4332E

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

†Ûiÿü0ÁúÞzX¬¶Ê+ƒün– {ú+iÉ^ Received on Wed Oct 19 2016 - 11:51:32 CEST