Recently I got an interesting request: The customer wanted to allow the application installation routine to create a guaranteed restore point without giving it all required privileges to do so. So the idea was to encapsulate creating and dropping a guaranteed restore point in a PLSQL package and granting the application owner the permission to execute the package. The problem with that approach is that SYSDBA-privileges are required to create a guaranteed restore point and the question came up if it is actually possible to have a PLSQL package created with DEFINER-rights, where the DEFINER has the SYSDBA-privilege? Actually that is not possible, because you have to be connected “AS SYSDBA” to have the SYSDBA-privilege. A package created from a user, who connected as SYSDBA does not inherit the SYSDBA-privilege as the following example shows:


sqlplus / as sysdba
 
create user dbadmin identified by dbadmin;
grant sysdba to dbadmin;
create user appluser identified by appluser;
grant create session to appluser;
connect dbadmin/dbadmin as sysdba
 
create or replace package grp_handling as
procedure create_grp;
procedure drop_grp;
end;
/
 
create or replace package body grp_handling as
procedure create_grp
as
begin
begin
execute immediate 'drop restore point before_appl_installation';
exception
when others then null;
end;
execute immediate 'create restore point before_appl_installation guarantee flashback database';
end;
procedure drop_grp
as
begin
execute immediate 'drop restore point before_appl_installation';
end;
end;
/
 
exec grp_handling.create_grp;
 
PL/SQL procedure successfully completed.
 
select name from v$restore_point;
 
NAME
----------------------------------------------
BEFORE_APPL_INSTALLATION
 
select owner,object_type from dba_objects where object_name='GRP_HANDLING';
 
OWNER OBJECT_TYPE
------------------------------ -----------------------
SYS PACKAGE
SYS PACKAGE BODY
 
select user from dual;
 
USER
------------------------------
SYS
 
grant execute on grp_handling to appluser;
 
connect appluser/appluser
exec sys.grp_handling.create_grp;
 
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.GRP_HANDLING", line 10
ORA-06512: at line 1

So first of all a user, who connects “AS SYSDBA” actually connects as SYS. Secondly the SYSDBA-privilege is not inherited as a DEFINER-right in PLSQL-objects.

So how to resolve the issue to create a guaranteed restore point from the appluser-Session then?
I suggested to wrap a dbms_scheduler-external callout in a Package as follows:

1.) Create the bash-Skript /home/oracle/GRP/cre_grp.bash as OS-User oracle


#!/bin/bash
. oraenv <<EOF
prem122
EOF
 
sqlplus / as sysdba <<EOF
begin
execute immediate 'drop restore point before_appl_installation';
exception
when others then null;
end;
/
create restore point before_appl_installation guarantee flashback database;
exit
EOF

–> Add execute permissions for the user: chmod u+x /home/oracle/GRP/cre_grp.bash

2.) Create credential and job


connect dbadmin as sysdba
BEGIN
DBMS_CREDENTIAL.create_credential(
credential_name => 'oracle_122',
username => 'oracle',
password => '<passwd OS-user oracle>'
);
END;
/
 
BEGIN
dbms_scheduler.CREATE_job
(
job_name => 'CRE_GUARANTEED_RP',
job_type => 'EXECUTABLE',
job_action => '/home/oracle/GRP/cre_grp.bash',
enabled => true,
auto_drop => false,
credential_name => 'oracle_122'
);
END;
/

3.) Create a procedure to run the Job


connect dbadmin as sysdba
create procedure run_my_GRP_job
as
begin
dbms_scheduler.run_job('CRE_GUARANTEED_RP');
end;
/
 
grant execute on run_my_GRP_job to appluser;

4.) Now the appluser can run the job:


connect appluser
exec sys.run_my_GRP_job;

Conclusion: You cannot provide SYSDBA-privileges through DEFINER-rights in PLSQL. In case you have to run PLSQL “AS SYSDBA” then you have to connect “AS SYSDBA”. Running SYSDBA-commands as a non-SYSDBA-user is possible with a workaround like through a procedure, which runs an external job.