Call: +44 (0)1904 557620 Call
Blog

Pete Finnigan's Oracle Security Weblog

This is the weblog for Pete Finnigan. Pete works in the area of Oracle security and he specialises in auditing Oracle databases for security issues. This weblog is aimed squarely at those interested in the security of their Oracle databases.

ORA-28050 - Can I drop the SYSTEM User?

Two things most annoy me with the Oracle database in terms of securing it and this is the abundance of default users in most Oracle databases that I perform security audits on and also the massive amount of PUBLIC grants that are there by default for all users.

The primary goal in securing Oracle is to stop people connecting to the database. If you can do that and only allow in the exact number of users necessary to do their job to connect. This can be achieved by network controls, Oracle network controls such as validnode checking and even database controls such as login triggers or Database Vault CONNECT command rules... If you then ensure that each account has only the exact rights necessary to do their job and no more then you are on your way to good data security and lock down. You can then lock down the access controls to the data itself and even use context based security such as VPD or OLS, DV, redaction, masking, TSDP and more. This is least privileges.

Then you have some level of initial control. An attack then must take place on one of a small number of general ways:

  1. The user accounts that are allowed to connect can exploit some other feature of the database or data controls. This means that if you have a valid account and can connect to the database you then need to find a gap in the database settings, data access controls or exploit the fact you didn't really have least rights

  2. The attacker could exploit a remote vulnerability in the database access protocols (TNS, OLOGON etc) and find a way to access the database without a username or with a username and no password. This could be as simple as guessing a default users password or as extreme as a network stack buffer overflow, or...

  3. The attacker could find an exploit in the application stack and tunnel to the database. For instance SQL Injection in application code.

  4. Others, possibly....


So, back to my first two points. We want to stop people connecting, for those that we do allow to connect we limit what they can use (programs), we have strong password controls and we then aim for least rights and finally proper data domains, data access controls and even context based security BUT we are put in a difficult position by many default accounts existing that we maybe don't need and those tens of thousands of PUBLIC grants.

If we could have a database with no default accounts and no PUBLIC grants then we would have a much stronger starting point. Unfortunately we get 44K, 45K in a stock 12.2c, 18c, 19c database.

So as part of this discussion and quest I wanted to look at the SYSTEM user. Most sites I go to the DBAs use SYSDBA to do their daily work or if not they use SYSTEM as the default DBA account. But what does SYSTEM have. First lets connect to my 11.2.0.4 database:

SQL> sho user
USER is "SYS"
SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

SQL>

Now what objects does SYSTEM have:

SQL> select count(*),object_type from dba_objects where owner='SYSTEM' group by object_type;

COUNT(*) OBJECT_TYPE
---------- -------------------
64 INDEX PARTITION
20 SEQUENCE
45 TABLE PARTITION
4 QUEUE
1 PROCEDURE
25 LOB
1 PACKAGE
1 PACKAGE BODY
2 TRIGGER
167 TABLE
253 INDEX

COUNT(*) OBJECT_TYPE
---------- -------------------
8 SYNONYM
14 VIEW
4 FUNCTION
9 TYPE

15 rows selected.

SQL>

What are the main PL/SQL objects owned by SYSTEM:

SQL> col object_name for a30
SQL> col object_type for a30
SQL> l
1* select object_name,object_type from dba_objects where owner='SYSTEM' and object_type in('PROCEDURE','FUNCTION','PACKAGE','PACKAGE BODY')
SQL> /

OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------
ORA$_SYS_REP_AUTH PROCEDURE
DBMS_REPCAT_AUTH PACKAGE
LOGMNR$TAB_GG_TABF_PUBLIC FUNCTION
LOGMNR$COL_GG_TABF_PUBLIC FUNCTION
LOGMNR$SEQ_GG_TABF_PUBLIC FUNCTION
LOGMNR$KEY_GG_TABF_PUBLIC FUNCTION
DBMS_REPCAT_AUTH PACKAGE BODY

7 rows selected.

SQL>

These are REPCAT and LogMiner objects. Interesting. What about tables:

SQL> select object_name,object_type from dba_objects where owner='SYSTEM' and object_type ='TABLE';

OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------
LOGMNR_SESSION_EVOLVE$ TABLE
LOGMNR_GLOBAL$ TABLE
LOGMNR_GT_TAB_INCLUDE$ TABLE
LOGMNR_GT_USER_INCLUDE$ TABLE
LOGMNR_GT_XID_INCLUDE$ TABLE
LOGMNR_UID$ TABLE
LOGMNRGGC_GTLO TABLE
LOGMNRGGC_GTCS TABLE
LOGMNRC_DBNAME_UID_MAP TABLE
LOGMNR_LOG$ TABLE
LOGMNR_PROCESSED_LOG$ TABLE

OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------
LOGMNR_SPILL$ TABLE
LOGMNR_AGE_SPILL$ TABLE
LOGMNR_RESTART_CKPT_TXINFO$ TABLE
LOGMNR_ERROR$ TABLE
LOGMNR_RESTART_CKPT$ TABLE
LOGMNR_INTEGRATED_SPILL$ TABLE
LOGMNR_FILTER$ TABLE
LOGMNR_SESSION_ACTIONS$ TABLE
LOGMNR_PARAMETER$ TABLE
LOGMNR_SESSION$ TABLE
LOGMNRT_MDDL$ TABLE

OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------
MVIEW$_ADV_WORKLOAD TABLE
MVIEW$_ADV_BASETABLE TABLE
MVIEW$_ADV_SQLDEPEND TABLE
MVIEW$_ADV_PRETTY TABLE
MVIEW$_ADV_TEMP TABLE
MVIEW$_ADV_FILTER TABLE
MVIEW$_ADV_LOG TABLE
MVIEW$_ADV_FILTERINSTANCE TABLE
MVIEW$_ADV_LEVEL TABLE
MVIEW$_ADV_ROLLUP TABLE
MVIEW$_ADV_AJG TABLE

OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------
MVIEW$_ADV_FJG TABLE
MVIEW$_ADV_GC TABLE
MVIEW$_ADV_CLIQUE TABLE
MVIEW$_ADV_ELIGIBLE TABLE
MVIEW$_ADV_OUTPUT TABLE
MVIEW$_ADV_EXCEPTIONS TABLE
MVIEW$_ADV_PARAMETERS TABLE
MVIEW$_ADV_INFO TABLE
MVIEW$_ADV_JOURNAL TABLE
MVIEW$_ADV_PLAN TABLE
AQ$_QUEUE_TABLES TABLE

OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------
AQ$_QUEUES TABLE
AQ$_SCHEDULES TABLE
AQ$_INTERNET_AGENTS TABLE
AQ$_INTERNET_AGENT_PRIVS TABLE
OL$ TABLE
OL$HINTS TABLE
OL$NODES TABLE
DEF$_ERROR TABLE
DEF$_DESTINATION TABLE
DEF$_CALLDEST TABLE
DEF$_DEFAULTDEST TABLE

OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------
DEF$_LOB TABLE
DEF$_PROPAGATOR TABLE
DEF$_ORIGIN TABLE
DEF$_PUSHED_TRANSACTIONS TABLE
REPCAT$_REPCAT TABLE
REPCAT$_FLAVORS TABLE
REPCAT$_REPSCHEMA TABLE
REPCAT$_SNAPGROUP TABLE
REPCAT$_REPOBJECT TABLE
REPCAT$_REPCOLUMN TABLE
REPCAT$_KEY_COLUMNS TABLE

OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------
REPCAT$_GENERATED TABLE
REPCAT$_REPPROP TABLE
REPCAT$_REPCATLOG TABLE
REPCAT$_DDL TABLE
REPCAT$_REPGROUP_PRIVS TABLE
REPCAT$_PRIORITY_GROUP TABLE
REPCAT$_PRIORITY TABLE
REPCAT$_COLUMN_GROUP TABLE
REPCAT$_GROUPED_COLUMN TABLE
REPCAT$_CONFLICT TABLE
REPCAT$_RESOLUTION_METHOD TABLE

OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------
REPCAT$_RESOLUTION TABLE
REPCAT$_RESOLUTION_STATISTICS TABLE
REPCAT$_RESOL_STATS_CONTROL TABLE
REPCAT$_PARAMETER_COLUMN TABLE
REPCAT$_AUDIT_ATTRIBUTE TABLE
REPCAT$_AUDIT_COLUMN TABLE
REPCAT$_FLAVOR_OBJECTS TABLE
REPCAT$_TEMPLATE_STATUS TABLE
REPCAT$_TEMPLATE_TYPES TABLE
REPCAT$_REFRESH_TEMPLATES TABLE
REPCAT$_USER_AUTHORIZATIONS TABLE

OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------
REPCAT$_OBJECT_TYPES TABLE
REPCAT$_TEMPLATE_REFGROUPS TABLE
REPCAT$_TEMPLATE_OBJECTS TABLE
REPCAT$_TEMPLATE_PARMS TABLE
REPCAT$_OBJECT_PARMS TABLE
REPCAT$_USER_PARM_VALUES TABLE
REPCAT$_TEMPLATE_SITES TABLE
REPCAT$_SITE_OBJECTS TABLE
REPCAT$_RUNTIME_PARMS TABLE
REPCAT$_TEMPLATE_TARGETS TABLE
REPCAT$_EXCEPTIONS TABLE

OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------
REPCAT$_INSTANTIATION_DDL TABLE
REPCAT$_EXTENSION TABLE
REPCAT$_SITES_NEW TABLE
LOGMNR_DICTSTATE$ TABLE
LOGMNRC_GTLO TABLE
LOGMNRC_GTCS TABLE
LOGMNRC_SEQ_GG TABLE
LOGMNRC_CON_GG TABLE
LOGMNRC_CONCOL_GG TABLE
LOGMNRC_IND_GG TABLE
LOGMNRC_INDCOL_GG TABLE

OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------
LOGMNRC_GSII TABLE
LOGMNRC_GSBA TABLE
LOGMNR_SEED$ TABLE
LOGMNR_DICTIONARY$ TABLE
LOGMNR_OBJ$ TABLE
LOGMNR_TAB$ TABLE
LOGMNR_COL$ TABLE
LOGMNR_ATTRCOL$ TABLE
LOGMNR_TS$ TABLE
LOGMNR_IND$ TABLE
LOGMNR_USER$ TABLE

OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------
LOGMNR_TABPART$ TABLE
LOGMNR_TABSUBPART$ TABLE
LOGMNR_TABCOMPART$ TABLE
LOGMNR_TYPE$ TABLE
LOGMNR_COLTYPE$ TABLE
LOGMNR_ATTRIBUTE$ TABLE
LOGMNR_LOB$ TABLE
LOGMNR_CON$ TABLE
LOGMNR_CDEF$ TABLE
LOGMNR_CCOL$ TABLE
LOGMNR_ICOL$ TABLE

OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------
LOGMNR_LOBFRAG$ TABLE
LOGMNR_INDPART$ TABLE
LOGMNR_INDSUBPART$ TABLE
LOGMNR_INDCOMPART$ TABLE
LOGMNR_LOGMNR_BUILDLOG TABLE
LOGMNR_NTAB$ TABLE
LOGMNR_OPQTYPE$ TABLE
LOGMNR_SUBCOLTYPE$ TABLE
LOGMNR_KOPM$ TABLE
LOGMNR_PROPS$ TABLE
LOGMNR_ENC$ TABLE

OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------
LOGMNR_REFCON$ TABLE
LOGMNR_PARTOBJ$ TABLE
LOGMNRP_CTAS_PART_MAP TABLE
SCHEDULER_PROGRAM_ARGS_TBL TABLE
SCHEDULER_JOB_ARGS_TBL TABLE
LOGSTDBY$PARAMETERS TABLE
LOGSTDBY$EVENTS TABLE
LOGSTDBY$APPLY_PROGRESS TABLE
LOGSTDBY$APPLY_MILESTONE TABLE
LOGSTDBY$SCN TABLE
LOGSTDBY$FLASHBACK_SCN TABLE

OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------
LOGSTDBY$PLSQL TABLE
LOGSTDBY$SKIP_TRANSACTION TABLE
LOGSTDBY$SKIP TABLE
LOGSTDBY$SKIP_SUPPORT TABLE
LOGSTDBY$HISTORY TABLE
LOGSTDBY$EDS_TABLES TABLE
DEF$_AQCALL TABLE
DEF$_AQERROR TABLE
SQLPLUS_PRODUCT_PROFILE TABLE
HELP TABLE
MVIEW$_ADV_INDEX TABLE

OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------
MVIEW$_ADV_PARTITION TABLE
MVIEW$_ADV_OWB TABLE

167 rows selected.

SQL>

Again a lot of LogMiner, repcat and even outlines in OL$. What roles does SYSTEM have granted directly; not roles granted to roles etc but just the direct grants:

SQL> select granted_role from dba_role_privs where grantee='SYSTEM';

GRANTED_ROLE
------------------------------
AQ_ADMINISTRATOR_ROLE
DBA
MGMT_USER

SQL>

So, finally what grants are made on the SYSTEM objects:

SQL> col privilege for a30
SQL> col grantee for a30
SQL> col table_name for a30
SQL> set lines 220
SQL> select p.privilege,p.grantee,p.table_name,o.object_type from dba_tab_privs p, dba_objects o where p.owner='SYSTEM' and o.owner=p.owner and o.object_name=p.table_name;

PRIVILEGE GRANTEE TABLE_NAME OBJECT_TYPE
------------------------------ ------------------------------ ------------------------------ --------------------
DELETE PUBLIC OL$ TABLE
INSERT PUBLIC OL$ TABLE
SELECT PUBLIC OL$ TABLE
UPDATE PUBLIC OL$ TABLE
DELETE PUBLIC OL$HINTS TABLE
INSERT PUBLIC OL$HINTS TABLE
SELECT PUBLIC OL$HINTS TABLE
UPDATE PUBLIC OL$HINTS TABLE
DELETE PUBLIC OL$NODES TABLE
INSERT PUBLIC OL$NODES TABLE
SELECT PUBLIC OL$NODES TABLE

