Avatar of Focker513
Focker513
Flag for United States of America

asked on 

Request assistance granting user permission to execute a sys function from within a package

I'm trying to grant a user execute permissions to SYS.DBMS_UTILITY.GET_PARAMETER_VALUE so I do not need to grant select on the V$ views.

I can grant it on the package DBMS_UTILITY but not the specific function GET_PARAMETER_VALUE:

SQL> grant execute on SYS.DBMS_UTILITY to TEST;

Grant succeeded.

SQL> grant execute on SYS.DBMS_UTILITY.GET_PARAMETER_VALUE to TEST;
grant execute on SYS.DBMS_UTILITY.GET_PARAMETER_VALUE to TEST
                                 *
ERROR at line 1:
ORA-00905: missing keyword

--Attempt at running function logged on as Test user:
SQL> conn Test/test
Connected.
SQL>
SQL> DECLARE
  2    parnam VARCHAR2(256);
  3    intval BINARY_INTEGER;
  4    strval VARCHAR2(256);
  5    partyp BINARY_INTEGER;
  6  BEGIN
  7    partyp := dbms_utility.get_parameter_value('open_cursors',
  8                                                intval, strval);
  9    dbms_output.put('parameter value is: ');
 10    IF partyp = 1 THEN
 11      dbms_output.put_line(strval);
 12    ELSE
 13      dbms_output.put_line(intval);
 14    END IF;
 15    IF partyp = 1 THEN
 16      dbms_output.put('parameter value length is: ');
 17      dbms_output.put_line(intval);
 18    END IF;
 19    dbms_output.put('parameter type is: ');
 20    IF partyp = 1 THEN
 21      dbms_output.put_line('string');
 22    ELSE
 23      dbms_output.put_line('integer');
 24    END IF;
 25  END;
 26  /
DECLARE
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_UTILITY", line 140
ORA-06512: at line 7

Open in new window


I'm using Oracle 12c and this is in a pluggable database.

Thanks
Oracle Database

Avatar of undefined
Last Comment
Focker513

8/22/2022 - Mon