oracle-101/Readme.md

# Oracle Cheatsheet for penetration testers
## Index

## Why
There are plenty of cheatsheets and documentation for oracle SQL Injections. There have also been countless presentations and research on the subject, however the material is very sparse, mostly out of date and totally non exhaustive.
As a penetration tester I have to deal daily with Oracle DBMS, both via SQL Injections, direct connecctions or access to the hosting machine.

## Basic Information
Most of the times Oracle RDMS run on linux, specifically RedHat or Oracle Linux. The most version I found in the wild were 9 which is extremely old, 10, 11 as most common and 12 in the best cases.
Oracle has an official client called `sqlplus`. Sometimes it is extremely useful to have `sqlplus` and the import and export utilities ready in standalone packages. Please see the #Downloads sections for that.
Oracle instances are defined in a file called `tnsnames.ora` where an instance name is associated to a connection string.

Example `tnsnames.ora`:

```
PROD_DB = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = 10.50.50.10) (PORT = 1521)) (CONNECT_DATA = (SID = PROD)))
PREPROD_DB = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = 10.50.50.10) (PORT = 1522)) (CONNECT_DATA = (SID = PREPROD)))
DEV_DB = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = 10.20.20.10) (PORT = 1521)) (CONNECT_DATA = (SID = DEVEL)))

```
This file support failover, load balancing and many more options, for further information refer to https://docs.oracle.com/cd/B28359_01/network.111/b28317/tnsnames.htm#NETRF007
Information specified in this file are extremely useful in order to know the database in use and their specifics. All information in the connect string is required to connect to an Oracle instance.

## Sqlplus
Sqlplus examples:

```
# Automatic login
sqlplus my_user/my_password@PROD_DB

# Prompt for password
sqlplus myuser@PROD_DB

# Connect to instance not present in tnsnames.ora
sqlplus my_user@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=192.168.1.10)(Port=1521))(CONNECT_DATA=(SID=REMOTE_SID)))

# In case there are problems with the shell double quotes can be used
sqlplus my_user@"(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=192.168.1.10)(Port=1521))(CONNECT_DATA=(SID=REMOTE_SID)))"

# For a local instance if logged in with the oracle user
sqlplus "/ as sysdba"
sqlplus "sys as sysdba"
sqlplus my_user/my_password@PROD_DB as sysdba
```

Better formatiing:
```
set pagesize 0;
sel linesize 5000;
```

## Import/Export
Oracle uses a proprietary format both for storing the actual data on the disk and for export/import process. While it is technically possible to dump a table trough sqlplus it is often very CPU consuming and not very efficient for large tables. In this case Oracle provides two different set of utilities:
 * imp/exp
 * impdp/expdp

Both require special privileges: this means that even if you have select privileges on a table that doesn't mean you have the privilege to bulk export it. Please refer to https://docs.oracle.com/database/121/SUTIL/GUID-8B6975D3-3BEC-4584-B416-280125EEC57E.htm

Now the old import export format has been reverse engineered and there's a python script available.

## Password hashes
Oracle password hashes are both stored inside the database and selectable from a privileged users and stored on disk.
```
$ORACLE_HOME/dbs/orapw<sid> # Unix,
%ORACLE_HOME%\database\PWD<sid>.ora # Windows
```
```
> SELECT NAME, PASSWORD FROM SYS.USER$;
SYS                            DCB748A5BC5390F2
PUBLIC
CONNECT
RESOURCE
DBA
SYSTEM                         EED9B65CCECDB2E9
SELECT_CATALOG_ROLE
EXECUTE_CATALOG_ROLE
DELETE_CATALOG_ROLE
EXP_FULL_DATABASE
IMP_FULL_DATABASE
OUTLN                          4A3BA55E08595C81
RECOVERY_CATALOG_OWNER
GATHER_SYSTEM_STATISTICS
LOGSTDBY_ADMINISTRATOR
AQ_ADMINISTRATOR_ROLE
AQ_USER_ROLE
SCHEDULER_ADMIN
DIP                            CE4A36B8E06CA59C
HS_ADMIN_ROLE
TSMSYS                         3DF26A8B17D0F29F
_NEXT_USER
OEM_ADVISOR
OEM_MONITOR
DBSNMP                         E066D214D5421CCC
PLUSTRACE
CTXSYS                         D1D21CA56994CAB6
CTXAPP
XDB                            E76A6BD999EF9FF1
ANONYMOUS                      anonymous
XDBADMIN
AUTHENTICATEDUSER
XDBWEBSERVICES
MDSYS                          72979A94BAD2AF80
HR                             4C6D73C3E8B0F0DA
FLOWS_FILES                    364B78B9EABB9E56
FLOWS_020100                   16E4C012E98710D0
```
Old password format is `3DES(upper($username.$password))` for <12c and an harder custom method for >12.
Both are supported by hashcat as `-m3100` and `-m 12300`. 

