Robert said that for some PACKAGE headers this is documented in the 12.2 PL/SQL documentation and it states that the AUTHID can be NULL. The documentation in "8.14 Invoker's Rights and Definer's Rights (AUTHID Property)" does indeed mention this but not clearly enough. Also my original test was on 11.2.0.4 and I checked and at least 11.1 documentation has the same mention that some PL/SQL has a NULL AUTHID. It states "The AUTHID property does not affect compilation, and has no meaning for units that have no code, such as collection types" and also "For units for which AUTHID has meaning, the view shows the value CURRENT_USER or DEFINER; for other units, the view shows NULL" BUT this does not really explain the scope of this.
It basically says that where a piece of PL/SQL has no executable code then there is no AUTHID meaning. So this makes sense in that if a package is only a header then there is no executable section therefore the AUTHID is NULL. We can create a header with a PROCEDURE and not even create the PACKAGE BODY and the AUTHID is set. We will see this shortly in my examples. The problem for me is we can find ways to execute code in a PACKAGE HEADER that has a NULL AUTHID. This lead me to then think, well if we can execute code is the NULL AUTHID really DEFINER or CURRENT_USER even though its not stated. So I found a way to test this and we can see
So, Gary suggested that I try %TYPE, %ROWTYPE and also executable code in the package header and see what happens. So first I looked into this; my tests initially are just run as SYSTEM but later I will dig deeper and create some examples across schemas. All of the tests are aimed at a PACKAGE header with no PACKAGE BODY as this seems to be the only case of PL/SQL in the database that does not have an AUTHID set. The test database is 19c.
First the contents of the pp.sql script used to show whether my package has an AUTHID assigned:
SQL> get pp
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 owner,object_name,object_type,authid from dba_procedures where object_name='TEST' and owner='SYSTEM'
7 .
SQL>
We will use this shortly. Next create a table so that we can try %TYPE on a column as a variable in the package as Gary suggested:
SQL> create table testtab (col01 number);
Table created.
SQL>
SQL> create or replace package test is
2 gc_var testtab.col01%type;
3 end test;
4 /
Package created.
SQL> @pp
OWNER OBJECT_NAME OBJECT_TYPE AUTHID
------------------------------ ------------------------------ ------------------------------ -------------
SYSTEM TEST PACKAGE
SQL>
This does not change the NULL AUTHID for my PACKAGE header. Next test a %ROWTYPE:
SQL> create or replace package test is
2 gc_var testtab%rowtype;
3 end test;
4 /
Package created.
SQL> @pp
OWNER OBJECT_NAME OBJECT_TYPE AUTHID
------------------------------ ------------------------------ ------------------------------ -------------
SYSTEM TEST PACKAGE
SQL>
Again no change. Use of a %TYPE or %ROWTYPE does not add a DEFINER or CURRENT_USER type to the PACKAGE header. So use of SQL references does not seem to change things. Next lets try a user defined type in the database and reference it in the PACKAGE header:
SQL> create type testtype as object (
2 name varchar2(30),
3 email varchar2(30));
4 /
Type created.
SQL> create or replace package test is
2 gc_type testtype;
3 end test;
4 /
Package created.
SQL> @pp
OWNER OBJECT_NAME OBJECT_TYPE AUTHID
------------------------------ ------------------------------ ------------------------------ -------------
SYSTEM TEST PACKAGE
SQL>
No, use of a TYPE does not seem to make a difference; what about a TYPE declared in the PACKAGE header itself:
SQL> create or replace package test is
2 type books is record (
3 title varchar2(30),
4 subject varchar2(30),
5 author varchar2(30));
6 gc_book books;
7 end test;
8 /
Package created.
SQL> @pp
OWNER OBJECT_NAME OBJECT_TYPE AUTHID
------------------------------ ------------------------------ ------------------------------ -------------
SYSTEM TEST PACKAGE
SQL>
Again no difference. What about a CURSOR:
SQL> get cc
1 create or replace package test is
2 cursor gc_main (cp_object in varchar2) is
3 select object_name,object_type,owner,authid
4 from sys.all_procedures
5 where object_name=cp_object;
6* end test;
SQL> @cc
Package created.
SQL> @pp
OWNER OBJECT_NAME OBJECT_TYPE AUTHID
------------------------------ ------------------------------ ------------------------------ -------------
SYSTEM TEST PACKAGE
SQL>
Again, no change. The declaration of a CURSOR does not cause the PACKAGE header to get an AUTHID defined. Just to show that we can cause an AUTHID to be added to a PACKAGE header we can add a procedure to the header to forward declare it. Remember that the procedure does not actually have to exist in a PACKAGE BODY - we will come back to that again at the end of this story. First create a PACKAGE with a PROCEDURE declared:
SQL> create or replace package test is
2 procedure tester(pv_in varchar2);
3 end test;
4 /
Package created.
SQL> @pp
OWNER OBJECT_NAME OBJECT_TYPE AUTHID
------------------------------ ------------------------------ ------------------------------ -------------
SYSTEM TEST PACKAGE DEFINER
SYSTEM TEST PACKAGE DEFINER
SQL>
Lets extend pp.sql to also show sub-units to see what the two lines returned mean:
SQL> get pp
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 col procedure_name for a30
7* select owner,object_name,procedure_name,object_type,authid from dba_procedures where object_name='TEST' and owner='SYSTEM'
8 .
SQL>
SQL> @pp
OWNER OBJECT_NAME PROCEDURE_NAME OBJECT_TYPE AUTHID
------------------------------ ------------------------------ ------------------------------ ------------------------------ -------------
SYSTEM TEST TESTER PACKAGE DEFINER
SYSTEM TEST PACKAGE DEFINER
SQL>
Next lets try a PACKAGE header with just a DATE and then initialise the date:
SQL> create or replace package test is
2 gc_var date;
3 end test;
4 /
Package created.
SQL> @pp
OWNER OBJECT_NAME PROCEDURE_NAME OBJECT_TYPE AUTHID
------------------------------ ------------------------------ ------------------------------ ------------------------------ -------------
SYSTEM TEST PACKAGE
SQL>
SQL> create or replace package test is
2 gc_var date:=trunc(sysdate);
3 end test;
4 /
Package created.
SQL> @pp
OWNER OBJECT_NAME PROCEDURE_NAME OBJECT_TYPE AUTHID
------------------------------ ------------------------------ ------------------------------ ------------------------------ -------------
SYSTEM TEST PACKAGE
SQL>
In both cases we have still not got an AUTHID set BUT we made some progress as we can call executable code in a PACKAGE header; the call to TRUNC() is made when the package is instantiated in memory when its used. Lets extend this to now use a function that I control so that we can progress this further:
QL> create or replace function testfunc return number is
2 begin
3 return(42);
4 end testfunc;
5 /
Function created.
SQL> create or replace package test is
2 gc_var number:=testfunc();
3 end test;
4 /
Package created.
SQL> @pp
OWNER OBJECT_NAME PROCEDURE_NAME OBJECT_TYPE AUTHID
------------------------------ ------------------------------ ------------------------------ ------------------------------ -------------
SYSTEM TEST PACKAGE
SQL>
So, we can call a user function in a PACKAGE that has no BODY and therefore we can possibly work out if a PACKAGE header that has no AUTHID is actually DEFINER or CURRENT_USER and maybe force it to one or the other. So I want to write a better function that can be called in the header that looks at the users details in terms of session, logged in user, current user and schema user and also what roles, privileges are granted and whats available at run time. I am creating an INVOKER rights function so that when it runs it runs in the context of the caller; in this case the function is called from the PACKAGE header so should then run in the context of the header; i.e. the caller:
SQL> get inv.sql
1 create or replace function testinv
2 return number
3 authid current_user
4 is
5 lv_user varchar2(100);
6 lv_username varchar2(100);
7 lv_curr varchar2(100);
8 lv_sess varchar2(100);
9 lv_scm varchar2(100);
10 begin
11 select 'USER: '||user userr,
12 'Username: '||username username,
13 'Current User: '||sys_context('userenv','current_user') curr,
14 'Session User: '||sys_context('userenv','session_user') sess,
15 'Current Schema: '||sys_context('userenv','current_schema') scm
16 into lv_user,lv_username,lv_curr,lv_sess,lv_scm
17 from user_users;
18 dbms_output.put_line(lv_user);
19 dbms_output.put_line(lv_username);
20 dbms_output.put_line(lv_curr);
21 dbms_output.put_line(lv_sess);
22 dbms_output.put_line(lv_scm);
23 for cc_priv in (
24 select 'SESSION' typ,role grants from session_roles
25 union
26 select 'ROLE' typ,granted_role grants from user_role_privs
27 union
28 select 'PRIVILEGE' typ,privilege grants from user_sys_privs
29 order by typ) loop
30 dbms_output.put_line(cc_priv.typ||chr(9)||cc_priv.grants);
31 end loop;
32 return(42);
33* end;
34 .
SQL>
SQL> @inv
Function created.
SQL> create or replace package test is
2 gc_var number:=testinv();
3 end test;
4 /
Package created.
SQL> @pp
OWNER OBJECT_NAME PROCEDURE_NAME OBJECT_TYPE AUTHID
------------------------------ ------------------------------ ------------------------------ ------------------------------ -------------
SYSTEM TEST PACKAGE
So, you can see that this PACKAGE header now calls executable code that we control. So lets run it and see what happens:
SQL> begin
2 dbms_output.put_line(test.gc_var);
3 end;
4 /
USER: SYSTEM
Username: SYSTEM
Current User: SYSTEM
Session User: SYSTEM
Current Schema: SYSTEM
PRIVILEGE CREATE MATERIALIZED VIEW
PRIVILEGE CREATE TABLE
PRIVILEGE DEQUEUE ANY QUEUE
PRIVILEGE ENQUEUE ANY QUEUE
PRIVILEGE GLOBAL QUERY REWRITE
PRIVILEGE MANAGE ANY QUEUE
PRIVILEGE SELECT ANY TABLE
PRIVILEGE UNLIMITED TABLESPACE
ROLE AQ_ADMINISTRATOR_ROLE
ROLE DBA
42
PL/SQL procedure successfully completed.
SQL>
OK, so the use of the variable in the PACKAGE header caused the invoker rights function to return 42 to the global variable in the package header. This means that the function which is INVOKER can be used to test if the header is DEFINER or INVOKER. The first indication shows that this PACKAGE header with a NULL AUTHID is actually DEFINER. We can see that the roles granted are not available in the session. If we run similar checks in the session then we can see that the roles are enabled in the session but they were disabled in the PACKAGE header and this was shown in the INVOKER function. I will prove this in a minute. First show the session details:
SQL> @s
USERR USERNAME CURR SESS SCM
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------
USER: SYSTEM Username: SYSTEM Current User: SYSTEM Session User: SYSTEM Current Schema: SYSTEM
TYP GRANTS
---------- ------------------------------
PRIVILEGE CREATE MATERIALIZED VIEW
PRIVILEGE CREATE TABLE
PRIVILEGE DEQUEUE ANY QUEUE
PRIVILEGE ENQUEUE ANY QUEUE
PRIVILEGE GLOBAL QUERY REWRITE
PRIVILEGE MANAGE ANY QUEUE
PRIVILEGE SELECT ANY TABLE
PRIVILEGE UNLIMITED TABLESPACE
ROLE AQ_ADMINISTRATOR_ROLE
ROLE DBA
SESSION AQ_ADMINISTRATOR_ROLE
TYP GRANTS
---------- ------------------------------
SESSION CAPTURE_ADMIN
SESSION DATAPUMP_EXP_FULL_DATABASE
SESSION DATAPUMP_IMP_FULL_DATABASE
SESSION DBA
SESSION EM_EXPRESS_ALL
SESSION EM_EXPRESS_BASIC
SESSION EXECUTE_CATALOG_ROLE
SESSION EXP_FULL_DATABASE
SESSION GATHER_SYSTEM_STATISTICS
SESSION HS_ADMIN_EXECUTE_ROLE
SESSION HS_ADMIN_SELECT_ROLE
TYP GRANTS
---------- ------------------------------
SESSION IMP_FULL_DATABASE
SESSION JAVA_ADMIN
SESSION OLAP_DBA
SESSION OLAP_XS_ADMIN
SESSION OPTIMIZER_PROCESSING_RATE
SESSION SCHEDULER_ADMIN
SESSION SELECT_CATALOG_ROLE
SESSION WM_ADMIN_ROLE
SESSION XDBADMIN
SESSION XDB_SET_INVOKER
32 rows selected.
SQL>
The ROLEs are available so must have been disabled by the PACKAGE header.
Lets now go back to the beginning and re-do this test with three users; one user who will own the PACKAGE, one user who will own the INVOKER rights function and one user to execute and use the PACKAGE header. So create the FUNCTION use and the INVOKER function first:
SQL> create user tfunc identified by tfunc;
User created.
SQL> grant create session, create procedure to tfunc;
Grant succeeded.
SQL> connect tfunc/tfunc@//192.168.56.77:1521/orclpdb.localdomain
Connected.
SQL> get inv
1 create or replace function testinv
2 return number
3 authid current_user
4 is
5 lv_user varchar2(100);
6 lv_username varchar2(100);
7 lv_curr varchar2(100);
8 lv_sess varchar2(100);
9 lv_scm varchar2(100);
10 begin
11 select 'USER: '||user userr,
12 'Username: '||username username,
13 'Current User: '||sys_context('userenv','current_user') curr,
14 'Session User: '||sys_context('userenv','session_user') sess,
15 'Current Schema: '||sys_context('userenv','current_schema') scm
16 into lv_user,lv_username,lv_curr,lv_sess,lv_scm
17 from user_users;
18 dbms_output.put_line(lv_user);
19 dbms_output.put_line(lv_username);
20 dbms_output.put_line(lv_curr);
21 dbms_output.put_line(lv_sess);
22 dbms_output.put_line(lv_scm);
23 for cc_priv in (
24 select 'SESSION' typ,role grants from session_roles
25 union
26 select 'ROLE' typ,granted_role grants from user_role_privs
27 union
28 select 'PRIVILEGE' typ,privilege grants from user_sys_privs
29 order by typ) loop
30 dbms_output.put_line(cc_priv.typ||chr(9)||cc_priv.grants);
31 end loop;
32 return(42);
33* end;
34 .
SQL> @inv
Function created.
SQL> grant execute on testinv to public;
Grant succeeded.
SQL>
Now create the PACKAGE header owner and grant him two roles; CONNECT and RESOURCE; these are not needed functionally we just want to see if they are disabled in the function and if so we can therefore know that the PACKAGE header is in fact DEFINER rights not NULL:
SQL> create user tpack identified by tpack;
User created.
SQL> grant create session,create procedure to tpack;
Grant succeeded.
SQL> grant connect, resource to tpack;
Grant succeeded.
SQL> connect tpack/tpack@//192.168.56.77:1521/orclpdb.localdomain
Connected.
SQL> create or replace package test is
2 gc_var number:=tfunc.testinv();
3 end test;
4 /
Package created.
SQL> grant execute on test to public;
Grant succeeded.
SQL> @pp
OWNER OBJECT_NAME PROCEDURE_NAME OBJECT_TYPE AUTHID
------------------------------ ------------------------------ ------------------------------ ------------------------------ -------------
TPACK TEST PACKAGE
SQL> @s
USERR USERNAME CURR SESS SCM
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------
USER: TPACK Username: TPACK Current User: TPACK Session User: TPACK Current Schema: TPACK
TYP GRANTS
---------- ------------------------------
PRIVILEGE CREATE PROCEDURE
PRIVILEGE CREATE SESSION
ROLE CONNECT
ROLE RESOURCE
SESSION CONNECT
SESSION RESOURCE
SESSION SODA_APP
7 rows selected.
SQL>
The PACKAGE owner at run time just in a session as that SCHEMA shows that the roles CONNECT, RESOURCE and SODA_APP are enable in the SESSION. Now create the user to access the PACKAGE header which will cause the NUMBER global variable to be intialised to 42 but to first print out the security and in particular it will show whether the PACKAGE header is DEFINER or INVOKER. As the PACKAGE header calls the function which is INVOKER we know that if the roles are turned on in the function then the PACKAGE header must be INVOKER; if the roles are turned off then the PACKAGE header must be DEFINER:
SQL> create user tuser identified by tuser;
User created.
SQL>
SQL> connect tuser/tuser@//192.168.56.77:1521/orclpdb.localdomain
Connected.
SQL> set serveroutput on
SQL> @s
USERR USERNAME CURR SESS SCM
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------
USER: TUSER Username: TUSER Current User: TUSER Session User: TUSER Current Schema: TUSER
TYP GRANTS
---------- ------------------------------
PRIVILEGE CREATE SESSION
SQL> begin
2 dbms_output.put_line(tpack.test.gc_var);
3 end;
4 /
USER: TUSER
Username: TPACK
Current User: TPACK
Session User: TUSER
Current Schema: TPACK
PRIVILEGE CREATE PROCEDURE
PRIVILEGE CREATE SESSION
ROLE CONNECT
ROLE RESOURCE
42
PL/SQL procedure successfully completed.
SQL>
As we can see the roles are not enabled in the SESSION so the PACKAGE header is for sure DEFINER. Lets do an alternate test to call an INVOKER rights procedure from a DEFINER rights procedure (not the PACKAGE header) just so we can compare the output:
SQL> connect tpack/tpack@//192.168.56.77:1521/orclpdb.localdomain
Connected.
SQL> create or replace procedure testf
2 authid definer is
3 fv_num number;
4 begin
5 fv_num:=tfunc.testinv();
6 end testf;
7 /
Procedure created.
SQL> grant execute on testf to public;
Grant succeeded.
SQL>
SQL> @pp
OWNER OBJECT_NAME PROCEDURE_NAME OBJECT_TYPE AUTHID
------------------------------ ------------------------------ ------------------------------ ------------------------------ -------------
TPACK TESTF PROCEDURE DEFINER
SQL>
Note the pp.sql script has been modified to show the details of the definer procedure not the package. Now run this DEFINER rights code and call the same INVOKER rights procedure and check the output:
SQL> connect tuser/tuser@//192.168.56.77:1521/orclpdb.localdomain
Connected.
SQL> set serveroutput on
SQL> begin
2 tpack.testf();
3 end;
4 /
USER: TUSER
Username: TPACK
Current User: TPACK
Session User: TUSER
Current Schema: TPACK
PRIVILEGE CREATE PROCEDURE
PRIVILEGE CREATE SESSION
ROLE CONNECT
ROLE RESOURCE
PL/SQL procedure successfully completed.
SQL>
The output is identical to the output from the running the INVOKER procedure from the PACKAGE header. This proves that a PACKAGE header that does not have AUTHID set in DBA/ALL/USER_PROCEDURES actually behaves as DEFINER rights even though its not.
So finally, can we force the PACKAGE header to be CURRENT_USER?
SQL> connect tpack/tpack@//192.168.56.77:1521/orclpdb.localdomain
Connected.
SQL> create or replace package test authid current_user is
2 gc_var number:=tfunc.testinv();
3 end test;
4 /
Package created.
SQL> @pp
OWNER OBJECT_NAME PROCEDURE_NAME OBJECT_TYPE AUTHID
------------------------------ ------------------------------ ------------------------------ ------------------------------ -------------
TPACK TEST PACKAGE
SQL>
No, we still cannot make the header INVOKER or CURRENT_USER. But, maybe there is a way:
SQL> sho user
USER is "TPACK"
SQL> create or replace package test authid current_user is
2 gc_var number:=tfunc.testinv();
3 procedure t;
4 end test;
5 /
Package created.
SQL> @pp
OWNER OBJECT_NAME PROCEDURE_NAME OBJECT_TYPE AUTHID
------------------------------ ------------------------------ ------------------------------ ------------------------------ -------------
TPACK TEST T PACKAGE CURRENT_USER
TPACK TEST PACKAGE CURRENT_USER
SQL>
So the PACKAGE header is CURRENT_USER only because we declared a procedure BUT we don't have to implement the body of the package and the procedure. Now what happens if we run the same initialise test as above:
SQL> connect tuser/tuser@//192.168.56.77:1521/orclpdb.localdomain
Connected.
SQL> set serveroutput on
SQL> begin
2 dbms_output.put_line(tpack.test.gc_var);
3 end;
4 /
USER: TUSER
Username: TUSER
Current User: TUSER
Session User: TUSER
Current Schema: TUSER
PRIVILEGE CREATE SESSION
42
PL/SQL procedure successfully completed.
SQL>
Now you can see all types of user are TUSER so the package header is for sure INVOKER. Its a hack to get it but we could do the same for DEFINER if we wanted.
So in summary; The documentation states that some cases exist where PL/SQL does not have an AUTHID or DEFINER or CURRENT_USER and it is in fact set to NULL. The cases where the AUTHID is NULL are not fully or properly laid out in the PL/SQL documentation. Also it would seem from this testing that even though the AUTHID is NULL in these cases its really still DEFINER.
What use is all of this?
Well it started with a customer issue in our PL/SQL code security scanner because this column was NULL in one of our checks and the follow on checks relied on it being INVOKER or DEFINER; we fixed that easily but it was useful to know how this really works. As I said in my first post "PL/SQL That is not DEFINER or INVOKER rights - BUG?" a few days ago to me it doesn't make sense that some PL/SQL can not have a mode of DEFINER or CURRENT_USER as even a PACKAGE header with just variables can have executable code.
Hope this helps someone!!
