How to list files from directory using external table in 12c

I’ve wrote about privilege escalation with external tables in this post:
https://blog.ora-600.pl/2014/12/23/simple-technics-of-privilege-escalation-part2-dbasysdba/

This time we will try to list all files within directory object, to which we have no EXECUTE privs. This is possible due to great new feature of database 12c that allows to use metacharacters to match multiple files in external table.

I have database 12c:

SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE	12.1.0.2.0	Production
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

And I’ll create a sample user with limited privileges:

SQL> create user rico identified by kaboom;

User created.

SQL> create or replace directory dir_oracle as '/home/oracle';

Directory created.

SQL> grant connect, resource to rico;

Grant succeeded.

SQL> grant read, write on directory dir_oracle to rico;

Grant succeeded.

Now let’s try to list all files in directory DIR_ORACLE using just external tables.

First we will create external table which will try to read all files from this directory:

SQL> conn rico/kaboom
Connected.
SQL> get exttab
  1  CREATE TABLE gen_list (
  2	 txt varchar2(4000)
  3    )
  4    ORGANIZATION EXTERNAL (
  5	 TYPE ORACLE_LOADER
  6	 DEFAULT DIRECTORY dir_oracle
  7	 ACCESS PARAMETERS (
  8  	RECORDS DELIMITED BY NEWLINE
  9  	LOGFILE '.ls.log'
 10	 	DISABLE_DIRECTORY_LINK_CHECK
 11		FIELDS TERMINATED BY ','
 12   	MISSING FIELD VALUES ARE NULL
 13   	 (
 14   	   txt
 15   	 )
 16	  )
 17	  LOCATION ('*')
 18    )
 19*	  REJECT LIMIT 0
SQL> /

Table created.

Selecting from this table will probably cause error and return no values – but it will create a logfile – .ls.log
And this log we will read using another table 😉

SQL> ed
Wrote file afiedt.buf

  1  CREATE TABLE get_list (
  2	 txt varchar2(4000)
  3    )
  4    ORGANIZATION EXTERNAL (
  5	 TYPE ORACLE_LOADER
  6	 DEFAULT DIRECTORY dir_oracle
  7	 ACCESS PARAMETERS (
  8  	RECORDS DELIMITED BY NEWLINE
  9  	NOLOGFILE
 10	 	DISABLE_DIRECTORY_LINK_CHECK
 11		FIELDS TERMINATED BY ','
 12   	MISSING FIELD VALUES ARE NULL
 13   	 (
 14   	   txt
 15   	 )
 16	  )
 17	  LOCATION ('.ls.log')
 18    )
 19*	  REJECT LIMIT 0
SQL> /

Table created.

SQL> select * from get_list;

TXT
--------------------------------------------------------------------------------


 LOG file opened at 02/10/16 23:32:54

KUP-05004:   Warning: Intra source concurrency disabled because parallel select
was not requested.


Location specification * has a wild card
  Matching files are:
    .Xauthority

TXT
--------------------------------------------------------------------------------
    .bash_history
    .bash_logout
    .bash_profile
    .bashrc
    .grid
    .kshrc
    .lesshst
    .ls.log
    .orcl
    afiedt.buf
    aggr.sql

TXT
--------------------------------------------------------------------------------
    alter.sql
    bash_profile.txt
    bh.sql
    count.sql
    dbm.env
    disks.sql
    ehcc.sql
    exttab.sql
    fmw_12.1.3.0.0_infrastructure.jar
    fmw_12.1.3.0.0_odi.jar
    grid.env

TXT
--------------------------------------------------------------------------------
    grid.env.bck
    hardcopy.0
    hr_spatial.dmp
    hr_spatial.log
    im_off.sql
    im_on.sql
    im_query1.sql
    im_query2.sql
    im_status.sql
    install2015-04-23_11-44-25.log
    install2015-04-23_11-57-20.log

TXT
--------------------------------------------------------------------------------
    java0.log
    join_group_small.sql
    my_init.ora
    ne
    netstat
    off_off.sql
    off_on.sql
    oratop.RDBMS_12.1_LINUX_X64
    orcl.env
    orcl.env.bck
    orcl1.env

TXT
--------------------------------------------------------------------------------
    orcl2.env
    orcl3.env
    orcl4.env
    plan.sql
    planoff.sql
    planon.sql
    ps_q01.sql
    ps_q01plan.sql
    q.sql
    q01.sql
    sales_six.sql

TXT
--------------------------------------------------------------------------------
    saprse_dg.sql
    sesja1.sql
    sesja2.sql
    sesja3.sql
    set_cluster_interconnect.lst
    set_cluster_interconnect.wk1
    size.sql
    small_Table.sql
    snapshot.sql
    spid.sql
    sql_stat.sql

TXT
--------------------------------------------------------------------------------
    sqldeveloper-4.1.2.20.64-x64.zip
    ssb.tar
    tables.sql
    test.xml
    union.sql
    v
    val.sql
    with.sql
    zap.sql
    zap1.sql
    zap2.sql

TXT
--------------------------------------------------------------------------------
    zap4.sql
    zap5.sql
    zap6.sql

Field Definitions for table GEN_LIST
  Record format DELIMITED BY NEWLINE
  Data in file has same endianness as the platform
  Rows with all null fields are accepted

  Fields in Data Source:


TXT
--------------------------------------------------------------------------------
    TXT 			    CHAR (255)
      Terminated by "
      Trim whitespace same as SQL Loader

That’s it 😉
So use external tables with attention. They are powerful and can be dangerous.