PRIVILEGE GRANTEE TABLE_NAME OBJECT_TYPE
------------------------------ ------------------------------ ------------------------------ --------------------
UPDATE PUBLIC OL$NODES TABLE
SELECT SYS REPCAT$_REPSCHEMA TABLE
SELECT SYS REPCAT$_REPPROP TABLE
SELECT SYS DEF$_CALLDEST TABLE
SELECT SYS DEF$_ERROR TABLE
SELECT SYS DEF$_DESTINATION TABLE
SELECT SELECT_CATALOG_ROLE SCHEDULER_PROGRAM_ARGS VIEW
SELECT SELECT_CATALOG_ROLE SCHEDULER_JOB_ARGS VIEW
SELECT SELECT_CATALOG_ROLE SCHEDULER_PROGRAM_ARGS_TBL TABLE
SELECT SELECT_CATALOG_ROLE SCHEDULER_JOB_ARGS_TBL TABLE
SELECT SYS DEF$_AQCALL TABLE

PRIVILEGE GRANTEE TABLE_NAME OBJECT_TYPE
------------------------------ ------------------------------ ------------------------------ --------------------
SELECT SYS DEF$_AQCALL QUEUE
SELECT PUBLIC PRODUCT_PRIVS VIEW
SELECT PUBLIC HELP TABLE
DELETE PUBLIC MVIEW$_ADV_OWB TABLE
INSERT PUBLIC MVIEW$_ADV_OWB TABLE
SELECT PUBLIC MVIEW$_ADV_OWB TABLE
UPDATE PUBLIC MVIEW$_ADV_OWB TABLE
DELETE PUBLIC MVIEW$_ADV_PARTITION TABLE
INSERT PUBLIC MVIEW$_ADV_PARTITION TABLE
SELECT PUBLIC MVIEW$_ADV_PARTITION TABLE
UPDATE PUBLIC MVIEW$_ADV_PARTITION TABLE

PRIVILEGE GRANTEE TABLE_NAME OBJECT_TYPE
------------------------------ ------------------------------ ------------------------------ --------------------
DELETE PUBLIC MVIEW$_ADV_INDEX TABLE
INSERT PUBLIC MVIEW$_ADV_INDEX TABLE
SELECT PUBLIC MVIEW$_ADV_INDEX TABLE
UPDATE PUBLIC MVIEW$_ADV_INDEX TABLE
DELETE OLAPSYS MVIEW$_ADV_OUTPUT TABLE
INSERT OLAPSYS MVIEW$_ADV_OUTPUT TABLE
SELECT OLAPSYS MVIEW$_ADV_OUTPUT TABLE
UPDATE OLAPSYS MVIEW$_ADV_OUTPUT TABLE
DELETE OLAPSYS MVIEW$_ADV_PARTITION TABLE
INSERT OLAPSYS MVIEW$_ADV_PARTITION TABLE
SELECT OLAPSYS MVIEW$_ADV_PARTITION TABLE

PRIVILEGE GRANTEE TABLE_NAME OBJECT_TYPE
------------------------------ ------------------------------ ------------------------------ --------------------
UPDATE OLAPSYS MVIEW$_ADV_PARTITION TABLE
DELETE OLAPSYS MVIEW$_ADV_INDEX TABLE
INSERT OLAPSYS MVIEW$_ADV_INDEX TABLE
SELECT OLAPSYS MVIEW$_ADV_INDEX TABLE
UPDATE OLAPSYS MVIEW$_ADV_INDEX TABLE

49 rows selected.

SQL>

So, SYSTEM looks like a DBA account as it has the DBA role and its well known for many years as the default DBA account in an Oracle but it also has objects such as OL$ and also LogMiner objects. This is a classic case of a conflict. Is it a DBA or is it a schema?. Well like the rest of us who create Oracle databases and create users and schemas Oracle seems to have muddied the waters a bit with SYSTEM.

Ideally no one should use SYSTEM; its a default and it is the opposite of Least Rights in the database so clearly does not serve a purpose in a well designed and secure database. So can we drop SYSTEM:

SQL> drop user system cascade;
drop user system cascade
*
ERROR at line 1:
ORA-28050: specified user or role cannot be dropped


SQL>

No, we are stuck with SYSTEM; this is bad news. Ideally we should be able to have an Oracle database with just SYS and no other users and no PUBLIC grants; but that is never going to happen. It should and then we have a chance of Least Rights in our databases.

OK, more soon!

Bye from WFH

Setting Users Impossible Passwords BY VALUES and Schema Only Accounts

I plan to try and write some Oracle security based blog posts whilst working from home. These promises when I have made them in the past usually end up not coming true due to other work and things getting more priority. But; I will try as it's good to spend a little time in research and writing and sharing in this important time. I am busy with work, so that's also good but I would like to try and find some time and share some Oracle Security knowledge to somehow help people (Oracle and Security people) stuck at home to at least learn something or at least be entertained a bit.

I do have a massive list of potential blog post subjects and topics that I always collect and write down. Some I have already started some research and tests and examples and some are just titles for now. As you will have seen I have blogged more in the last couple of years than I did for some time; I want to try and do more and more regularly and get the knowledge out there.

So here goes for todays subject. A guy from the USA sent me a question. He said in Oracle 11g he used syntax such as:

SQL> connect system/oracle1@//192.168.56.85:1521/bfora.localdomain
Connected.
SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

SQL> alter user pete identified by values 'LOCKED';

User altered.

SQL>

He said when he does this it creates a value in SYS.USER$.PASSWORD that can never be hashed to a real hex value so the account cannot be logged into if it is open. He asked if his thinking is correct or flawed.

Yes, this is the most famous undocumented feature of Oracle and I have been telling people to do this with accounts for 20 years or more to stop them from being logged into. In more recent times (since proxy was added to Oracle) this is a great tool to use make sure that a user does not log into a schema directly. So we do not give the schema passwords to developers / DBAs or release teams. Instead the schema remains inaccessible directly and instead the support or release is done via a proxy account. In this way no one logs onto schemas but code can still be run and installed to create objects such as tables, views, PL/SQL code. We can even enable audit targeted at the support person or release person using their proxy and audit every action whilst not auditing every action as the schema when not proxied.

But the syntax he showed and used in 11g is actually the syntax for 10g and earlier as the syntax in the BY VALUES clause didn't include the SHA1 password hash. We can see that it did work:

SQL> connect sys/oracle1@//192.168.56.85:1521/bfora.localdomain as sysdba
Connected.
SQL> select name,password,spare4 from sys.user$ where name='PETE';

NAME PASSWORD
------------------------------ ------------------------------
SPARE4
--------------------------------------------------------------------------------
PETE LOCKED


