I always understood that PL/SQL objects in the database that are not explicitly changed to INVOKER rights - CURRENT_USER or DEFINER. During testing of an issue with PFCLScan where a customer was using our project template to analyse PL/SQL and it failed we tried to replicate the same issue here. The issue turned out that we are listing lots of details around PL/SQL in one of our checks in one policy BUT some PL/SQL in the database had a NULL AUTHID; i.e. it is not DEFINER or INVOKER. So this caused an error as our checks code expected a value for each piece of PL/SQL or DEFINER or CURRENT_USER.
So whilst the fix to our policy and check was easy, NVL the AUTHID column to NULL and then the PFCLScan project runs correctly it lead me to investigate this. How many pieces of PL/SQL have a NULL AUTHID? and why?
I was testing in 11g as that is where we had some sample PL/SQL code that I could look at. So I did a quick check for the count of AUTHID:
SQL> select count(*),authid from dba_procedures group by authid;
COUNT(*) AUTHID
---------- ------------
10884 CURRENT_USER
53
17765 DEFINER
So 53 objects in my database have a NULL AUTHID. So what do they have in common. So it looks like they are all PACKAGE. We can get the details from DBA_PROCEDURES
SQL> select owner,object_name, procedure_name,object_type,authid from dba_procedures where authid is null order by owner;
OWNER OBJECT_NAME PROCEDURE_NAME OBJECT_TYPE AUTHID
------------------------------ ------------------------------ ------------------------------ ------------- ------------
APEX_030200 WWV_FLOW_TRANSLATION_UTILITIES PACKAGE
APEX_030200 WWV_FLOW_THEME_GLOBALS PACKAGE
APEX_030200 WWV_FLOW_FND_GLOBAL PACKAGE
APEX_030200 WWV_FLOW_GLOBAL PACKAGE
APEX_030200 WWV_FLOW_TREE_GLOBAL_VARS PACKAGE
APEX_030200 WWV_FLOW_FND_USER_PW_PREF PACKAGE
APEX_030200 WWV_FLOW_IMAGE_PREFIX PACKAGE
APEX_030200 WWV_FLOW_FILE_OBJECT_ID PACKAGE
CORE CNST PACKAGE
CORE TYP PACKAGE
CTXSYS DR_DEF PACKAGE
OWNER OBJECT_NAME PROCEDURE_NAME OBJECT_TYPE AUTHID
------------------------------ ------------------------------ ------------------------------ ------------- ------------
CTXSYS DRILIST PACKAGE
CTXSYS DRIG PACKAGE
CTXSYS DRIOBJ PACKAGE
CTXSYS CTX_ULEXER PACKAGE
MDSYS PRVTPC PACKAGE
MDSYS SDO_VERS PACKAGE
MDSYS SDOCURPKG PACKAGE
OLAPSYS CWM$EXCEPTIONS PACKAGE
OLAPSYS CWM2_OLAP_EXCEPTIONS PACKAGE
ORDSYS ORDIMAGEEXCEPTIONS PACKAGE
ORDSYS ORDDOCEXCEPTIONS PACKAGE
OWNER OBJECT_NAME PROCEDURE_NAME OBJECT_TYPE AUTHID
------------------------------ ------------------------------ ------------------------------ ------------- ------------
ORDSYS ORDIMAGESIEXCEPTIONS PACKAGE
ORDSYS ORDIMAGECONSTANTS PACKAGE
ORDSYS ORDVIDEOEXCEPTIONS PACKAGE
ORDSYS ORDAUDIOEXCEPTIONS PACKAGE
ORDSYS ORDSOURCEEXCEPTIONS PACKAGE
SYS DM_CL_CUR PACKAGE
SYS DM_GLM_CUR PACKAGE
SYS DM_NMF_CUR PACKAGE
SYS PBUTL PACKAGE
SYS DBMS_FILE_GROUP_DECL PACKAGE
SYS LCR$_XML_SCHEMA PACKAGE
OWNER OBJECT_NAME PROCEDURE_NAME OBJECT_TYPE AUTHID
------------------------------ ------------------------------ ------------------------------ ------------- ------------
SYS DM_SVM_CUR PACKAGE
SYS KUPCC PACKAGE
SYS DBMS_TYPES PACKAGE
SYS BLAST_CUR PACKAGE
SYS DM_MODB_CUR PACKAGE
SYS GENDATATYPEIDCONSTANTS PACKAGE
SYS DBMS_DB_VERSION PACKAGE
SYS DBMS_CRYPTO_TOOLKIT_TYPES PACKAGE
SYS OWA_CX PACKAGE
SYS DBMS_STREAMS_ADM_UTL_INT PACKAGE
SYS DBMS_LCR PACKAGE
OWNER OBJECT_NAME PROCEDURE_NAME OBJECT_TYPE AUTHID
------------------------------ ------------------------------ ------------------------------ ------------- ------------
SYS WWV_FLOW_KEY PACKAGE
SYS GENDATABASEINTERFACE PACKAGE
SYS DBMS_STREAMS_DECL PACKAGE
SYS GENMDMPROPERTYIDCONSTANTS PACKAGE
SYS GENMDMOBJECTIDCONSTANTS PACKAGE
SYS ODCICONST PACKAGE
SYS DBMS_REGISTRY_SERVER PACKAGE
SYS UTL_IDENT PACKAGE
SYS GENMDMCLASSCONSTANTS PACKAGE
53 rows selected.
SQL>
OK, we are getting somewhere. What if we look to see if all of these are PACKAGE without a BODY. I created a simple script to do this:
SQL> get p.sql
1 set lines 220
2 col owner for a30
3 col object_name for a30
4 col object_type for a30
5 col authid for a13
6 select p.owner,p.object_name,p.object_type,p.authid
7 from dba_procedures p,
8 (select owner,object_name from dba_objects where object_type='PACKAGE'
9 minus
10 select owner,object_name from dba_objects where object_type='PACKAGE BODY') o
11 where p.owner=o.owner
12 and p.object_name=o.object_name
13 and ((p.subprogram_id=0 and p.object_type='PACKAGE')
14* or (p.subprogram_id=1 and p.object_type<>'PACKAGE'))
And the results are:
SQL> @p
OWNER OBJECT_NAME OBJECT_TYPE AUTHID
------------------------------ ------------------------------ ------------- ------------
APEX_030200 WWV_FLOW_FILE_OBJECT_ID PACKAGE
APEX_030200 WWV_FLOW_FND_GLOBAL PACKAGE
APEX_030200 WWV_FLOW_FND_USER_PW_PREF PACKAGE
APEX_030200 WWV_FLOW_GLOBAL PACKAGE
APEX_030200 WWV_FLOW_IMAGE_PREFIX PACKAGE
APEX_030200 WWV_FLOW_THEME_GLOBALS PACKAGE
APEX_030200 WWV_FLOW_TRANSLATION_UTILITIES PACKAGE
APEX_030200 WWV_FLOW_TREE_GLOBAL_VARS PACKAGE
CORE CNST PACKAGE
CORE TYP PACKAGE
CTXSYS CTX_CATSEARCH PACKAGE CURRENT_USER
OWNER OBJECT_NAME OBJECT_TYPE AUTHID
------------------------------ ------------------------------ ------------- ------------
CTXSYS CTX_CONTAINS PACKAGE CURRENT_USER
CTXSYS CTX_MATCHES PACKAGE CURRENT_USER
CTXSYS CTX_ULEXER PACKAGE
CTXSYS CTX_XPCONTAINS PACKAGE CURRENT_USER
CTXSYS DRIG PACKAGE
CTXSYS DRILIST PACKAGE
CTXSYS DRIOBJ PACKAGE
CTXSYS DRISCORE PACKAGE CURRENT_USER
CTXSYS DRISCORR PACKAGE DEFINER
CTXSYS DR_DEF PACKAGE
MDSYS SDOCURPKG PACKAGE
OWNER OBJECT_NAME OBJECT_TYPE AUTHID
------------------------------ ------------------------------ ------------- ------------
MDSYS SDO_GEOR_LIZARDTECH PACKAGE CURRENT_USER
MDSYS SDO_NET_MEM PACKAGE CURRENT_USER
MDSYS SDO_TOPO_MAP PACKAGE CURRENT_USER
OLAPSYS CWM$EXCEPTIONS PACKAGE
OLAPSYS CWM2_OLAP_EXCEPTIONS PACKAGE
ORDSYS ORDAUDIOEXCEPTIONS PACKAGE
ORDSYS ORDDOCEXCEPTIONS PACKAGE
ORDSYS ORDIMAGECONSTANTS PACKAGE
ORDSYS ORDIMAGEEXCEPTIONS PACKAGE
ORDSYS ORDIMAGESIEXCEPTIONS PACKAGE
ORDSYS ORDIMGSIG_PKG PACKAGE CURRENT_USER
OWNER OBJECT_NAME OBJECT_TYPE AUTHID
------------------------------ ------------------------------ ------------- ------------
ORDSYS ORDSOURCEEXCEPTIONS PACKAGE
ORDSYS ORDVIDEOEXCEPTIONS PACKAGE
SYS BLAST_CUR PACKAGE
SYS DBMS_CRYPTO_TOOLKIT_TYPES PACKAGE
SYS DBMS_DBFS_CONTENT_SPI PACKAGE DEFINER
SYS DBMS_DB_VERSION PACKAGE
SYS DBMS_FILE_GROUP_DECL PACKAGE
SYS DBMS_LCR PACKAGE
SYS DBMS_RESULT_CACHE_API PACKAGE DEFINER
SYS DBMS_STANDARD PACKAGE DEFINER
SYS DBMS_STREAMS_ADM_UTL_INT PACKAGE
OWNER OBJECT_NAME OBJECT_TYPE AUTHID
------------------------------ ------------------------------ ------------- ------------
SYS DBMS_STREAMS_DECL PACKAGE
SYS DBMS_TYPES PACKAGE
SYS DM_CL_CUR PACKAGE
SYS DM_GLM_CUR PACKAGE
SYS DM_MODB_CUR PACKAGE
SYS DM_NMF_CUR PACKAGE
SYS DM_SVM_CUR PACKAGE
SYS KUPCC PACKAGE
SYS LCR$_XML_SCHEMA PACKAGE
SYS ODCICONST PACKAGE
SYS OWA_CX PACKAGE
OWNER OBJECT_NAME OBJECT_TYPE AUTHID
------------------------------ ------------------------------ ------------- ------------
SYS PBUTL PACKAGE
SYS PLITBLM PACKAGE DEFINER
SYS SYS_STUB_FOR_PURITY_ANALYSIS PACKAGE DEFINER
SYS UTL_IDENT PACKAGE
SYS UTL_MATCH PACKAGE DEFINER
SYS WWV_FLOW_KEY PACKAGE
XDB DBMS_XMLDOM_ICD PACKAGE CURRENT_USER
XDB XDB_PVTRIG_PKG PACKAGE CURRENT_USER
63 rows selected.
Hmm, we had 53 PL/SQL packages that do not have DEFINER or CURRENT_USER set for AUTHID but we have 63 objects here that are PACKAGE without a PACKAGE BODY but of course as there are 63 and not 53 then 10 of them do have a DEFINER or CURRENT_USER set for the AUTHID. Nothing popped out immediately but I thought I would check 19c next as all of the above was in 11.2.0.4. Lets see the same issue in my 19c database by running my p.sql script again from above:
C:\_aa\PB\bin>sqlplus system/oracle1@//192.168.56.77:1521/orclpdb.localdomain
SQL*Plus: Release 11.2.0.4.0 Production on Fri Jan 24 14:45:08 2020
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
SQL>
SQL> @p
OWNER OBJECT_NAME OBJECT_TYPE AUTHID
------------------------------ ------------------------------ ------------------------------ -------------
CTXSYS CTX_CATSEARCH PACKAGE CURRENT_USER
CTXSYS CTX_CONTAINS PACKAGE CURRENT_USER
CTXSYS CTX_XPCONTAINS PACKAGE CURRENT_USER
CTXSYS DRIOBJ PACKAGE
CTXSYS DRISCORE PACKAGE CURRENT_USER
CTXSYS DRISCORR PACKAGE DEFINER
CTXSYS DR_DEF PACKAGE
MDSYS SDOCURPKG PACKAGE
MDSYS SDO_JAVA_STP PACKAGE CURRENT_USER
MDSYS SDO_RDF_JAVA_STP PACKAGE CURRENT_USER
ORDSYS ORDAUDIOEXCEPTIONS PACKAGE
OWNER OBJECT_NAME OBJECT_TYPE AUTHID
------------------------------ ------------------------------ ------------------------------ -------------
ORDSYS ORDIMAGEEXCEPTIONS PACKAGE
SYS DBMS_REGISTRY_SERVER PACKAGE
SYS DBMS_STANDARD PACKAGE DEFINER
SYS DBMS_STREAMS_DECL PACKAGE
SYS DBMS_TYPES PACKAGE
SYS DBMS_WRR_STATE PACKAGE
SYS OWA_CX PACKAGE
SYS PBUTL PACKAGE
SYS SYS_STUB_FOR_PURITY_ANALYSIS PACKAGE DEFINER
SYS UTL_IDENT PACKAGE
XDB XDB_PVTRIG_PKG PACKAGE CURRENT_USER
OWNER OBJECT_NAME OBJECT_TYPE AUTHID
------------------------------ ------------------------------ ------------------------------ -------------
CTXSYS CTX_MATCHES PACKAGE CURRENT_USER
CTXSYS CTX_ULEXER PACKAGE
CTXSYS DRIG PACKAGE
CTXSYS DRILIST PACKAGE
LBACSYS LBAC_ERRORS PACKAGE
MDSYS SDO_NET_MEM PACKAGE CURRENT_USER
MDSYS SDO_SEM_CONST PACKAGE
MDSYS SDO_TOPO_MAP PACKAGE CURRENT_USER
MDSYS SEM_RDFSA_CONST PACKAGE
MDSYS SEM_RDFSA_CONST_INTERNAL PACKAGE
ORDSYS ORDDOCEXCEPTIONS PACKAGE
OWNER OBJECT_NAME OBJECT_TYPE AUTHID
------------------------------ ------------------------------ ------------------------------ -------------
ORDSYS ORDIMAGECONSTANTS PACKAGE
ORDSYS ORDIMAGESIEXCEPTIONS PACKAGE
ORDSYS ORDSOURCEEXCEPTIONS PACKAGE
ORDSYS ORDVIDEOEXCEPTIONS PACKAGE
SYS DBMS_CRYPTO_TOOLKIT_TYPES PACKAGE
SYS DBMS_DBFS_CONTENT_SPI PACKAGE DEFINER
SYS DBMS_DB_VERSION PACKAGE
SYS DBMS_FILE_GROUP_DECL PACKAGE
SYS DBMS_LCR PACKAGE
SYS DBMS_RESULT_CACHE_API PACKAGE DEFINER
SYS DBMS_SERVICE_CONST PACKAGE
OWNER OBJECT_NAME OBJECT_TYPE AUTHID
------------------------------ ------------------------------ ------------------------------ -------------
SYS DBMS_STREAMS_ADM_UTL_INT PACKAGE
SYS KUPCC PACKAGE
SYS ODCICONST PACKAGE
SYS ORA_DM_REFCUR_PKG PACKAGE
SYS PLITBLM PACKAGE DEFINER
SYS UTL_MATCH PACKAGE DEFINER
XDB DBMS_XMLDOM_ICD PACKAGE CURRENT_USER
51 rows selected.
SQL>
Check for the number of NULL AUTHID columns:
SQL> select count(*) from dba_procedures where authid is null;
COUNT(*)
----------
41
SQL>
Lets not see if we can replicate the issue. First create a package header with a procedure definition in it:
SQL> create or replace package test is
2 procedure testit;
3 end test;
4 /
Package created.
SQL> select owner,object_name,object_type,authid from dba_procedures where object_name='TEST' and owner='SYSTEM';
OWNER OBJECT_NAME OBJECT_TYPE AUTHID
------------------------------ ------------------------------ ------------------------------ -------------
SYSTEM TEST PACKAGE DEFINER
SYSTEM TEST PACKAGE DEFINER
That's not it, obvious really as a header with a procedure definition does not make sense if the body without the procedure implementation. So the next check is to create package header without any procedure and only global variables:
SQL> create or replace package test as
2 gc_var constant number:=1;
3 end test;
4 /
Package created.
SQL> select owner,object_name,object_type,authid from dba_procedures where object_name='TEST' and owner='SYSTEM';
OWNER OBJECT_NAME OBJECT_TYPE AUTHID
------------------------------ ------------------------------ ------------------------------ -------------
SYSTEM TEST PACKAGE
SQL>
OK, that proves it. If you create a PL/SQL package header and only have globals defined in it and no procedures or functions that need implementing then Oracle creates the PACKAGE HEADER and does not set DEFINER or CURRENT_USER in DBA_PROCEDURES. So we can replicate the issue and we know in what circumstances it occurs BUT is it a bug or not? Not sure? if you have a package header and it doesn't have executable code then does it make sense for it to be DEFINER or CURRENT_USER. It sort of makes sense that neither is defined BUT if we forced an error:
SQL> create or replace package test as
2 gc_var number(10);
3 end test;
4 /
Package created.
SQL>
SQL> select owner,object_name,object_type,authid from dba_procedures where object_name='TEST' and owner='SYSTEM';
OWNER OBJECT_NAME OBJECT_TYPE AUTHID
------------------------------ ------------------------------ ------------------------------ -------------
SYSTEM TEST PACKAGE
SQL>
SQL> begin
2 test.gc_var:='test test test';
3 end;
4 /
begin
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 2
SQL>
Maybe it could be an issue so maybe the package header even with no function or procedure should have the ability to define DEFINER or CURRENT_USER. Lets see if we can set the AUTHID:
SQL> create or replace package test authid definer as
2 gc_var number(10);
3 end test;
4 /
Package created.
SQL> select owner,object_name,object_type,authid from dba_procedures where object_name='TEST' and owner='SYSTEM';
OWNER OBJECT_NAME OBJECT_TYPE AUTHID
------------------------------ ------------------------------ ------------------------------ -------------
SYSTEM TEST PACKAGE
SQL> create or replace package test authid current_user as
2 gc_var number(10);
3 end test;
4 /
Package created.
SQL> select owner,object_name,object_type,authid from dba_procedures where object_name='TEST' and owner='SYSTEM';
OWNER OBJECT_NAME OBJECT_TYPE AUTHID
------------------------------ ------------------------------ ------------------------------ -------------
SYSTEM TEST PACKAGE
SQL>
So, we cannot force the AUTHID for PL/SQL package header when there is no procedure or function in the package.
Note: Part 2 - PL/SQL Package with no DEFINER or INVOKER rights - Part 2 is available that takes this investigation further
