Oracle Grant Debug Privilege without Alter-CodePudding

I need to grant to some developer a privilege just to be able to debug package/procedure/function on Oracle database.

But when I grant debug any procedure or debug connect session, he/she is also able to change the code. How can I prevent it?

CodePudding user response：

The solution is to de-couple the owner of the procedure from the developer. Let me show you how

Create a user who owns a procedure

SQL> create user test4 identified by Oracle_1234 ;

User created.

SQL> grant create table, create procedure to test4 ;

Grant succeeded.

SQL> create procedure test4.pr_test ( p1 in number )
  2  is
  3  begin
  4  dbms_output.put_line(p1);
  5  end;
  6  /

Procedure created.

Create a user who has debug over the procedure

SQL> create user test5 identified by Oracle_1234 ;

User created.

SQL> grant debug connect session , create session to test5 ;

Grant succeeded.

SQL> grant debug on test4.pr_test to test5 ;

Grant succeeded.

Now, the user test5 can debug the procedure owned by test4, but he can execute it, nor change it.

sqlplus test5/Oracle_1234

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Oct 6 17:04:20 2021
Version 19.6.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.6.0.0.0

SQL> exec test4.pr_test ;
BEGIN test4.pr_test ; END;

            *
ERROR at line 1:
ORA-06550: line 1, column 13:
PLS-00904: insufficient privilege to access object TEST4.PR_TEST
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

SQL> create or replace procedure test4.pr_test
  2  is
  3  begin
  4  null;
  5  end;
  6  /
create or replace procedure test4.pr_test
*
ERROR at line 1:
ORA-01031: insufficient privileges

The best way to avoid that is to decouple owners/schemas from users, thereby you can grant debug, but the user won't be able to change the code or execute the procedure.

By the way, debug any procedure is a really bad security practice. ANY privileges should not be granted to anyone, unless is strictly necessary.