Overview
Oracle is probably the most popular database server out there, with the largest share of the market. It's used in most vertical market areas for a range of storage needs such as financial records, human resources, billing, and so on. One of the reasons for this is that Oracle was an earlier player in the RDBMS area and it provided versions of its database that ran on most operating systems; and it still does, although it seems its preferred OS of choice is moving away from Solaris toward Linux. In the wild you more often come across Oracle running on these platforms but there's also a good deal of Oracle running on HP-UX and AIX. It also seems with the explosion of e-Commerce a few years back that Oracle gained a lot of traction as the database of choice for web applications. This took the database one step closer to the hands of attackers and indeed, once Oracle came into the light from out of the backend of the backend, it gained more attention from the security side of things.
Oracle produces, in my opinion and as far as storing and
querying data is concerned, one of the best database servers available. It's
incredibly configurable and highly functional. There's an interface into the
RDBMS to suit almost any developer taste and for every business use that can be
dreamed of, it seems that Oracle has already provided the solution. All of this
comes at a cost, though. Each sliver of functionality provides a breadth of
attack surface; each solution a potential attack vector. The problem isn't just
getting to grips with the abundance of functionality to configure, however. The
code behind the RDBMS has historically been subject to a number of buffer
overflows, and other security problems such as PL/SQL Injection in default
packages and procedures have required patches in the past. All this said, as
long as your database server doesn't ever get attacked, and of course assuming
you're running Oracle, then you can long enjoy the great benefits this powerful
RDBMS provides. But let's face it: in today's world it's not a case of, "Will I
be attacked?" It's a case of "When will I be attacked?" So, if you are actually
concerned about your Oracle security or lack thereof, read on.
Examining the Oracle Architecture
We begin this chapter by examining the physical layout of
the database, such as the Oracle processes and how they interact with the
network. We move on to examining authentication and authorization and then move
to the logical layout of the database.
Oracle Processes and Oracle on the Network
This section describes the major components of Oracle and
their interaction with the network. We begin with perhaps the most crucial
network-facing component, the TNS Listener.
The Oracle TNS Listener
The TNS Listener is the hub of all communications in Oracle.
"TNS" stands for Transparent Network Substrate and this is the protocol that
Oracle uses to communicate between client and server. The TNS protocol is
described on the Ethereal web site at http://www.ethereal.com/docs/dfref/t/tns.html.
The TNS Listener responds to a number of commands such as
"version," "status," and "services," and when a database server is first
started, it registers with the TNS Listener using the service_register_NSGR
command. This lets the TNS Listener know that the database server is ready to
accept connections. Incidentally, although the service_register_NSGR command is
intended to be used locally the command can be sent over the network. In the
past there have been denial of service issues with this command that can kill
the TNS Listener.
When a client wishes to access the database server, the client
connects first to the Listener. The Listener replies back with a TCP port that
the client should connect to. The client connects to this port and then
authenticates to the database server. If, however, the database has been
configured in MTS, or Multi Threaded Server, mode then no port is assigned as
such and communication with the database server takes place over the same TCP
port that the Listener is listening on. The TNS Listener usually listens on TCP
port 1521 but, depending upon the version of Oracle and what applications have
been installed this port may be different, for example 1526. Regardless, the TNS
Listener can be configured to listen on any TCP port.
The TNS Listener is also integral to PL/SQL and external
procedures that we'll talk about later. Essentially when a PL/SQL procedure
calls an external procedure, the RDBMS connects to the Listener, and the
Listener launches a program called extproc to which the RDBMS connects. Extproc
loads the library and executes the required function. As you'll see later this
can be abused by attackers to run commands without a user ID or password.
If the XML Database is enabled—and it is by default in Oracle 9
and later—the TNS Listener holds open TCP port 2100 and 8080. The former allows
querying of XML data over the FTP protocol and the latter over HTTP. The
Listener proxies traffic on these ports to the RDBMS.
In versions of Oracle prior to 10g, the TNS Listener could be
administered remotely. What makes this particularly dangerous is the fact that
by default the Listener is installed without a password so it is possible for
anyone to administer the Listener. A password should be set to help secure the
system. The Listener Control Utility, lsnrctl, is the tool used to manage the
Listener. Using this tool it's possible, among other things, to query the
Listener for registered database services and retrieve status information:
C:\oracle\ora92\bin>lsnrctl LSNRCTL for 32-bit Windows: Version 9.2.0.1.0 - Production on 10-OCT-2004 17:31:49 Copyright (c) 1991, 2002, Oracle Corporation. All rights reserved. Welcome to LSNRCTL, type "help" for information. LSNRCTL> set current_listener 10.1.1.1 Current Listener is 192.168.0.34 LSNRCTL> status Connecting to (DESCRIPTION=(CONNECT_DATA=(SID=*)(SERVICE_NAME=10.1.1.1)) (ADDRESS=(PROTOCOL=TCP)(HOST=10.1.1.1)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for 32-bit Windows: Version 9.2.0.1.0 - Production Start Date 10-OCT-2004 16:12:50 Uptime 0 days 1 hr. 19 min. 23 sec Trace Level off Security ON SNMP OFF Listener Parameter File C:\oracle\ora92\network\admin\listener.ora Listener Log File C:\oracle\ora92\network\log\listener.log Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC0ipc))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=GLADIUS)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=GLADIUS)(PORT=8080)) (Presentation=HTTP)(Session=RAW)) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=GLADIUS)(PORT=2100)) (Presentation=FTP)(Session=RAW)) Services Summary... Service "ORAXP" has 1 instance(s). Instance "ORAXP", status UNKNOWN, has 1 handler(s) for this service... Service "PLSExtProc" has 1 instance(s). Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service... Service "oraxp.ngssoftware.com" has 1 instance(s). Instance "oraxp", status READY, has 1 handler(s) for this service... Service "oraxpXDB.ngssoftware.com" has 1 instance(s). Instance "oraxp", status READY, has 1 handler(s) for this service... The command completed successfully LSNRCTL>
As you can see this leaks all kinds of useful information. As an
interesting aside, if the Listener receives an invalid TNS packet, it will reply
with a packet similar to
IP Header
Length and version: 0x45
Type of service: 0x00
Total length: 94
Identifier: 61557
Flags: 0x4000
TTL: 128
Protocol: 6 (TCP)
Checksum: 0x884c
Source IP: 10.1.1.1
Dest IP: 10.1.1.2
TCP Header
Source port: 1521
Dest port: 3100
Sequence: 2627528132
ack: 759427443
Header length: 0x50
Flags: 0x18 (ACK PSH )
Window Size: 17450
Checksum: 0xe1e8
Urgent Pointer: 0
Raw Data
00 36 00 00 04 00 00 00 22 00 00 2a 28 44 45 53 ( 6 " *(DES)
43 52 49 50 54 49 4f 4e 3d 28 45 52 52 3d 31 31 (CRIPTION=(ERR=11)
35 33 29 28 56 53 4e 4e 55 4d 3d 31 35 31 30 30 (53)(VSNNUM=15100)
30 30 36 35 29 29 (0065)))
Looking at the value of VSNNUM, 151000065 in this case, we can derive the version of the server. When 151000065 is converted into hex we begin to see it better: 9001401. This equates to Oracle version 9.0.1.4.1. The following code can be used to query this information:
/************************************
/ Compile from a command line
/
/ C:\>cl /TC oraver.c /link wsock32.lib
/
*/
#include <stdio.h>
#include <windows.h>
#include <winsock.h>
int GetOracleVersion(void);
int StartWinsock(void);
struct hostent *he;
struct sockaddr_in s_sa;
int ListenerPort=1521;
char host[260]="";
unsigned char TNSPacket[200]=
"\x00\x46\x00\x00\x01\x00\x00\x00\x01\x37\x01\x2C\x00\x00\x08\x00"
"\x7F\xFF\x86\x0E\x00\x00\x01\x00\x00\x0C\x00\x3A\x00\x00\x07\xF8"
"\x0C\x0C\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x0A\x4C\x00\x00"
"\x00\x03\x00\x00\x00\x00\x00\x00\x00\x00";
int main(int argc, char *argv[])
{
unsigned int err=0;
if(argc == 1)
{
printf("\n\t*** OraVer ***");
printf("\n\n\tGets the Oracle version number.");
printf("\n\n\tC:\\>%s host [port]",argv[0]);
printf("\n\n\tDavid Litchfield\n\tdavidl@ngssoftware.com\n\t22th April 2003\n");
return 0;
}
strncpy(host,argv[1],256);
if(argc == 3)
ListenerPort = atoi(argv[2]);
err = StartWinsock();
if(err==0)
printf("Error starting Winsock.\n");
else
GetOracleVersion();
WSACleanup();
return 0;
}
int StartWinsock()
{
int err=0;
unsigned int addr;
WORD wVersionRequested;
WSADATA wsaData;
wVersionRequested = MAKEWORD( 2, 0 );
err = WSAStartup( wVersionRequested, &wsaData );
if ( err != 0 )
return 0;
if ( LOBYTE( wsaData.wVersion ) != 2 || HIBYTE( wsaData.wVersion ) != 0 )
return 0;
s_sa.sin_addr.s_addr=INADDR_ANY;
s_sa.sin_family=AF_INET;
if (isalpha(host[0]))
{
he = gethostbyname(host);
if(he == NULL)
{
printf("Failed to look up %s\n",host);
return 0;
}
memcpy(&s_sa.sin_addr,he->h_addr,he->h_length);
}
else
{
addr = inet_addr(host);
memcpy(&s_sa.sin_addr,&addr,4);
}
return 1;
}
int GetOracleVersion(void)
{
unsigned char resp[200]="";
unsigned char ver[8]="";
unsigned char h=0,l=0,p=0,q=0;
int snd=0,rcv=0,count=0;
SOCKET cli_sock;
char *ptr = NULL;
cli_sock=socket(AF_INET,SOCK_STREAM,0);
if (cli_sock==INVALID_SOCKET)
return printf("\nFailed to create the socket.\n");
s_sa.sin_port=htons((unsigned short)ListenerPort);
if (connect(cli_sock,(LPSOCKADDR)&s_sa,sizeof(s_sa))==SOCKET_ERROR)
{
printf("\nFailed to connect to the Listener.\n");
goto The_End;
}
snd=send(cli_sock, TNSPacket , 0x3A , 0);
snd=send(cli_sock, "NGSSoftware\x00" , 12 , 0);
rcv = recv(cli_sock,resp,196,0);
if(rcv == SOCKET_ERROR)
{
printf("\nThere was a receive error.\n");
goto The_End;
}
while(count < rcv)
{
if(resp[count]==0x00)
resp[count]=0x20;
count++;
}
ptr = strstr(resp,"(VSNNUM=");
if(!ptr)
{
printf("\nFailed to get the version.\n");
goto The_End;
}
ptr = ptr + 8;
count = atoi(ptr);
count = count << 4;
memmove(ver,&count,4);
h = ver[3] >> 4;
l = ver[3] << 4;
l = l >> 4;
p = ver[1] >> 4;
q = ver[0] >> 4;
printf("\nVersion of Oracle is %d.%d.%d.%d.%d\n",h,l,ver[2],p,q);
The_End:
closesocket(cli_sock);
return 0;
}
The Oracle RDBMS
Because we'll be talking about the Oracle RDBMS in depth in later sections, we'll simply cover a few of the more important details here. One of the major differences between Oracle running on Windows and Oracle running on UNIX-based platforms is the number of processes that combine to create the actual RDBMS. On Windows there is simply the oracle.exe process, but on UNIX platforms there are multiple processes each responsible for some part of functionality. Using ps we can list these processes:
$ ps -ef | grep oracle oracle 17749 1 0 11:26:13 ? 0:00 ora_pmon_orasidsol oracle 10109 1 0 Sep 18 ? 0:01 /u01/oracle/product/9.2.0/bin/tnslsnr listener920 -inherit oracle 17757 1 0 11:26:16 ? 0:01 ora_smon_orasidsol oracle 17759 1 0 11:26:17 ? 0:00 ora_reco_orasidsol oracle 17751 1 0 11:26:15 ? 0:01 ora_dbw0_orasidsol oracle 17753 1 0 11:26:16 ? 0:01 ora_lgwr_orasidsol oracle 17755 1 0 11:26:16 ? 0:05 ora_ckpt_orasidsol oracle 17762 1 0 11:30:59 ? 1:34 oracleorasidsol (LOCAL=NO)
Each RDBMS process has the name of the database SID appended to it—in this case orasidsol. The following list looks at each process and discusses what each does.
- The PMON process. This is the Process Monitor process and its job is to check if any of the other processes fail, and perform housekeeping tasks if one does such as free handles and so on.
- The SMON process. This is the System Monitor process and it is responsible for crash recovery if a database instance crashes.
- The RECO process. This is the Distributed Transaction Recovery process and handles any unresolved transactions.
- The DBWR process. This is the Database Writer process. There may be many such processes running. From the preceding ps listing we can see only one—numbered 0.
- The LGWR process. This is the Log Writer process and is responsible for handling redo logs.
- The CKPT process. This is the Checkpoint process and every so often it nudges the Database Writer process to flush its buffers.
All of these background processes are present on Windows, too; they're just all rolled up into the main oracle.exe process.
The oracleorasidsol process is what is termed the shadow or server process. It is actually this process that the client interacts with. Information about processes and sessions is stored in the V$PROCESS and V$SESSION tables in SYS schema.
The Oracle Intelligent Agent
This component is peripheral to the actual RDBMS but is integral to its management. The Intelligent Agent performs a number of roles, but probably its most significant function is to gather management and performance data, which can be queried through SNMP or Oracle's own proprietary protocols. The Agent listens on TCP port 1748, 1808, and 1809. As far as SNMP is concerned the port is configurable and may be the default of UDP 161 or often dbsnmp can be found listening for SNMP requests on 1161. In Oracle 10g dbsnmp has gone and in its place is the emagent.
Performance data can be queried remotely without having to present a username or password using the Oracle Enterprise Manager tool—specifically using the "Performance Manager" of the "Diagnostic Pack." This, needless to say, can provide attackers with a wealth of information about the remote system. For example, they could list all running processes, get memory usage, and so on.
Another of the tools provided by Oracle to manage the Intelligent Agent is the agentctl utility. Using this tool the Agent can be stopped, started, queried for its status, and blackouts started and stopped. A blackout essentially tells the Agent to stop gathering data or stop executing jobs. The agentctl utility is somewhat limited though; it can't really be used to query remote systems. However, it does use sockets on the local system to communicate with the Agent so a couple of strategic break points in a debugging session will reveal what traffic is actually being passed backward and forward. If you prefer to use port redirection tools for this kind of work this will do admirably, also. Whichever way you dump the packets you'll quickly notice that none of the communications are authenticated. This means, for example, an attacker could define blackouts or stop the Agent without having to present any username or password. The following code can be used to dump information from the Intelligent Agent:
#include <stdio.h>
#include <windows.h>
#include <winsock.h>
#define DBSNMPPORT 1748
int QueryDBSNMP(int in);
int StartWinsock(void);
struct sockaddr_in s_sa;
struct hostent *he;
unsigned int addr;
char host[260]="";
unsigned char Packet_1[]=
"\x00\x6A\x00\x00\x01\x00\x00\x00\x01\x38\x01\x2C\x00\x00\x08\x00"
"\x7F\xFF\x86\x0E\x00\x00\x01\x00\x00\x30\x00\x3A\x00\x00\x00\x64"
"\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\xB4\x00\x00"
"\x00\x0B\x00\x00\x00\x00\x00\x00\x00\x00\x28\x4F\x45\x4D\x5F\x4F"
"\x4D\x53\x3D\x28\x56\x45\x52\x53\x49\x4F\x4E\x3D\x28\x52\x45\x4C"
"\x45\x41\x53\x45\x3D\x39\x2E\x32\x2E\x30\x2E\x31\x2E\x30\x29\x28"
"\x52\x50\x43\x3D\x32\x2E\x30\x29\x29\x29\x54\x76\x10";
unsigned char Packet_2[]=
"\x00\x42\x00\x00\x06\x00\x00\x00\x00\x00\x28\x41\x44\x44\x52\x45"
"\x53\x53\x3D\x28\x50\x52\x4F\x54\x4F\x43\x4F\x4C\x3D\x74\x63\x70"
"\x29\x28\x48\x4F\x53\x54\x3D\x31\x36\x39\x2E\x32\x35\x34\x2E\x33"
"\x32\x2E\x31\x33\x33\x29\x28\x50\x4F\x52\x54\x3D\x31\x37\x34\x38"
"\x29\x29\x00\x3E\x00\x00\x06\x00\x00\x00\x00\x00\x20\x08\xFF\x03"
"\x01\x00\x12\x34\x34\x34\x34\x34\x78\x10\x10\x32\x10\x32\x10\x32"
"\x10\x32\x10\x32\x54\x76\x00\x78\x10\x32\x54\x76\x10\x00\x00\x80"
"\x01\x00\x00\x00\x00\x00\x84\x03\xBC\x02\x80\x02\x80\x02\x00\x00";
unsigned char Packet_3[]=
"\x00\x52\x00\x00\x06\x00\x00\x00\x00\x00\x44\x00\x00\x80\x02\x00"
"\x00\x00\x00\x04\x00\x00\xB0\x39\xD3\x00\x90\x00\x23\x00\x00\x00"
"\x44\x32\x44\x39\x46\x39\x35\x43\x38\x32\x42\x46\x2D\x30\x35\x45"
"\x44\x2D\x45\x30\x30\x30\x2D\x37\x32\x33\x30\x30\x38\x33\x31\x35"
"\x39\x42\x30\x02\x00\x30\x01\x01\x00\x01\x00\x00\x00\x00\x00\x00"
"\x00\x00\x00\x1E\x00\x00\x06\x00\x00\x00\x00\x00\x10\x00\x00\x80"
"\x05\x00\x00\x00\x00\x04\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00";
unsigned char Packet_4[]=
"\x00\x0A\x00\x00\x06\x00\x00\x00\x00\x40";
int main(int argc, char *argv[])
{
int count = 56;
if(argc != 3)
{
printf("\n\n\n\tOracle DBSNMP Tool\n\n\t");
printf("C:\\>%s host status|stop",argv[0]);
printf("\n\n\tDavid Litchfield\n\t");
printf("davidl@ngssoftware.com");
printf("\n\t4th June 2004\n\n\n\n");
return 0;
}
strncpy(host,argv[1],250);
if(!StartWinsock())
return printf("Error starting Winsock.\n");
if(stricmp(argv[2],"status")==0)
{
printf("\n\nStatus...\n\n");
Packet_3[69] = 0x38;
}
if(stricmp(argv[2],"stop")==0)
{
printf("\n\nStopping...\n\n");
Packet_3[69] = 0x37;
}
QueryDBSNMP(Packet_3[69]);
WSACleanup();
return 0;
}
int StartWinsock()
{
int err=0;
WORD wVersionRequested;
WSADATA wsaData;
wVersionRequested = MAKEWORD( 2, 0 );
err = WSAStartup( wVersionRequested, &wsaData );
if (err != 0)
return 0;
if (LOBYTE(wsaData.wVersion) !=2 || HIBYTE(wsaData.wVersion) !=0)
{
WSACleanup();
return 0;
}
if (isalpha(host[0]))
{
he = gethostbyname(host);
s_sa.sin_addr.s_addr=INADDR_ANY;
s_sa.sin_family=AF_INET;
memcpy(&s_sa.sin_addr,he->h_addr,he->h_length);
}
else
{
addr = inet_addr(host);
s_sa.sin_addr.s_addr=INADDR_ANY;
s_sa.sin_family=AF_INET;
memcpy(&s_sa.sin_addr,&addr,4);
he = (struct hostent *)1;
}
if (he == NULL)
return 0;
return 1;
}
int QueryDBSNMP(int in)
{
unsigned char resp[1600]="";
int snd=0,rcv=0,count=0;
unsigned int ttlbytes=0;
unsigned int to=2000;
struct sockaddr_in cli_addr;
SOCKET cli_sock;
cli_sock=socket(AF_INET,SOCK_STREAM,0);
if (cli_sock==INVALID_SOCKET)
{
printf("socket error.\n");
return 0;
}
cli_addr.sin_family=AF_INET;
cli_addr.sin_addr.s_addr=INADDR_ANY;
cli_addr.sin_port=htons((unsigned short)0);
//setsockopt(cli_sock,SOL_SOCKET,SO_RCVTIMEO,(char *)&to,sizeof(unsigned int));
if (bind(cli_sock,(LPSOCKADDR)&cli_addr,sizeof(cli_addr))==SOCKET_ERROR)
{
closesocket(cli_sock);
printf("bind error");
return 0;
}
s_sa.sin_port=htons((unsigned short)DBSNMPPORT);
if (connect(cli_sock,(LPSOCKADDR)&s_sa,sizeof(s_sa))==SOCKET_ERROR)
{
closesocket(cli_sock);
printf("Connect error");
return 0;
}
snd=send(cli_sock, Packet_1 , 0x6A , 0);
rcv = recv(cli_sock,resp,1500,0);
if(rcv == SOCKET_ERROR)
{
closesocket(cli_sock);
printf("recv error.\n");
return 0;
}
PrintResponse(rcv,resp);
snd=send(cli_sock, Packet_2 , 0x80 , 0);
rcv = recv(cli_sock,resp,1500,0);
if(rcv == SOCKET_ERROR)
{
closesocket(cli_sock);
printf("recv error.\n");
return 0;
}
PrintResponse(rcv,resp);
snd=send(cli_sock, Packet_3 , 0x70 , 0);
rcv = recv(cli_sock,resp,1500,0);
if(rcv == SOCKET_ERROR)
{
closesocket(cli_sock);
printf("recv error.\n");
return 0;
}
PrintResponse(rcv,resp);
if(in == 0x37)
{
closesocket(cli_sock);
return printf("Oracle Intelligent Agent has stopped");
}
snd=send(cli_sock, Packet_4 , 0x0A , 0);
rcv = recv(cli_sock,resp,1500,0);
if(rcv == SOCKET_ERROR)
{
closesocket(cli_sock);
printf("recv error.\n");
return 0;
}
closesocket(cli_sock);
return 0;
}
int PrintResponse(int size, unsigned char *ptr)
{
int count = 0;
int chk = 0;
int sp = 0;
printf("%.4X ",count);
while(count < size)
{
if(count % 16 == 0 && count > 0)
{
printf(" ");
chk = count;
count = count - 16;
while(count < chk)
{
if(ptr[count]<0x20)
printf(".");
else
printf("%c",ptr[count]);
count ++;
}
printf("\n%.4X ",count);
}
printf("%.2X ",ptr[count]);
count ++;
}
count = count - chk;
count = 17 - count;
while(sp < count)
{
printf(" ");
sp++;
}
count = chk;
while(count < size)
{
if(ptr[count]<0x20)
printf(".");
else
printf("%c",ptr[count]);
count ++;
}
printf("\n\n\n\n");
return 0;
}
The Intelligent Agent often needs to communicate with the database server and requires a user account and password for the RDBMS. By default this is DBSNMP/DBSNMP—one of the better known default Oracle accounts. When performing a security audit of an Oracle database server, I often find that all the default passwords have been changed except this one. The reason is that if you change the password on the database server, snmp traps don't work; you need to inform the Intelligent Agent of the password change, too. It seems that this is often too much hassle and is left in its default state. To properly change the password for the dbsnmp account you'll need to edit the snmp_rw.ora file as well. You can find this file on the ORACLE_HOME/network/admin directory. Add the following:
SNMP.CONNECT.SID.NAME=dbsnmp SNMP.CONNECT.SID.PASSWORD=password
"SID" is the SID of the database server. You can get this from the snmp_ro.ora file in the same directory. Once done, change the password for DBSNMP in Oracle.
Note—never change a password using the ALTER USER command. The reason you shouldn't do this is because the SQL is logged if tracing is on, meaning that the password is also logged in clear text. Use the password command in SQL*Plus instead. In this case an encrypted version of the password is logged making it more secure against prying eyes.
Oracle Authentication and Authorization
Oracle supports two kinds of accounts: database accounts and operating system accounts. Operating system accounts are authenticated externally by the operating system and are generally preceded with OP$, whereas database accounts are authenticated against the database server. A number of users are created by default when the database is installed; some of these are integral to the correct operation of the database whereas others are simply created because a package has been installed. The most important database login on an Oracle server is the SYS login. SYS is god as far as the database is concerned and can be likened to the root account on UNIX systems or Administrator on Windows. SYS is installed with a default password of CHANGE_ON_INSTALL, although, as of 10g, the user is prompted for a password to assign—which is good (various components that you install can define default usernames and passwords—Appendix C includes a list of more than 600 default account names and passwords). Another key account is SYSTEM. This is just as powerful as SYS and has a default password of MANAGER. Incidentally, passwords in Oracle are converted to uppercase making them easier to brute force if one can get a hold of the password hashes. Details such as usernames and passwords are stored in the SYS.USER$ table.
SQL> select name,password from sys.user$ where type#=1; NAME PASSWORD ------------------------------ ------------------------------ SYS 2696A092833AFD9F SYSTEM ED58B07310B19002 OUTLN 4A3BA55E08595C81 DIP CE4A36B8E06CA59C DMSYS BFBA5A553FD9E28A DBSNMP E066D214D5421CCC WMSYS 7C9BA362F8314299 EXFSYS 66F4EF5650C20355 ORDSYS 7EFA02EC7EA6B86F ORDPLUGINS 88A2B2C183431F00 SI_INFORMTN_SCHEMA 84B8CBCA4D477FA3 MDSYS 72979A94BAD2AF80 CTXSYS 71E687F036AD56E5 OLAPSYS 3FB8EF9DB538647C WK_TEST 29802572EB547DBF XDB 88D8364765FCE6AF ANONYMOUS anonymous SYSMAN 447B729161192C24 MDDATA DF02A496267DEE66 WKSYS 69ED49EE1851900D WKPROXY B97545C4DD2ABE54 MGMT_VIEW B7A76767C5DB2BFD SCOTT F894844C34402B67 23 rows selected.
Both SYS and SYSTEM are DBA privileged accounts but on a typical system you'll also find at least a few more DBAs—namely MDSYS, CTXSYS, WKSYS, and SYSMAN. You can list all DBAs with the following query:
SQL> select distinct a.name from sys.user$ a, sys.sysauth$ b where a.user#=b.grantee# and b.privilege#=4; NAME ----------------------------- CTXSYS SYS SYSMAN SYSTEM WKSYS
(If you know a bit about Oracle and are wondering why I'm not using the DBA_USERS and DBA_ROLE_PRIVS views, see the last chapter in the Oracle section—you can't trust views.)
This is enough on users and roles at the moment. Let's look at how database users are authenticated.
Database Authentication
When a client authenticates to the server, rather than sending a password across the wire in clear text like most other RDBMSes Oracle chooses to encrypt it. Here's how the authentication process works. First, the client connects to the TNS Listener and requests access to the RDBMS, specifying its SID. Provided the SID is valid the Listener responds with a TCP port and redirects the client to this port. On connecting to this port, to an Oracle shadow process, the client presents their username:
CLIENT to SERVER 00 c4 00 00 06 00 00 00 00 00 03 76 02 e0 91 d3 ( v ) 00 06 00 00 00 01 00 00 00 cc a2 12 00 04 00 00 ( ) 00 9c a0 12 00 8c a4 12 00 06 73 79 73 74 65 6d ( system) 0d 00 00 00 0d 41 55 54 48 5f 54 45 52 4d 49 4e ( AUTH_TERMIN) 41 4c 07 00 00 00 07 47 4c 41 44 49 55 53 00 00 (AL GLADIUS ) 00 00 0f 00 00 00 0f 41 55 54 48 5f 50 52 4f 47 ( AUTH_PROG) 52 41 4d 5f 4e 4d 0b 00 00 00 0b 73 71 6c 70 6c (RAM_NM sqlpl) 75 73 2e 65 78 65 00 00 00 00 0c 00 00 00 0c 41 (us.exe A) 55 54 48 5f 4d 41 43 48 49 4e 45 12 00 00 00 12 (UTH_MACHINE ) 57 4f 52 4b 47 52 4f 55 50 5c 47 4c 41 44 49 55 (WORKGROUP\GLADIU) 53 00 00 00 00 00 08 00 00 00 08 41 55 54 48 5f (S AUTH_) 50 49 44 08 00 00 00 08 38 37 32 3a 32 34 33 36 (PID 872:2436) 00 00 00 00 ( )
Here you can see the client is attempting to authenticate as the "SYSTEM" user. If the user exists on the remote system, the server responds with a ses-sion key:
SERVER TO CLIENT 00 87 00 00 06 00 00 00 00 00 08 01 00 0c 00 00 ( ) 00 0c 41 55 54 48 5f 53 45 53 53 4b 45 59 20 00 ( AUTH_SESSKEY ) 00 00 20 39 31 33 42 36 46 38 36 37 37 30 39 44 ( 913B6F867709D) 34 34 35 39 34 34 34 41 32 41 36 45 31 31 43 44 (4459444A2A6E11CD) 45 38 45 00 00 00 00 04 01 00 00 00 00 00 00 00 (E8E ) 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ( ) 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ( ) 00 00 02 00 00 00 00 00 00 00 00 00 00 00 00 00 ( ) 00 00 00 00 00 00 00 ( )
Note that if the user does not exist on the remote server, no session key is issued. This is useful for an attacker. He or she can work out whether or not a given account exists on the server. (See the "Oracle Auditing" section at the end of this chapter to catch attacks like this.) Anyway, assuming the user does exist, the session key is sent back to the client. The client uses this session key to encrypt its password and send it back to the server for validation.
03 26 00 00 06 00 00 00 00 00 03 73 03 e0 91 d3 ( & s ) 00 06 00 00 00 01 01 00 00 e8 b1 12 00 07 00 00 ( ) 00 a0 ae 12 00 2c b4 12 00 06 73 79 73 74 65 6d ( , system) 0d 00 00 00 0d 41 55 54 48 5f 50 41 53 53 57 4f ( AUTH_PASSWO) 52 44 20 00 00 00 20 36 37 41 41 42 30 37 46 38 (RD 67AAB07F8) 45 32 41 32 46 33 42 45 44 41 45 43 32 33 31 42 (E2A2F3BEDAEC231B) 36 42 32 41 30 35 30 00 00 00 00 0d 00 00 00 0d (6B2A050 )
Once authenticated to the database server, a user's actions are controlled using authorization. In Oracle, authorization is dictated by system and object privileges.
Authorization
System privileges define what a user can do to the database, whereas object privileges define what a user can do to database objects such as tables and procedures. For example, there's a system privilege that, if granted, allows a user to create procedures and once created, object privileges can be granted that allow another user to execute it. There are 173 system privileges in Oracle 10g—these can be listed with the following query:
SQL> select distinct name from sys.system_privilege_map;
As far as object privileges go there are far fewer defined—23:
SQL> select distinct name from sys.table_privilege_map;
Key System Privileges
There are a few system privileges, which if granted, can be abused to gain complete control of the database server. Let's look at a few.
EXECUTE ANY PROCEDURE
This gives the grantee the ability to run any procedure on the server. We'll talk more about procedures later on but suffice to say this is one of the most powerful system privileges. If granted, the user can become a DBA in the blink of an eye.
SELECT ANY DICTIONARY
Any data in the database that is integral to the operation of the database are stored in a bunch of tables collectively known as the Oracle Data Dictionary. These tables are stored in the SYS schema. If users have the SELECT ANY DICTIONARY privilege it means that they can select from any of these tables. For example they could select password hashes from the SYS.USER$ table. The DBSNMP account is a good case study for this—it's not a DBA but it does have this system privilege. It's an easy task for DBSNMP to get DBA privileges due to this.
Oracle Auditing
This section discusses Oracle auditing—auditing in the sense of tracking what users are doing and when. Unless you check whether auditing is on or not, you're never going to know whether "big brother" is watching—if you're attacking the system at least. If you're defending a system, then auditing should be on—but not necessarily for everything. For a busy database server if every action is audited, the audit trail can become massive. At a minimum, failed and successful log on attempts should be audited as well as access to the audit trail itself.
Oracle can either log to the file system or to a database table and this is controlled with an entry in the init.ora file. To log audit information to the database, add an entry like
audit_trail = db
To log audit information to the file system, change the "db" to "os". If audit_trail is set to "none," then no auditing is performed. If logging occurs in the database, then events are written to the SYS.AUD$ table in the data dictionary. This table stands out from others in the dictionary because rows can be deleted from it. This has significance to the validity or accuracy of the log if access to the SYS.AUD$ is not restricted, and audited.
Once auditing is enabled you need to configure what actions, events, and so on should be audited. For a full list of what can be logged refer to the Oracle documentation, but here I'll show how to turn on auditing for failed and successful log in attempts and how to protect the AUD$ table itself.
Log on to the system with DBA privileges, or at least an account that has either the AUDIT ANY or AUDIT SYSTEM privilege and issue the following statement:
AUDIT INSERT, UPDATE, DELETE ON SYS.AUD$ BY ACCESS;
This protects access to the audit trail so if someone attempts to manipulate it, the access itself will be logged. Once done, then issue
AUDIT CREATE SESSION;
This will turn on logging for log on attempts.
When attacking a system it is often useful to know what actions and so on are being audited because this will usually point you toward the "valuable" information. For example, all access to the HR.WAGES table might be audited. To see a list of what tables are audited, run the following query:
SELECT O.NAME FROM SYS.OBJ$ O, SYS.TAB$ T WHERE T.AUDIT$ LIKE '%A%' AND O.OBJ#=T.OBJ#
What's happening here? Well, the SYS.TAB$ table contains a column called AUDIT$. This column is a varchar(38) with each varchar being a dash or an A:
------AA----AA------AA----------
Depending upon where an A or a dash occurs defines what action is audited, whether it be a SELECT, UPDATE, INSERT, and so on.
If execute is audited for a procedure, this can be checked by running
SELECT O.NAME FROM SYS.OBJ$ O, SYS.PROCEDURE$ P WHERE P.AUDIT$ LIKE '%S%' AND O.OBJ# = P.OBJ#Download the book.
