Re: ORA-1031 when calling a procedure

From: <l.flatz_at_bluewin.ch>
Date: Wed, 19 Oct 2016 06:46:13 +0000
Message-ID: <10324260.4652.1476859573175.JavaMail.webmail_at_bluewin.ch>

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

--

http://www.freelists.org/webpage/oracle-l Received on Wed Oct 19 2016 - 08:46:13 CEST