Oracle Database – port 1521

Oracle database quick reference.

💡 Use Oscanner

Connect to instance
Queries
DBSAT
Oscanner

Listener (port 1521 / port 1526)

tnscmd10g version -h $IP

lsnrctl status

SQLNet (port 1523)

  • Protocol sqlnet2 & cichild-lm

Connect to instance

SQL Developer Command Line (SQLcl)

Oracle SQL Developer Command Line (SQLcl) is a free command line interface for Oracle Database. It allows you to interactively or batch execute SQL and PL/SQL.

cd sqlcl/bin

Help

./sqlcl -H

Login

Same syntax as SQL*Plus EZconnect.

./sqlcl -L <username>/<password>@<connect_identifier>
./sqlcl -L <username>/<password>@//${IP}:1521
./sqlcl -L <username>/<password>@//${IP}:1521/<service name>

SQL*Plus client

Installation

See How to get Oracle Support working with Kali Linux (Metasploit).

Download the Oracle Instant Client packages for your version of Kali Linux
Instant Client for Linux AMD64 (32-bit and 64-bit)
http://www.oracle.com/technetwork/database/database-technologies/instant-client/overview/index.html

The packages you will need are:
    instantclient-basic-linux***.zip
    instantclient-sqlplus-linux***.zip
    instantclient-sdk-linux***.zip


mkdir /opt/oracle
Unzip all files and move all "instantclient10_1" directories under /opt/oracle (merge content)
You should now have a path called /opt/oracle/instantclient10_1

# Create symbolic link
cd /opt/oracle/instantclient10_1
ln libclntsh.so.10.1 libclntsh.so
ls -lh libclntsh.so

You also need to configure the appropriate environment variables, perhaps by inserting them into your .bashrc file, logging out and back in for them to apply.

export PATH=$PATH:/opt/oracle/instantclient10_1
export SQLPATH=/opt/oracle/instantclient10_1
export TNS_ADMIN=/opt/oracle/instantclient10_1
export LD_LIBRARY_PATH=/opt/oracle/instantclient10_1
export ORACLE_HOME=/opt/oracle/instantclient10_1

# If you have succeeded, you should be able to run sqlplus from a command prompt:
sqlplus

SQL*Plus: Release 12.1.0.2.0 Production on Mon Jun 1 17:22:53 2015

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

Enter user-name:

tnsnames.ora

Edit tnsnames.ora to add information about the service/instance.

cat $ORACLE_HOME/network/admin/tnsnames.ora
MYDB1=
  (DESCRIPTION=
    (ADDRESS=
      (PROTOCOL=TCP)
      (HOST=hostname)
      (PORT=1521)
    )
    (CONNECT_DATA=
      (SERVICE_NAME=MYDB)
    )
  )
TNS_ADMIN=/home/user1
export TNS_ADMIN
sqlplus user@MYDB1

Direct connect (without tnsnames.ora)

# Use quotes in Unix or () will be interpreted by the shell
# Don't put the password on command line, it will be kept in bash history...
sqlplus user/password@(description=(address_list=(address=.......DBNAME)))

sqlplus scott/tiger@"(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=dbserver.domain.com)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=myservice.domain.com)))"

# URL-like syntax is supported by (much) later releases of sqlplus:
# EZconnect syntax
sqlplus user/password@//hostname/service_name
sqlplus user/password@//hostname:port/service_name

Oracle Thin JDBC Driver

jdbc:oracle:thin:@<server>[:<1521>]:<database_name>
jdbc:oracle:thin:@<server>[:<1521>]/<service_name>
jdbc:oracle:thin:@(description=(address=(host=HOSTNAME)(protocol=tcp)(port=PORT))(connect_data=(service_name=SERVICENAME)))

On database server

cat /etc/oratab
. oraenv
[enter SID]
sqlplus / as sysdba

or

SET ORACLE_SID=SID
sqlplus / as sysdba

sqlplus /nolog

Using Metasploit

Investigate Metasploit modules to login.

   864   auxiliary/scanner/oracle/isqlplus_login                                                   normal  No     Oracle iSQL*Plus Login Utility
   867   auxiliary/scanner/oracle/oracle_login                                                     normal  No     Oracle RDBMS Login Utility

Patches

$ORACLE_HOME/OPatch/opatch lsinventory
$ORACLE_HOME/OPatch/opatch lsinventory -display

 History of patch application

select * from sys.registry$history;

 Status of patches on a database

select dbms_qopatch.GET_OPATCH_LIST from dual;

Queries

Tested on Oracle 19.

Comments

-- this is a one line comment
/* this is a multi line comment */
-- String identifier is single quote: '

String manipualtion

Concatenation

SELECT 'foo'||'bar' FROM DUAL;

Substring

SELECT SUBSTR('foobar', 4, 2) FROM DUAL;

Queries from within CDBs

https://docs.oracle.com/database/121/ADMIN/cdb_mon.htm#ADMIN13720

List pluggable databases (PDB)

select name, open_mode from v$pdbs;

Connect to PDB

alter session set container=pdb1;

Current release number

COL PRODUCT FORMAT A35
COL VERSION FORMAT A15
COL STATUS FORMAT A15 
SELECT * FROM PRODUCT_COMPONENT_VERSION;

PRODUCT                                  VERSION     STATUS
---------------------------------------- ----------- -----------
NLSRTL                                   10.2.0.1.0  Production
Oracle Database 10g Enterprise Edition   10.2.0.1.0  Prod
PL/SQL                                   10.2.0.1.0  Production
...

Database

Database version

SELECT banner FROM v$version
SELECT version FROM v$instance

Current database name

SELECT global_name FROM global_name;

Users

List all users

SELECT *
FROM all_users
ORDER BY username;
SELECT username FROM all_users;

Show current user