SQL>

But it is not strictly correct as we should also have the SHA1 hash in SYS.USER$.SPARE4 but it works still in 11g. The questioner said if the method is correct then he gets an error in 12c when he uses the same syntax. Lets try in 18c:

SQL> connect system/oracle1@//192.168.56.78:1523/xepdb1
Connected.
SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production

SQL>

Lets create a sample user to test with:

SQL> create user pete identified by pete;

User created.

SQL>

Now try and use the old syntax as the questioner said he used it in 12c and got an error:

SQL> alter user pete identified by values 'LOCKED';
alter user pete identified by values 'LOCKED'
*
ERROR at line 1:
ORA-02153: invalid VALUES password string


SQL>

Yes, we get the same issue that the questioner said he got in 12c. He asked is this still possible from 12c as he felt its more secure to stop connections as schemas. I agree, its better to never connect as the schema so this is a method BUT 18c and 19c have a new way. Lets look at the old way first. The BY VALUES clause failed because it doesn't include the later password hashes. So instead we can use DBMS_METADATA.GET_DDL to get the right syntax:

SQL> set serveroutput on
SQL> begin
2 dbms_output.put_line(dbms_metadata.get_ddl('USER','PETE'));
3 end;
4 /

CREATE USER "PETE" IDENTIFIED BY VALUES
'S:03E63AA057AE0C4C55D184E9BF44719C43ABBC5E8B8A5C8FE06ECAFE9EDA;T:BC005789B76E3B
7E1BCD1DFDEF98B04C8DA8B1DC02170444001A59092242301AA18D93BD99D9E60187BD38DDE9A274
21C0FB77C72166B53A27199F17203E7D8B69FCD1E1F8EE26A1CA57D8BD9ED78D97'

DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP"

PL/SQL procedure successfully completed.

SQL>

This gives me the right syntax. I can modify the statement above and change CREATE USER to ALTER USER and also change the hashes to 0000... instead. I can run this as follows:

SQL> ALTER USER "PETE" IDENTIFIED BY VALUES 'S:000000000000000000000000000000000000000000000000000000000000;T:0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000';

User altered.

SQL>

What is stored in sys.user$:

SQL> connect sys/oracle1@//192.168.56.78:1523/xepdb1 as sysdba
Connected.
SQL> select name,password,spare4 from sys.user$ where name='PETE';

NAME
--------------------------------------------------------------------------------
PASSWORD
--------------------------------------------------------------------------------
SPARE4
--------------------------------------------------------------------------------
PETE

S:000000000000000000000000000000000000000000000000000000000000;T:000000000000000
00000000000000000000000000000000000000000000000000000000000000000000000000000000
00000000000000000000000000000000000000000000000000000000000000000


SQL>

Can we instead change the string to LOCKED or similar as with the older syntax:

SQL> ALTER USER "PETE" IDENTIFIED BY VALUES 'S:LOCKED;T:LOCKED';
ALTER USER "PETE" IDENTIFIED BY VALUES 'S:LOCKED;T:LOCKED'
*
ERROR at line 1:
ORA-02153: invalid VALUES password string


SQL>

No, it seems not, maybe we can use LOCKED as a string but match the length of the original strings, 60 characters for the SHA1 S: string and 160 characters for the SHA2 T: string:

SQL> ALTER USER "PETE" IDENTIFIED BY VALUES 'S:LOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKED;T:LOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCK';
ALTER USER "PETE" IDENTIFIED BY VALUES 'S:LOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKED;T:LOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCKEDLOCK'
*
ERROR at line 1:
ORA-02153: invalid VALUES password string


SQL>

No, that doesn't work, what if we just change the zeros to a HEX letter such as A:

SQL> ALTER USER "PETE" IDENTIFIED BY VALUES 'S:AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA;T:AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA';

User altered.

SQL>

So the number uses is most likely tested to see if it is a valid HEX string. Lets change one letter to an invalid hex string and see what happens:

SQL> ALTER USER "PETE" IDENTIFIED BY VALUES 'S:GAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA;T:AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA';
ALTER USER "PETE" IDENTIFIED BY VALUES 'S:GAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA;T:AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'
*
ERROR at line 1:
ORA-02153: invalid VALUES password string


SQL>

I changed the first letter to G of the SHA1 hash and the error comes back. So lets try one more test. If we change the SHA1 hash to 59 characters BUT valid HEX what happens:

SQL> ALTER USER "PETE" IDENTIFIED BY VALUES 'S:AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA;T:AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA';
ALTER USER "PETE" IDENTIFIED BY VALUES 'S:AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA;T:AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'
*
ERROR at line 1:
ORA-02153: invalid VALUES password string


SQL>

So, in summary, the old BY VALUES still works in 12c, 18c, 19c BUT its much stricter than the methods in 11g where you could type in any old text as an invalid password. Now you must supply a string for the SHA1 and SHA2 hashes that are the correct length, 60 characters and 160 characters and also the hash value must be valid HEX. So if you use this method set the strings to valid HEX, BUT that presumably means that because its valid hex then its maybe possible to enter some password that would hash to that HEX value. The chances of someone guessing a password combination that would hash to the HEX value you chose is pretty pretty slim.

In 18c and 19c there is now a better method. The BY VALUES is in effect now built into the DDL syntax with the NO AUTHENTICATION syntax. This is called "SCHEMA ONLY ACCOUNTS" in Oracle and is a new welcome shortcut. We can test this for my sample user PETE in 18cXE:

SQL> alter user pete no authentication;

User altered.

SQL>

The values in SYS.USER$ are:

SQL> select name,password,spare4 from sys.user$ where name='PETE';

NAME
--------------------------------------------------------------------------------
PASSWORD
--------------------------------------------------------------------------------
SPARE4
--------------------------------------------------------------------------------
PETE

S:000000000000000000000000000000000000000057C9FE8ED313BD2F8D26;T:000000000000000
00000000000000000000000000000000000000000000000000000000000000000000000000000000
000000000000000000000000000000000104D5ED049FF791913E7242803485640


SQL>

The values are not all zeros. For instance the last 20 characters of the SHA1 hash are the SALT. A simple SQL script called sha1.sql on my site from 2007 shows how this password algorithm works and shows that the SALT is the last 20 characters of this hash. This is interesting and if you chose to use the BY VALUES instead of this feature in 18c or 19c or indeed 20c then it would make sense to generate a random SALT also and not just use zeros. If you are on 12.1 or 12.2 then the NO AUTHENTICATION syntax is not there anyway. Why not just use all ZEROs or all 99999s or AAAAs or whatever. Well if everyone just used all 000s then it may be possible that someone at some point could brute force inputs (possible passwords) and crack or find a password that could hash to 60 zeros or 60 AAAs or whatever. A random SALT would prevent that pre-defined hash/password problem. Also don't use DBMS_METADATA.GET_DDL and use the same SYS.USER$.SPARE4 values from one database for all passwords in another.

Any schema in your database should have one of these impossible passwords or from 18c a SCHEMA ONLY ACCOUNT - the same thing really.

