Infrastructure at your Service

Franck Pachot

Oracle 12c privilege analysis rocks

By Franck Pachot

12c came with a very nice feature: privilege analysis. You don’t know which privileges are required? then just grant DBA, run your application, and check which minimal privileges are needed. And today, I’ve discovered how it is very powerful: you can even see privileges used internally, even when not done by SQL, and even not documented.

It starts like that, with a question from Vladimir Sitnikov (who publishes very interesting stuff from his twitter account) in the tone of a challenge:

@FranckPachot Ok, ace. Do you think dbms_utility.get_parameter_value requires special grants (e.g. in current 11gR2)?

— Vladimir Sitnikov (@VladimirSitnikv) November 26, 2014

So I got to the doc which has a special security model for some functions but nothing about get_parameter_value.

Then I created a simple user with only CREATE SESSION privilege and got:

SQL> drop user TEST;
User dropped.
SQL> grant create session to TEST identified by TEST;
Grant succeeded.
SQL> connect TEST/TEST
SQL> variable i number
SQL> variable s varchar2(1000)
SQL> variable t number
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_UTILITY", line 140
ORA-06512: at line 1

So, which privileges do you need? Let’s try the 12c privilege analysis:

SQL> grant dba to TEST;
Grant succeeded.

SQL> connect / as sysdba

SQL> exec dbms_privilege_capture.create_capture (name=>'demo',type =>dbms_privilege_capture.g_role,roles=>role_name_list('DBA'));
PL/SQL procedure successfully completed.

SQL> exec dbms_privilege_capture.enable_capture (name=>'demo');
PL/SQL procedure successfully completed.

SQL> connect TEST/TEST

SQL> exec :t:=dbms_utility.get_parameter_value('NLS_LENGTH_SEMANTICS',:i,:s);
PL/SQL procedure successfully completed.

SQL> print s


SQL> connect / as sysdba

SQL> exec dbms_privilege_capture.disable_capture(name=>'demo');
PL/SQL procedure successfully completed.

SQL> exec dbms_privilege_capture.generate_result(name=>'demo');
PL/SQL procedure successfully completed.

SQL> select object_owner,object_name,obj_priv from dba_used_objprivs ;

---------- --------------- ----------

SQL> select path from dba_used_objprivs_path ;


SQL> exec dbms_privilege_capture.drop_capture (name=>'demo');
PL/SQL procedure successfully completed.

I’ve granted the DBA privilege and have run the privilege analysis capture on that role while calling the function. And bingo: you need to be granted SELECT on V_$PARAMETER (which come into DBA role through the SELECT_CATALOG_ROLE) … which sounds legitimate as the goal is to get a parameter value.

But do you know what? DBMS_UTILITY.GET_PARAMETER_VALUE do not execute any select statement. That behavior is documented in that package for other function, but not for the GET_PARAMETER_VALUE one:

Rem The dbms_utility package is run-as-caller (psdicd.c) only for
Rem its name_resolve, compile_schema, analyze_schema, wait_on_pending_dml,
Rem and expand_sql_text procedures. This package is not run-as-caller
Rem w.r.t. SQL (psdpgi.c) so that the SQL works correctly (runs as
Rem SYS). The privileges are checked via dbms_ddl.


That function calls a C function (KSPGPNICD) so we don’t know what happens behind. If you sql_trace it, you don’t see anything about V_$PARAMETER.

But privilege analysis show the required privileges anyway, and that rocks.


  • Mayur says:

    The select privilege on v_$parameter was not mandated in oracle 11g and is only an addition/plugged hole in oracle 12c. I was using a function in 11g which I am not able to use on oracle 12c now because I cannot provide the user with the select privileges on V_$PARAMETER, do you know any workaround on this?

  • Hi Mayur,
    Sorry, I don’t know how to workaround this. This has been introduced by OCT 2014 PSU.

  • Salemikael A. says:


    Thank you very much. i’ve look around for work around/solution but there was nothing till i found your blog . you saved my day!

    S A.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Franck Pachot
Franck Pachot

Principal Consultant / Database Evangelist
Oracle ACE Director, Oracle Database OCM 12c
AWS Database Specialty certified, AWS Data Hero
Oak Table member

RSS for this blog: feed
Twitter: @FranckPachot
LinkedIn :
Podcast en français: DBPod