ORA-1031 when calling a procedure

From: Remigiusz Sokołowski <remigiusz.sokolowski_at_nordea.com>
Date: Wed, 19 Oct 2016 08:37:06 +0200
Message-ID: <64ee70f9-4185-8977-c4e0-eec94deab2c3_at_nordea.com>



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
Received on Wed Oct 19 2016 - 08:37:06 CEST

Original text of this message