Re: Re: ORA-1031 when calling a procedure

I was at that point too. IHAC who using as shell script and sudo. That is indeed not so nice. Maybe somebody else has a better solution? I wonder about an external procedure.

Regards

Lothar

----Ursprüngliche Nachricht----
Von : remigiusz.sokolowski_at_nordea.com Datum : 19/10/2016 - 09:03 (UTC)
An : l.flatz_at_bluewin.ch
Cc : oracle-l_at_freelists.org
Betreff : 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



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