More on this soon!! Bye from WFH (Working From Home)

CoronaVirus - We are Still Open

Everyone must now be affected in some way about coronavirus. We had an inkling that Boris Johnson and his government would enact a more severe lock down in the UK. So in anticipation I decided on Monday that we needed to work from home and not travel to our office anymore this week. So we spent time on Monday packing up all of our IT and other files and paperwork and tools. This is all now transported and re-installed and working.

So we are still open and working full time as normal helping people secure and protect data in their Oracle databases. The only thing that has changed is that we cannot receive manual post (letters) and our office phone is not being manned as we cannot now visit the office and its not worth to re-direct for a short while.

We are open for business and are working still for a number of clients world wide remotely currently helping them in their projects to secure their data held and processed in their Oracle databases. I also recently added a lot of new online training dates to our public training schedule and this is great to fill time when you cannot work normally and of course its coronavirus safe as you can join a live in person class safely from your own home or office and interact and ask questions live with me over web conferencing.

So please note our landline phone is not being manned as its in our office and we are all working remotely now until we can get back to the office. Please contact us by email or via a direct message on any of our social media channels. Please see buttons in the footer of any of our website pages for our social channels.

Thanks!!

XS$NULL - Can we login to it and does it really have no privileges?

I have read on line about XS$NULL over the years and particularly the documentation that states that it has no privileges. The documentation states the following:

An internal account that represents the absence of a user in a session. Because XS$NULL is not a user, this account can only be accessed by the Oracle Database instance. XS$NULL has no privileges and no one can authenticate as XS$NULL, nor can authentication credentials ever be assigned to XS$NULL.


I have always been fascinated by the idea that there could be a user of the database that did indeed have no privileges and if this were true did it really have no PUBLIC grants. If this is true then that would be great as we can use that as a start point in the database for our own designed users and not a general database account that inherits 44/45K public grants in 12.2-19c roughly. I read (or maybe I was told) some years ago that XS$NULL didn't have any PUBLIC grants. I did some searching now on line and I cannot find this in writing. I am sure I did read it or was told though. The other option of course is to remove all of the PUBLIC grants from the database but this is a thankless task and difficult to maintain and difficult to support but not impossible if you really wanted to do it.

So I wanted to somehow find out two things;

1 - Can we connect to XS$NULL in the database and
2 - Does it really have no grants?

First lets connect to an older 11.1.0.6 database and try and change the XS$NULL password and also change its account status to OPEN:

C:\_aa\PB\bin>sqlplus /nolog

SQL*Plus: Release 11.2.0.4.0 Production on Thu Feb 13 09:23:27 2020

Copyright (c) 1982, 2013, Oracle. All rights reserved.

SQL> connect system/oracle1@//192.168.1.38:1521/orcl
Connected.
SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Personal Oracle Database 11g Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE 11.1.0.6.0 Production
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production

SQL>
SQL> drop user pete cascade;

User dropped.

SQL> alter user xs$null identified by password;

User altered.

SQL> alter user xs$null account unlock;

User altered.

SQL> select username,account_status from dba_users where username='XS$NULL';

USERNAME ACCOUNT_STATUS
------------------------------ --------------------------------
XS$NULL OPEN

We can change the password of the XS$NULL account and open the account but can we log in?

SQL> connect xs$null/password@//192.168.1.38:1521/orcl
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.
SQL>

Nope, so no luck there. The documentation in various places does state that the XS$NULL user is protected in the Oracle C code to prevent a direct log in. OK, what about a proxy access to the same account:

SQL> connect system/oracle1@//192.168.1.38:1521/orcl
Connected.
SQL> create user pete identified by pete;

User created.

SQL> grant create session to pete;

Grant succeeded.

SQL> alter user xs$null grant connect through pete;

User altered.

SQL> connect pete[xs$null]/pete@//192.168.1.38:1521/orcl
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.

No, we cannot proxy to XS$NULL either. What about an 11.2.0.1 database instead. Can we change the password and alter the account status:

C:\_aa\PB\bin>sqlplus /nolog

SQL*Plus: Release 11.2.0.4.0 Production on Thu Feb 13 09:29:46 2020

Copyright (c) 1982, 2013, Oracle. All rights reserved.

SQL> connect system/oracle1@//192.168.1.35:1521/orcl
Connected.
SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Personal Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> select username,account_status from dba_users where username='XS$NULL';

USERNAME ACCOUNT_STATUS
------------------------------ --------------------------------
XS$NULL EXPIRED & LOCKED

SQL> alter user xs$null account unlock;
alter user xs$null account unlock
*
ERROR at line 1:
ORA-01031: insufficient privileges


SQL> alter user xs$null identified by password;
alter user xs$null identified by password
*
ERROR at line 1:
ORA-01031: insufficient privileges


No, stronger reaction from the database from 11.2.0.1, we cannot change the password or account status. So Oracle locked XS$NULL down even further. Connect as SYSDBA and look at the details of the XS$NULL account:

SQL> connect sys/oracle1@//192.168.1.35:1521/orcl as sysdba
Connected.
SQL> set serveroutput on
SQL> @print 'select * from sys.user$ where name=''''XS$NULL'''''
old 33: --lv_str:=translate('&&1','''','''''');
new 33: --lv_str:=translate('select * from sys.user$ where name=''XS$NULL''','''','''''');
old 34: print('&&1');
new 34: print('select * from sys.user$ where name=''XS$NULL''');
Executing Query [select * from sys.user$ where name='XS$NULL']
USER# : 2147483638
NAME : XS$NULL
TYPE# : 1
PASSWORD : DC4FCC8CB69A6733
DATATS# : 4
TEMPTS# : 3
CTIME : 30-MAR-10
PTIME : 30-MAR-10
EXPTIME : 30-MAR-10
LTIME : 30-MAR-10
RESOURCE$ : 0
AUDIT$ :
DEFROLE : 1
DEFGRP# :
DEFGRP_SEQ# :
ASTATUS : 9
LCOUNT : 0
DEFSCHCLASS : DEFAULT_CONSUMER_GROUP
EXT_USERNAME :
SPARE1 : 0
SPARE2 :
SPARE3 :
SPARE4 :
S:3F2D8517683D2043573991089A69E02918BB6C73645BA200A86B0299B37C
SPARE5 :
SPARE6 :
-------------------------------------------

PL/SQL procedure successfully completed.

SQL>

So, there even though the ALTER USER syntax does not work, we can still change the account status to OPEN:

SQL> update sys.user$ set astatus=0 where name='XS$NULL';

1 row updated.

SQL> commit;

Commit complete.

SQL>

So XS$NULL is OPEN but what is its password:

SQL> @cracker-v2.9.sql
PL/SQL cracker: Release 2.9.0.0.0 - Production on Thu Feb 13 09:34:19 2020
Copyright (c) 2008 - 2017 PeteFinnigan.com Limited. All rights reserved.

T [Username ] [Password (10g) ] [Password (11g) ] FL ST
================================================================================================================

