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
Connected.
SQL> variable i number
SQL> variable s varchar2(1000)
SQL> variable t number
SQL> exec :t:=DBMS_UTILITY.GET_PARAMETER_VALUE('NLS_LENGTH_SEMANTICS',:i,:s);
BEGIN :t:=DBMS_UTILITY.GET_PARAMETER_VALUE('NLS_LENGTH_SEMANTICS',:i,:s); END;
*
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
Connected.

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
Connected.

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

SQL> print s

S
--------------------------------------------------------------
BYTE

SQL> connect / as sysdba
Connected.

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 ;

OBJECT_OWN OBJECT_NAME     OBJ_PRIV
---------- --------------- ----------
SYS        V_$PARAMETER    SELECT

SQL> select path from dba_used_objprivs_path ;

PATH
--------------------------------------------------------------
GRANT_PATH('TEST', 'DBA', 'EXP_FULL_DATABASE', 'SELECT_CATALOG_ROLE')
GRANT_PATH('TEST', 'DBA', 'EM_EXPRESS_ALL', 'EM_EXPRESS_BASIC', 'SELECT_CATALOG_ROLE')
GRANT_PATH('TEST', 'DBA', 'DATAPUMP_IMP_FULL_DATABASE', 'EXP_FULL_DATABASE', 'SELECT_CATALOG_ROLE')
GRANT_PATH('TEST', 'DBA', 'DATAPUMP_EXP_FULL_DATABASE', 'EXP_FULL_DATABASE', 'SELECT_CATALOG_ROLE')
GRANT_PATH('TEST', 'DBA', 'IMP_FULL_DATABASE', 'SELECT_CATALOG_ROLE')
GRANT_PATH('TEST', 'DBA', 'DATAPUMP_IMP_FULL_DATABASE', 'IMP_FULL_DATABASE', 'SELECT_CATALOG_ROLE')
GRANT_PATH('TEST', 'DBA', 'SELECT_CATALOG_ROLE')

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.