Entries tagged with “DBA”.
Did you find what you wanted?
Mon 29 Jul 2013
Posted by Slavik under DBA, Oracle
Comments Off on New interesting feature of Oracle 12c
This looks like an interesting feature of Oracle 12c. I’m still not sure about the security implications but it does say interesting things about pure network monitoring security tools. Now, more than ever, what you see on the network can be something completely different than what runs on the database. So, you can see a statement like ‘select * from dual’ on the network but in the database it will be translated to ‘select * from credit_cards’…
Again, it’s early days of Oracle 12c and we all need to process a lot to understand the security implications of new features as well as the new shiny security features Oracle 12c offers.
Wed 5 Dec 2012
Posted by Slavik under Data Discovery, DBA, Oracle
Comments Off on Poor man’s data discovery for Oracle
I’m sure we all did something similar once or twice in our DBA lives. I had to create a simple script to perform regular expression based data discovery for Oracle. This script will be used as a check in our McAfee Database Vulnerability Manager. We do support data discovery directly in the tool but the advantage of such a script is that all the data is processed directly in the database without pulling it over the network to the discovery tool.
First, we need to create the tables to hold the discovered data:
CREATE SEQUENCE seq_data_discovery MAXVALUE 9999999999 CYCLE
CREATE TABLE data_discovery
CONSTRAINT data_discovery_pk PRIMARY KEY (id)
CREATE TABLE data_discovery_rows
CONSTRAINT data_discovery_rows_fk FOREIGN KEY (id) REFERENCES data_discovery(id)
Next, comes the stored procedure to populate the data using simple PL/SQL.
CREATE OR REPLACE procedure discover_data(p_regex IN VARCHAR2, p_owner IN VARCHAR2 := '%',
p_table_name IN VARCHAR2 := '%', p_limit IN NUMBER := NULL)
-- Discover data based on the given parameters.
-- We iterate on relevant column types for the given owners and tables and match
-- the contents to the given regex returning data into a table to the specified limit
-- Might want to handle varios XML types as well
FOR r_col IN (
SELECT tc.owner, tc.table_name, tc.column_name, tc.data_type
FROM all_tab_columns tc, all_tables t
WHERE tc.owner LIKE p_owner AND
tc.table_name LIKE p_table_name AND
tc.table_name NOT LIKE 'DATA_DISCOVERY%' AND
tc.owner = t.owner AND
tc.table_name = t.table_name AND
tc.data_type IN ('VARCHAR2', 'NVARCHAR2', 'NUMBER', 'CHAR', 'NCHAR', 'CLOB', 'NCLOB')
ORDER BY owner, table_name, column_id) LOOP
-- Insert the header
INSERT INTO data_discovery
VALUES (seq_data_discovery.nextval, r_col.owner, r_col.table_name, r_col.column_name)
RETURNING id INTO l_id;
l_stmt := 'INSERT INTO data_discovery_rows SELECT ' || TO_CHAR(l_id) || ', rowid, $col FROM "' ||
r_col.owner || '"."' || r_col.table_name ||
'" WHERE REGEXP_LIKE($col, :1, ''i'')';
IF p_limit IS NOT NULL
l_stmt := l_stmt || ' AND rownum <= :2';
IF r_col.data_type IN ('VARCHAR2', 'NVARCHAR2', 'CHAR', 'NCHAR')
l_stmt := REPLACE(l_stmt, '$col', '"' || r_col.column_name || '"');
ELSIF r_col.data_type IN ('CLOB', 'NCLOB')
l_stmt := REPLACE(l_stmt, '$col', 'dbms_lob.substr("' || r_col.column_name || '", 4000)');
ELSIF r_col.data_type = 'NUMBER'
l_stmt := REPLACE(l_stmt, '$col', 'TO_CHAR("' || r_col.column_name || '")');
IF p_limit IS NOT NULL
EXECUTE IMMEDIATE l_stmt USING p_regex, p_limit;
EXECUTE IMMEDIATE l_stmt USING p_regex;
I’m sure this can be improved in many ways but I basically had 30 minutes to hack this together and connect it to the scan tool.
Thu 27 Jan 2011
I was preparing a presentation for RMOUG and wanted to show how easy it is to crack Oracle passwords once you get the hashes.
There are a lot of Oracle password crackers out there but I find that using low level C code in a presentation makes the audience leave before you get to the half of the page. Using PL/SQL is a possibility but I wanted a very quick and concise way of showing the relevant code. So, Python to the rescue. But, I did not want to write code that requires installation of the Python Oracle drivers and would be tied to a specific compiled version so I used a simple class I wrote about here to do the DB connection.
The main piece of code is the password calculation which in Python looks like this:
s = hashlib.sha1()
That’s about it. The rest of the code plays with various options, handles the select from the database, iteration on the dictionary file and splitting the hash from the spare4 field into the hash and salt. I find that using Python allows you to do quick hacks and add more checks to the cracker easily.
So, here is the code. Love to hear your thoughts. Also, if someone can test it on Windows I will be grateful.
PS – if I’ll get permission from Alex, I will post his password file and complete the download package.
Tue 18 Aug 2009
A guest post by Roy Fox, Sentrigo’s Head of Security Research. Thanks Roy!
Black boxes are rarely entirely black. Many have side effects in addition to their functional effects, and virtually all consume external resources of one kind or another. When these effects or consumption are detectable, and when they reveal information on the internal workings, process flow or data, the otherwise-black box has a side channel.
Side channels are most important when they reveal information on unknown inputs, such as cryptographic keys. Oracle’s VPD mechanism is a good example of a process built with the purpose of controlling access to its input, but with the result of leaking information on its input. Even worse, side channels in VPD circumvent other access control measures, and leak information which was harder to get without the VPD.
We’re going to give an example of side-channel attacks, using the VPD functions from the introduction published by Arup Nanda in Oracle Magazine. We’re running Oracle 10.2.0.4 on Linux x86. In this example, we use VPD to circumvent the mechanism that hides the existence of a table from a user who doesn’t have privileges for that table. Another example, which we didn’t implement, could be to use the VPD mechanism to gather information on the tables used in calculating the VPD predicate.
Thu 16 Apr 2009
Posted by Slavik under DBA, Oracle, security, sentrigo
Comments Off on Sentrigo integrates with Repscan to provide vulnerability assessment
Anybody using Oracle databases, and anyone who is concerned about vulnerability assessment should be familiar with Repscan – the best scanner for Oracle databases, developed by Alexander Kornbrust’s Red-Database-Security.
The scanner, built upon Alex’s extensive experience in doing thousands of pen tests and database reviews, has some very unique features and tests. At Sentrigo, I always considered Repscan as extremely useful, flexible and easy to use and this is why I’m happy to announce that we’ve integrated it with Hedgehog to provide an even stronger database security solution.
One of the unique features that I like is the fact that everything is available from the command line on Linux, Mac and Windows so you can use your favorite scheduling system to run the tests. I know that most users prefer GUI (which is available as well) but I’m a command line type of guy 🙂
You can easily download Repscan from the Sentrigo Website, where you can get the limited trial-version at no-charge. This is a great way to test the waters, and then, move into into the fully-functional product once you’ve tested.
Here are some of the highlights – check it out for yourself, and let me know what you think!
Repscan’s Product Highlights
- Detects insecure PL/SQL-Code
- Shows the patch level of all your databases in one-click
- Finds security problems such as SQL Injections, hardcoded passwords, deprecated functions
- Detects weak or default passwords
- More than 115 Oracle tables checked for password information
- Provides penetration testing reports
- Detects changed database objects including root kits
- Detects altered data (including modifications of privilege and user tables)
- Discovers forensic traces from common security and hacker tools
- Complements and integrates with Sentrigo’s Hedgehog family of database activity monitoring software
Fri 30 Jan 2009
Posted by Slavik under DBA, Fuzzor, Oracle
I was playing a bit with FuzzOr and trying out different Oracle built-in schemas on 11g when I stumbled across something interesting in the parameters for MDSYS.SDO_JOIN. This caused FuzzOr to fail in fuzzing the function so I took a closer look.
SYS> select argument_name, type_owner, type_name, position, sequence from all_arguments where object_name = ‘SDO_JOIN’;
ARGUMENT_NAME TYPE_OWNER TYPE_NAME POSITION SEQUENCE
--------------------- -------------- ----------------- -------- --------
TABLE2_PARTITION 8 10
TABLE1_PARTITION 7 9
PRESERVE_JOIN_ORDER 6 8
PARAMS 5 7
COLUMN_NAME2 4 6
TABLE_NAME2 3 5
COLUMN_NAME1 2 4
TABLE_NAME1 1 3
MDSYS SDO_ROWIDPAIR 1 2
MDSYS SDO_ROWIDSET 0 1
10 rows selected.
So, do you notice anything weird here? SDO_ROWIDPAIR is listed as a parameter in the same position as TABLE_NAME1 but has no parameter name. Interesting. Initially, I thought that there might be something wrong with the view all_arguments but querying directly the argument$ table produced the same results.
Describing the function did not mention SDO_ROWIDPAIR at all:
SYS> desc MDSYS.SDO_JOIN
FUNCTION MDSYS.SDO_JOIN RETURNS SDO_ROWIDSET
Argument Name Type In/Out Default?
------------------------------ -------------- ------- --------
TABLE_NAME1 VARCHAR2 IN
COLUMN_NAME1 VARCHAR2 IN
TABLE_NAME2 VARCHAR2 IN
COLUMN_NAME2 VARCHAR2 IN
PARAMS VARCHAR2 IN DEFAULT
PRESERVE_JOIN_ORDER NUMBER IN DEFAULT
TABLE1_PARTITION VARCHAR2 IN DEFAULT
TABLE2_PARTITION VARCHAR2 IN DEFAULT
The source code is wrapped but the header is:
FUNCTION SDO_Join (TABLE_NAME1 VARCHAR2, COLUMN_NAME1 VARCHAR2,
TABLE_NAME2 VARCHAR2, COLUMN_NAME2 VARCHAR2,
PARAMS VARCHAR2 DEFAULT NULL,
PRESERVE_JOIN_ORDER NUMBER DEFAULT 0,
TABLE1_PARTITION VARCHAR2 DEFAULT NULL,
TABLE2_PARTITION VARCHAR2 DEFAULT NULL)
As you see, no mention of SDO_ROWIDPAIR which is in-fact mentioned later in the function as a declared local variable TAB_REC.
Anyway, this function is declared as AUTHID CURRENT_USER so even if can be injected (which I suspect that it can) it will not benefit the attacker.
Still, it’s an interesting case where the arguments table does not reflect the actual code and I suspect that there is a bug in populating this table.
Have you encountered similar cases?
Tue 20 Jan 2009
In light of last week’s CPU announcements, I invited my colleague Aviv Pode, Sentrigo’s Head of Security Research, to submit a special guest blog post. Thanks Aviv!
Oracle releases Critical Patch Updates (CPUs) every three months, containing security code fixes to vulnerabilities discovered by its security personnel or external researchers and hackers. By exploring these CPUs we can obtain valuable information about the vulnerabilities addressed by the patches and use them to create exploits that attack or hack the database. Thus, ironically, each time Oracle releases a new CPU to help protect databases, it actually increases the risk of Oracle databases worldwide being attacked.
This blog post describes and demonstrates the simple process of exploring Oracle’s CPUs to create working exploits that can be used to attack or hack an Oracle database. Only basic familiarity in information security and databases is required.
I will demonstrate the ease in which hackers can turn Oracle CPUs to attack vectors and its intent is to show IT security personnel the way their opponents operate. I’d like to emphasize that the best way to protect the databases against the hackers is a mix of several defense layers:
- You must apply the CPUs as soon as you can after they are released.
- You must harden the database and disable any functions in the database that you do not need.
- You should deploy security measures such as monitoring and virtual patching in order to augment the security.
Oracle Databases & Critical Patch Updates
Oracle is considered the leading and most widely used Database Management System (DBMS) in the enterprise world. Oracle databases are at the backbone of most critical or sensitive information systems in the world, from government and military, through telecommunications, commercial and financial companies, to some small businesses and web applications. Thus, Oracle databases store probably the most sensitive and valuable information in the world, anything from credit card information and personal health records to business transactions and national security documents.
In the past, Oracle installations contained weak default configurations, which made it easy for malicious users to penetrate Oracle databases. Among these weak default settings were active privileged user accounts with default known passwords, weak authentication settings and more. Since then, Oracle hardened these default settings, reducing many of the quick and easy attack methods that were publicly known and possible, leaving an interesting attack vector – built-in code vulnerability exploitation.
Code patches are released by software vendors to correct bugs discovered in their products. In many cases, these bugs affect security and the patches are actually fixes to security vulnerabilities that reside in the code itself. Oracle releases its Critical Patch Updates (CPUs) every three months since January 2005. These CPUs, which in the past contained fixes to bugs not only affecting security, now focus only on security related issues. Oracle, naturally, publishes very little information about the vulnerabilities or attack possibilities and refrain from delivering valuable information into the wrong hands. At most, Oracle indicates the high-level component being addressed or details the required privileges and severity level of the bug (using Common Vulnerability Scoring System [CVSS]).
However, the CPUs are not helping protect Oracle customers much. An interesting situation exists in most Oracle installations worldwide, severely compromising data confidentiality, integrity and availability in those systems. A survey conducted by Sentrigo showed that about 90 percent of Oracle customers do not install Oracle CPUs in the 6 months following their release, while about 60 percent do not install them at all – ever. This means that most of Oracle databases worldwide currently contain un-patched vulnerabilities in built-in components, which may be exploited by hackers or malicious users.
Exploring Oracle CPUs
Oracle CPUs are available for every supported chipset and operating system, as a compressed directory containing a bulk of sub-directories. The CPU contains meta-data for the entire patch, listing identification and very basic information about the bug fixes, stored in text and xml files. Each sub-directory is referred to as a ‘molecule’. Each molecule has a unique identification number. The molecule with the number corresponding to the name of the compressed directory is a special molecule with information about the entire CPU. Besides this special molecule, each molecule contains a fix of a different vulnerability found in a built-in component of Oracle. Inside each molecule we will find two sub-directories – “files” and “etc”. Under “files” we can find the corrected files, while under “etc” meta-data such as the location of the corrected files in the Oracle Home, the feature or component these files relate to or the affected versions of Oracle.
Oracle database CPUs usually contain fixes to four types of files: Binary, Java, PL/SQL and SQL files, although on occasion corrections are done in configuration and other types of files. The meta-data in the ‘etc’ sub-directory indicates how the ‘opatch’ (Oracle’s utility for applying patches) should apply this fix. Usually it indicates a simple ‘copy’ to replace a PL/SQL component or SQL script, or an ‘archive’ to store a fixed object (.o binary) file in an archive (.a) file. This can be viewed in the “actions” file, under “etc/config”.
Now that we know what kinds of files we expect to find inside the CPU, we can proceed to see how these files can be used to find and understand the vulnerabilities fixed by the patch.
Finding an exploitable vulnerability
In order to demonstrate, we will explore the July ‘08 CPU for Oracle 10.2.0.3 running on Linux 32 bit. After downloading and extracting the compressed file we will find 55 molecules. For our learning purpose we can pick a simple one, let’s say – 7154835. This molecule contains, under ‘files’, a single PLB file, to be stored in the directory specified in the ‘etc/config/actions’ file, under the Oracle Home.
PLB files are wrapped PL/SQL files, which contain the code of built-in Oracle components originally written in PL/SQL by Oracle. The PL/SQL code is wrapped using Oracle’s propriety wrapping algorithm. However, the algorithm for Oracle 9i wrapped code has been cracked and published by David Litchfield of NGS-Security at Black-Hat. Oracle 10g and 11g wrapping algorithm has not yet been published, however it is safe to assume that it is available to hackers worldwide. It is important to note here that even Oracle does not consider this wrapping algorithm to be anything more than an obfuscation and it is not cryptologically strong like PKI infrastructure, for example.
Once the PLB file has been unwrapped (using tools available for the hackers) we can view the plain text PL/SQL code. This can not only help us find the vulnerabilities that were patched, but actually find new unknown ones by analyzing the code – but this is a matter for another post 🙂 To continue with our demonstration, the PLB file we found in the molecule and unwrapped was ‘prvtdefr.plb’. We will locate and un-wrap the same file from an unpatched Oracle installation, or better-yet – patched with the previous CPU – April ’08. The file could be found in the same corresponding directory under the Oracle Home, as specified in the molecule meta-data ‘actions’ file – ‘$ORACLE_HOME/rdbms/admin/’.
Now we have two versions of the same Oracle built-in code file, one before and one after the code fix. All we need to do is compare the two files, locate the changes and in most cases a minimal additional effort will be required to understand the vulnerability.
Using a simple text-diff utility, we find several changed lines of code, which we can view in clear text. We can see that changes were made in the ‘dbms_defer_sys’ package, whose code is implemented here. Scrolling down we can examine the changes made in the ‘delete_tran_inner’ procedure. First, let’s look at the procedure header:
PROCEDURE DELETE_TRAN_INNER(DEFERRED_TRAN_ID IN VARCHAR2,
DESTINATION IN VARCHAR2, CATCHUP IN RAW) IS
We can see three parameters passed to this procedure, among them ‘DESTINATION’, a varchar2.
In the old PL/SQL file, before the code fix, inside the procedure we can see concatenation of parameters passed to the procedure, into new variables:
COND1 := 'd.deferred_tran_id=''' || DELETE_TRAN_INNER.DEFERRED_TRAN_ID
|| ''' AND ';
COND2 := 'd.dblink=''' || NLS_UPPER(DELETE_TRAN_INNER.DESTINATION)
|| ''' AND ';
These variables are later concatenated into an SQL SELECT statement and then executed:
DBMS_SQL.PARSE(SQLCURSOR, 'SELECT d.deferred_tran_id, d.dblink ' ||
'FROM "_DEFTRANDEST" d ' ||
'WHERE ' || COND1 || COND2 || COND3 || ' 1 = 1', DBMS_SQL.V7);
IGNORE := DBMS_SQL.EXECUTE(SQLCURSOR);
Let us now examine the corresponding lines in the new fixed code:
COND1 := 'd.deferred_tran_id=:deferred_tran_id AND ';
COND2 := 'd.dblink=:destination AND ';
We can see that bind variables are used, as a safe way to avoid SQL Injection vulnerabilities. From these code changes we can conclude that the patch is intended to fix an SQL injection vulnerability in the old PL/SQL code. However, the ‘delete_tran_inner’ is a private procedure, which a user cannot execute directly. In this simple case, a closer look will find a public ‘delete_tran’ procedure that a user can execute directly, which in turn calls the ‘delete_tran_inner’ without performing input validation or sanitization either. We can now continue to create an exploit for this vulnerability.
Creating an Exploit
After locating the fixed vulnerability and identifying the weak procedure and parameters which we can exploit, we can write (or copy from the web) an exploit and simply adjust it to target our vulnerable procedure. The code we found opens a cursor which grants DBA privileges to Scott, the malicious user account we will use to hack the database. By calling the ‘delete_tran’ procedure in ‘dbms_defer_sys’ and passing an SQL injection, we can execute the evil cursor:
C := DBMS_SQL.OPEN_CURSOR;
EXECUTE IMMEDIATE ''grant DBA to SCOTT'';
DBMS_DEFER_SYS.DELETE_TRAN('x',''' and 1=DBMS_SQL.EXECUTE('||C||')--');
Theoretically, executing this code on an Oracle 10.2.03 un-patched with the July ’08 CPU will result in Scott being granted DBA privileges to the entire database. As a side note, this code will not work on an 11g database because the dbms_sql package was hardened to check if privileges have changed between the parse and the execute stages.
This demonstration focused on a PL/SQL molecule. Many molecules contain binary files which we cannot examine as easily as what we have seen here. In order to examine these binary files, more sophisticated tools (than ‘diff’) are required, such as DataResue’s IDA-Pro and Zynamics BinDiff. However, the idea remains the same – compare the old and new code, find the changed function, understand the fix and create a targeted exploit. In such more complex cases, if you have found the code fix but do not understand the vulnerability or do not know how to successfully exploit it, a fuzzer may come in handy. Running a good fuzzer on the old version of the fixed function or procedure will, in many cases, reveal the information hackers are looking for.
As we have demonstrated, using Oracle’s CPUs to find vulnerabilities and create working exploits that target, attack and hack Oracle databases is quite simple. As a result, hackers and malicious users can easily create such exploits and publish them on the web for other users to utilize. In actuality, this means every time Oracle releases a CPU, hackers are given more critical information on how to successfully attack Oracle databases, which increases the risk level for Oracle installations worldwide.
The conclusion is simple: Apply patches AS SOON AS POSSIBLE. Harden your database by disabling unnecessary components. Or in case your organization is part of the majority of users who cannot afford it, for availability (downtime) practical or sensitivity considerations – apply a security measure such as virtual patching to block attacks targeting the database.
Fri 9 Jan 2009
Posted by Slavik under DBA, Oracle, patching, Uncategorized
Comments Off on Oracle CPU Pre-Release Announcement – January 2009
Oracle has released an announcement about the upcoming January CPU. This time it contains very serious WebLogic and secure backup vulnerabilities, along with 10 vulnerabilities on the database side. The total number of vulnerabilities is in line with the previous CPUs while the database related vulnerabilities are a bit less than usual compared with the 15 in the October CPU, 11 in the July CPU and 15 in April.
It’s worth noting that none of the database server vulnerabilities are remotely exploitable which makes them a target for insiders or by using SQL injection in applications.
Some of the vulnerabilities are found in optional components like Oracle Spatial. The take-away here is as follows: Install only what you use, don’t install features you are not going to use. Remove them if installed by default.
My advice here is to wait about a week or two to make sure that there are no issues with the patch and then patch as soon as possible – but only after ensuring that your applications are not breaking.
If you can’t patch quickly or unable to patch at all due to valid reasons , try virtual patching as a stop-gap solution.
Fri 9 Jan 2009
Today, I had to download an Oracle 11g installation to a headless Linux server without using a browser. Of course, using wget immediately came to mind but since you need to log-in, just pointing wget to the file does not work and will redirect you to the login page. I did a quick Google search and found the following solutions. But, I’m using Firefox which saves the cookies in a sqlite database that is not compatible with the old Netscape cookies format that wget works with and I had no patience to use Lynx.
A quick search on the Mozilla add-ons page showed me this little gem. To download this little add-on you must register to the site because it is experimental. This add-on adds a new menu item under tools called “Export cookies…” and as the name suggests it allows you to export the cookies including session cookies.
So, the solution was to simply log-in to OTN and then export the cookies to a cookies.txt file. After uploading the file to the server, running wget with –load-cookies did the trick:
wget –load-cookies=cookies.txt http://download.oracle.com/otn/linux/oracle11g/linux_11gR1_database_1013.zip
Do you have a simpler way?
Tue 6 Jan 2009
Happy New Year everyone!
As promised, in this blog post I will deal with the PL/SQL fuzzer I’ve created in my spare time and during flights. The goal for creating it was to provide an easy tool for the DBA to test PL/SQL code inside the database. This tested code can be internally developed or by a 3rd party. Before describing the architecture of the fuzzer and showing examples, I would like to make the following clarifications / warnings:
- Fuzzing on production is a BIG no-no. Never run the fuzzer on any database you care about. Always use test copies because running the fuzzer may crash / corrupt the database.
- The fuzzer cannot guaranty that the code is not vulnerable, it can only try and find existing vulnerabilities. Running the fuzzer on a procedure and receiving a clean result does not mean that this procedure is free of vulnerabilities because the fuzzor does not analyze the code and does not visit all the code paths.
- The fuzzer is in no way shape or form a finished product. It will blow in your face. It will fail when running your code. It contains multiple bugs. USE RESPONSIBLY!!!
Now that the warnings part is over, let’s talk about the design.
I chose PL/SQL for the following reasons:
- Easy to run SQL statements
- Built-in the database
- Cross platform
- Good enough for the task
- DBAs already speak it fluently
- Can be easily scheduled as a DB job from inside the database
The design is fairly simple and is based on the following requirements:
- Must use database tables to track executions across invocations and to change various fuzzing parameters
- Must try and find interesting (dynamic) code using discovery
- Must easily generate reports on the fuzzing results