Pete Finnigan's Oracle Security Weblog

Over many years I have advocated using security in PL/SQL that checks that a caller is coming from the right place. For many years we have been able to do this with the call stack and test on our PL/SQL code whether the caller is coming from the right place or not.

Oracle added in 12c the ACCESSIBLE BY clause that added a similar idea to the PL/SQL language. So we can define a piece of PL/SQL and limit where that piece of PL/SQL can be called from with the ACCESSIBLE BY clause. This is a great feature BUT my method still has some advantages as we have more control by using the call stack in our own code so we can even check the line number we are calling from. This is good as it can help enforce that the caller has not been modified as the check is in the callee.

The main advantage of ACCESSIBLE BY though is that it is baked into the PL/SQL as part of the language whereas my method is code in user space.

There are advantages for each method.

The earlier versions of this syntax were limited to the object level but we can now add these clauses for individual procedures and functions in a package now.

There is no dictionary data for what has been set as far as I can tell. So if we need to analyse the PL/SQL in a database for use of the ACCESSIBLE BY clause then we need to parse the PL/SQL source code and extract the use of this syntax. I have created a simple script that does this and its use is here in 23c:

SQL> @sc_access
Owner                   Name                            Type                    Sub-Object                              Line            Accessible List
==================      =======================         ==================      ==================                      ========        =================================================
SYS                     CDBVIEW_INTERNAL                PACKAGE                 .                                       2               PACKAGE SYS.CDBVIEW
SYS                     DBMS_SQLTUNE_UTIL0              PACKAGE                 CHECK_DV_ACCESS                         431             PACKAGE SYS.DBMS_SQLTUNE
SYS                     DBMS_SQLTUNE_UTIL1              PACKAGE                 GET_SEQ_REMOTE                          697             PACKAGE SYS.DBMS_SQLTUNE_INTERNAL
SYS                     DBMS_RULE_INTERNAL              PACKAGE                 I_EVALUATE                              46              PACKAGE DBMS_CHAIN_INVOKER
SYS                     DBMS_PLUGTS                     PACKAGE                 GETDEBUGENABLE                          121             PACKAGE SYS.DBMS_PLUGTSP
SYS                     DBMS_PLUGTS                     PACKAGE                 KCP_SETPIRENA                           529             PACKAGE SYS.KUPW$WORKER
SYS                     DBMS_TTS                        PACKAGE                 PUT_PROTECTED_TSE_KEY                   289             PACKAGE SYS.KUPW$WORKER
SYS                     DBMS_TTS                        PACKAGE                 GET_AFN_DBID                            310             PACKAGE SYS.KUPW$WORKER
SYS                     DBMS_TTS                        PACKAGE                 GET_AFN_DBIDXENDIAN                     331             PACKAGE SYS.KUPW$WORKER
SYS                     DBMS_TTS                        PACKAGE                 CONVERTENCRYPTEDDATAFILECOPY            386             PACKAGE SYS.KUPW$WORKER
SYS                     UTL_XML                         PACKAGE                 PARSEEXPR                               129             PACKAGE SYS.DBMS_METADATA
SYS                     UTL_XML                         PACKAGE                 PARSEQUERY                              143             PACKAGE SYS.DBMS_METADATA
SYS                     UTL_XML                         PACKAGE                 XMLPARSE                                206             PACKAGE SYS.DBMS_METADATA_INT
SYS                     UTL_XML                         PACKAGE                 ISNAMEOMF                               293             PACKAGE SYS.DBMS_METADATA_UTIL
SYS                     UTL_XML                         PACKAGE                 SSCPARSE                                382             PACKAGE SYS.DBMS_METADATA_INT
GSMADMIN_INTERNAL       DBMS_GSM_DBADMIN                PACKAGE                 GETSHARDINGMETHOD                       72              PACKAGE SYS.DBMS_GSM_FIXED
SYS                     DBMS_METADATA_INT               PACKAGE                 ADD_TRANSFORM                           298             PACKAGE SYS.DBMS_METADATA
SYS                     DBMS_METADATA_UTIL              PACKAGE                 SET_MARKER                              818             PACKAGE SYS.DBMS_METADATA_INT
SYS                     DBMS_UPG_JSON0                  PACKAGE                 .                                       2               PACKAGE SYS.DBMS_UPG_JSON
SYS                     DBMS_UPG_JSON_INT               PACKAGE                 .                                       2               PACKAGE SYS.DBMS_UPG_JSON, SYS.DBMS_SODA
CTXSYS                  DRIACCHELP                      PACKAGE                 .                                       2               PACKAGE DRIACC
CTXSYS                  DRIOPT                          PACKAGE                 GET_P_INV_COUNT                         10              PACKAGE CTXSYS.DRVDDL
CTXSYS                  DRIOPT                          PACKAGE                 SET_P_INV_COUNT                         14              PACKAGE CTXSYS.DRVDDL
CTXSYS                  DRIOPT                          PACKAGE                 RESET_AGGMERGE_HANDLE                   215             PACKAGE CTXSYS.DRVDML
CTXSYS                  DRIXMD                          PACKAGE                 SETUPXMLSEARCHPREFS                     1103            PACKAGE CTXSYS.DRIXMD
CTXSYS                  DRIXMD                          PACKAGE                 INSERT_INDEX                            1445            PACKAGE CTXSYS.DRIIMP
CTXSYS                  DRIXMD                          PACKAGE                 INSERT_PARTITION                        1466            PACKAGE CTXSYS.DRIIMP
CTXSYS                  DRIXMD                          PACKAGE                 INSERT_OBJECT                           1473            PACKAGE CTXSYS.DRIIMP
CTXSYS                  DRIXMD                          PACKAGE                 INSERT_VALUE                            1479            PACKAGE CTXSYS.DRIIMP
CTXSYS                  DRIXMD                          PACKAGE                 INSERT_SUB_VALUE                        1488            PACKAGE CTXSYS.DRIIMP
CTXSYS                  DRIXMD                          PACKAGE                 INSERT_PENDING                          1494            PACKAGE CTXSYS.DRIIMP
CTXSYS                  DRIXMD                          PACKAGE                 INSERT_CDICOL                           1507            PACKAGE CTXSYS.DRIIMP
CTXSYS                  DRIXMD                          PACKAGE                 INSERT_IDX_DICTIONARY                   1513            PACKAGE CTXSYS.DRIIMP
CTXSYS                  DRIXMD                          PACKAGE                 INSERT_USER_EXTRACT_RULE                1526            PACKAGE CTXSYS.DRIIMP
CTXSYS                  DRIXMD                          PACKAGE                 INSERT_USER_EXTRACT_TYPE                1534            PACKAGE CTXSYS.DRIIMP
CTXSYS                  DRIXMD                          PACKAGE                 VALIDATE_ATTR                           1549            PACKAGE DRIVAL, PROCEDURE VALIDATE_ATTR
CTXSYS                  DRIXMD                          PACKAGE                 GETOBJECTATTID                          1566            PACKAGE CTXSYS.DRIXMD
CTXSYS                  DRIXMD                          PACKAGE                 SECONDARYINDEXEXISTS                    1574            PACKAGE CTXSYS.DRVXTAB
CTXSYS                  DRIXMD                          PACKAGE                 GETWALLETPATH                           1599            PACKAGE CTXSYS.DRVDML
CTXSYS                  DRITHSC                         PACKAGE                 SET_PV_LAST_ID                          368             PACKAGE CTX_THES
CTXSYS                  DRIUTL                          PACKAGE                 PARSE_OBJECT_NAME                       38              PACKAGE DRVUTL, PROCEDURE PARSE_OBJECT_NAME
CTXSYS                  DRVDDL                          PACKAGE                 GET_PV_IDX_MEM                          937             TYPE TEXTINDEXMETHODS, TYPE RULEINDEXMETHODS
CTXSYS                  DRVDDL                          PACKAGE                 SET_PV_IDX_MEM                          942             TYPE TEXTINDEXMETHODS, TYPE RULEINDEXMETHODS, TYPE XPATHINDEXMETHODS
CTXSYS                  DRVDDL                          PACKAGE                 GET_PV_IDX_SYNC_TYPE                    946             TYPE TEXTINDEXMETHODS, TYPE RULEINDEXMETHODS
CTXSYS                  DRVDDL                          PACKAGE                 SET_PV_IDX_SYNC_TYPE                    951             TYPE TEXTINDEXMETHODS, TYPE RULEINDEXMETHODS, TYPE XPATHINDEXMETHODS
CTXSYS                  DRVDDL                          PACKAGE                 GET_PV_IS_IMPORT                        955             TYPE TEXTINDEXMETHODS, TYPE RULEINDEXMETHODS, TYPE XPATHINDEXMETHODS
CTXSYS                  DRVDDL                          PACKAGE                 SET_PV_IS_IMPORT                        960             TYPE TEXTINDEXMETHODS, TYPE RULEINDEXMETHODS, TYPE XPATHINDEXMETHODS
CTXSYS                  DRVDDL                          PACKAGE                 GET_PV_MASTER_PARAMS                    964             TYPE TEXTINDEXMETHODS
CTXSYS                  DRVDDL                          PACKAGE                 SET_PV_MASTER_PARAMS                    969             TYPE TEXTINDEXMETHODS, TYPE RULEINDEXMETHODS, TYPE XPATHINDEXMETHODS
CTXSYS                  DRVDDL                          PACKAGE                 GET_PV_PARTITION_POPULATE               973             TYPE TEXTINDEXMETHODS, CTXSYS.DRIPARSE
CTXSYS                  DRVDDL                          PACKAGE                 SET_PV_PARTITION_POPULATE               978             TYPE TEXTINDEXMETHODS
CTXSYS                  DRVDDL                          PACKAGE                 GET_PV_TABLESPACE                       982             TYPE TEXTINDEXMETHODS
CTXSYS                  DRVDDL                          PACKAGE                 SET_PV_TABLESPACE                       987             TYPE TEXTINDEXMETHODS
CTXSYS                  DRVDDL                          PACKAGE                 GET_OPT_REBUILD_ERROR_TEXT_COUNT        991             PACKAGE CTXSYS.DRVXMD
CTXSYS                  DRVDDL                          PACKAGE                 SET_OPT_REBUILD_ERROR_TEXT              997             PACKAGE CTXSYS.DRVXMD
CTXSYS                  DRVDDL                          PACKAGE                 SET_OPT_REBUILD_ERROR_TYPE              1003            PACKAGE CTXSYS.DRVXMD
CTXSYS                  DRVDDL                          PACKAGE                 REMOVESEARCHON                          1011            TYPE TEXTINDEXMETHODS
CTXSYS                  DRVODM                          PACKAGE                 SVM_TRAIN                               59              PACKAGE CTXSYS.CTX_CLS
CTXSYS                  DRVXMD                          PACKAGE                 INDEXHASPATHHASHINDEX                   439             PACKAGE CTXSYS.DRVXTAB
SYS                     WWV_DBMS_SQL_APEX_220200        PACKAGE                 .                                       3               PACKAGE APEX_220200.WWV_FLOW_CODE_EXEC_MLE,PACKAGE APEX_220200.WWV_FLOW_DYNAMIC_EXEC,PACKAGE APEX_220200.WWV_FLOW_SESSION_RAS
APEX_220200             WWV_FLOW_CODE_EXEC_MLE          PACKAGE                 .                                       3               PACKAGE WWV_FLOW_CODE_EXEC
APEX_220200             WWV_FLOW_CODE_EXEC_PLSQL        PACKAGE                 .                                       3               PACKAGE WWV_FLOW_CODE_EXEC
APEX_220200             WWV_FLOW_APP_INSTALL_INT        PACKAGE                 .                                       3               PACKAGE WWV_FLOW_IMP_PARSER
APEX_220200             WWV_FLOW_REGION_LIST            PACKAGE                 .                                       3               PACKAGE WWV_FLOW_REGION_NATIVE
APEX_220200             WWV_FLOW_ZIP_INT                PACKAGE                 .                                       1                PACKAGE WWV_FLOW_ZIP
APEX_220200             WWV_FLOW_ADVISOR_CHECKS_I       PACKAGE                 .                                       3               PACKAGE WWV_FLOW_ADVISOR_CHECKS_API
APEX_220200             WWV_FLOW_SPATIAL_INT            PACKAGE                 .                                       3               PACKAGE WWV_FLOW_SPATIAL_API

PL/SQL procedure successfully completed.

SQL>

This shows the lines of code that have ACCESSIBLE BY and also the object name, owner and procedure or function if it's a package. It also shows the line number for the ACCESSIBLE BY clause and also the allowed caller list.

There are some limits to this script and its naive at this stage. I extract the package procedure and function names from the source and match to the line number of the ACCESSIBLE BY clause. There are probably some bugs in it and it doesn't handle the case of wrapped code should the package header be wrapped. It has also only been tested on the default 23c Free install. But its a useful start point to see what PL/SQL code already uses this clause. it doesn't handle perfectly the case where the ACCESSIBLE BY is on the same line as the PROCEDURE or FUNCTION and also doesn't handle multi line cases.

Its a simple script and here it is:

