Wednesday, September 16, 2009

yes i do oracle

So, one of the things I have had to do recently is partially reverse engineer some data structures in a couple oracle databases, Now this would have been a trivial task if one of the following were available:

(if you just want code examples scroll down =)

1An ER diagram (document your shit people always document it)
2 Foreign Key structures (for those that don't know this involves telling oracle, or another standards compliant RDBMS, information about what is in the columns in the table, that for example Column A in table beta is the foreign key for table gama, and is contained withing column G this means that any data in column G must be found in column A and that there is a relationship between those two table.
a simplistic Banking example: lets say that data about who you are (birthday, name, address, all that good ol personal shit)is in one table that also contains your social security number and that your soc is used in the table that holds all the data about your accounts, account type, current balance, APR for the account, all stored neatly in one table. The soc in the you table is a foreign key to the accounts table, because you cant have an entry in the accounts table without there being a user in the you table to match a soc to. (I know there are holes in this, not the least being that there wouldn't be one table for any of these things and that you really wouldn't use a soc as a foreign key, but it is a unique number, and if you can see these holes what are you doing reading this section =P )

If your database has either then this probably isn't for you, instead use those resources, its faster, cheaper, and less prone to both pissing people off (explained later) and error then the method i am going to describe This is for those times when you are dealing with a vendor system that you need to extract data out of in a way that the system was never intended to give. A tenant of ALL RDBMS is that you have relations between the data, now this requires you to have some semblance of intelligence to use, and you need to understand the app you are going after, as random relations mean nothing a similar method (which I will detail in a later post) should be used to begin your search, finding some table that contains data you know (be it user names, a part number, a description etc, to give you your starting point, The following is a slightly obfuscated example for how you can check all the columns in one table (table A) against all the columns in another table (table B)



select distinct 'select '''|| trim(t1.table_name) || ':'|| trim(t1.column_name)||
':::'|| trim(t2.table_name) || ':'|| trim(t2.column_name)||
''' from dual;'|| chr(10)||'select count(*) '||
substr(trim(t1.column_name),1,15) ||'_'||
substr(trim(t2.column_name),1,14) ||' from ' || trim(t1.owner) ||'.'||
trim(t1.table_name)||' t1, '|| trim(t2.owner) ||
'.'||trim(t2.table_name)||' t2 where t1.'||trim(t1.column_name) ||
' =t2.'||trim(t2.column_name) ||' ;'
from all_tab_columns t1, all_tab_columns t2
where t1.owner = 'SCHEMATABLEA' and
t2.owner = 'SCHEMATABLEB' and
t1.table_name = 'NAMETABLEA' and
t2.table_name = 'NAMETABLEB' and
t1.data_type = 'NUMBER' and
t2.data_type = 'NUMBER'


The above assumes you can get access to the ALL_tab_columns table, which is a table detailing every column in every table for all tables you have been granted access to, this might vary in other distributions but i suspect you will find a similar table of information.

In the where section replace SCHEMATABLEA with the schema owner of the first table you want to compare, and SCHEMATABLEB with the owner of the second, nametablea and nametableb should be self explanitory. the last two restrict the columns looked at, in this case it assumes that the relationship columns in the tables will only be numeric, and not a text or other field.

this creates an output similar to this:


select 'REPCAT$_REPOBJECT:FLAG:::LOGSTDBY$SKIP:ACTIVE' from dual;
select count(*) FLAG_ACTIVE
from SYSTEM.REPCAT$_REPOBJECT t1, SYSTEM.LOGSTDBY$SKIP t2
where t1.FLAG =t2.ACTIVE ;
select 'REPCAT$_REPOBJECT:FLAG:::LOGSTDBY$SKIP:ERROR' from dual;
select count(*) FLAG_ERROR
from SYSTEM.REPCAT$_REPOBJECT t1, SYSTEM.LOGSTDBY$SKIP t2
where t1.FLAG =t2.ERROR ;
select 'REPCAT$_REPOBJECT:FLAG:::LOGSTDBY$SKIP:NAME' from dual;
select count(*) FLAG_NAME
from SYSTEM.REPCAT$_REPOBJECT t1, SYSTEM.LOGSTDBY$SKIP t2
where t1.FLAG =t2.NAME ;

(NOTE* the sql here has had additional carriage returns inserted so that all will show on the page, I need to get better at this blogging stuff so i can insert scroll bars etc normal output would output 2 lines per table/column combination, each SQL statement on its own line)


save this output and run it against the database, (in this case I took 2 random oracle system tables and ran the query against them.
It will in turn generate output similar to this:





'REPCAT$_REPOBJECT:FLAG:::LOGSTDBY$SKIP:NAME'
---------------------------------------------
REPCAT$_REPOBJECT:FLAG:::LOGSTDBY$SKIP:NAME

1 rows selected


FLAG_NAME
----------------------
0

1 rows selected


6 lines of data for each item you select, the upper output simply tells you the tablename:columnname for each table, separated by a triple ':'
the second set of output is a count of how many rows matched, in this case 0 you will want to grep for non 0 numbers in the output in some fashion.

This is a starter post for how to actually RE a database, the starting tools if you will, the above query can be expanded, simply leaving out one of the table names searches against all tables in that schema. This is the catch with people wanting to kill you, you can easily end up running 100k or even millions of queries against the database if it is large. this may seem like a good idea at first, but performance issues will creep in to other users, do this off hours and study the outputs during

adios for now, tomorrow.. hopefully a better explanation of this code and how it really works

1 comment:

  1. yeah, need to fix the formatting on this... any clues?

    ReplyDelete