U [SYS ] [ORACLE1 ] [oracle1 ] DI OP
U [SYSTEM ] [ORACLE1 ] [oracle1 ] DI OP
U [OUTLN ] [OUTLN ] [outln ] PU EL
U [DIP ] [DIP ] [dip ] PU EL
U [ORACLE_OCM ] [OCM_3XP1R3D ] [OCM_3XP1R3D ] DE EL
U [DBSNMP ] [ORACLE1 ] [oracle1 ] DI OP
U [APPQOSSYS ] [APPQOSSYS ] [APPQOSSYS ] PU EL
U [WMSYS ] [WMSYS ] [wmsys ] PU EL
U [XS$NULL ] [NO_PWD ] [NO_PWD ] DE OP
U [EXFSYS ] [EXFSYSSS ] [exfsysss ] DE EL
U [CTXSYS ] [ORACLE1 ] [oracle1 ] DI EL
U [XDB ] [CHANGE_ON_INSTALL ] [change_on_install ] DE EL
U [OLAPSYS ] [NO_PASSWORD ] [no_password ] DE EL
U [ORDSYS ] [ORDSYS ] [ordsys ] PU EL
U [ORDDATA ] [ORDDATA ] [orddata ] PU EL
U [ORDPLUGINS ] [ORDPLUGINS ] [ordplugins ] PU EL
U [SI_INFORMTN_SCHEMA ] [SI_INFORMTN_SCHEMA ] [si_informtn_schema ] PU EL
U [MDSYS ] [MDSYS ] [mdsys ] PU EL
U [MDDATA ] [MDDATA ] [MDDATA ] PU EL
R [SPATIAL_WFS_ADMIN ] [ ] [spatial_wfs_admin ] PU OP
U [SPATIAL_WFS_ADMIN_USR ] [SPATIAL_WFS_ADMIN_USR ] [spatial_wfs_admin_usr ] PU EL
R [WFS_USR_ROLE ] [WFS_USR_ROLE ] [wfs_usr_role ] PU OP
R [SPATIAL_CSW_ADMIN ] [SPATIAL_CSW_ADMIN ] [spatial_csw_admin ] PU OP
U [SPATIAL_CSW_ADMIN_USR ] [SPATIAL_CSW_ADMIN_USR ] [spatial_csw_admin_usr ] PU EL
R [CSW_USR_ROLE ] [CSW_USR_ROLE ] [csw_usr_role ] PU OP
U [SYSMAN ] [ORACLE1 ] [oracle1 ] DI OP
U [MGMT_VIEW ] [ ] [ ] -- OP
U [FLOWS_FILES ] [ ] [ ] -- EL
U [APEX_PUBLIC_USER ] [ ] [ ] -- EL
U [APEX_030200 ] [ ] [ ] -- EL
U [OWBSYS ] [OWBSYS ] [OWBSYS ] PU EL
R [OWB$CLIENT ] [S ] [s ] DE OP
U [OWBSYS_AUDIT ] [OWBSYS_AUDIT ] [owbsys_audit ] PU EL
U [SCOTT ] [TIGER ] [tiger ] DE EL
U [HR ] [CHANGE_ON_INSTALL ] [change_on_install ] DE EL
U [OE ] [CHANGE_ON_INSTALL ] [change_on_install ] DE EL
U [IX ] [CHANGE_ON_INSTALL ] [change_on_install ] DE EL
U [SH ] [CHANGE_ON_INSTALL ] [change_on_install ] DE EL
U [PM ] [CHANGE_ON_INSTALL ] [change_on_install ] DE EL
U [BI ] [CHANGE_ON_INSTALL ] [change_on_install ] DE EL
U [ORASCAN ] [ORASCAN ] [orascan ] PU OP
U [JEAN ] [JEAN ] [jean ] PU OP
U [PDEMO ] [PDEMO ] [pdemo ] PU OP
U [LINKPTARGET ] [LINKPTARGET ] [linkptarget ] PU OP
U [LINKTARGET ] [LINKTARGET ] [linktarget ] PU OP
U [PROXY ] [PROXY ] [proxy ] PU OP


INFO: Number of crack attempts = [25527]
INFO: Elapsed cracking time = [.86 Seconds]
INFO: Total elapsed time = [.86 Seconds]
INFO: Cracks per second = [29680]

PL/SQL procedure successfully completed.

SQL>

So the password of the XS$NULL user is NO_PWD; lets try a log in:

SQL> connect xs$null/NO_PWD@//192.168.1.35:1521/orcl
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.
SQL>

Still no luck; the C code in the Oracle database obviously prevents a log in. Put the status back to make sure the database is still clean:

SQL> connect sys/oracle1@//192.168.1.35:1521/orcl as sysdba
Connected.
SQL> update sys.user$ set astatus=0 where name='XS$NULL';

1 row updated.

SQL> commit;

Commit complete.

SQL> @print 'select * from sys.user$ where name=''''XS$NULL'''''

PL/SQL procedure successfully completed.

SQL> set serveroutput on
SQL> @print 'select * from sys.user$ where name=''''XS$NULL'''''
Executing Query [select * from sys.user$ where name='XS$NULL']
USER# : 2147483638
NAME : XS$NULL
TYPE# : 1
PASSWORD : DC4FCC8CB69A6733
DATATS# : 4
TEMPTS# : 3
CTIME : 30-MAR-10
PTIME : 30-MAR-10
EXPTIME : 30-MAR-10
LTIME : 30-MAR-10
RESOURCE$ : 0
AUDIT$ :
DEFROLE : 1
DEFGRP# :
DEFGRP_SEQ# :
ASTATUS : 0
LCOUNT : 0
DEFSCHCLASS : DEFAULT_CONSUMER_GROUP
EXT_USERNAME :
SPARE1 : 0
SPARE2 :
SPARE3 :
SPARE4 : S:3F2D8517683D2043573991089A69E02918BB6C73645BA200A86B0299B37C
SPARE5 :
SPARE6 :
-------------------------------------------

PL/SQL procedure successfully completed.


OK, what about proxy:

SQL> create user pete identified by pete;

User created.

SQL> grant create session to pete;

Grant succeeded.

SQL> alter user xs$null grant connect through pete;
alter user xs$null grant connect through pete
*
ERROR at line 1:
ORA-01031: insufficient privileges


SQL>

OK, another difference since 11.1.0.6 as the C code in the Oracle database also now prevents a proxy connection to XS$NULL; this wasn't the case in 11.1.0.6. Lets try 18c XE and start to look at Real Application Security (RAS):

C:\_aa\PB\bin>sqlplus system/oracle1@//192.168.1.97:1521/xepdb1

SQL*Plus: Release 11.2.0.4.0 Production on Thu Feb 13 10:43:16 2020

Copyright (c) 1982, 2013, Oracle. All rights reserved.


Connected to:
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production

SQL>
SQL> exec xs_principal.create_user(name=>'daustin',schema=>'hr');

