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.

[Previous entry: "Bug Bounty"] [Next entry: "CoronaVirus - We are Still Open"]

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