-- -----------------------------------------------------------------------------
--                 WWW.PETEFINNIGAN.COM LIMITED
-- -----------------------------------------------------------------------------
-- Script Name : sc_access.sql
-- Author      : Pete Finnigan
-- Date        : December 2023
-- -----------------------------------------------------------------------------
-- Description : This script checks for ACCESSIBKLE BHY clauses
-- -----------------------------------------------------------------------------
-- Maintainer  : Pete Finnigan (http://www.petefinnigan.com)
-- Copyright   : Copyright (C) 2023 PeteFinnigan.com Limited. All rights
--               reserved. All registered trademarks are the property of their
--               respective owners and are hereby acknowledged.
-- -----------------------------------------------------------------------------
-- License     : This software is licensed to you by PeteFinnigan.com Limited. All
--               rights and ownership and copyright in the software are retained by
--               PeteFinnigan.com Limited in all possible cases. Posession of this
--               software does not infer any additonal rights. If you receive this
--               software without these copyright notices and license text, this text,
--               license and copyright still applies. This text, copyright and license
--               must not be removed under any circumstances. This applies to all 
--               text displayed as comments up until and including the version history,
--               This text also applies to any copyright, banner or other text 
--               refering to PeteFinnigan.com Limited ownership that is output by
--               the program.
--
--               This software is free to use BUT it is NOT open source and NOT GPL 
--               or any similar license and it is NOT in the public domain.
--
--               You are permitted to use this software commercially or privately 
--               provided these notices or banners as described are not removed. You
--               may modify the software and use it internally but this does not
--               infer any additonal rights in the software. i.e. if you delete
--               some of our code or change variable names or add features that does 
--               not make it your code and does not give you the right to remove 
--               our ownership in this software shown in these notices. This software must
--               not be made available or published in anyway, any language, any 
--               modified form or original form except by PeteFinnigan.com Limited. 
--               You must not incorporate this software into any free or commercial 
--               product or software and you must not sell or give away any software that 
--               includes this sofware. 
--               
--
--               In short this text is not written by a lawyer so please respect the 
--               intent that you can use or modify it freely but not give it away 
--               yourself or take away our right to attribution.
--               If someone else needs a copy please ask them to come to PeteFinnigan.com
--               Limited and we will happily let them also have a free copy. Also
--               as you may expect freely use does not include adding this software to 
--               a commercial or free product (without our permission) but you
--               can use it internally in projects. We put our time into the free scripts
--               on our website or training courses and give these tools away for free and 
--               in return we expect our copyright and ownership to always remain. 
--               We like to help people but we also want to benefit from the fact
--               our name becomes known through these scripts and tools and software 
--               that we make. We hope this makes sense.
--
--       
-- -----------------------------------------------------------------------------
-- Version History
-- ===============
--
-- Who         version     Date      Description
-- ===         =======     ======    ======================
-- P.Finnigan  1.0         Dec 2023  First Issue.
-- -----------------------------------------------------------------------------

set serveroutput on
set lines 500

declare
    --
    lv_t varchar2(4000);
    --
    cursor c_main is
    select  o.owner,
            o.name,
            o.type,
            o.line,
            substr(o.text,instr(o.text,'(',1)+1,((instr(o.text,')',-1))-instr(o.text,'(',1))-1) text
    from (
        select  i.owner,
                i.name,
                i.type,
                i.line,
                upper(i.text) text
        from    dba_source i
        where   upper(i.text) like '%ACCESSIBLE%BY%(%)%'
    and     i.text not like '%--%') o;
    --
    function getsub(pv_owner in varchar2,
                pv_name in varchar2,
                pv_type in varchar2,
                pv_line in number) return varchar2
    is
        cursor c_code (cv_owner in varchar2,
                cv_name in varchar2,
                cv_type in varchar2) is
        select  c.text,
                c.line
        from    dba_source c
        where   c.owner=cv_owner
        and     c.name=cv_name
        and     c.type=cv_type
        and     (upper(c.text) like '%PROCEDURE%' or upper(c.text) like '%FUNCTION%')
        and     upper(c.text) not like '%--%';
        --
        lv_text varchar2(4000);
        --
    begin
        --
        for lv_code in c_code(pv_owner,pv_name,pv_type) loop
            if(lv_code.line>pv_line) then
                exit;
            end if;
            lv_text:=lv_code.text;
        end loop;
        --
        return(lv_text);
        --
    end;
    --
    function extract(pv_text in varchar2) return varchar2
    is
        lv_ret varchar2(4000);
    begin
        if(instr(upper(pv_text),'PROCEDURE',1)>0) then
            lv_ret:=replace(replace(ltrim(substr(upper(pv_text),instr(upper(pv_text),'PROCEDURE',1)+9)),chr(10)),chr(13));
            if(instr(upper(lv_ret),'(',1)>0) then
                lv_ret:=substr(upper(lv_ret),1,instr(upper(lv_ret),'(',1)-1);
            end if;
            if(instr(upper(lv_ret),' ',1)>0) then
                lv_ret:=substr(upper(lv_ret),1,instr(upper(lv_ret),' ',1)-1);
            end if;
            if(instr(upper(lv_ret),')',1)>0) then
                lv_ret:=substr(upper(lv_ret),1,instr(upper(lv_ret),')',1)-1);
            end if;
            --
        elsif(instr(upper(pv_text),'FUNCTION',1)>0) then
            lv_ret:=replace(replace(ltrim(substr(upper(pv_text),instr(upper(pv_text),'FUNCTION',1)+8)),chr(10)),chr(13));
            if(instr(upper(lv_ret),'(',1)>0) then
                lv_ret:=substr(upper(lv_ret),1,instr(upper(lv_ret),'(',1)-1);
            end if;
            if(instr(upper(lv_ret),' ',1)>0) then
                lv_ret:=substr(upper(lv_ret),1,instr(upper(lv_ret),' ',1)-1);
            end if;
            if(instr(upper(lv_ret),'RETURN',1)>0) then
                lv_ret:=substr(upper(lv_ret),1,instr(upper(lv_ret),'RETURN',1));
            end if;
            if(instr(upper(lv_ret),')',1)>0) then
                lv_ret:=substr(upper(lv_ret),1,instr(upper(lv_ret),')',1)-1);
            end if;
        else
            lv_ret:='';
        end if;
        return(lv_ret);
    end;
    --
begin
    --
    dbms_output.put_line(rpad('Owner',20,' ')||chr(9)||
                     rpad('Name',25,' ')||chr(9)||
                     rpad('Type',20,' ')||chr(9)||
                     rpad('Sub-Object',35,' ')||chr(9)||
                     rpad('Line',10,' ')||chr(9)||
                     'Accessible List');
    dbms_output.put_line(rpad('==================',20,' ')||chr(9)||
                         rpad('=======================',25,' ')||chr(9)||
                         rpad('==================',20,' ')||chr(9)||
                         rpad('==================',35,' ')||chr(9)||
                         rpad('========',10,' ')||chr(9)||
                     '=================================================');
    for lv_main in c_main loop
        --
        lv_t:=extract(getsub(lv_main.owner,lv_main.name,lv_main.type,lv_main.line));
        if(lv_t is null) then
            lv_t:='.';
        end if;
        
        dbms_output.put_line(rpad(lv_main.owner,20,' ')||chr(9)||
                            rpad(lv_main.name,25,' ')||chr(9)||
                            rpad(lv_main.type,20,' ')||chr(9)||
                            rpad(lv_t,35,' ')||chr(9)||
                            rpad(lv_main.line,10,' ')||chr(9)||
                            lv_main.text);
        --
    end loop;
    --
end;
/

We implement the same checks in our PL/SQL security code scanner differently as it has a proper PL/SQL parser for evaluating PL/SQL code for issues. I may spend some more time on this above script to iron out the edge cases and I will post here again when thats done.

#oracleace #sym_42 #oracle #database #security #plsql #securecode

If you look at the permissions in the database that are possible for a PL/SQL procedure then it looks, at first site to be a little odd. Lets see the possible permissions for PL/SQL (Procedure)

SQL> select * from system_privilege_map where name like '%PROCEDURE%';

 PRIVILEGE NAME                                       PROPERTY
---------- ---------------------------------------- ----------
      -140 CREATE PROCEDURE                                  0
      -141 CREATE ANY PROCEDURE                              0
      -142 ALTER ANY PROCEDURE                               0
      -143 DROP ANY PROCEDURE                                0
      -144 EXECUTE ANY PROCEDURE                             0
      -241 DEBUG ANY PROCEDURE                               0

6 rows selected.

SQL>

If we just show those that are not %ANY% then:

SQL> select * from system_privilege_map where name like '%PROCEDURE%' and name not like '%ANY%';

 PRIVILEGE NAME                                       PROPERTY
---------- ---------------------------------------- ----------
      -140 CREATE PROCEDURE                                  0

SQL>

There is only one permission for PL/SQL and that is CREATE PROCEDURE. Why is there not ALTER PROCEDURE or DROP PROCEDURE or EXECUTE PROCEDURE or DEBUG PROCEDURE?

There are %ANY% versions of these so that a user granted the ANY can operate DDL on another users PL/SQL except SYS because of O7_dictionary_accessibility or the new 23c dictionary protection if its 23c.

So, if we create a user and grant CREATE PROCEDURE that user can of course create a procedure. So lets do that, note we must also grant CREATE SESSION:

C:\>sqlplus sys/oracle@//192.168.56.18:1521/freepdb1 as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Dec 28 09:13:35 2023

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


Connected to:
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release

SQL> create user plsql identified by plsql;

User created.

SQL> grant create procedure to plsql;

Grant succeeded.

SQL> grant create session to plsql;

Grant succeeded.

SQL>

Now create that procedure:

SQL> connect plsql/plsql@//192.168.56.18:1521/freepdb1
Connected.
SQL> create procedure test is begin null; end;
  2  /

Procedure created.

SQL>

OK, we have a PL/SQL procedure and only CREATE PROCEDURE but we can do other actions such as ALTER or DROP. Lets show ALTER PROCEDURE:

SQL> sho user
USER is "PLSQL"
SQL> alter procedure test compile;

Procedure altered.

SQL>

This is because of the OBJECT OWNER PRINCIPAL.

If an Oracle user has an object then it can do ACTIONS on that object without specific database permissions. This is the OBJECT OWNER PRINCIPAL.

We see something similar in database auditing. We we can for instance in standard auditing audit a permission such as ALTER USER but it will not catch a user change his password where we must audit the ACTION/STATEMENT USER instead and this catches a user changing their own password with ALTER USER but it doesn't audit ALTER USER when its changing someone else's password with ALTER USER. This is the difference between a privilege and a statement. i.e. we can issue CREATE OR REPLACE PROCEDURE... when we have CREATE ANY PROCEDURE (a privilege) and create a PL/SQL procedure in another schema then we use a privilege BUT if we have a PL/SQL procedure as the owner we can still do CREATE OF REPLACE PROCEDURE... and in this case its a statement.

Confusing!

One final example; if we revoke CREATE PROCEDURE from the user PLSQL can we still ALTER or DROP it?

SQL> connect sys/oracle@//192.168.56.18:1521/freepdb1 as sysdba
Connected.
SQL> revoke create procedure from plsql;

Revoke succeeded.

SQL>
SQL> connect plsql/plsql@//192.168.56.18:1521/freepdb1
Connected.
SQL> alter procedure test compile;

Procedure altered.

SQL> drop procedure test;

Procedure dropped.

SQL>

Interesting; We only need CREATE PROCEDURE to create a procedure and afterwards even if we no longer have CREATE PROCEDURE we can still issue, compile, alter, drop, debug against our own objects.

This is the OBJECT OWNER PRINCIPAL and also the difference between a privilege and a statement.

So after installation of an application we no longer need the CREATE privileges BUT we could not stop an attacker from dropping, altering,. re-creating, compiling and debugging the schema objects as the schema. We could use Database Vault or simple DDL triggers to prevent this

#oracleace #sym_42 #oracle #database #security #plsql #permissions #grants

As part of any security audit we want to test the security or strength of passwords as well as any password management settings. We test database passwords of course with PL/SQL crackers and also C based crackers. We test RAS passwords where they exist and also HTTPD passwords,

A high percentage of databases include an APEX installation so we also want to test APEX passwords. The passwords are stored in the WWV_FLOW_FND_USER table in the WEB_PASSWORD2 column.

It is interesting to note that if someone updated a password directly with SQL then it would be in the WEB_PASSWORD column and be clear text. As part of an audit we want to make sure that there are no clear text passwords.

First log into the database:

C:\>sqlplus scanner/scanner@//192.168.56.18:1521/freepdb1

SQL*Plus: Release 11.2.0.4.0 Production on Fri Dec 22 11:30:42 2023

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


Connected to:
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release

SQL>

Print out the WWV_FLOW_FND_USER table:

SQL> set lines 220
SQL> set serveroutput on
SQL> @sc_print 'select * from apex_220200.wwv_flow_fnd_user'
old  32:        lv_str:=translate('&&1','''','''''');
new  32:        lv_str:=translate('select * from apex_220200.wwv_flow_fnd_user','''','''''');
Executing Query [select * from apex_220200.wwv_flow_fnd_user]
USER_ID                       : 3800883939144175
SECURITY_GROUP_ID             : 10
USER_NAME                     : ADMIN
FIRST_NAME                    :
LAST_NAME                     :
CREATION_DATE                 : 03-APR-23
CREATED_BY                    : SYS
LAST_UPDATE_DATE              : 15-DEC-23
LAST_UPDATED_BY               : ADMIN
START_DATE                    : 03-APR-23
END_DATE                      : 29-MAR-43
PERSON_TYPE                   :
EMAIL_ADDRESS                 :
WEB_PASSWORD2                 : 44A592AEEFFE5DCF40239F21D77838EDABC26EF81F6D558018FA4CD13B1AF45D94911E5BBBB4BD5734CE2B966FDFBA5802DD819EF12AD3B71411FF98772FF4F9
WEB_PASSWORD_VERSION          : 5;5;10000
LAST_LOGIN                    : 15-DEC-23
BUILDER_LOGIN_COUNT           : 4
LAST_AGENT                    : Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/120.0.0.0 Safari/537.36 Edg/120.0.0.0
LAST_IP                       : 192.168.56.1
ACCOUNT_LOCKED                : N
ACCOUNT_EXPIRY                : 03-APR-23
FAILED_ACCESS_ATTEMPTS        : 0
LAST_FAILED_LOGIN             :
FIRST_PASSWORD_USE_OCCURRED   : Y
CHANGE_PASSWORD_ON_FIRST_USE  : N
ALLOW_APP_BUILDING_YN         : N
ALLOW_SQL_WORKSHOP_YN         : N
ALLOW_TEAM_DEVELOPMENT_YN     : N
DEFAULT_SCHEMA                :
ALLOW_ACCESS_TO_SCHEMAS       :
DESCRIPTION                   :
WEB_PASSWORD                  :
WEB_PASSWORD_RAW              :
PASSWORD_DATE                 :
PASSWORD_ACCESSES_LEFT        :
PASSWORD_LIFESPAN_ACCESSES    :
PASSWORD_LIFESPAN_DAYS        :
DEFAULT_DATE_FORMAT           :
KNOWN_AS                      :
EMPLOYEE_ID                   :
PERSON_ID                     :
PROFILE_IMAGE                 :
PROFILE_IMAGE_NAME            :
PROFILE_MIMETYPE              :
PROFILE_FILENAME              :
PROFILE_LAST_UPDATE           :
PROFILE_CHARSET               :
ATTRIBUTE_01                  :
ATTRIBUTE_02                  :
ATTRIBUTE_03                  :
ATTRIBUTE_04                  :
ATTRIBUTE_05                  :
ATTRIBUTE_06                  :
ATTRIBUTE_07                  :
ATTRIBUTE_08                  :
ATTRIBUTE_09                  :
ATTRIBUTE_10                  :
-------------------------------------------
USER_ID                       : 7407615931466835
SECURITY_GROUP_ID             : 100001
USER_NAME                     : CUSTADMIN
FIRST_NAME                    : Pete
LAST_NAME                     : Finnigan
CREATION_DATE                 : 15-DEC-23
CREATED_BY                    : ADMIN
LAST_UPDATE_DATE              : 15-DEC-23
LAST_UPDATED_BY               : CUSTADMIN
START_DATE                    : 15-DEC-23
END_DATE                      : 10-DEC-43
PERSON_TYPE                   :
EMAIL_ADDRESS                 : pete@petefinnigan.com
WEB_PASSWORD2                 : 1894A011E2E1F02902A55551191C7058015C85A63F00D683D236367CDFF50F326EE6981B2B8E463068DABCEBC8D1CD67E3B115F1A3DC6626ECEEC7C0E3979E72
WEB_PASSWORD_VERSION          : 5;5;10000
LAST_LOGIN                    : 15-DEC-23
BUILDER_LOGIN_COUNT           : 3
LAST_AGENT                    : Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/120.0.0.0 Safari/537.36 Edg/120.0.0.0
LAST_IP                       : 192.168.56.1
ACCOUNT_LOCKED                :
ACCOUNT_EXPIRY                : 15-DEC-23
FAILED_ACCESS_ATTEMPTS        : 0
LAST_FAILED_LOGIN             :
FIRST_PASSWORD_USE_OCCURRED   : Y
CHANGE_PASSWORD_ON_FIRST_USE  : Y
ALLOW_APP_BUILDING_YN         :
ALLOW_SQL_WORKSHOP_YN         :
ALLOW_TEAM_DEVELOPMENT_YN     :
DEFAULT_SCHEMA                : CUSTOMER
ALLOW_ACCESS_TO_SCHEMAS       :
DESCRIPTION                   :
WEB_PASSWORD                  :
WEB_PASSWORD_RAW              :
PASSWORD_DATE                 :
PASSWORD_ACCESSES_LEFT        :
PASSWORD_LIFESPAN_ACCESSES    :
PASSWORD_LIFESPAN_DAYS        :
DEFAULT_DATE_FORMAT           :
KNOWN_AS                      : Pete
EMPLOYEE_ID                   :
PERSON_ID                     :
PROFILE_IMAGE                 :
PROFILE_IMAGE_NAME            :
PROFILE_MIMETYPE              :
PROFILE_FILENAME              :
PROFILE_LAST_UPDATE           :
PROFILE_CHARSET               :
ATTRIBUTE_01                  :
ATTRIBUTE_02                  :
ATTRIBUTE_03                  :
ATTRIBUTE_04                  :
ATTRIBUTE_05                  :
ATTRIBUTE_06                  :
ATTRIBUTE_07                  :
ATTRIBUTE_08                  :
ATTRIBUTE_09                  :
ATTRIBUTE_10                  :
-------------------------------------------

PL/SQL procedure successfully completed.

SQL>

As you can see we have two users and each has a password hash in the WEB_PASSWORD2 column. In the database there has been a number of algorithms used over the years from a custom DES used as a hash from Oracle 6, The SHA1 algorithm from 11.1 and the PDKDF2 and SHA2 from 12.1.0.2.

Apex in current versions also uses PBKDF2 for default authentication and uses a SALT of the security group, username, userID and also specifies the number of iterations of the algorithm. In Apex its possible to change the algorithm and hash function and also number of iterations via instance parameters and of course its possible to create custom authentication schemes. So to check passwords usefully in APEX we can only check the standard hashed ones. This is fine. We can use the WEB_PASSWORD_VERSION to check which passwords are standard or not.

The salt values are stored also in the table so are easy to use. To crack or check the passwords we can use a built in Apex API WWV_FLOW_CRYPTO.HASH_PASSWORD() to test this. So we can try and crack the ADMIN and CUSTADMIN users passwords but first lets get the values we need from the WWV_FLOW_FND_USER table:

SQL> select trim(to_char(user_id,'99999999999999999999')),security_group_id,user_name,web_password_version,web_password2 from apex_220200.wwv_flow_fnd_user;

TRIM(TO_CHAR(USER_ID, SECURITY_GROUP_ID USER_NAME                                                                                            WEB_PASSWORD_VERSION
--------------------- ----------------- ---------------------------------------------------------------------------------------------------- --------------------
WEB_PASSWORD2
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
3800883939144175                     10 ADMIN                                                                                                5;5;10000
44A592AEEFFE5DCF40239F21D77838EDABC26EF81F6D558018FA4CD13B1AF45D94911E5BBBB4BD5734CE2B966FDFBA5802DD819EF12AD3B71411FF98772FF4F9

7407615931466835                 100001 CUSTADMIN                                                                                            5;5;10000
1894A011E2E1F02902A55551191C7058015C85A63F00D683D236367CDFF50F326EE6981B2B8E463068DABCEBC8D1CD67E3B115F1A3DC6626ECEEC7C0E3979E72


SQL>

Now we can test the password and see if its correct or not for the ADMIN user:

C:\>sqlplus sys/oracle@//192.168.56.18:1521/freepdb1 as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Dec 22 12:31:37 2023

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


Connected to:
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release

SQL> set serveroutput on
SQL> get sc_apx_p.sql
  1  begin
  2      dbms_output.put_line(apex_220200.wwv_flow_crypto.hash_password('oracle','5;5;10000',10,'ADMIN',3800883939144175));
  3* end;
SQL> /
44A592AEEFFE5DCF40239F21D77838EDABC26EF81F6D558018FA4CD13B1AF45D94911E5BBBB4BD57
34CE2B966FDFBA5802DD819EF12AD3B71411FF98772FF4F9

PL/SQL procedure successfully completed.

SQL>

We need to run this as SYS or SYSTEM or a user with the APEX_ADIMISTRATOR_ROLE so I simply logged in as SYS as my user SCANNER doesn't have the APEX_ADMINISTRATOR_ROLE. The password was cracked BUT I knew the password was oracle in advance of course so I could simply test it. I could create a loop and try lots of passwords starting with "A" then "B" and so on or use common passwords from a dictionary.

There are a few comments:

• The algorithm is very slow as that was the idea behind PBKDF2 to make it hard to crack passwords by needing a lot of work to be done; thats why it does 10,000 iterations. We cannot realistically test a lot of potential passwords with PL/SQL.


• As an auditor I do not want the user used for the audit to be granted APEX_ADMINISTRATOR_ROLE. I prefer to have just CREATE SESSION, SELECT ANY DICTIONARY, SELECT ANY TABLE and access to SYS.USER$ and execute on DBMS_CRYPTO


• As an auditor I do not want to execute any API such as WWV_FLOW_CRYPTO.HASH_PASSWORD as I cannot be certain without a lot of checking and testing that it does not change anything and then I would need to convince the customer


• A C based solution would be better but in PL/SQL we need an anonymous block that does not call any APIs except DBMS_CRYPTO

I have created a simple PL/SQL framework as an anonymous block with its own PBKDF2 function. It gets the salt details and runs the algorithm and checks if the resultant hash matches. A sample run is here:

SQL> @sc_apx_pass.sql

CHK[3.1]: Apex Password Cracker
DBG: Schema=[APEX_220200]
DBG: lv_user_id       =[3800883939144175]
DBG: lv_security_group=[10]
DBG: lv_user_name     =[ADMIN]
DBG: lv_web_password2
=[44A592AEEFFE5DCF40239F21D77838EDABC26EF81F6D558018FA4CD13B1AF45D94911E5BBBB4BD
5734CE2B966FDFBA5802DD819EF12AD3B71411FF98772FF4F9]
ISSUE|CHK[3.1]: password for user [ADMIN] cracked - [oracle]
DBG: lv_user_id       =[7407615931466835]
DBG: lv_security_group=[100001]
DBG: lv_user_name     =[CUSTADMIN]
DBG: lv_web_password2
=[1894A011E2E1F02902A55551191C7058015C85A63F00D683D236367CDFF50F326EE6981B2B8E46
3068DABCEBC8D1CD67E3B115F1A3DC6626ECEEC7C0E3979E72]

PL/SQL procedure successfully completed.

SQL>

The admin password was cracked but the CUSTADMIN was not. We use a small dictionary as a simple initial check and its slow.

Make sure that you set the APEX password profile settings as this will prevent a simple cracker like this finding a password.

#oracleace #sym_42 #oracle #apex #security #password #cracking

My main focuses are 1) securing data in Oracle databases; either through performing security audits or helping people design and implement anything Oracle security related such as Database Vault or designing audit trails or VPD or encryption or... and 2) securing code in the database - PL/SQL - this is by doing PL/SQL code audits or securing PL/SQL or helping design security into PL/SQL or with PL/SQL.

Where these two goals meet is with APEX where I also get involved in helping secure environments and also applications. One part of this is APEX instance wide parameters and I am particular interested in the security parameters. There is a table and view that its based on that hold the parameters:

SQL> col owner for a30
SQL> col object_name for a30
SQL> col object_type for a30
SQL> set lines 220
SQL> l
  1* select owner,object_name,object_type from dba_objects where object_name in ('APEX_INSTANCE_PARAMETERS','WWV_FLOW_PLATFORM_PREFS')
SQL> /

OWNER                          OBJECT_NAME                    OBJECT_TYPE
------------------------------ ------------------------------ ------------------------------
PUBLIC                         APEX_INSTANCE_PARAMETERS       SYNONYM
APEX_220200                    WWV_FLOW_PLATFORM_PREFS        TABLE
APEX_220200                    APEX_INSTANCE_PARAMETERS       VIEW

SQL>

My user used to do the audit has these permissions:

find_all_privs: Release 1.0.7.0.0 - Production on Sun Dec 17 20:13:22 2023
Copyright (c) 2004 PeteFinnigan.com Limited. All rights reserved.

NAME OF USER TO CHECK                 [ORCL]: SCANNER
OUTPUT METHOD Screen/File                [S]:
FILE NAME FOR OUTPUT              [priv.lst]:
OUTPUT DIRECTORY [DIRECTORY  or file (/tmp)]:

User => SCANNER has been granted the following privileges
====================================================================
        SYS PRIV => CREATE SESSION grantable => NO
        SYS PRIV => SELECT ANY DICTIONARY grantable => NO
        TABLE PRIV => SELECT object => SYS.USER$ grantable => NO

PL/SQL procedure successfully completed.

For updates please visit http://www.petefinnigan.com/tools.htm

SQL>

I have limited rights and I cannot access WWV_FLOW_PLATFORM_PREFS without additional permissions such as SELECT ANY TABLE but I can access APEX_INSTANCE_PARAMETERS. Think at a high level of the APEX_% views like DBA_% and the WWV_FLOW_% as the XXX$ tables. We cannot look at WWV_FLOW_PLATFORM_PREFS:

SQL> col name for a30
SQL> col value for a30
SQL> select name,value  from apex_220200.wwv_flow_platform_prefs;
select name,value  from apex_220200.wwv_flow_platform_prefs
                                    *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL>

But we can see the parameters in APEX_INSTANCE_PARAMETERS:

SQL> col name for a60
SQL> col value for a100
SQL> set lines 220
SQL> set pages 0
SQL> select name,value  from apex_instance_parameters order by name;
ACCOUNT_LIFETIME_DAYS                                        9999
ALLOW_DB_MONITOR                                             N
ALLOW_LEGACY_THEME_COPY                                      N
ALLOW_PERSISTENT_AUTHENTICATION                              N
ALLOW_PUBLIC_FILE_UPLOAD                                     N
ALLOW_RAS                                                    N
APEX_BUILDER_AUTHENTICATION                                  APEX
APEX_LISTENER_FOP_PATH                                       _/fop2pdf
APPLICATION_ACTIVITY_LOGGING                                 U
APP_EXPORT_TO_DB_ENABLED                                     Y
AUDIT_LOG_RETENTION                                          90
AUTOEXTEND_TABLESPACES                                       Y
AUTO_SCHEMA_NAME                                             N
AUTO_WORKSPACE_NAME                                          N
BIGFILE_TABLESPACES_ENABLED                                  N
CHECK_FOR_UPDATES                                            Y
CLONE_SESSION_ENABLED                                        Y
CONTENT_CACHE_MAX_FILE_SIZE                                  1048576
CONTENT_CACHE_SIZE_TARGET                                    104857600
DEBUG_MESSAGE_PAGE_VIEW_LIMIT                                50000
DEFAULT_THEMES                                               42,51
DELETE_UPLOADED_FILES_AFTER_DAYS                             14
DG_ALLOW_FORMULAS                                            Y
DG_ALLOW_JSON_DATA_SOURCES                                   Y
DG_ALLOW_MULTI_VALUE                                         Y
DG_ALLOW_SQL_DATA_SOURCES                                    Y
DG_ALLOW_TABLE_DATA_SOURCES                                  Y
DG_ALLOW_TABLE_DATA_SOURCE_WHERE_CLAUSE                      Y
DG_MAXIMUM_NUMBER_OF_JSON_ROWS_TO_LOAD_PER_BUILTIN           500000
DG_MAXIMUM_NUMBER_OF_ROWS_TO_RETRIEVE_FROM_REST_DATA_SOURCE  500000
DG_MAXIMUM_ROWS_PER_TABLE                                    500000
DG_PRIORITIZE_SPEED_OVER_RANDOMNESS                          Y
DG_USE_POWERSET_IF_AVAILABLE                                 Y
DISABLE_ADMIN_LOGIN                                          N
DISABLE_WORKSPACE_LOGIN                                      N
DISABLE_WS_PROV                                              N
ENABLE_TRANSACTIONAL_SQL                                     N
ENCRYPTED_TABLESPACES_ENABLED                                N
EXPIRE_FND_USER_ACCOUNTS                                     Y
GALLERY_FILE_URLS                                            https://apex.oracle.com/manifests/v1/#APEX_BASE_VERSION#/manifest.json
GET_STARTED_URL                                              https://apex.oracle.com/go/get_started_apex
HPROF_DIRECTORY                                              -
IGNORED_FRIENDLY_URL_PARAMETERS                              utm_campaign,utm_source,utm_medium,utm_term,utm_content,fbclid
IMAGE_PREFIX                                                 /i/
INSTANCE_HASH                                                B14959B75AC2F04B0E067DCCC70E88D833E1A39C609AEC3F34E13C6D619A6D6A
INSTANCE_ID                                                  2400181437117375
KEEP_SESSIONS_ON_UPGRADE                                     N
KILL_SESSIONS_LAST_CALL_ET                                   3600
KILL_SESSIONS_WAIT_TIME_SEC                                  120
LOGIN_THROTTLE_DELAY                                         5
LOGIN_THROTTLE_METHODS                                       USERNAME_IP
MAX_APPLICATION_BACKUPS                                      25
MAX_DATA_EXPORT_IMAGES                                       1000
MAX_LOGIN_FAILURES                                           4
MAX_SCRIPT_SIZE                                              500000
MAX_SESSION_IDLE_SEC                                         3600
MAX_SESSION_LENGTH_SEC                                       28800
MAX_WEBSERVICE_REQUESTS                                      1000
MLE_LANGUAGES                                                JAVASCRIPT
PASSWORD_ALPHA_CHARACTERS                                    abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ
PASSWORD_HISTORY_DAYS                                        0
PASSWORD_NOT_LIKE_WORDS                                      oracle
PASSWORD_PUNCTUATION_CHARACTERS                              !"`'#$%&()[]{},.*+-/|\:;<=>?_~
PERSISTENT_AUTHENTICATION_MAX_DAYS                           30
PLSQL_EDITING                                                Y
PRINT_BIB_LICENSED                                           NONE
REJOIN_EXISTING_SESSIONS                                     P
REQUIRE_HTTPS                                                N
REQ_NEW_SCHEMA                                               N
RESTFUL_SERVICES_ENABLED                                     Y
SELF_SERVICE_SCHEMA_PREFIX                                   WKSP_
SERVICE_REQUESTS_ENABLED                                     Y
SERVICE_REQUEST_FLOW                                         MANUAL
SMTP_HOST_ADDRESS                                            localhost
SMTP_HOST_PORT                                               25
SMTP_TLS_MODE                                                N
SQL_SCRIPT_MAX_OUTPUT_SIZE                                   200000
STATS_LAST_SENT                                              20231215
STRONG_SITE_ADMIN_PASSWORD                                   N
SYSTEM_HELP_URL                                              https://apex.oracle.com/doc222
TASK_RETENTION_PERIOD_DAYS                                   7
TOP_THEMES                                                   42,51
TRACING_ENABLED                                              Y
UPGRADE_DEFERRED                                             N
UPGRADE_DEFER_DAYS                                           45
UPGRADE_DEFER_DAYS_MAX                                       90
USERNAME_VALIDATION                                          *
WEBSERVICE_LOGGING                                           U
WORKSPACE_EMAIL_MAXIMUM                                      1000
WORKSPACE_ISSUE_FILES_YN                                     Y
WORKSPACE_ISSUE_FS_LIMIT                                     15728640
WORKSPACE_MAX_OUTPUT_SIZE                                    2000000
WORKSPACE_PROVISION_DEMO_OBJECTS                             N
WORKSPACE_TEAM_DEV_FILES_YN                                  N
WORKSPACE_TEAM_DEV_FS_LIMIT                                  15728640
WS_REQUEST_USAGE_Q                                           N
ZIP_FILE_MAX_EXPANSION_FACTOR                                200

97 rows selected.

SQL>

What are the permissions granted to APEX_INSTANCE_PARAMETERS?

SQL> @sc_who_can_access
Enter value for owner_to_find: APEX_220200
Enter value for object_to_find: APEX_INSTANCE_PARAMETERS
Checking object => APEX_220200.APEX_INSTANCE_PARAMETERS
====================================================================


Object type is => VIEW (TAB)
        Privilege => READ is granted to =>
        Role => PUBLIC (ADM = NO)

PL/SQL procedure successfully completed.

SQL>

This is easy, READ is granted to PUBLIC. What about the access to WWV_FLOW_PLATFORM_PREFS:

SQL> @sc_who_can_access
Enter value for output_method: S
Enter value for owner_to_find: APEX_220200
Enter value for object_to_find: WWV_FLOW_PLATFORM_PREFS
Checking object => APEX_220200.WWV_FLOW_PLATFORM_PREFS
====================================================================



PL/SQL procedure successfully completed.

SQL>

There is no access to WWV_FLOW_PLATFORM_PREFS by default. The APEX_INSTANCE_PARAMETERS also has some limited security built in:

SQL> @sc_print 'select * from dba_views where view_name=''''APEX_INSTANCE_PARAMETERS'''''
Executing Query [select * from dba_views where view_name='APEX_INSTANCE_PARAMETERS']
OWNER                         : APEX_220200
VIEW_NAME                     : APEX_INSTANCE_PARAMETERS
TEXT_LENGTH                   : 292
TEXT                          : select name,
       case
       when name in ('WALLET_PWD','SMTP_PASSWORD') and value is not null then '***'
       else value
       end,
       created_on,
       last_updated_on
  from
wwv_flow_platform_prefs,
       wwv_flow_current_sgid sgid
 where sgid.has_apex_admin_read_role_yn = 'Y'
TEXT_VC                       : select name,
       case
       when name in ('WALLET_PWD','SMTP_PASSWORD') and value is not null then '***'
       else value
       end,
       created_on,
       last_updated_on
  from
wwv_flow_platform_prefs,
       wwv_flow_current_sgid sgid
 where sgid.has_apex_admin_read_role_yn = 'Y'
TYPE_TEXT_LENGTH              :
TYPE_TEXT                     :
OID_TEXT_LENGTH               :
OID_TEXT                      :
VIEW_TYPE_OWNER               :
VIEW_TYPE                     :
SUPERVIEW_NAME                :
EDITIONING_VIEW               : N
READ_ONLY                     : N
CONTAINER_DATA                : N
BEQUEATH                      : DEFINER
ORIGIN_CON_ID                 : 3
DEFAULT_COLLATION             : USING_NLS_COMP
CONTAINERS_DEFAULT            : NO
CONTAINER_MAP                 : NO
EXTENDED_DATA_LINK            : NO
EXTENDED_DATA_LINK_MAP        : NO
HAS_SENSITIVE_COLUMN          : NO
ADMIT_NULL                    : NO
PDB_LOCAL_ONLY                : NO
DUALITY_VIEW                  : NO
-------------------------------------------

PL/SQL procedure successfully completed.

SQL>

What is this role mentioned in the where clause?

SQL> select role from dba_roles where role like 'APEX%';
APEX_ADMINISTRATOR_READ_ROLE
APEX_ADMINISTRATOR_ROLE
APEX_GRANTS_FOR_NEW_USERS_ROLE

3 rows selected.

SQL>

And lets see the view/table that is used in the where clause of APEX_INSTANCE_PARAMETERS:

SQL> @sc_print 'select * from dba_views where view_name=''''WWV_FLOW_CURRENT_SGID'''''
Executing Query [select * from dba_views where view_name='WWV_FLOW_CURRENT_SGID']
OWNER                         : APEX_220200
VIEW_NAME                     : WWV_FLOW_CURRENT_SGID
TEXT_LENGTH                   : 412
TEXT                          : select nullif(wwv_flow.get_sgid,0)                       security_group_id,
       cu,
       nls_sort,
       (select wwv_flow_security.has_apex_admin_read_role_yn(cu) from sys.dual)
has_apex_admin_read_role_yn
  from ( select /*+ no_merge cardinality(t 1) */
                sys_context('userenv','current_user') cu,
                sys_context('userenv','nls_sort')     nls_sort
           from
sys.dual t ) d
TEXT_VC                       : select nullif(wwv_flow.get_sgid,0)                       security_group_id,
       cu,
       nls_sort,
       (select wwv_flow_security.has_apex_admin_read_role_yn(cu) from sys.dual)
has_apex_admin_read_role_yn
  from ( select /*+ no_merge cardinality(t 1) */
                sys_context('userenv','current_user') cu,
                sys_context('userenv','nls_sort')     nls_sort
           from
sys.dual t ) d
TYPE_TEXT_LENGTH              :
TYPE_TEXT                     :
OID_TEXT_LENGTH               :
OID_TEXT                      :
VIEW_TYPE_OWNER               :
VIEW_TYPE                     :
SUPERVIEW_NAME                :
EDITIONING_VIEW               : N
READ_ONLY                     : N
CONTAINER_DATA                : N
BEQUEATH                      : DEFINER
ORIGIN_CON_ID                 : 3
DEFAULT_COLLATION             : USING_NLS_COMP
CONTAINERS_DEFAULT            : NO
CONTAINER_MAP                 : NO
EXTENDED_DATA_LINK            : NO
EXTENDED_DATA_LINK_MAP        : NO
HAS_SENSITIVE_COLUMN          : NO
ADMIT_NULL                    : NO
PDB_LOCAL_ONLY                : NO
DUALITY_VIEW                  : NO
-------------------------------------------

PL/SQL procedure successfully completed.

SQL>

So, you can see that both the SGID view and the WWV_FLOW_PLATFORM_PREFS are BEQUETH DEFINER which is the default for a view, so the SGID view runs the code as APEX_220200 as that is the owner of this view. Who has this read role:

SQL> @sc_who_has_role
Enter value for output_method: S
Enter value for role_to_find: APEX_ADMINISTRATOR_READ_ROLE
Investigating Role => APEX_ADMINISTRATOR_READ_ROLE (PWD = NO) which is granted to =>
====================================================================
        User => SYS (ADM = YES)

PL/SQL procedure successfully completed.

SQL>

So, APEX_220200 does not have this role, only SYS has it. Also the check for this role is in a cartesian join with WWV_FLOW_PLATFORM_PREFS. What does this role do in the context of this APEX_INSTANCE_PARAMETERS view?

If we connect as SCANNER who does not have the role and select from the view we get 97 rows. If we connect as SYS who does have the role and select from the view we get 97 rows but remember the view runs as APEX_220200 who does not have the role. If we grant the role to APEX_220200 and select again as SCANNER we get the same 97 rows.

What does this obvious security check do?

If we run the API used in the SGID view as SYS for a current user of SYS we get:

SQL> set serveroutput on
SQL> begin
  2  dbms_output.put_line(apex_220200.wwv_flow_security.has_apex_admin_read_role_yn('SYS'));
  3  end;
  4  /
Y

PL/SQL procedure successfully completed.

And if we run it as current user SCANNER who does not have the role we get:

SQL> begin
  2  dbms_output.put_line(apex_220200.wwv_flow_security.has_apex_admin_read_role_yn('SCANNER'));
  3  end;
  4  /
Y

PL/SQL procedure successfully completed.

SQL>

Also 'Y' is returned. Hmmmm. What does the source of this package say:

SQL> select text from dba_source where name ='WWV_FLOW_SECURITY';
...
TEXT
--------------------------------------------------------------------------------
--
--==============================================================================


-- return Y if p_username should be able to read APEX dictionary views for all
-- workspaces. The user is entitled to query all workspaces if
--
--   - user is wwv_flow.g_flow_schema_owner, SYS, SYSTEM
--   - user has been granted APEX_ADMINISTRATOR_ROLE
--   - user has been granted APEX_ADMINISTRATOR_READ_ROLE
--   - user has been granted SELECT ANY DICTIONARY

TEXT
--------------------------------------------------------------------------------
--==============================================================================


function has_apex_admin_read_role_yn (
    p_username in varchar2 default null )
    return varchar2;
--
--==============================================================================

...

OK, so now we are getting somewhere with the security check. The comment in the package says you can read this view IF:

• The current user is the schema owner - in this example APEX_220200


• The current user is SYS or SYSTEM


• The current user has the APEX_ADMINISTRATOR_ROLE


• The current user has the APEX_ADMINISTRATOR_READ_ROLE


• The current user has SELECT ANY DICTIONARY

So, in my example above SYS has APEX_ADMINISTRATOR_READ_ROLE and SCANNER has SELECT ANY DICTIONARY. So lets create a new user TESTER with just CREATE SESSION:

SQL> sho user
USER is "SYS"
SQL> grant create session to tester identified by tester;

Grant succeeded.

SQL>

Connect to TESTER and try the view:

SQL> connect tester/tester@//192.168.56.18:1521/freepdb1
Connected.
SQL> select name,value from apex_instance_parameters;

no rows selected

SQL>

So, it works, of course!

This view is PUBLIC and can be accessed by the schema, SYS, SYSTEM, grantees of APEX_ADMINISTRATOR_ROLE and APEX_ADMINISTRATOR_READ_ROLE and if the user has SELECT ANY DICTIONARY.

A simple security mechanism to allow a PUBLIC view and then limited access except to users controlled by roles, privileges or a specific set of users. I have been teaching and advising similar methods for your own code for many many years as its simple to do in a limited way and can be done in Standard Editions where extra tools are not available.

Oracle could have used VPD or Database Vault but this is a simple mechanism based on the APEX dictionary views and code. Can it be "got around?" - of course, but any mechanism can be abused with enough effort and other rights. This is why DV or VPD would be better as they are closer to the Oracle command kernel.

If we connect to SYS and let TESTER have ALTER USER:

C:\> sqlplus sys/oracle@//192.168.56.18:1521/freepdb1 as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Dec 18 11:02:04 2023

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


Connected to:
Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release

SQL> grant alter user to tester;

Grant succeeded.

SQL>

Connect to TESTER and abuse:

SQL> connect tester/tester@//192.168.56.18:1521/freepdb1
Connected.
SQL> alter user scanner grant connect through tester;

User altered.

SQL> connect tester[scanner]/tester@//192.168.56.18:1521/freepdb1
Connected.
SQL> select count(*) from apex_instance_parameters;

  COUNT(*)
----------
        97

SQL>

This is not hacking as its not SQL Injection or abusing a flaw in code. Its taking advantage of a user with one privilege (ALTER USER) and using it to bypass security in another user (SCANNER and piggy backing on its SELECT ANY DICTIONARY).

The APEX dictionary view security is fine and simple but will never be 100% if someone has access to other rights. There are many other ways to get around this; I leave it to you to work out what other methods there could be

#oracleace #sym_42 #oracle #apex #security #hacking #database

Over the years I have been asked to look at many databases to tell the customer how they were breached or hacked. This is part of forensic analysis and breach response. Quite often there is no audit trails in the databases being looked at and whilst there are some things we can do without audit trails such as analyse SQL statements in the SGA or in the library cache there are surprisingly few pieces of data that we can use to analyse what might have happened.

The idea of missing ID's can be useful to look for missing data or when data might have been removed. We will use the idea of a user for this analysis but it could be other data as well not just users. There are a few scenarios for our example of a user being manipulated and how we may detect this when there is no auditing around this:

• Hacker adds a user and its still there : We can check dates and see if the user is created after start of the breach so could be the work of the attacker


• Hacker adds a user and removed it : There will be no create date for the user as it has been removed. There is no GAP in IDs as it was the last user created. We can check what the next user ID is and if there is still a gap based on this number we can assume a user was added and removed but we don't know when


• The hacker uses an existing user : Again the create date is not useful as the hacker didn't create the user, it existed already. BUT, it could be locked or expired or password changed during the attack phase.


• The hacker uses an existing user and removed it : The dates above are not there as the user was removed

We could use block analysis or redo logs or archive logs to confirm our assumptions. Because manipulation of users is recorded in the redo/archive logs then we can see this and get exact dates/times. If all else fails we could open the system data file in a hex editor and locate the records for user changes and removed users and see the dates at least for create, lock, expire or password changes but no transaction dates.

Lets test adding a user to the database and see the dates afterwards. First look at the existing user records:

SQL> col user_id for 9999999999999
SQL> col username for a30
SQL> col lock_date for a20
SQL> col expiry_date for a20
SQL> col password_change_date for a20
SQL> set lines 220
SQL> select user_id,username,to_char(created,'DD-MON-YYYY:HH24:MI:SS'),to_char(lock_date,'DD-MON-YYYY:HH24:MI:SS'),to_char(expiry_date,'DD-MON-YYYY:HH24:MI:SS'),to_char(password_change_date,'DD-MON-YYYY:HH24:MI:SS') from dba_users order by created;

       USER_ID USERNAME                       TO_CHAR(CREATED,'DD-MON-YYYY: TO_CHAR(LOCK_DATE,'DD-MON-YYY TO_CHAR(EXPIRY_DATE,'DD-MON-Y TO_CHAR(PASSWORD_CHANGE_DATE,
-------------- ------------------------------ ----------------------------- ----------------------------- ----------------------------- -----------------------------
             0 SYS                            26-MAR-2023:02:26:54
             8 AUDSYS                         26-MAR-2023:02:26:55          03-APR-2023:16:50:15
             9 SYSTEM                         26-MAR-2023:02:26:56
    2147483620 SYSRAC                         26-MAR-2023:02:26:56
    2147483617 SYSBACKUP                      26-MAR-2023:02:26:56          03-APR-2023:16:50:15
    2147483619 SYSKM                          26-MAR-2023:02:26:56          03-APR-2023:16:50:15
    2147483618 SYSDG                          26-MAR-2023:02:26:56          03-APR-2023:16:50:15
            13 OUTLN                          26-MAR-2023:02:27:05          03-APR-2023:16:50:15
            25 GSMUSER                        26-MAR-2023:03:11:57          03-APR-2023:16:50:15
            24 GSMADMIN_INTERNAL              26-MAR-2023:03:11:57          03-APR-2023:16:50:15
            26 DIP                            26-MAR-2023:03:12:06          26-MAR-2023:03:12:06

       USER_ID USERNAME                       TO_CHAR(CREATED,'DD-MON-YYYY: TO_CHAR(LOCK_DATE,'DD-MON-YYY TO_CHAR(EXPIRY_DATE,'DD-MON-Y TO_CHAR(PASSWORD_CHANGE_DATE,
-------------- ------------------------------ ----------------------------- ----------------------------- ----------------------------- -----------------------------
    2147483638 XS$NULL                        26-MAR-2023:03:12:31          26-MAR-2023:03:12:31
            40 REMOTE_SCHEDULER_AGENT         26-MAR-2023:03:12:38          03-APR-2023:16:50:15
            41 DBSFWUSER                      26-MAR-2023:03:12:41          03-APR-2023:16:50:15
            53 GGSHAREDCAP                    26-MAR-2023:03:15:43          03-APR-2023:16:50:15
            55 SYS$UMF                        26-MAR-2023:03:27:03          03-APR-2023:16:50:15
            62 DGPDB_INT                      26-MAR-2023:03:29:06          03-APR-2023:16:50:15
            75 DBSNMP                         26-MAR-2023:03:33:52          03-APR-2023:16:50:15
            76 APPQOSSYS                      26-MAR-2023:03:33:52          03-APR-2023:16:50:15
            82 GSMCATUSER                     26-MAR-2023:03:33:56          03-APR-2023:16:50:15
            83 GGSYS                          26-MAR-2023:03:34:04          03-APR-2023:16:50:15
            86 ANONYMOUS                      26-MAR-2023:03:35:41          03-APR-2023:16:50:15

       USER_ID USERNAME                       TO_CHAR(CREATED,'DD-MON-YYYY: TO_CHAR(LOCK_DATE,'DD-MON-YYY TO_CHAR(EXPIRY_DATE,'DD-MON-Y TO_CHAR(PASSWORD_CHANGE_DATE,
-------------- ------------------------------ ----------------------------- ----------------------------- ----------------------------- -----------------------------
            85 XDB                            26-MAR-2023:03:35:41          03-APR-2023:16:50:15
            95 WMSYS                          26-MAR-2023:03:48:27          03-APR-2023:16:50:15
           104 OJVMSYS                        26-MAR-2023:03:50:24          07-APR-2023:14:53:16
           106 CTXSYS                         26-MAR-2023:03:53:39          03-APR-2023:16:50:15
           109 OLAPSYS                        26-MAR-2023:03:55:37          03-APR-2023:16:50:15
           112 MDSYS                          26-MAR-2023:04:00:40          03-APR-2023:16:50:15
           113 MDDATA                         26-MAR-2023:04:00:40          03-APR-2023:16:50:15
           127 LBACSYS                        26-MAR-2023:04:05:33          03-APR-2023:16:50:15
       1279990 DVSYS                          26-MAR-2023:04:06:30          03-APR-2023:16:50:15
           129 DVF                            26-MAR-2023:04:06:30          03-APR-2023:16:50:15
           130 PDBADMIN                       03-APR-2023:16:50:15                                                                      03-APR-2023:16:50:15

       USER_ID USERNAME                       TO_CHAR(CREATED,'DD-MON-YYYY: TO_CHAR(LOCK_DATE,'DD-MON-YYY TO_CHAR(EXPIRY_DATE,'DD-MON-Y TO_CHAR(PASSWORD_CHANGE_DATE,
-------------- ------------------------------ ----------------------------- ----------------------------- ----------------------------- -----------------------------
           133 APEX_PUBLIC_USER               03-APR-2023:16:53:17                                                                      03-APR-2023:17:00:25
           131 APEX_220200                    03-APR-2023:16:53:17          03-APR-2023:16:53:17
           132 FLOWS_FILES                    03-APR-2023:16:53:17          03-APR-2023:16:53:17
           137 APEX_LISTENER                  03-APR-2023:17:00:24                                                                      03-APR-2023:17:00:24
           138 APEX_REST_PUBLIC_USER          03-APR-2023:17:00:24                                                                      03-APR-2023:17:00:24
           139 ORDS_PUBLIC_USER               03-APR-2023:17:00:39                                                                      03-APR-2023:17:00:39
           140 ORDS_METADATA                  03-APR-2023:17:00:40
           143 HRREST                         03-APR-2023:17:01:12                                                                      03-APR-2023:17:01:12
           144 HR                             03-APR-2023:17:01:14                                                                      03-APR-2023:17:01:14
           145 OE                             03-APR-2023:17:01:19                                                                      03-APR-2023:17:01:19
           146 PM                             03-APR-2023:17:01:56                                                                      03-APR-2023:17:01:56

       USER_ID USERNAME                       TO_CHAR(CREATED,'DD-MON-YYYY: TO_CHAR(LOCK_DATE,'DD-MON-YYY TO_CHAR(EXPIRY_DATE,'DD-MON-Y TO_CHAR(PASSWORD_CHANGE_DATE,
-------------- ------------------------------ ----------------------------- ----------------------------- ----------------------------- -----------------------------
           147 IX                             03-APR-2023:17:02:02                                                                      03-APR-2023:17:02:02
           148 SH                             03-APR-2023:17:02:09                                                                      03-APR-2023:17:02:09
           149 BI                             03-APR-2023:17:02:48                                                                      03-APR-2023:17:02:48
           150 AV                             03-APR-2023:17:02:53                                                                      03-APR-2023:17:02:53
           151 PFCLSCAN                       05-APR-2023:13:05:50                                                                      21-APR-2023:12:52:44
           152 VA                             06-APR-2023:15:20:00                                                                      26-MAY-2023:11:46:46
           153 VB                             06-APR-2023:15:21:44                                                                      06-APR-2023:15:21:44
           154 VC                             06-APR-2023:15:26:36                                                                      06-APR-2023:15:26:36
           157 VF                             10-APR-2023:11:11:31                                                                      10-APR-2023:11:11:31
           158 VG                             10-APR-2023:12:06:57                                                                      10-APR-2023:12:06:57
           159 VE                             14-APR-2023:13:21:53                                                                      14-APR-2023:13:21:53

       USER_ID USERNAME                       TO_CHAR(CREATED,'DD-MON-YYYY: TO_CHAR(LOCK_DATE,'DD-MON-YYY TO_CHAR(EXPIRY_DATE,'DD-MON-Y TO_CHAR(PASSWORD_CHANGE_DATE,
-------------- ------------------------------ ----------------------------- ----------------------------- ----------------------------- -----------------------------
           160 VJ                             02-MAY-2023:12:51:56                                                                      02-MAY-2023:12:51:56
           165 VV                             26-JUN-2023:08:52:17                                                                      26-JUN-2023:08:52:17
           166 VX                             26-JUN-2023:09:55:57                                                                      26-JUN-2023:09:55:57
           167 SCHEMA1                        26-JUN-2023:14:18:49                                                                      26-JUN-2023:14:18:49
           168 SCHEMA2                        26-JUN-2023:14:19:32                                                                      26-JUN-2023:14:19:32
           169 CON1                           26-JUN-2023:14:27:16                                                                      26-JUN-2023:14:27:16
           170 CON2                           26-JUN-2023:14:28:23                                                                      26-JUN-2023:14:28:23
           171 TESTTEST                       14-JUL-2023:08:56:43                                                                      14-JUL-2023:08:56:43
           172 TT1                            09-OCT-2023:09:15:27                                                                      09-OCT-2023:09:15:27
           173 UU1                            09-OCT-2023:09:16:49                                                                      09-OCT-2023:09:16:49
           174 TEST33                         03-NOV-2023:14:30:26                                                                      03-NOV-2023:14:30:26

       USER_ID USERNAME                       TO_CHAR(CREATED,'DD-MON-YYYY: TO_CHAR(LOCK_DATE,'DD-MON-YYY TO_CHAR(EXPIRY_DATE,'DD-MON-Y TO_CHAR(PASSWORD_CHANGE_DATE,
-------------- ------------------------------ ----------------------------- ----------------------------- ----------------------------- -----------------------------
           187 ORABLOG                        16-NOV-2023:15:09:30                                                                      16-NOV-2023:15:09:30
           188 VM                             16-NOV-2023:15:09:53                                                                      16-NOV-2023:15:09:53
           189 SQL_F                          16-NOV-2023:15:10:39                                                                      16-NOV-2023:15:10:39
           190 VQ                             16-NOV-2023:15:29:46                                                                      16-NOV-2023:15:29:46
           191 SCANNER                        29-NOV-2023:12:43:36                                                                      29-NOV-2023:12:43:36
           192 PROXY                          29-NOV-2023:14:21:46                                                                      29-NOV-2023:14:21:46

72 rows selected.

SQL>

As we can see already there are issues that make analysis hard. The first is that the list is ordered by created date and not ID we whilst user IDs for user created users and most others are 3 digits there are some users that have large USER_IDS such as 2147483620 for SYSRAC and others in a different sequence such as 1279990 for DVSYS.

Also by using CEATED as the order by some IDs are out of order such as 85 and 86 which have the same date. If we change the ORDER BY to include the USER_ID:

SQL> select user_id,username,to_char(created,'DD-MON-YYYY:HH24:MI:SS'),to_char(lock_date,'DD-MON-YYYY:HH24:MI:SS'),to_char(expiry_date,'DD-MON-YYYY:HH24:MI:SS'),to_char(password_change_date,'DD-MON-YYYY:HH24:MI:SS') from dba_users order by created,user_id;

       USER_ID USERNAME                       TO_CHAR(CREATED,'DD-MON-YYYY: TO_CHAR(LOCK_DATE,'DD-MON-YYY TO_CHAR(EXPIRY_DATE,'DD-MON-Y TO_CHAR(PASSWORD_CHANGE_DATE,
-------------- ------------------------------ ----------------------------- ----------------------------- ----------------------------- -----------------------------
             0 SYS                            26-MAR-2023:02:26:54
             8 AUDSYS                         26-MAR-2023:02:26:55          03-APR-2023:16:50:15
             9 SYSTEM                         26-MAR-2023:02:26:56
    2147483617 SYSBACKUP                      26-MAR-2023:02:26:56          03-APR-2023:16:50:15
    2147483618 SYSDG                          26-MAR-2023:02:26:56          03-APR-2023:16:50:15
    2147483619 SYSKM                          26-MAR-2023:02:26:56          03-APR-2023:16:50:15
    2147483620 SYSRAC                         26-MAR-2023:02:26:56
            13 OUTLN                          26-MAR-2023:02:27:05          03-APR-2023:16:50:15
            24 GSMADMIN_INTERNAL              26-MAR-2023:03:11:57          03-APR-2023:16:50:15
            25 GSMUSER                        26-MAR-2023:03:11:57          03-APR-2023:16:50:15
            26 DIP                            26-MAR-2023:03:12:06          26-MAR-2023:03:12:06

       USER_ID USERNAME                       TO_CHAR(CREATED,'DD-MON-YYYY: TO_CHAR(LOCK_DATE,'DD-MON-YYY TO_CHAR(EXPIRY_DATE,'DD-MON-Y TO_CHAR(PASSWORD_CHANGE_DATE,
-------------- ------------------------------ ----------------------------- ----------------------------- ----------------------------- -----------------------------
    2147483638 XS$NULL                        26-MAR-2023:03:12:31          26-MAR-2023:03:12:31
            40 REMOTE_SCHEDULER_AGENT         26-MAR-2023:03:12:38          03-APR-2023:16:50:15
            41 DBSFWUSER                      26-MAR-2023:03:12:41          03-APR-2023:16:50:15
            53 GGSHAREDCAP                    26-MAR-2023:03:15:43          03-APR-2023:16:50:15
            55 SYS$UMF                        26-MAR-2023:03:27:03          03-APR-2023:16:50:15
            62 DGPDB_INT                      26-MAR-2023:03:29:06          03-APR-2023:16:50:15
            75 DBSNMP                         26-MAR-2023:03:33:52          03-APR-2023:16:50:15
            76 APPQOSSYS                      26-MAR-2023:03:33:52          03-APR-2023:16:50:15
            82 GSMCATUSER                     26-MAR-2023:03:33:56          03-APR-2023:16:50:15
            83 GGSYS                          26-MAR-2023:03:34:04          03-APR-2023:16:50:15
            85 XDB                            26-MAR-2023:03:35:41          03-APR-2023:16:50:15

       USER_ID USERNAME                       TO_CHAR(CREATED,'DD-MON-YYYY: TO_CHAR(LOCK_DATE,'DD-MON-YYY TO_CHAR(EXPIRY_DATE,'DD-MON-Y TO_CHAR(PASSWORD_CHANGE_DATE,
-------------- ------------------------------ ----------------------------- ----------------------------- ----------------------------- -----------------------------
            86 ANONYMOUS                      26-MAR-2023:03:35:41          03-APR-2023:16:50:15
            95 WMSYS                          26-MAR-2023:03:48:27          03-APR-2023:16:50:15
           104 OJVMSYS                        26-MAR-2023:03:50:24          07-APR-2023:14:53:16
           106 CTXSYS                         26-MAR-2023:03:53:39          03-APR-2023:16:50:15
           109 OLAPSYS                        26-MAR-2023:03:55:37          03-APR-2023:16:50:15
           112 MDSYS                          26-MAR-2023:04:00:40          03-APR-2023:16:50:15
           113 MDDATA                         26-MAR-2023:04:00:40          03-APR-2023:16:50:15
           127 LBACSYS                        26-MAR-2023:04:05:33          03-APR-2023:16:50:15
           129 DVF                            26-MAR-2023:04:06:30          03-APR-2023:16:50:15
       1279990 DVSYS                          26-MAR-2023:04:06:30          03-APR-2023:16:50:15
           130 PDBADMIN                       03-APR-2023:16:50:15                                                                      03-APR-2023:16:50:15

       USER_ID USERNAME                       TO_CHAR(CREATED,'DD-MON-YYYY: TO_CHAR(LOCK_DATE,'DD-MON-YYY TO_CHAR(EXPIRY_DATE,'DD-MON-Y TO_CHAR(PASSWORD_CHANGE_DATE,
-------------- ------------------------------ ----------------------------- ----------------------------- ----------------------------- -----------------------------
           131 APEX_220200                    03-APR-2023:16:53:17          03-APR-2023:16:53:17
           132 FLOWS_FILES                    03-APR-2023:16:53:17          03-APR-2023:16:53:17
           133 APEX_PUBLIC_USER               03-APR-2023:16:53:17                                                                      03-APR-2023:17:00:25
           137 APEX_LISTENER                  03-APR-2023:17:00:24                                                                      03-APR-2023:17:00:24
           138 APEX_REST_PUBLIC_USER          03-APR-2023:17:00:24                                                                      03-APR-2023:17:00:24
           139 ORDS_PUBLIC_USER               03-APR-2023:17:00:39                                                                      03-APR-2023:17:00:39
           140 ORDS_METADATA                  03-APR-2023:17:00:40
           143 HRREST                         03-APR-2023:17:01:12                                                                      03-APR-2023:17:01:12
           144 HR                             03-APR-2023:17:01:14                                                                      03-APR-2023:17:01:14
           145 OE                             03-APR-2023:17:01:19                                                                      03-APR-2023:17:01:19
           146 PM                             03-APR-2023:17:01:56                                                                      03-APR-2023:17:01:56

       USER_ID USERNAME                       TO_CHAR(CREATED,'DD-MON-YYYY: TO_CHAR(LOCK_DATE,'DD-MON-YYY TO_CHAR(EXPIRY_DATE,'DD-MON-Y TO_CHAR(PASSWORD_CHANGE_DATE,
-------------- ------------------------------ ----------------------------- ----------------------------- ----------------------------- -----------------------------
           147 IX                             03-APR-2023:17:02:02                                                                      03-APR-2023:17:02:02
           148 SH                             03-APR-2023:17:02:09                                                                      03-APR-2023:17:02:09
           149 BI                             03-APR-2023:17:02:48                                                                      03-APR-2023:17:02:48
           150 AV                             03-APR-2023:17:02:53                                                                      03-APR-2023:17:02:53
           151 PFCLSCAN                       05-APR-2023:13:05:50                                                                      21-APR-2023:12:52:44
           152 VA                             06-APR-2023:15:20:00                                                                      26-MAY-2023:11:46:46
           153 VB                             06-APR-2023:15:21:44                                                                      06-APR-2023:15:21:44
           154 VC                             06-APR-2023:15:26:36                                                                      06-APR-2023:15:26:36
           157 VF                             10-APR-2023:11:11:31                                                                      10-APR-2023:11:11:31
           158 VG                             10-APR-2023:12:06:57                                                                      10-APR-2023:12:06:57
           159 VE                             14-APR-2023:13:21:53                                                                      14-APR-2023:13:21:53

       USER_ID USERNAME                       TO_CHAR(CREATED,'DD-MON-YYYY: TO_CHAR(LOCK_DATE,'DD-MON-YYY TO_CHAR(EXPIRY_DATE,'DD-MON-Y TO_CHAR(PASSWORD_CHANGE_DATE,
-------------- ------------------------------ ----------------------------- ----------------------------- ----------------------------- -----------------------------
           160 VJ                             02-MAY-2023:12:51:56                                                                      02-MAY-2023:12:51:56
           165 VV                             26-JUN-2023:08:52:17                                                                      26-JUN-2023:08:52:17
           166 VX                             26-JUN-2023:09:55:57                                                                      26-JUN-2023:09:55:57
           167 SCHEMA1                        26-JUN-2023:14:18:49                                                                      26-JUN-2023:14:18:49
           168 SCHEMA2                        26-JUN-2023:14:19:32                                                                      26-JUN-2023:14:19:32
           169 CON1                           26-JUN-2023:14:27:16                                                                      26-JUN-2023:14:27:16
           170 CON2                           26-JUN-2023:14:28:23                                                                      26-JUN-2023:14:28:23
           171 TESTTEST                       14-JUL-2023:08:56:43                                                                      14-JUL-2023:08:56:43
           172 TT1                            09-OCT-2023:09:15:27                                                                      09-OCT-2023:09:15:27
           173 UU1                            09-OCT-2023:09:16:49                                                                      09-OCT-2023:09:16:49
           174 TEST33                         03-NOV-2023:14:30:26                                                                      03-NOV-2023:14:30:26

       USER_ID USERNAME                       TO_CHAR(CREATED,'DD-MON-YYYY: TO_CHAR(LOCK_DATE,'DD-MON-YYY TO_CHAR(EXPIRY_DATE,'DD-MON-Y TO_CHAR(PASSWORD_CHANGE_DATE,
-------------- ------------------------------ ----------------------------- ----------------------------- ----------------------------- -----------------------------
           187 ORABLOG                        16-NOV-2023:15:09:30                                                                      16-NOV-2023:15:09:30
           188 VM                             16-NOV-2023:15:09:53                                                                      16-NOV-2023:15:09:53
           189 SQL_F                          16-NOV-2023:15:10:39                                                                      16-NOV-2023:15:10:39
           190 VQ                             16-NOV-2023:15:29:46                                                                      16-NOV-2023:15:29:46
           191 SCANNER                        29-NOV-2023:12:43:36                                                                      29-NOV-2023:12:43:36
           192 PROXY                          29-NOV-2023:14:21:46                                                                      29-NOV-2023:14:21:46

72 rows selected.

SQL>

This fixes the issue of USER_ID 85 and 86. The other thing that stands out is the big gaps in USER_IDs such as SYS = 0 and SYSTEM = 9. This is because ROLES also get a USER_ID as both are stored in SYS.USER$. Lets get the details instead from USER$. First here is a simple script:

SQL> get for_user
  1  -- user analysis
  2  --col user_id for 9999999999999
  3  --col username for a30
  4  --col lock_date for a20
  5  --col expiry_date for a20
  6  --col password_change_date for a20
  7  --set lines 220
  8  --
  9  --select  user_id,
 10  --    username,
 11  --    to_char(created,'DD-MON-YYYY:HH24:MI:SS'),
 12  --    to_char(lock_date,'DD-MON-YYYY:HH24:MI:SS'),
 13  --    to_char(expiry_date,'DD-MON-YYYY:HH24:MI:SS'),
 14  --    to_char(password_change_date,'DD-MON-YYYY:HH24:MI:SS')
 15  --from dba_users order by created,user_id
 16  --/
 17  col user# for 9999999999999
 18  col name for a32
 19  col ctime for a20
 20  col ltime for a20
 21  col exptime for a20
 22  col ptime for a20
 23  col type# for a4
 24  select  user#,
 25          decode(type#,0,'ROLE',1,'USER','UNKN'),
 26          name,
 27          to_char(ctime,'DD-MON-YYYY:HH24:MI:SS'),
 28          to_char(ltime,'DD-MON-YYYY:HH24:MI:SS'),
 29          to_char(exptime,'DD-MON-YYYY:HH24:MI:SS'),
 30          to_char(ptime,'DD-MON-YYYY:HH24:MI:SS')
 31  from    sys.user$
 32* order by ctime,user#
 33  .
SQL>

Now run it and see the results:

SQL> @for_user

         USER# DECO NAME                             TO_CHAR(CTIME,'DD-MON-YYYY:HH TO_CHAR(LTIME,'DD-MON-YYYY:HH TO_CHAR(EXPTIME,'DD-MON-YYYY: TO_CHAR(PTIME,'DD-MON-YYYY:HH
-------------- ---- -------------------------------- ----------------------------- ----------------------------- ----------------------------- -----------------------------
             0 USER SYS                              26-MAR-2023:02:26:54
             1 ROLE PUBLIC                           26-MAR-2023:02:26:55
             2 ROLE CONNECT                          26-MAR-2023:02:26:55
             3 ROLE RESOURCE                         26-MAR-2023:02:26:55
             4 ROLE DBA                              26-MAR-2023:02:26:55
             5 ROLE PDB_DBA                          26-MAR-2023:02:26:55
             6 ROLE AUDIT_ADMIN                      26-MAR-2023:02:26:55
             7 ROLE AUDIT_VIEWER                     26-MAR-2023:02:26:55
             8 USER AUDSYS                           26-MAR-2023:02:26:55          03-APR-2023:16:50:15
           193 ROLE _NEXT_USER                       26-MAR-2023:02:26:55
             9 USER SYSTEM                           26-MAR-2023:02:26:56

         USER# DECO NAME                             TO_CHAR(CTIME,'DD-MON-YYYY:HH TO_CHAR(LTIME,'DD-MON-YYYY:HH TO_CHAR(EXPTIME,'DD-MON-YYYY: TO_CHAR(PTIME,'DD-MON-YYYY:HH
-------------- ---- -------------------------------- ----------------------------- ----------------------------- ----------------------------- -----------------------------
            10 ROLE SELECT_CATALOG_ROLE              26-MAR-2023:02:26:56
            11 ROLE EXECUTE_CATALOG_ROLE             26-MAR-2023:02:26:56
            12 ROLE CAPTURE_ADMIN                    26-MAR-2023:02:26:56
    2147483617 USER SYSBACKUP                        26-MAR-2023:02:26:56          03-APR-2023:16:50:15
    2147483618 USER SYSDG                            26-MAR-2023:02:26:56          03-APR-2023:16:50:15
    2147483619 USER SYSKM                            26-MAR-2023:02:26:56          03-APR-2023:16:50:15
    2147483620 USER SYSRAC                           26-MAR-2023:02:26:56
            13 USER OUTLN                            26-MAR-2023:02:27:05          03-APR-2023:16:50:15
            14 ROLE EXP_FULL_DATABASE                26-MAR-2023:02:27:30
            15 ROLE IMP_FULL_DATABASE                26-MAR-2023:02:27:30
            16 ROLE AVTUNE_PKG_ROLE                  26-MAR-2023:02:27:46

         USER# DECO NAME                             TO_CHAR(CTIME,'DD-MON-YYYY:HH TO_CHAR(LTIME,'DD-MON-YYYY:HH TO_CHAR(EXPTIME,'DD-MON-YYYY: TO_CHAR(PTIME,'DD-MON-YYYY:HH
-------------- ---- -------------------------------- ----------------------------- ----------------------------- ----------------------------- -----------------------------
            17 ROLE CDB_DBA                          26-MAR-2023:03:10:41
            18 ROLE APPLICATION_TRACE_VIEWER         26-MAR-2023:03:11:00
            19 ROLE ACCHK_READ                       26-MAR-2023:03:11:00
            20 ROLE LOGSTDBY_ADMINISTRATOR           26-MAR-2023:03:11:48
            21 ROLE DBFS_ROLE                        26-MAR-2023:03:11:55
            22 ROLE GSMUSER_ROLE                     26-MAR-2023:03:11:57
            23 ROLE GSMROOTUSER_ROLE                 26-MAR-2023:03:11:57
            24 USER GSMADMIN_INTERNAL                26-MAR-2023:03:11:57          03-APR-2023:16:50:15
            25 USER GSMUSER                          26-MAR-2023:03:11:57          03-APR-2023:16:50:15
            26 USER DIP                              26-MAR-2023:03:12:06          26-MAR-2023:03:12:06
            27 ROLE SAGA_ADM_ROLE                    26-MAR-2023:03:12:11

         USER# DECO NAME                             TO_CHAR(CTIME,'DD-MON-YYYY:HH TO_CHAR(LTIME,'DD-MON-YYYY:HH TO_CHAR(EXPTIME,'DD-MON-YYYY: TO_CHAR(PTIME,'DD-MON-YYYY:HH
-------------- ---- -------------------------------- ----------------------------- ----------------------------- ----------------------------- -----------------------------
            28 ROLE SAGA_PARTICIPANT_ROLE            26-MAR-2023:03:12:11
            29 ROLE SAGA_CONNECT_ROLE                26-MAR-2023:03:12:11
            30 ROLE AQ_ADMINISTRATOR_ROLE            26-MAR-2023:03:12:13
            31 ROLE AQ_USER_ROLE                     26-MAR-2023:03:12:13
            32 ROLE DATAPUMP_EXP_FULL_DATABASE       26-MAR-2023:03:12:16
            33 ROLE DATAPUMP_IMP_FULL_DATABASE       26-MAR-2023:03:12:16
            34 ROLE ADM_PARALLEL_EXECUTE_TASK        26-MAR-2023:03:12:28
            35 ROLE PROVISIONER                      26-MAR-2023:03:12:30
            36 ROLE XS_SESSION_ADMIN                 26-MAR-2023:03:12:30
            37 ROLE XS_NAMESPACE_ADMIN               26-MAR-2023:03:12:30
            38 ROLE XS_CACHE_ADMIN                   26-MAR-2023:03:12:30

         USER# DECO NAME                             TO_CHAR(CTIME,'DD-MON-YYYY:HH TO_CHAR(LTIME,'DD-MON-YYYY:HH TO_CHAR(EXPTIME,'DD-MON-YYYY: TO_CHAR(PTIME,'DD-MON-YYYY:HH
-------------- ---- -------------------------------- ----------------------------- ----------------------------- ----------------------------- -----------------------------
            39 ROLE XS_CONNECT                       26-MAR-2023:03:12:30
    2147483638 USER XS$NULL                          26-MAR-2023:03:12:31          26-MAR-2023:03:12:31
            40 USER REMOTE_SCHEDULER_AGENT           26-MAR-2023:03:12:38          03-APR-2023:16:50:15
            41 USER DBSFWUSER                        26-MAR-2023:03:12:41          03-APR-2023:16:50:15
            42 ROLE SQL_FIREWALL_ADMIN               26-MAR-2023:03:12:48
            43 ROLE SQL_FIREWALL_VIEWER              26-MAR-2023:03:12:48
            44 ROLE OSAK_ADMIN_ROLE                  26-MAR-2023:03:12:49
            45 ROLE GATHER_SYSTEM_STATISTICS         26-MAR-2023:03:13:49
            46 ROLE OPTIMIZER_PROCESSING_RATE        26-MAR-2023:03:13:49
            47 ROLE DBMS_MDX_INTERNAL                26-MAR-2023:03:14:00
            48 ROLE BDSQL_ADMIN                      26-MAR-2023:03:14:35

         USER# DECO NAME                             TO_CHAR(CTIME,'DD-MON-YYYY:HH TO_CHAR(LTIME,'DD-MON-YYYY:HH TO_CHAR(EXPTIME,'DD-MON-YYYY: TO_CHAR(PTIME,'DD-MON-YYYY:HH
-------------- ---- -------------------------------- ----------------------------- ----------------------------- ----------------------------- -----------------------------
            49 ROLE BDSQL_USER                       26-MAR-2023:03:14:35
            50 ROLE RECOVERY_CATALOG_OWNER           26-MAR-2023:03:14:56
            51 ROLE RECOVERY_CATALOG_OWNER_VPD       26-MAR-2023:03:14:56
            52 ROLE RECOVERY_CATALOG_USER            26-MAR-2023:03:14:56
            53 USER GGSHAREDCAP                      26-MAR-2023:03:15:43          03-APR-2023:16:50:15
            54 ROLE SYSUMF_ROLE                      26-MAR-2023:03:27:03
            55 USER SYS$UMF                          26-MAR-2023:03:27:03          03-APR-2023:16:50:15
            56 ROLE MAINTPLAN_APP                    26-MAR-2023:03:27:04
            57 ROLE NOTIFICATIONS_USER               26-MAR-2023:03:27:06
            58 ROLE NOTIFICATIONS_ADMIN              26-MAR-2023:03:27:06
            59 ROLE SCHEDULER_ADMIN                  26-MAR-2023:03:28:51

         USER# DECO NAME                             TO_CHAR(CTIME,'DD-MON-YYYY:HH TO_CHAR(LTIME,'DD-MON-YYYY:HH TO_CHAR(EXPTIME,'DD-MON-YYYY: TO_CHAR(PTIME,'DD-MON-YYYY:HH
-------------- ---- -------------------------------- ----------------------------- ----------------------------- ----------------------------- -----------------------------
            60 ROLE PPLB_ROLE                        26-MAR-2023:03:29:06
            61 ROLE DGPDB_ROLE                       26-MAR-2023:03:29:06
            62 USER DGPDB_INT                        26-MAR-2023:03:29:06          03-APR-2023:16:50:15
            63 ROLE HS_ADMIN_SELECT_ROLE             26-MAR-2023:03:29:25
            64 ROLE HS_ADMIN_EXECUTE_ROLE            26-MAR-2023:03:29:25
            65 ROLE HS_ADMIN_ROLE                    26-MAR-2023:03:29:25
            66 ROLE GLOBAL_AQ_USER_ROLE              26-MAR-2023:03:29:28
            67 ROLE OGG_CAPTURE                      26-MAR-2023:03:33:51
            68 ROLE OGG_APPLY                        26-MAR-2023:03:33:51
            69 ROLE OGG_APPLY_PROCREP                26-MAR-2023:03:33:51
            70 ROLE OGG_SHARED_CAPTURE               26-MAR-2023:03:33:51

         USER# DECO NAME                             TO_CHAR(CTIME,'DD-MON-YYYY:HH TO_CHAR(LTIME,'DD-MON-YYYY:HH TO_CHAR(EXPTIME,'DD-MON-YYYY: TO_CHAR(PTIME,'DD-MON-YYYY:HH
-------------- ---- -------------------------------- ----------------------------- ----------------------------- ----------------------------- -----------------------------
            71 ROLE XSTREAM_CAPTURE                  26-MAR-2023:03:33:51
            72 ROLE XSTREAM_APPLY                    26-MAR-2023:03:33:51
            73 ROLE OEM_ADVISOR                      26-MAR-2023:03:33:52
            74 ROLE OEM_MONITOR                      26-MAR-2023:03:33:52
            75 USER DBSNMP                           26-MAR-2023:03:33:52          03-APR-2023:16:50:15
            76 USER APPQOSSYS                        26-MAR-2023:03:33:52          03-APR-2023:16:50:15
            77 ROLE GSMCATUSER_ROLE                  26-MAR-2023:03:33:54
            78 ROLE GSMADMIN_ROLE                    26-MAR-2023:03:33:54
            79 ROLE GSM_POOLADMIN_ROLE               26-MAR-2023:03:33:54
            80 ROLE GDS_CATALOG_SELECT               26-MAR-2023:03:33:54
            81 ROLE SHARDED_SCHEMA_OWNER             26-MAR-2023:03:33:54

         USER# DECO NAME                             TO_CHAR(CTIME,'DD-MON-YYYY:HH TO_CHAR(LTIME,'DD-MON-YYYY:HH TO_CHAR(EXPTIME,'DD-MON-YYYY: TO_CHAR(PTIME,'DD-MON-YYYY:HH
-------------- ---- -------------------------------- ----------------------------- ----------------------------- ----------------------------- -----------------------------
            82 USER GSMCATUSER                       26-MAR-2023:03:33:56          03-APR-2023:16:50:15
            83 USER GGSYS                            26-MAR-2023:03:34:04          03-APR-2023:16:50:15
            84 ROLE GGSYS_ROLE                       26-MAR-2023:03:34:04
            85 USER XDB                              26-MAR-2023:03:35:41          03-APR-2023:16:50:15
            86 USER ANONYMOUS                        26-MAR-2023:03:35:41          03-APR-2023:16:50:15
            87 ROLE XDBADMIN                         26-MAR-2023:03:35:41
            88 ROLE XDB_SET_INVOKER                  26-MAR-2023:03:35:59
            89 ROLE AUTHENTICATEDUSER                26-MAR-2023:03:36:01
            90 ROLE XDB_WEBSERVICES                  26-MAR-2023:03:36:01
            91 ROLE XDB_WEBSERVICES_WITH_PUBLIC      26-MAR-2023:03:36:01
            92 ROLE XDB_WEBSERVICES_OVER_HTTP        26-MAR-2023:03:36:01

         USER# DECO NAME                             TO_CHAR(CTIME,'DD-MON-YYYY:HH TO_CHAR(LTIME,'DD-MON-YYYY:HH TO_CHAR(EXPTIME,'DD-MON-YYYY: TO_CHAR(PTIME,'DD-MON-YYYY:HH
-------------- ---- -------------------------------- ----------------------------- ----------------------------- ----------------------------- -----------------------------
            93 ROLE SODA_APP                         26-MAR-2023:03:46:36
            94 ROLE DB_DEVELOPER_ROLE                26-MAR-2023:03:46:50
            95 USER WMSYS                            26-MAR-2023:03:48:27          03-APR-2023:16:50:15
            96 ROLE WM_ADMIN_ROLE                    26-MAR-2023:03:48:33
            97 ROLE JAVAUSERPRIV                     26-MAR-2023:03:50:20
            98 ROLE JAVAIDPRIV                       26-MAR-2023:03:50:20
            99 ROLE JAVASYSPRIV                      26-MAR-2023:03:50:20
           100 ROLE JAVADEBUGPRIV                    26-MAR-2023:03:50:20
           101 ROLE EJBCLIENT                        26-MAR-2023:03:50:20
           102 ROLE JMXSERVER                        26-MAR-2023:03:50:20
           103 ROLE DBJAVASCRIPT                     26-MAR-2023:03:50:20

         USER# DECO NAME                             TO_CHAR(CTIME,'DD-MON-YYYY:HH TO_CHAR(LTIME,'DD-MON-YYYY:HH TO_CHAR(EXPTIME,'DD-MON-YYYY: TO_CHAR(PTIME,'DD-MON-YYYY:HH
-------------- ---- -------------------------------- ----------------------------- ----------------------------- ----------------------------- -----------------------------
           104 USER OJVMSYS                          26-MAR-2023:03:50:24          07-APR-2023:14:53:16
           105 ROLE JAVA_ADMIN                       26-MAR-2023:03:50:44
           106 USER CTXSYS                           26-MAR-2023:03:53:39          03-APR-2023:16:50:15
           107 ROLE CTXAPP                           26-MAR-2023:03:53:40
           108 ROLE OLAP_XS_ADMIN                    26-MAR-2023:03:55:31
           109 USER OLAPSYS                          26-MAR-2023:03:55:37          03-APR-2023:16:50:15
           110 ROLE OLAP_DBA                         26-MAR-2023:03:55:38
           111 ROLE OLAP_USER                        26-MAR-2023:03:55:38
           112 USER MDSYS                            26-MAR-2023:04:00:40          03-APR-2023:16:50:15
           113 USER MDDATA                           26-MAR-2023:04:00:40          03-APR-2023:16:50:15
           114 ROLE RDFCTX_ADMIN                     26-MAR-2023:04:04:29

         USER# DECO NAME                             TO_CHAR(CTIME,'DD-MON-YYYY:HH TO_CHAR(LTIME,'DD-MON-YYYY:HH TO_CHAR(EXPTIME,'DD-MON-YYYY: TO_CHAR(PTIME,'DD-MON-YYYY:HH
-------------- ---- -------------------------------- ----------------------------- ----------------------------- ----------------------------- -----------------------------
           115 ROLE GRAPH_DEVELOPER                  26-MAR-2023:04:04:38
           116 ROLE GRAPH_ADMINISTRATOR              26-MAR-2023:04:04:38
           117 ROLE GRAPH_USER                       26-MAR-2023:04:04:38
           118 ROLE PGX_SESSION_CREATE               26-MAR-2023:04:04:38
           119 ROLE PGX_SERVER_GET_INFO              26-MAR-2023:04:04:38
           120 ROLE PGX_SERVER_MANAGE                26-MAR-2023:04:04:38
           121 ROLE PGX_SESSION_READ_MODEL           26-MAR-2023:04:04:38
           122 ROLE PGX_SESSION_MODIFY_MODEL         26-MAR-2023:04:04:38
           123 ROLE PGX_SESSION_NEW_GRAPH            26-MAR-2023:04:04:38
           124 ROLE PGX_SESSION_GET_PUBLISHED_GRAPH  26-MAR-2023:04:04:38
           125 ROLE PGX_SESSION_COMPILE_ALGORITHM    26-MAR-2023:04:04:38

         USER# DECO NAME                             TO_CHAR(CTIME,'DD-MON-YYYY:HH TO_CHAR(LTIME,'DD-MON-YYYY:HH TO_CHAR(EXPTIME,'DD-MON-YYYY: TO_CHAR(PTIME,'DD-MON-YYYY:HH
-------------- ---- -------------------------------- ----------------------------- ----------------------------- ----------------------------- -----------------------------
           126 ROLE PGX_SESSION_ADD_PUBLISHED_GRAPH  26-MAR-2023:04:04:38
           127 USER LBACSYS                          26-MAR-2023:04:05:33          03-APR-2023:16:50:15
           128 ROLE LBAC_DBA                         26-MAR-2023:04:05:33
           129 USER DVF                              26-MAR-2023:04:06:30          03-APR-2023:16:50:15
       1279990 USER DVSYS                            26-MAR-2023:04:06:30          03-APR-2023:16:50:15
       1279991 ROLE DV_ACCTMGR                       26-MAR-2023:04:06:40
       1279992 ROLE DV_OWNER                         26-MAR-2023:04:06:40
       1279993 ROLE DV_ADMIN                         26-MAR-2023:04:06:40
       1279994 ROLE DV_SECANALYST                    26-MAR-2023:04:06:40
    2147483627 ROLE DV_PATCH_ADMIN                   26-MAR-2023:04:06:40
    2147483628 ROLE DV_MONITOR                       26-MAR-2023:04:06:40

         USER# DECO NAME                             TO_CHAR(CTIME,'DD-MON-YYYY:HH TO_CHAR(LTIME,'DD-MON-YYYY:HH TO_CHAR(EXPTIME,'DD-MON-YYYY: TO_CHAR(PTIME,'DD-MON-YYYY:HH
-------------- ---- -------------------------------- ----------------------------- ----------------------------- ----------------------------- -----------------------------
    2147483629 ROLE DV_STREAMS_ADMIN                 26-MAR-2023:04:06:40
    2147483630 ROLE DV_GOLDENGATE_ADMIN              26-MAR-2023:04:06:40
    2147483631 ROLE DV_XSTREAM_ADMIN                 26-MAR-2023:04:06:40
    2147483632 ROLE DV_GOLDENGATE_REDO_ACCESS        26-MAR-2023:04:06:40
    2147483633 ROLE DV_AUDIT_CLEANUP                 26-MAR-2023:04:06:40
    2147483634 ROLE DV_DATAPUMP_NETWORK_LINK         26-MAR-2023:04:06:40
    2147483635 ROLE DV_POLICY_OWNER                  26-MAR-2023:04:06:41
           130 USER PDBADMIN                         03-APR-2023:16:50:15                                        30-SEP-2023:16:50:15          03-APR-2023:16:50:15
           131 USER APEX_220200                      03-APR-2023:16:53:17          03-APR-2023:16:53:17
           132 USER FLOWS_FILES                      03-APR-2023:16:53:17          03-APR-2023:16:53:17
           133 USER APEX_PUBLIC_USER                 03-APR-2023:16:53:17                                                                      03-APR-2023:17:00:25

         USER# DECO NAME                             TO_CHAR(CTIME,'DD-MON-YYYY:HH TO_CHAR(LTIME,'DD-MON-YYYY:HH TO_CHAR(EXPTIME,'DD-MON-YYYY: TO_CHAR(PTIME,'DD-MON-YYYY:HH
-------------- ---- -------------------------------- ----------------------------- ----------------------------- ----------------------------- -----------------------------
           134 ROLE APEX_ADMINISTRATOR_READ_ROLE     03-APR-2023:16:53:21
           135 ROLE APEX_ADMINISTRATOR_ROLE          03-APR-2023:16:53:21
           136 ROLE APEX_GRANTS_FOR_NEW_USERS_ROLE   03-APR-2023:16:53:21
           137 USER APEX_LISTENER                    03-APR-2023:17:00:24                                                                      03-APR-2023:17:00:24
           138 USER APEX_REST_PUBLIC_USER            03-APR-2023:17:00:24                                                                      03-APR-2023:17:00:24
           139 USER ORDS_PUBLIC_USER                 03-APR-2023:17:00:39                                                                      03-APR-2023:17:00:39
           140 USER ORDS_METADATA                    03-APR-2023:17:00:40
           141 ROLE ORDS_ADMINISTRATOR_ROLE          03-APR-2023:17:00:52
           142 ROLE ORDS_RUNTIME_ROLE                03-APR-2023:17:00:52
           143 USER HRREST                           03-APR-2023:17:01:12                                                                      03-APR-2023:17:01:12
           144 USER HR                               03-APR-2023:17:01:14                                                                      03-APR-2023:17:01:14

         USER# DECO NAME                             TO_CHAR(CTIME,'DD-MON-YYYY:HH TO_CHAR(LTIME,'DD-MON-YYYY:HH TO_CHAR(EXPTIME,'DD-MON-YYYY: TO_CHAR(PTIME,'DD-MON-YYYY:HH
-------------- ---- -------------------------------- ----------------------------- ----------------------------- ----------------------------- -----------------------------
           145 USER OE                               03-APR-2023:17:01:19                                                                      03-APR-2023:17:01:19
           146 USER PM                               03-APR-2023:17:01:56                                                                      03-APR-2023:17:01:56
           147 USER IX                               03-APR-2023:17:02:02                                                                      03-APR-2023:17:02:02
           148 USER SH                               03-APR-2023:17:02:09                                                                      03-APR-2023:17:02:09
           149 USER BI                               03-APR-2023:17:02:48                                                                      03-APR-2023:17:02:48
           150 USER AV                               03-APR-2023:17:02:53                                                                      03-APR-2023:17:02:53
           151 USER PFCLSCAN                         05-APR-2023:13:05:50                                                                      21-APR-2023:12:52:44
           152 USER VA                               06-APR-2023:15:20:00                                                                      26-MAY-2023:11:46:46
           153 USER VB                               06-APR-2023:15:21:44                                                                      06-APR-2023:15:21:44
           154 USER VC                               06-APR-2023:15:26:36                                                                      06-APR-2023:15:26:36
           157 USER VF                               10-APR-2023:11:11:31                                                                      10-APR-2023:11:11:31

         USER# DECO NAME                             TO_CHAR(CTIME,'DD-MON-YYYY:HH TO_CHAR(LTIME,'DD-MON-YYYY:HH TO_CHAR(EXPTIME,'DD-MON-YYYY: TO_CHAR(PTIME,'DD-MON-YYYY:HH
-------------- ---- -------------------------------- ----------------------------- ----------------------------- ----------------------------- -----------------------------
           158 USER VG                               10-APR-2023:12:06:57                                                                      10-APR-2023:12:06:57
           159 USER VE                               14-APR-2023:13:21:53                                                                      14-APR-2023:13:21:53
           160 USER VJ                               02-MAY-2023:12:51:56                                                                      02-MAY-2023:12:51:56
           165 USER VV                               26-JUN-2023:08:52:17                                                                      26-JUN-2023:08:52:17
           166 USER VX                               26-JUN-2023:09:55:57                                                                      26-JUN-2023:09:55:57
           167 USER SCHEMA1                          26-JUN-2023:14:18:49                                                                      26-JUN-2023:14:18:49
           168 USER SCHEMA2                          26-JUN-2023:14:19:32                                                                      26-JUN-2023:14:19:32
           169 USER CON1                             26-JUN-2023:14:27:16                                                                      26-JUN-2023:14:27:16
           170 USER CON2                             26-JUN-2023:14:28:23                                                                      26-JUN-2023:14:28:23
           171 USER TESTTEST                         14-JUL-2023:08:56:43                                                                      14-JUL-2023:08:56:43
           172 USER TT1                              09-OCT-2023:09:15:27                                                                      09-OCT-2023:09:15:27

         USER# DECO NAME                             TO_CHAR(CTIME,'DD-MON-YYYY:HH TO_CHAR(LTIME,'DD-MON-YYYY:HH TO_CHAR(EXPTIME,'DD-MON-YYYY: TO_CHAR(PTIME,'DD-MON-YYYY:HH
-------------- ---- -------------------------------- ----------------------------- ----------------------------- ----------------------------- -----------------------------
           173 USER UU1                              09-OCT-2023:09:16:49                                                                      09-OCT-2023:09:16:49
           174 USER TEST33                           03-NOV-2023:14:30:26                                                                      03-NOV-2023:14:30:26
           187 USER ORABLOG                          16-NOV-2023:15:09:30                                                                      16-NOV-2023:15:09:30
           188 USER VM                               16-NOV-2023:15:09:53                                                                      16-NOV-2023:15:09:53
           189 USER SQL_F                            16-NOV-2023:15:10:39                                                                      16-NOV-2023:15:10:39
           190 USER VQ                               16-NOV-2023:15:29:46                                                                      16-NOV-2023:15:29:46
           191 USER SCANNER                          29-NOV-2023:12:43:36                                                                      29-NOV-2023:12:43:36
           192 USER PROXY                            29-NOV-2023:14:21:46                                                                      29-NOV-2023:14:21:46

195 rows selected.

SQL>

There are a few things of interest:

• Roles also use the bigger numbers in some cases for the series 2147483635 or 1279994


• PUBLIC is shown as TYPE=0 and therefore in this script its shows as a ROLE but its a USER GROUP not a role.


• The sequence _NEXT_USER has a ID value of 193 and is shown in this script as a ROLE but its a sequence and it shows the next normal USER ID to use


• There are gaps visible. Lets use the example of the gap between IDs 154 to 157. User id 154 is user VC and the next user id is 157 for user VF. So it would appear two users are missing, ID 155 and ID 156, we can reasonably know the IDs but not the names. We know user VC was created on 06-APR-2023:15:26:36 and VF on 10-APR-2023:11:11:31 SO we can say for certain that users 155 and 156 were created between the 6th April and the 10th April BUT we do not know when they were removed. We can say for certain that they were removed after the CREATE date and before the current SYSDATE - a much bigger range of dates. We can see user IDs 152 and 153 have names VA and VB and the last user before the gap is VC and the next user is VF and the one after is VG (ID 158). So we can assume as a working hypothesis that the missing users are VD and VE. We can see VE exists as ID 159 so the assumption is VD and VE were deleted and VE was also re-created. This is good news to reduce the date range of when the users were deleted. Because user VE as User ID 159 was created on 10-APR-2023:12:06:57 then this narrows the window of when user IDs 155 and 156 were removed. If one of these users was VE then it could not be added if it existed so its likely now that it was deleted between the create date of User ID 154 and 159


• Because the next user sequence is 193 and the last current user ID is 192 we can assume no users were removed after the creation date of user ID 192

The next step is to ascertain when the attack took place - or currently is assumed to have taken place. As more evidence is looked at we can narrow the dates of the attack to be more certain of what events took place during the attack dates and slowly narrow the date range.

We would need to look at more data, objects created, tables, views, PL/SQL and also grants made. Even if we have no audit trails we can still use gaps in data to help us investigate. Once we have a more detailed view of events that occurred during the attack we can start to use other data to try and correlate this; the SQL, redo, library cache, even external logs.

The biggest problem is that if an attack was purely reading data then no objects are created or dropped and if there is no audit trails it is much harder to find other data to corroborate an attack.

All is not lost if there are no audit trails BUT its harder to find evidence or prove something happened in the database but techniques such as these require work and detective work but can be fruitful.

#oracleace #sym_42 #oracle #database #security #forensics #liveresponse #databreach

One of the key security issues I come across when performing security audits is the proliferation of passwords located on SQL files and OS shell scripts and more. If you get access to the server you can learn a lot of the database passwords and if you were an attacker use them. We see the same issues in binaries also where the password is hard coded into C code for instance.

Even if you pass in the password on the command line then often in a Unix/Linux environment the password is visible in process lists or is captured in OS history files.

There have been may solutions over the years including adding spaces to the process list to shove the passwords off to the right or clearing history files or adding passwords to hidden files or to environment variables.

The better solution if a password must be used in a program or script is to use a wallet and Oracles secure password store where credentials are stored in a wallet. This is simple to use and then the username and password is no longer visible when used as this is replaced by "/" - slash. On connecting to the database the OCI libraries (The original OCI not the Cloud) know that the method is OCI_CRED_EXT on OCISessionBegin() and know that the sqlnet.ora SQLNET.WALLET_OVERRIDE=TRUE means do and look for the credentials in the wallet referenced also in the sqlnet.ora. So, in this case the password is not seen in clear text and is retrieved from the wallet and passed to the OCI connection sequence of C functions.

So, to use this is simple. If you use a full client the tools are available but if you use an instant client then you can create the wallet on the server and copy to the PC or client Linux/Unix box.

First create the wallet on the server:

[root@localhost ~]# su - oracle
[oracle@localhost ~]$ pwd
/home/oracle
[oracle@localhost ~]$ mkdir client
[oracle@localhost ~]$ cd client
[oracle@localhost client]$ orapki wallet create -wallet . -auto_login
Oracle PKI Tool Release 23.2.0.0.0 - Production
Version 23.2.0.0.0
Copyright (c) 2004, 2023, Oracle and/or its affiliates. All rights reserved.
Enter password:
Enter password again:
Operation is successfully completed.
[oracle@localhost client]$

The wallet password is used only to manage the wallet not to open it. In this case we set -auto_login as we want the wallet to auto open on the PC BUT we cannot set it to -auto_login_local on the server as the wallet will then not auto open on the PC. This is a security issue itself as we want the wallet to auto-open BUT it means an attacker can copy the wallet to their own PC and then connect to the database without knowledge of the password. We have two options here; the first is to get the wallet tools on the client and use auto_login_local; the second is to not use auto_login at all BUT the user would need to pass the wallet password on database login to open it and then just passes the original problem to the next step; i.e. a password is needed. So, if you have to use instant clients and wallets then this is a security risk. You can also use other database features such as Database Vault or logon triggers to limit any connection from a wallet to known clients.

OK, so now as a database credential to the wallet

[oracle@localhost client]$ mkstore -wrl . -createCredential freepdb1 scanner scanner                                      
Oracle Secret Store Tool Release 23.2.0.0.0 - Production
Version 23.2.0.0.0
Copyright (c) 2004, 2023, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:
[oracle@localhost client]$

As you can see the database credentials (username/password) are linked to the TNS entry so if you would like to connect to multiple database users then you can add multiple TNS entry linked credentials to the wallet. i.e. one per user and then add these to the TNS names and use each TNS for each user added.

Now we an ftp the server created wallet (secure password store) from the database server to the client PC. Then create the sqlnet.ora on the client and include:

WALLET_LOCATION=
	(SOURCE=
		(METHOD=file)
		(METHOD_DATA=(DIRECTORY=C:\_aa\PB\bin)))
SQLNET.WALLET_OVERRIDE = TRUE
SSL_CLIENT_AUTHENTICATION=FALSE
SSL_VERSION = 0

In my file i point to my wallet location and also set SQLNET.WALLET_OVERRIDE=TRUE to say use the wallet for the password. The tnsnames.ora is just a standard entry as follows:

FREEPDB1=
(DESCRIPTION=
	(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.18)(PORT=1521))
	(CONNECT_DATA=
		(SERVICE_NAME=freepdb1)))

Remember if we want to connect to multiple credentials (users/password) in a wallet we need multiple TNS entries and multiple credentials in the wallet. So, now lets connect to the database using the wallet (secure password store)

C:\_aa\PD>sqlplus /nolog

SQL*Plus: Release 11.2.0.4.0 Production on Wed Nov 29 15:28:51 2023

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

SQL> connect /@freepdb1
Connected.
SQL>

As you can see I can connect to the database now without using a password directly on command line or in a script should I want to connect to the database in a script.

In Oracle 23c Oracle have deprecated the mkstore command. Oracle state that they will be enhancing the orapki tool after 23c to add the functionality in mkstore to it.

#oracleace #sym_42 #23c #wallet #oracle #secure #password #store #credentials