PL/SQL procedure successfully completed.

SQL> exec sys.xs_principal.set_password('daustin','welcome1');

PL/SQL procedure successfully completed.

SQL> connect daustin/welcome1@//192.168.1.97:1521/xepdb1
^C

The connection to the sample user DAUSTIN which is from Oracles "Real Application Security HR Demo" doesn't work; it hangs when I try and connect to the database as the RAS DLAU user (RAS direct database access user). This is initially caused by me using an 11.2.0.4 Oracle instant client which doesn't know how to deal with RAS DLAU user connections. OK, so change my client to an 18c instant client and that gets kme past the hang on logon above:

C:\opn\instantclient_18_5>sqlplus /nolog

SQL*Plus: Release 18.0.0.0.0 - Production on Thu Feb 13 10:53:58 2020
Version 18.5.0.0.0

Copyright (c) 1982, 2018, Oracle. All rights reserved.

SQL> connect daustin/welcome1@//192.168.1.97:1521/xepdb1
ERROR:
ORA-01045: user DAUSTIN lacks CREATE SESSION privilege; logon denied


SQL>

OK, so another problem. A RAS user needs the ability to create a session; the same as a real database user needs the ability to create a session. The sample page above does not include this step and interestingly the Knox book published by Oracle Press - Oracle database 12c security - also does not include any fix for this. That book includes an example DLAU user as well and also does not solve the ability to create a session as a RAS DLAU user. So this issue is fixed by granting the RAS application role (RAS level role, not database level role) to the sample DLAU user:

SQL> sho user
USER is "SYS"
SQL> begin
2 sys.xs_principal.grant_roles('daustin','XSCONNECT');
3 end;
4 /

PL/SQL procedure successfully completed.

SQL> connect daustin/welcome1@//192.168.1.97:1521/xepdb1
Connected.
SQL>

So connected now as a RAS DLAU user; but why you may ask?

Well because RAS connects you to XS$NULL in the database. So we can see if this XS$NULL user really does have no rights such as PUBLIC grants. Lets see who we are in the database:

SQL> connect daustin/welcome1@//192.168.1.97:1521/xepdb1
Connected.
SP2-0310: unable to open file "LOGIN.SQL"
SQL> sho user
USER is "DAUSTIN"
SQL> select ora_invoking_xs_user from dual;

ORA_INVOKING_XS_USER
--------------------------------------------------------------------------------
DAUSTIN

SQL> select ora_invoking_user from dual;

ORA_INVOKING_USER
--------------------------------------------------------------------------------
XS$NULL

SQL> select * from session_privs;

PRIVILEGE
----------------------------------------
CREATE SESSION

SQL> select * from session_roles;

ROLE
--------------------------------------------------------------------------------
XS_CONNECT
XS_SESSION_ADMIN

So we are DAUSTIN in RAS but XS$NULL in the database. Can we select from ALL_USERS which has SELECT granted to PUBLIC:

SQL> select count(*) from all_users;

COUNT(*)
----------
37


Yes, so its likely we have PUBLIC. Lets just dig a bit deeper into what the database session shows for this RAS DLAU user:

SQL> select sys_context('userenv','authenticated_identity') from dual;

SYS_CONTEXT('USERENV','AUTHENTICATED_IDENTITY')
--------------------------------------------------------------------------------
DAUSTIN

SQL> select sys_context('userenv','current_schema') from dual;

SYS_CONTEXT('USERENV','CURRENT_SCHEMA')
--------------------------------------------------------------------------------
HR

SQL> select sys_context('userenv','current_user') from dual;

SYS_CONTEXT('USERENV','CURRENT_USER')
--------------------------------------------------------------------------------
XS$NULL

SQL> select sys_context('userenv','proxy_user') from dual;

SYS_CONTEXT('USERENV','PROXY_USER')
--------------------------------------------------------------------------------


SQL> select sys_context('userenv','session_user') from dual;

SYS_CONTEXT('USERENV','SESSION_USER')
--------------------------------------------------------------------------------
XS$NULL

SQL> select * from v$xs_session_ns_attributes;

no rows selected

SQL> select xs_sys_context('xs$session','created_by') from dual;

XS_SYS_CONTEXT('XS$SESSION','CREATED_BY')
--------------------------------------------------------------------------------
2147493728

SQL> select xs_sys_context('xs$session','current_xs_user') from dual;

XS_SYS_CONTEXT('XS$SESSION','CURRENT_XS_USER')
--------------------------------------------------------------------------------
DAUSTIN

SQL> select xs_sys_context('xs$session','session_xs_user') from dual;

XS_SYS_CONTEXT('XS$SESSION','SESSION_XS_USER')
--------------------------------------------------------------------------------
DAUSTIN

SQL> select xs_sys_context('xs$session','username') from dual;

XS_SYS_CONTEXT('XS$SESSION','USERNAME')
--------------------------------------------------------------------------------
DAUSTIN

SQL>

So we are XS$NULL in the database and we do seem to have PUBLIC. We can test DBMS_OUTPUT as daustin the RAS DLAU user:

SQL> set serveroutput on
SQL> exec dbms_output.put_line('hello');
hello

PL/SQL procedure successfully completed.

SQL>

If we check RAS session roles we will see:

SQL> select role_name from v$xs_session_roles;

ROLE_NAME
--------------------------------------------------------------------------------
XSAUTHENTICATED
DBMS_AUTH
DBMS_PASSWD
XSSESSIONADMIN
XSPUBLIC
XSCONNECT

6 rows selected.

SQL>

RAS has an XSPUBLIC role at the application level and the manual states that its similar to PUBLIC at the database level. Can we revoke XSPUBLIC from our sample user DAUSTIN? first also revoke the XSSESSIONADMIN RAS role

SQL> exec sys.xs_principal.revoke_roles('daustin','XSSESSIONADMIN');

PL/SQL procedure successfully completed.

SQL> exec sys.xs_principal.revoke_roles('daustin','XSPUBLIC');
BEGIN sys.xs_principal.revoke_roles('daustin','XSPUBLIC'); END;

*
ERROR at line 1:
ORA-46216: XS entities are not related.
ORA-06512: at "SYS.XS_PRINCIPAL", line 374
ORA-06512: at "SYS.XS_ADMIN_INT", line 107
ORA-06512: at "SYS.XS_PRINCIPAL_INT", line 185
ORA-06512: at "SYS.XS_PRINCIPAL_INT", line 727
ORA-06512: at "SYS.XS_PRINCIPAL", line 364
ORA-06512: at line 1


SQL>

That works for the XSSESSIONADMIN role but not for XSPUBLIC; The error doesn't give a good clue. But, we can disable a RAS role such as XSPUBLIC:

SQL> sho user
USER is "DAUSTIN"
SQL> exec dbms_xs_sessions.disable_role('XSPUBLIC');

PL/SQL procedure successfully completed.

SQL> select role_name from v$xs_session_roles;

ROLE_NAME
--------------------------------------------------------------------------------
XSAUTHENTICATED
DBMS_AUTH
DBMS_PASSWD
XSCONNECT

