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
Focker513Asked:
Who is Participating?
 
slightwv (䄆 Netminder) Commented:
It would appear that you still need to grant select on the V$_PARAMETER view to allow GET_PARMATER_VALUE to do what it does.

http://blog.dbi-services.com/12c-privilege-analysis-rocks/

You might need to create a wrapper procedure/view to restrict specific parameters and grant select/execute on that to the TEST user.
0
 
Focker513Author Commented:
Okay thanks for the info
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.