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.
Ravin Bharath Maharaj
30.09.2023Thank you for sharing. Is there a way to check when the capture process was started