SQL>

That works BUT does that disable access to the database PUBLIC grants:

SQL> exec dbms_output.put_line('Hello World');
Hello World

PL/SQL procedure successfully completed.

SQL>

No, it doesn't stop the use of database PUBLIC objects by the underlying XS$NULL user. Pity. It would have been very useful. What about a database user that just as CREATE SESSION, can we revoke PUBLIC from it:

SQL> create user pete1 identified by pete1;

User created.

SQL> grant create session to pete1;

Grant succeeded.

SQL> revoke public from pete1;
revoke public from pete1
*
ERROR at line 1:
ORA-01951: ROLE 'PUBLIC' not granted to 'PETE1'


SQL>

What happens if we try and revoke PUBLIC from XS$NULL?

SQL> revoke public from xs$null;
revoke public from xs$null
*
ERROR at line 1:
ORA-28222: may not modify reserved user


That's interesting; this again proves that XS$NULL is an internal user that is different...

So in summary:
1 - we are able to connect to XS$NULL via the RAS DLAU users only and not direct. We can change the account status and password in 11.1 but not log in directly as XS$NULL. We cannot connect with a proxy to XS$NULL in 11.1. We cannot change the XS$NULL account using normal syntax in 11.2 and cannot log in or proxy.
2 - The Oracle documentation for RAS DLAU and the Oracle press book both miss out the session role at the RAS user level in their examples. Does this indicate that not many people use this functionality?
3 - We can disable XSPUBLIC from a RAS DLAU user
4 - The RAS DLAU user does seem to still have all PUBLIC grants inherited via XS$NULL in the actual database though
5 - The XS$NULL user does therefore have rights; not as stated in the documentation; it has all the 45k PUBLIC grants in 12-19c

It would have been really great if there was a true database user that is not in the PUBLIC group so that it does not have any rights at all. That should be the basis that we start any account from; I.e. no grants at all not even PUBLIC; then we can start to really make users that are truly least rights; maybe one day Oracle will give us that ability. Maybe...

Bug Bounty

There has been a rise on bug bounty programs and websites that help researchers find and disclose bugs to website and other owners with the hope of a payout from the owner of the vulnerable wesbsites. Some big well known websites have their own bounty programs and pay researchers for finding critical bugs in their sites that could potentially cause them financial issues if someone breached the websites and stole content.

These sites that offer bug bounties are in principal OK and I don't have an issue BUT I keep getting emails from various bug bounties and also individual researchers that tell me that they have found a security issue in my website. The problem for me is that over last quite a few years I have had the same bug reported to me maybe more than 20 times. I have kept quiet about this every time until now but I want to post now a blog to discuss this bug and hope that future researchers may read this and may not cause me to spend time responding and saying the same thing over and over.

Yesterday afternoon I received notification from a bug bounty website; actually 13 notifications; 13 exact same emails from them stating the same thing. Someone had found a security flaw in my website. Also the email (s) states something like:

DISCLAIMER: we have no direct or indirect relations with security researchers. We only verify the issue/bug and notify asap.

And before that it states something like:

Following ISO/IEC 29147 standard guidelines, we verified the vulnerability's existence before contacting you. Please contact the researcher direct to find details of the bug and if we sent this in error please forward it

The emails from them never state what the issue actually is and instead says that you have to contact the researcher. If you go to the websites details page for the issue also states:

we as a non-profit, never act as an intermediary between website owners and security researchers. we only verify and publish when fixed and during the notification period.

So basically, they collect bugs, do not accept any responsibility for what's reported and state that they will never get involved in any discussion on the bugs reported. This time the researcher responded promptly and courteously and agreed that it wasn't actually a bug and I asked him to remove the entry from the bug website but the entry page still exists and it says the bug was patched - not true. This is in fact happened multiple times; apparently I have patched this bug many times - not true. The last time (or maybe two times ago; I don't remember for sure) it took a lot of effort to get the researcher to agree that it was not a bug; in fact that time the researcher would not accept my word and would not remove the issue from the bug website. I got no help from the bug website either. This time (yesterday) the researcher was good and accepted it and was very polite. Whilst these issues keep happening and I keep getting reports of the same bug my site appears in these bounty websites list of vulnerable websites. I noticed higher than normal traffic yesterday and it may be a coincidence but having your website added to a list of vulnerable websites doesn't help; probably.

So what is this bug that people keep finding. Well put this search string in Google:

filetype:sql password

And my website comes third for the page "Oracle Default Passwords - Pete Finnigan". Open that page and at the top you will see:

-- This file part of a free tool to audit Oracle database
-- default passwords that may still be present. The tool is
-- presented here http://www.petefinnigan.com/default/default_password_checker.htm
-- This link shows how to install and run this tool in your Oracle database
--
-- This file is NOT a dump of user passwords from petefinnigan.com.

insert into osp_accounts
(product
, security_level
, username
, password
, hash_value
, commentary
) values (
'Oracle'
,3
,'BRIO_ADMIN'
,'BRIO_ADMIN'
,'EB50644BE27DF70B'
,'BRIO_ADMIN is an account of a 3rd party product.'
)
/
...

So, obviously this is not an SQL file for passwords or accounts for my website. This is part of a free tool to check for Oracle default passwords in peoples Oracle databases. It even states it in the comments in the file that this is not a part of my website and not passwords for my website.

So, please if you are a researcher please read the comments in that SQL file first before you report it; its not a vulnerability, it's part of a free tool to help people with Oracle Security

Also bug programs; when you accept a bug like this please verify it properly and read the contents of the url that's reported as a bug and then reject it. How many reports do these sites actually reject?

Finally bug programs; please also consider that sending 13 identical emails to someone where there is no existing business relationship and no consent to receive those emails is considered an issue in Europe now. Justifying that its reporting a bug is fine but not 13 times.

In fact, posting this blog post will show the same passwords again and more detailed Google dorks that maybe look for SQL inserts and the word password may direct future researchers to this page as well.

In summary; nothing wrong with reporting bugs but it should be done with responsibility; I.e. researcher check first; the program reported to should also do the proper checks that they claim many times to do and make some efforts to send one email to the contact email not to 13 random guesses.

PL/SQL Package with no DEFINER or INVOKER rights - Part 2

I posted about a discovery I made whilst testing for an issue in our PL/SQL code analyser checks in PFCLScan last week as I discovered that the AUTHID column in DBA_PROCEDURES or ALL_PROCEDURES or USER_PROCEDURES can be NULL; this caused an issue in one of our built in tests in the scanner but it was easy to fix. This lead me to investigate this issue and find out how and why some PL/SQL code has a NULL value for this column. That post is titled "PL/SQL That is not DEFINER or INVOKER rights - BUG?" and attracted a few comments.

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!!

PL/SQL That is not DEFINER or INVOKER rights - BUG?

Note: Part 2 - PL/SQL Package with no DEFINER or INVOKER rights - Part 2 is available that takes this investigation further

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