systemtap : A mini oracle db firewall

Mahmoud Hatem linux, SYSTEMTAP

As the blog post name suggest this article is about writing a “mini” program for displaying and filtering statement execution issued from a specific IP address (parameter 1) on a specific database (parameter 2). This is heavily based on the great work done by Luca Canali here (Must read !).

Let’s begin by an example :

UPDATE 14/10/2016 : ORACLE 11.2.0.4 / stap 3.0 / OEL 6 / Kernel UEK4

I configured the script to prohibit “drop” and “create” statement. (Hard coded).

Launch the script :

stap -v mini_db_firewall.stp -g -x 6790 “172.0.0.8” “testdb”

  • 6790 is my listener pid
  • “172.0.0.8” Source ip address to monitor
  • “testdb” database service name to monitor

Capture 3

From a remote machine with IP address 172.0.0.8 :
Capture 4

Like showed the script track session creation from the specified IP address to the specified service name.When a connection is established, all parsed sql statement will be displayed and “create” and “drop” statement will be blocked.You can for example audit/block suspicious DBA activities “in stealth mode” (No audit parameters set in the database and no need for a connection either) without letting them noticing anything.

The first question when writing this script was how to find the oracle process pid that a session will be assigned when connecting through the listener ? For this we will need to take a closer look at how the listener will spawn the oracle process for us.(I will treat only the case of a dedicated connection).

Let’s use “strace” on the listener : strace -f -p 6790Capture 1

So basically this is what happened :

  1. The listener with pid 6790 received a connection request : accept(14, {sa_family=AF_INET, sin_port=htons(56049), sin_addr=inet_addr(“
  2. The listened forked a new child process : clone(Process 17864 attached and put it self in a wait for the child process to exit.wait4(17864, 
  3. The child process with pid 17864 forked another child process [pid 17864] clone(Process 17865 attached  and then exited [pid 17864] exit_group(0) so that the listener resume activity.[pid  6790] <… wait4 resumed> [{WIFEXITED(s) 
  4. The child process with pid 17865 will execute the oracle executable [pid 17865] execve(“/app/oracle/12.1.0/dbhome_1/bin/oracle”, [“oracletestdb”

This flow of execution was already well explained here

So now that we know how the oracle process is created and how to get the PID  we can attach to the created process using systemtap to track statement parsing.Displaying and filtering sql statement  is based on Luca Canali work already well explained.

I used a probe on the function “opiinfv” to get the connection info and check for the requested service name.

DOWNLOAD : mini_db_firewall.stp

That’s it 😀

 

 

5 thoughts on “systemtap : A mini oracle db firewall”

  1. Hatem what is ther version of stap and kernel , I got error of using ctime function as follows :

    semantic error: arity mismatch found (function ‘ctime’ takes 1 args): identifier ‘ctime’ at mini_db_script.stp:26:72
    source: printf(“User connected from %s on %s\n”,inet_get_ip_source(sock),ctime());
    ^
    identifier ‘ctime’ at /usr/share/systemtap/tapset/linux/ctime.stp:46:10
    source: function ctime:string(epochsecs:long)

    Reply

    • Hi Adam,

      You are right , i should have precised the different version used (Kernel,database,os,stap) as the result of the script depend heavily on them (function name,argument/register value,etc). Sadly , i don’t remember the test env (database version,kernel,etc) i used when writing this script so i adjusted it (changing register in this case) to work on a new env : database 11.2.0.4 / kernel uek4 / OEL6 / stap 3.0.

      Beside, this script contain also part of code that i used in another blog post “https://mahmoudhatem.wordpress.com/2016/04/26/simple-oracle-data-masking-using-systemtap/” as an example of data masking (it may not work very well but it’s just for demonstration purpose)

      If you need any help please ask 🙂

      Reply

  2. Hi Thanks for clarification it is very important , there is a lot of problem with OEL7 and UEK4 + Oracle 12c.
    I also change your script little bit (I will send you when I put on my blog with all yours Copyrights etc.) , but what I see that there is lot more problem with tracing tools in new kernel versions.

    Reply

Leave a comment