SELECT user FROM dual;

Objects

List all objects accessible to the current user

SELECT *
FROM all_objects
WHERE object_type NOT IN ('INDEX', 'INDEXTYPE', 'TYPE', 'OPERATOR', 'SEQUENCE', 'SYNONYM')
ORDER BY owner, object_name;

List all tables accessible to the current user

SELECT owner || '.' || table_name T
FROM all_tables
WHERE owner NOT IN ('ANONYMOUS','CTXSYS','DBSNMP','EXFSYS','LBACSYS','MDSYS','MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS','ORDSYS','OUTLN','SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM','TSMSYS','WK_TEST','WKSYS','WKPROXY','WMSYS','XDB')
ORDER BY owner, table_name;

List all tables & concatenate row results

TIP: Use this when a SQL injection only returns 1 row

SELECT listagg(owner||'.'||table_name,',') within group (order by owner, table_name) 
WHERE owner NOT IN ('ANONYMOUS','CTXSYS','DBSNMP','EXFSYS','LBACSYS','MDSYS','MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS','ORDSYS','OUTLN','SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM','TSMSYS','WK_TEST','WKSYS','WKPROXY','WMSYS','XDB')
FROM all_tables group by 1

List objects in the current user’s schema

SELECT *
FROM user_objects
ORDER BY object_name;

Display columns of a table

DESCRIBE schema.tablename;
SELECT column_name, table_name
FROM all_tab_columns
WHERE owner='<SCHEMA>'

Grants & Roles

List all grants given DIRECTLY to the user (not via roles)

SELECT *
FROM user_tab_privs;

List roles granted to user

SELECT *
FROM user_role_privs;

OS Commands

NOT TESTED RECENTLY, only on older versions

Execute OS commands

select os_command.exec('ls -l') from dual

Execute OS commands in PL/SQL

declare
  cmd varchar2(100):='C:\docs\runme.sql';
begin
   host (cmd);
end;
/

Execute OS commands from within SQL*Plus client

!hostname

Configuration / Parameters

-- Configuration file, in $ORACLE_HOME/...
pfile & spfile: look for *.ora

-- Password files
The default location for the password file is: $ORACLE_HOME/dbs/orapw$ORACLE_SID on Unix,
%ORACLE_HOME%\database\PWD%ORACLE_SID%.ora on Windows. 
The init parameter REMOTE_LOGIN_PASSWORDFILE specifies if a password file is used to authenticate the Oracle DBA or not

-- Start Oracle
SET ORACLE_SID=SID
sqlplus / as sysdba
startup [PFILE=path\filename]

-- Stop Oracle
SET ORACLE_SID=SID
sqlplus / as sysdba
shutdown immediate

Default Users / Passwords

https://docs.oracle.com/cd/B28359_01/server.111/b28337/tdpsg_user_accounts.htm#TDPSG20303 

-- New database default password: welcome
-- scott/tiger

-- Predefined Oracle Database Administrative User Accounts
ANONYMOUS
CTXSYS
DBSNMP  -- open
EXFSYS
LBACSYS
MDSYS
MGMT_VIEW  -- open
OLAPSYS
OWBSYS
ORDPLUGINS
ORDSYS
OUTLN
SI_INFORMTN_SCHEMA
SYS/change_on_install     -- open (but not necessarily remotely)
SYSMAN  -- open
SYSTEM/manager  -- open
TSMSYS
WK_TEST
WKSYS
WKPROXY
WMSYS
XDB

-- Predefined Non-Administrative User Accounts
APEX_PUBLIC_USER
DIP
FLOWS_30000
FLOWS_FILES
MDDATA
ORACLE_OCM
SPATIAL_CSW_ADMIN_USR
SPATIAL_WFS_ADMIN_USR
XS$NULL

Database Security Assessment Tool (DBSAT)

Requires Oracle Database 11.2.0.4 and later.

Collector

Executes SQL queries (primarily by querying database dictionary views) and runs operating system commands to collect data from the system to be assessed. The collected data is written to a JSON file that is used by the DBSAT Reporter in the analysis phase.

Must be run on the server that contains the database AND as an OS user with read permissions on files and directories under ORACLE_HOME. Must be run on each pluggable database separately.

./dbsat collect -n <database_connect_string> <output_file>
./dbsat collect -n system@ORCL output_ORCL

Reporter

Analyzes the collected data and generates the Oracle Database Security Assessment Report in HTML, Excel, JSON, and Text formats.

Can be run on any machine: PC, laptop, or server (not limited to the database server).

./dbsat report [-a] [-n] [-g] [-x <section>] <input_file>
./dbsat report -n output_ORCL

Discoverer

Executes SQL queries and collects data from the system to be assessed, based on the settings specified in the configuration files. It does this primarily by querying database dictionary views. The collected data is then used to generate the Oracle Database Sensitive Data Assessment Report in HTML and CSV formats.

Can be run on any machine: PC, laptop, or server (not limited to the database server).

Copy sample configuration file:

.../dbsat/Discover/conf/sample_dbsat.config

Keep the [Database], [Discovery Parameters], and [Sensitive Categories] entries for the sections or the execution of the Discoverer will issue an error.

./dbsat discover [-n] -c <config_file> <output_file>
./dbsat discover -n -c Discover/conf/custom_dbsat.config PDB1

Oscanner

Oscanner is an Oracle assessment framework developed in Java. It has a plugin-based architecture and comes with a couple of plugins that currently do:

  • Sid Enumeration
  • Passwords tests (common & dictionary)
  • Enumerate Oracle version
  • Enumerate account roles
  • Enumerate account privileges
  • Enumerate account hashes
  • Enumerate audit information
  • Enumerate password policies
  • Enumerate database links
IP=x.x.x.x
oscanner -s $IP -P 1521