Fopr more info:
 - http://marcel.vandewaters.nl/oracle/security/password-hashes
 - https://www.trustwave.com/en-us/resources/blogs/spiderlabs-blog/changes-in-oracle-database-12c-password-hashes/

## Recon
To begin with Oracle has plenty of system tables and views to keep track of its properties. Some of them have either `all`, `dba` or `user` prefix. What does it mean?

From https://sqljana.wordpress.com/2016/12/21/oracle-data-dictionary-views-user-vs-all-vs-dba-views-100-level-basics/

> USER* Views
> USER_*: Views that start with USER_ list only the objects owned by the currently logged in user

> ALL* Views
> ALL_*: Views that start with ALL_ list only the objects the currently logged in user has permissions to access

> DBA* Views
> DBA_*: Views that start with DBA_ list all objects unless restricted by the WHERE clause

Most of the time the most useful will be the ones with the `all_` prefix, except if we already are dba.
Query to `SYS` tables and to `dba_*` do require high privileges.


Oracle default databases to exclude to get cleaner results for custom tables/columns/procedures (from https://github.com/sqlmapproject/sqlmap/blob/master/lib/core/settings.py):
```
('ANONYMOUS', 'APEX_030200', 'APEX_PUBLIC_USER', 'APPQOSSYS', 'BI', 'CTXSYS', 'DBSNMP', 'DIP', 'EXFSYS', 'FLOWS_%', 'FLOWS_FILES', 'HR', 'IX', 'LBACSYS', 'MDDATA', 'MDSYS', 'MGMT_VIEW', 'OC', 'OE', 'OLAPSYS', 'ORACLE_OCM', 'ORDDATA', 'ORDPLUGINS', 'ORDSYS', 'OUTLN', 'OWBSYS', 'PM', 'SCOTT', 'SH', 'SI_INFORMTN_SCHEMA', 'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'SYS', 'SYSMAN', 'SYSTEM', 'WKPROXY', 'WKSYS', 'WK_TEST', 'WMSYS', 'XDB', 'XS$NULL')
```

Check Oracle version:
```
> SELECT BANNER FROM V$VERSION;
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.
```
```
> SELECT INSTANCE_NAME, HOST_NAME, EDITION, VERSION FROM V$INSTANCE;
INSTANCE_NAME    HOST_NAME                                                        EDITION VERSION
---------------- ---------------------------------------------------------------- ------- -----------------
XE               oracle.lsd.cat                                                   XE      10.2.0.1.0
```
```
> SELECT PRODUCT, VERSION FROM  PRODUCT_COMPONENT_VERSION;

PRODUCT                                                          VERSION
---------------------------------------------------------------- ----------------------------------------------------------------
NLSRTL                                                           10.2.0.1.0
Oracle Database 10g Express Edition                              10.2.0.1.0
PL/SQL                                                           10.2.0.1.0
TNS for Linux:                                                   10.2.0.1.0

```

Get the current user:
```
SELECT USER FROM DUAL;
SELECT USERNAME FROM USER_USERS;
```

Get current user privileges:
```
SELECT * FROM SESSION_PRIVS;
```
Check if Java is available:
```
SELECT DBMS_JAVA.GET_OJVM_PROPERTY(PROPSTRING=>'java.version') FROM DUAL;
```

List all users
```
SELECT USER, PASSWORD FROM SYS.USER$
SELECT USERNAME, PASSWORD from DBA_USERS;
SELECT USERNAME FROM ALL_USERS;
```

List all databases
```
SELECT TABLESPACE_NAME FROM USER_TABLESPACES;
SELECT USERNAME, DEFAULT_TABLESPACE from DBA_USERS;
```

List all tables
```
SELECT OWNER, TABLE_NAME FROM ALL_TABLES;
```

List all columns
```
SELECT OWNER, TABLE_NAME, COLUMNS NAME FROM ALL_TAB_COLUMNS;
```

Linked instances
```
SELECT * FROM SYS.LINK$;
SELECT * FROM DBA_DB_LINKS;
SELECT OWNER, USERNAME, HOST, DB_LINK FROM ALL_DB_LINKS;
```
Password for DB Links may be cleartext, encrypted or non present depending on the version. See #Linked_Instances

It is also possibile to view past queries. This is extremely useful to understand how the database is being used.
```
SELECT SQL_TEXT FROM V$SQL;
SELECT SQL_TEXT FROM WRH$_SQLTEXT;
```

List all procedures:
```
SELECT OWNER, OBJECT_NAME, OBJECT_TYPE FROM ALL_OBJECTS WHERE OBJECT_TYPE IN ('PROCEDURE', 'FUNCTION', 'PACKAGE', 'PACKAGE BODY');
```
List user procedures:
```
SELECT OBJECT_NAME, OBJECT_TYPE FROM ALL_OBJECTS WHERE OBJECT_TYPE IN ('PROCEDURE', 'FUNCTION', 'PACKAGE', 'PACKAGE BODY');
```

Get all non system procedures:
```
SELECT OWNER, OBJECT_NAME, OBJECT_TYPE FROM ALL_OBJECTS WHERE OBJECT_TYPE IN ('PROCEDURE', 'FUNCTION', 'PACKAGE', 'PACKAGE BODY') AND WHERE OWNER NOT IN ('ANONYMOUS', 'APEX_030200', 'APEX_PUBLIC_USER', 'APPQOSSYS', 'BI', 'CTXSYS', 'DBSNMP', 'DIP', 'EXFSYS', 'FLOWS_%', 'FLOWS_FILES', 'HR', 'IX', 'LBACSYS', 'MDDATA', 'MDSYS', 'MGMT_VIEW', 'OC', 'OE', 'OLAPSYS', 'ORACLE_OCM', 'ORDDATA', 'ORDPLUGINS', 'ORDSYS', 'OUTLN', 'OWBSYS', 'PM', 'SCOTT', 'SH', 'SI_INFORMTN_SCHEMA', 'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'SYS', 'SYSMAN', 'SYSTEM', 'WKPROXY', 'WKSYS', 'WK_TEST', 'WMSYS', 'XDB', 'XS$NULL')

```

Get the source code of an object:
```
SELECT TEXT FROM ALL_SOURCE WHERE NAME = 'MY_PROCEDURE' ORDER BY LINE;
```
Somatimes a source code might be obfuscated with a method that Oracle calls 'wrapping'. This is the case of most default procedures. THis format is easily reversible:
 - https://github.com/DarkAngelStrike/UnwrapperPLSQL/blob/master/unwrap.py
 - https://www.codecrete.net/UnwrapIt/


## Common error based vectors

## Linked instances
Linked instances are used very often. They're very useful to an attacker because they extend the attack surface and may even allow to jump to different network.

Basic Syntax:
```
SELECT USER FOM DUAL@MY_LINKED_DB;
```

Password are stored cleartext or a in a reversible format:
```
> SELECT PASSWORDX FROM SYS.LINK$;

PASSWORDX
--------------------------------------------------
05D3BB995A60D0F7BC8724E1FB001819169BD0818171F28256
```
To decode the password look at:
 - 
 -

It is important to note that the whole recon process might be done in every linked databases. The user in the linked database might have higher privileges, a different Oracle version and different procedures and packages. It might be possibile to gain RCE in a linked database and not in the one used as entry point.

Queries to linked database may also be nested which means that it is possible to run queries on a database linked to a database linked to the main instance thus making a 'jump' to a third database.
While the following syntax will not work:
```
SELECT USER FROM DUAL@DB1@DB2; error
```
It is possible to create a view or a synomin to overcome the syntax problem as described here https://stackoverflow.com/questions/2094843/possible-to-link-to-another-database-link
```
DB1:
CREATE SYNONYM X FOR MyTable@sqlServerDB

DB2:
(assumes db link to DB1 connects as owner of synonym)
SELECT * from X@DB1
```

## Procedures, packages and functions

## Stacked queries e privilege escalation

## Command execution from sqlplus

## Command execution from SQL Injection