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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of Remigiusz Sokolowski
Sent: Wednesday, October 19, 2016 3:04 AM
To: l.flatz@xxxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxx 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@xxxxxxxxxx
Datum : 19/10/2016 - 08:37 (UTC)
An : oracle-l@xxxxxxxxxxxxx
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
