This is a tutorial on Database security covering Oracle and Sql Server. We not only teach you about how to secure databases but also how they function.

Oradebug

This is a program that is available within sqlplus. We first log in as system and after giving our password we are not allowed to run the command oradebug as we do not have sufficient privileges.

We then run the following command to get sysdba privileges.

SQL> connect / as sysdba

We could have also run the above command as

SQL> connect sys/sys as sysdba

The first command we run is to see the help of Oradebug, We run this as

SQL> oradebug help

This shows us two screen full’s of all the help options available to us.

Let us now use the oradebug utility to turn tracing on. We first run the following sql command.

SQL> select program , spid from v$process order by program;

Tables that begin with v$, x$ are special tables that are really not tables but give us a peek into the internals of oracle. The v$process table gives us a list of programs that oracle has executed at this point in time. These programs will not show up within task manager at all.

We see about 24 processes running at this point in time. The spid field is the internal process id and in our case the name SHAD appears 6 times. We start a new copy of sql plus, log in as system, and then when we run the same program, the process shad appears 7 times. Thus each time we start a new copy of sql plus, a fresh process of SHAD is created. We note the spid of the latest SHAD. In our case it is 1796.

SQL> oradebug setospid 1796

Oracle pid: 20, Windows thread id: 1796, image: ORACLE.EXE (SHAD)

We then use oradebug to set the spid to the newly started copy of sql plus.

SQL> oradebug event 10046 trace name context forever, level 12

Statement processed.

We then set the trace on by using trace level 12. There are 4 levels of tracing , 1 4 , 8 and 12. The level 1 is the least or basic where say bind variables are displayed as :b1. Level 4 is level 1 plus the actual data values of bind variables. Level 8 is level 1 plus wait events. Level 12 is a combo of all the above 3 levels

Now that we have set the tracing on, all this data has to be stored in some trc file. To find out the name of this file we run the following command.

SQL> oradebug tracefile_name

d:\oracle\product\10.2.0\admin\orcl\udump\orcl_ora_1796.trc

We have installed oracle in D drive and this file is about 1 k large and contains some basic data. We then create a table in the other sql plus copy that we just started.

SQL> create table a1 ( vno number);

When we see the trace file, it size is more than 250k and all that we ran was a simple create table command. A simple select statement increases the size by only 2k. To set the trace off we run the following command.

SQL> oradebug event 10046 trace name context off

This will stop the tracing and any commands we write will not get reflected in the trace file. We should use the program tkprof to format the trace files as they get very large.

SQL> oradebug setmypid

If we are setting the pid to our program we do not have to know its pid and can use setmypid instead.

SQL> oradebug dump systemstate 10

The above dumps the entire system dump which gives us a 2MB file.