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:<\/p>\n
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.<\/p>\n So how to resolve the issue to create a guaranteed restore point from the appluser-Session then? 1.) Create the bash-Skript \/home\/oracle\/GRP\/cre_grp.bash as OS-User oracle<\/p>\n –> Add execute permissions for the user: chmod u+x \/home\/oracle\/GRP\/cre_grp.bash<\/p>\n 2.) Create credential and job<\/p>\n 3.) Create a procedure to run the Job<\/p>\n 4.) Now the appluser can run the job:<\/p>\n 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.<\/p>\n","protected":false},"excerpt":{"rendered":" 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 […]<\/p>\n","protected":false},"author":35,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229,198,59],"tags":[1437,96,1438],"type_dbi":[],"class_list":["post-11578","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","category-database-management","category-oracle","tag-definer-rights","tag-oracle","tag-sysdba"],"acf":[],"yoast_head":"\n
\nsqlplus \/ as sysdba
\n
\ncreate user dbadmin identified by dbadmin;
\ngrant sysdba to dbadmin;
\ncreate user appluser identified by appluser;
\ngrant create session to appluser;
\nconnect dbadmin\/dbadmin as sysdba
\n
\ncreate or replace package grp_handling as
\n procedure create_grp;
\n procedure drop_grp;
\nend;
\n\/
\n
\ncreate or replace package body grp_handling as
\nprocedure create_grp
\nas
\nbegin
\n begin
\n execute immediate 'drop restore point before_appl_installation';
\n exception
\n when others then null;
\n end;
\n execute immediate 'create restore point before_appl_installation guarantee flashback database';
\nend;
\nprocedure drop_grp
\nas
\nbegin
\n execute immediate 'drop restore point before_appl_installation';
\nend;
\nend;
\n\/
\n
\nexec grp_handling.create_grp;
\n
\nPL\/SQL procedure successfully completed.
\n
\nselect name from v$restore_point;
\n
\nNAME
\n----------------------------------------------
\nBEFORE_APPL_INSTALLATION
\n
\nselect owner,object_type from dba_objects where object_name='GRP_HANDLING';
\n
\nOWNER OBJECT_TYPE
\n------------------------------ -----------------------
\nSYS PACKAGE
\nSYS PACKAGE BODY
\n
\nselect user from dual;
\n
\nUSER
\n------------------------------
\nSYS
\n
\ngrant execute on grp_handling to appluser;
\n
\nconnect appluser\/appluser
\nexec sys.grp_handling.create_grp;
\n
\n*
\nERROR at line 1:
\nORA-01031: insufficient privileges
\nORA-06512: at \"SYS.GRP_HANDLING\", line 10
\nORA-06512: at line 1
\n<\/code><\/p>\n
\nI suggested to wrap a dbms_scheduler-external callout in a Package as follows:<\/p>\n
\n#!\/bin\/bash
\n. oraenv <<EOF
\nprem122
\nEOF
\n
\nsqlplus \/ as sysdba <<EOF
\nbegin
\n execute immediate 'drop restore point before_appl_installation';
\nexception
\n when others then null;
\nend;
\n\/
\ncreate restore point before_appl_installation guarantee flashback database;
\nexit
\nEOF
\n<\/code><\/p>\n
\nconnect dbadmin as sysdba
\nBEGIN
\n DBMS_CREDENTIAL.create_credential(
\n credential_name => 'oracle_122',
\n username => 'oracle',
\n password => '<passwd OS-user oracle>'
\n );
\nEND;
\n\/
\n
\nBEGIN
\n dbms_scheduler.CREATE_job
\n (
\n job_name => 'CRE_GUARANTEED_RP',
\n job_type => 'EXECUTABLE',
\n job_action => '\/home\/oracle\/GRP\/cre_grp.bash',
\n enabled => true,
\n auto_drop => false,
\n credential_name => 'oracle_122'
\n );
\nEND;
\n\/
\n<\/code><\/p>\n
\nconnect dbadmin as sysdba
\ncreate procedure run_my_GRP_job
\nas
\nbegin
\n dbms_scheduler.run_job('CRE_GUARANTEED_RP');
\nend;
\n\/
\n
\ngrant execute on run_my_GRP_job to appluser;
\n<\/code><\/p>\n
\nconnect appluser
\nexec sys.run_my_GRP_job;
\n<\/code><\/p>\n