Tyler Muth’s Blog

Technology with a focus on Oracle, Application Express and Linux

Archive for the ‘Security’ Category

PL/SQL LDAP over SSL – Please Test

Posted by Tyler Muth on January 30, 2009

I’ve been meaning to write this for quite a while now (measured in years), but never got around to it.  None of the DBMS_LDAP code samples support SSL, including the APEX LDAP Authentication Scheme.  Most LDAP directories require SSL for authentication, as you would otherwise send usernames and passwords in clear text.  Depending on your network topography, the risk of someone capturing that data might be very low, but just the same, it should really be SSL. 

One perceived barrier to making this work was my belief that this required an Oracle Wallet which are a bit of a hassle to setup.  There was also some confusion on my part that Oracle Wallets required a license for the Advanced Security Option (ASO), so this would limit the use of this code to only the subset of our customers that have purchased this option.  I’m still looking for the email on this but I believe we confirmed that the Oracle Wallet Manger by itself (such as for DBMS_LDAP or UTL_HTTP) does not require ASO.  Only when you are using the explicitly defined features of ASO such as encrypted column / tablespaces / backups, network encryption, and a few others I can’t remember.  If someone has more info on this, please feel free to comment as I’m too [ busy | lazy | (select adjective from adjectives where id = round(dbms_random.value(1, 100)))] to find it [today | tomorrow | (select sysdate + round(dbms_random.value(1,365)) from dual) ].  When I finally sat down to write this for my current project, I quickly discovered that for SSL Mode 1 (which is what 99.999% of people want), no wallet is required!  Can’t believe I put this off for so long based on a false assumption.

So what does this code do? It can:

  • Bind to an LDAP directory using SSL or non-SSL
  • Authenticate a user using their exact Distinguished Name (DN) such as: cn=tyler.muth, l=amer, dc=mycompany, dc=com
  • Given a starting point (search base) and a username (tyler.muth), search for that user, retrieve their DN, then authenticate.  This handles users in multiple sub-trees.

This is NOT production code! This is NOT production code! This is NOT production code!  Why am I posting it here then?  Because I want people to test it.  I’ve tested it on the project I’m working on, but that doesn’t mean it’s bug free.  I want people who are familiar with PL/SQL and LDAP to review and test my code.  As I receive suggested changes to the code, I’ll update it.  When I feel comfortable that enough people have tested, I’ll blog again and “release it” to the community to use.  My goal is make this as flexible and easy as possible so it just becomes a utility that people can use when they need LDAP.  I can’t make any promises, but if it makes it back into APEX, then you will have helped to improve a product.  If you are not all that familiar with PL/SQL and LDAP, or are looking for a production ready solution, this is probably not for you.  When it’s ready, this blog post will be mostly empty and point to the new location of the code.

I’ve only tested this against OID using an 11.something database, so I’d love to have people test this against Active Directory and any other LDAP directories they work with and at least 10gR2 and XE.  Mark Wilcox is testing it with Oracle Virtual Directory in the next few days as well.  I also ran Wireshark on the database server to capture the communitcation between the database and the LDAP directory.  As expected, in non-ssl mode, my username and password were in the clear.  With SSL enabled, everything was garbage.   Please use Wireshark with caution as it may violate network policies of your organization, get you fired, and accelerate global warming.  It’s a great tool when used ethicly.

I really appreciate the feedback I’ve recieved in the past from this community, especially in pointing out errors with my code.  Thanks in advance for your help.

Customize the ldap_globals package with data from your environment. For most environments, you’ll only need to change g_host and g_search_base.

create or replace package ldap_globals
authid definer

	g_host         	constant varchar2(255)   := 'ldap.mycompany.com';
    g_port          constant number          := 636;
    g_search_base   constant varchar2(255)   := 'dc=mycompany,dc=com';
    g_search_filter constant varchar2(255)   := 'cn=';
	g_exact_dn      constant boolean   		 := false;
	g_use_ssl       constant boolean   		 := true;

	-- if g_exact_dn is false, then we will start at the search base and find the user to get their DN.
	--   if true then we will just bind with the DN and you should pass the fully qualified user DN into p_username

	-- SSL is hard-coded to use mode 1, as modes 2 and 3 are highly unlikely.  See the DBMS_LDAP doc for more info.
	-- No wallet is needed for mode 1

end ldap_globals;
show errors

There’s no need (that I know of) to customize any code in this function.

create or replace function  ldap_authenticate(
	p_username        in    varchar2,
	p_password        in    varchar2)
return boolean
authid definer
	l_retval      pls_integer;
	l_session     dbms_ldap.session;
	l_attrs       dbms_ldap.string_collection;
	l_message     dbms_ldap.message;
	l_entry       dbms_ldap.message;
	l_dn          varchar2(1000);
	l_user_handle dbms_ldap_utl.handle;
	l_return      pls_integer;
	l_user_type   pls_integer;

	l_filter      varchar2(256);

	function do_connect
		return pls_integer
		l_ldap_user   varchar2(256) := NULL;
		l_ldap_pwd    varchar2(256) := NULL;
		dbms_ldap.use_exception := true;
		l_session := dbms_ldap.init( ldap_globals.g_host , ldap_globals.g_port);

    if ldap_globals.g_use_ssl = true then
      l_retval := DBMS_LDAP.open_ssl(
					ld 				=> l_session,
                    sslwrl			=> NULL, -- wallet location, ie file:/etc/ORACLE/WALLETS/oracle
                    sslwalletpasswd	=> NULL, -- wallet password
                    sslauth			=> 1); -- NO_AUTH :1, ONE_WAY_AUTH: 2, TWO_WAY_AUTH: 3
    end if; 

		return dbms_ldap.simple_bind_s( l_session, l_ldap_user, l_ldap_pwd );

	procedure do_close
		l_retval := dbms_ldap.unbind_s( l_session );
	end do_close;

	procedure handle_error(p_message in varchar2)

		$IF $$logger $THEN

		RAISE_APPLICATION_ERROR (-20001,p_message);

	l_user_type := DBMS_LDAP_UTL.AUTH_SIMPLE;

	l_retval := do_connect;

	if ldap_globals.g_exact_dn = false then

		l_attrs(1)  := 'dn';
    l_filter := '('|| ldap_globals.g_search_filter|| p_username||')';
		l_retval := dbms_ldap.search_s( l_session, ldap_globals.g_search_base, dbms_ldap.scope_subtree, l_filter, l_attrs, 0, l_message );

		l_retval := DBMS_LDAP.count_entries(l_session, l_message);

		if l_retval > 1 then
			handle_error('More than one entry found when searching for users DN.');
		end if;

		if l_retval = 0 then
			handle_error('No entries were found when searching for user DN');
		end if;

		l_entry := dbms_ldap.first_entry( l_session, l_message );
		l_dn := dbms_ldap.get_dn( l_session, l_entry );
		l_dn := p_username;
	end if; -- ldap_globals.g_exact_dn = false

	l_retval := null;
	l_retval := DBMS_LDAP_UTL.create_user_handle(
				user_hd     => l_user_handle,
				user_type   => DBMS_LDAP_UTL.TYPE_DN,
				user_id     => l_dn);

      dbms_ldap.use_exception := false;
      l_return := null;
			l_return := dbms_ldap.simple_bind_s(
				ld      => l_session,
				dn      => l_dn,
				passwd  => p_password
      if l_return != 0 then
        return false;
      end if;

		exception when others then

    dbms_ldap.use_exception := true;
	if l_return is null then
		handle_error('simple_bind_s did not return a value.');
		return false;
	end if;

	if l_return = 0 then
		return true;
	end if;

	exception when others then
		return false;
end ldap_authenticate;
show errors

A simple test script. If using SQL Developer, make sure you enable output on the DBMS_OUTPUT tab.

set serveroutput on
	l_return boolean;
	l_return := ldap_authenticate(
					p_username	=> 'first.last',
					p_password	=> '');

	if l_return = true then

Posted in Application Express, Oracle, PLSQL, Security | Tagged: , , , | 79 Comments »

mod_rewrite and APEX part 2

Posted by Tyler Muth on August 13, 2008

Here’s another Apache mod_rewrite example that I would love to get some feedback on from the community.

RewriteCond %{REQUEST_URI}%{QUERY_STRING} !/pls/(apex|builder)/f?p=4155:.*
RewriteCond %{REQUEST_URI}%{QUERY_STRING} /pls/(apex|builder)/f?p=(4[0-9]{3}:.*)
RewriteCond %{REMOTE_ADDR}        !^(127\.0\.0\.1|192\.168\.1\.[0-9]{1,3})$
RewriteRule /pls/(apex|builder)/ - [F]
  1. Any request for a 4000 series application (the APEX dev environment) on either the “apex” or “builder” DAD…
  2. …Not coming from either localhost or a 192.168.1.x address…
  3. Return a 403 “Forbidden” page.

This is a simple way to protect your development environment from people that don’t belong there.  The runtime-only installation option introduced in APEX 3.1 is even more secure, but there may be users on previous versions or that do not want to use the runtime only option.  Thoughts?

Posted in APEX, Security | 4 Comments »

Wake Up and Smell Reality

Posted by Tyler Muth on August 5, 2008

In case you missed the news, yet another laptop with unencrypted Personally Identifiable Information (PII) was lost (though this one was found again, news story here).  This type of story is in the news all too often.  If you think I’m overreacting, take a look at this Chronology of Data Breaches.  You might even look for your organization or organizations that have your data.  The intended audience of this blog, DBAs and developers, are often the ones who know the most about how their own organization’s data is stored, so I hope at least a few of you read it and think about security a little bit more.  So, here it goes…


  • If you are storing unencrypted PII in your database, WAKE UP!
  • If you are backing up PII without encrypting it, WAKE UP!
  • If you are exporting PII in clear text and sharing it with groups (mainframe extracts come to mind here) WAKE UP!
  • If you have any of your customers unencrypted PII on your laptop, WAKE UP!
  • If you transport unencrypted PII on a flash drive, seriously, WAKE UP!
Did I get your blood pressure up?  I hope so, people tend to remember things when emotion is involved.  I’m not trying to offend anyone, but we all need to take this issue a little more seriously.  So, what can you do?
  • Backups are a prime target, and an easy one to solve.  I blogged about RMAN Encrypted Backups, there’s also Oracle Secure Backup (free for a single machine), as well as a plethora of free or for cost file encryption utilities.
  • If you don’t need the info, DON’T STORE IT in the first place.  
  • If you need to say lookup a record using a Social Security Number, but don’t need to display or edit it, store the hash of an SSN using dbms_crypto.mac.  You can then hash the search term, then do a simple equality search on hash = hash.   
  • Use dbms_crypto to programmatically encrypt data if you can.
  • Use Transparent Data Encryption if you can’t or don’t have time to change the application code.
  • Don’t even think of using SSNs as primary keys.  They’ll end up getting propagated to all child tables, and what are you going to do when someone changes their SSN?
  • Use TrueCrypt to encrypt a volume on your laptop, or even your whole drive.  I’ve been using it on my laptops for 2 years now and it’s always worked flawlessly.  It also works on flashdrives. 
  • Help define policies for protecting sensitive information.
  • Talk to your colleagues and managers about concerns you have.  Just starting a dialog about security is a huge step in the right direction.
  • Pick up a copy of “Effective Oracle Database 10g Security by Design” by David Knox.  Yes, David is a friend of mine.  No, I don’t get ANY money from this book.  Note that of the 10 reviews on Amazon, 9 of them give this book 5 stars!  I’ve read it cover to cover and reference it often, it’s such a great resource.
Other Oracle technologies to consider when thinking about security include 11g Tablespace Encryption, Oracle Database Vault, Virtual Private Database, and many more.


Posted in Security | Tagged: , | 1 Comment »


Posted by Tyler Muth on June 2, 2008

mod_security is an Apache module designed as a sort of web application firewall. It’s most useful for preventing SQL Injection and Cross Site Scripting (or XSS). If you are a web developer and could not immediately describe both of those concepts to a colleague, stop reading this and go read more about both concepts. In fact, Oracle Server Technologies has a great, free, online course that covers SQL Injection here. These classes of vulnerabilities have become quite popular and are the vectors to many of the latest security breaches. If you’re using APEX, there are a lot of built-in features and default settings that prevent you from coding these vulnerabilities into your applications, but you should really know the threat so you understand the risk of say, turning off the character escaping in APEX reports or of using a concatenated string in a query instead of a bind variable.

I played around with mod_security about a year ago, but since it required compiling an unsupported module into Oracle HTTP Server, I didn’t invest much time in it. I recently installed the 11g Oracle HTTP Server (OHS based on Apache 2.0 and noticed that it shipped with mod_security, so I thought this would be a good time to bring it up with the community. The only downside is that the version shipping with OHS is mod_security 1.8.4. This version came out in 2004 and I can’t even find the documentation for it anymore (even via archive.org). So, I used the mod_security 1.9 doc to put together some examples of what you can do. You can also compile mod_security in with previous versions of OHS (make sure you set the PERL5LIB environment variable or it will fail, use the setup instructions from this article on compiling mod_php). I have not tried to remove the old version of mod_security from OHS and compile in a new one… sticking to the supported stuff or now. Also, I have been told that they will be updating the version of mod_security that ships with OHS in future versions.


I decided to demonstrate this on Windows since most of my examples are Linux based. I added the following line to ORACLE_HOME\ohs\conf\httpd.conf:

include "C:\oracle\http_home\ohs\conf\mod_security.conf

This file and the rules file can be downloaded here.

Below are a few VERY simple rules to give you an idea of what mod_security does. The rules I included are a little more complex, but hopefully more complete.

SecFilter "delete[[:space:]]+from"
SecFilter "insert[[:space:]]+into"
SecFilter "select.+from"
SecFilter "<[[:space:]]*script"

mod_security will inspect both POST and GET requests before handing the requests off to other modules such as mod_plsql, so these database centric attacks never actually reach the database. It offers many advanced features as well, including the ability to scan uploaded files with anti-virus software before they get past mod_security (documented here). You can control whether or not it returns an error page, or simply logs the event and continues. You can even filter output, say Oracle errors if you’re concerned about an Oracle error exposing details about your schema structure.

In my opinion, mod_security is no substitute for awareness, secure coding practices, and code review. My question to the community is do you think it adds significant value? Do want to read more about it if I were to include it in a more formal publication? Are there changes you would make to my example rules?

Posted in Oracle, Security | Tagged: , , , | 7 Comments »

Wrap and compile in one step

Posted by Tyler Muth on September 14, 2007

The PL/SQL wrap utility is a great way to obfuscate code that you do not want anyone to view. Typically this is used for security functions Virtual Private Database and Fine Grained Auditing policies, encryption and hashing functions, and authentication and authorization procedures. The source of unwrapped code can easily be retrieved from the ALL_SOURCE view or almost any IDE that connects to the database.

Prior to 10g, you needed to use the command-line wrap binary located in $ORACLE_HOME/bin. This was a 2 step process, requiring you to first wrap the code from the shell, then compile it using SQL*Plus. 10g introduced the DBMS_DDL package, allowing you to wrap code from within PL/SQL and compile it in one step. This means you don’t need to be on a machine where Oracle is installed, allowing you to use other tools such as APEX and SQL Developer to wrap your code.

Here’s a quick example:

-- Run the following grant as sys:
-- grant execute on dbms_ddl to demo;
  l_function    varchar2(32767);
  l_function := q'!
    create or replace function wrap_test
      return varchar2
       return 'Yep, it worked';
    end wrap_test; !';
  -- Toggle the comments on the following 2 lines to toggle wrapped / not wrapped
  -- execute immediate l_function;

Here’s a screenshot of the wrapped source code in SQL Developer:
Wrapped Code


Posted in Oracle, Security | 2 Comments »

RMAN Encrypted Backups

Posted by Tyler Muth on September 8, 2007

A number of recent high profile data thefts have resulted from lost or stolen backups that were not encrypted. Offsite backups are a good first-step in a Disaster Recovery plan, but they also create a huge risk for data theft. Even if the backup is not offsite, a backup sitting around your office represents nicely packaged target for someone to steal. If you need a reality check, take a look at some of the events listed on the Chronology of Data Breaches:

  • Bank of America: Lost backup tape, 1,200,000 records
  • Ameritrade: Lost backup tape, 200,000 records
  • Time Warner: Lost backup tapes, 600,000 records
  • CitiFinancial: Lost backup tapes, 3,900,000 records

Encrypted backups provide a huge improvement in security without having to change your application code or database structures. Obviously, sensitive data stored unencrypted in the database is still a problem, but it’s typically a much more challenging problem to solve. More on that issue in future blog posts…

Oracle offers several solutions to encrypt backups. Oracle Secure Backup is designed for direct backups to tape, offers several options for encryption, and even comes in a free “Express” version for tape devices directly attached to a single server. Starting with 10gR2, RMAN can also encrypt backups to disk when used in conjunction with the Advanced Security Option (ASO) for the database. I’m only going to cover the RMAN solution in this post since I don’t have access to a tape device.

RMAN encryption can either use a password, an Oracle Wallet, or both as keys to encrypt a backup. If you choose to use both a wallet and a password to encrypt, you only need to one of the keys to decrypt your backup. So, choosing to use both options doesn’t make your backup more secure, it simply allows you to use either method for decryption in case you lose the wallet or forget the password. The beginning of this article on TDE provides a simple example of configuring a wallet. For more detailed information, here’s a link to the documentation for RMAN Encrypted Backups.

The following 5 minute screencast demonstrates RMAN encrypted backups, first using Enterprise Manager Database Control, then in more detail using the RMAN command-line interface.

ScreenCast Click here for the full-size version[splashcast GKBY3325TQ]


Unencrypted Backup:
rman target /
configure channel device type disk format '/backup/%N_%s' maxpiecesize 2 G;
backup tablespace test1 tag=unencrypted;

Password only Encrypted Backup:
rman target /
set encryption on identified by 'L!eFLW@Bf=U,ptC>' only;
backup tablespace test1 tag=encrypted;

Password and Wallet Encrypted Backup:
rman target /
configure encryption for database on;
set encryption on identified by 'L!eFLW@Bf=U,ptC>';
backup tablespace test1 tag=encrypted;

Restore Tablespace from Backup:
rman target /
sql 'alter tablespace test1 offline immediate';
set decryption identified by 'L!eFLW@Bf=U,ptC>';
restore tablespace test1;
recover tablespace test1;
sql 'alter tablespace test1 online';

Posted in Oracle, Security | 8 Comments »

Oracle Wallet w/ Self-Signed Certificate

Posted by Tyler Muth on July 27, 2007

I was working with some of the security tools included with the Oracle Advanced Security Option and wanted to create a new wallet without going through the hassle of requesting a certificate from one of the popular certificate authorities. After struggling a bit with creating a self-signed certificate, I found a great HowTo by Jim Coulter entitled “How To Build an Oracle Wallet with OpenSSL”. Since I’m planning on a number of future security posts that rely on the Oracle Wallet Manager, I wanted to repost Jim’s HowTo here so they would all be at the same location. Just to be clear, this is NOT my HowTo, it’s Jim’s and I want to give him full credit for a very concise and accurate HowTo.

This HowTo is relevant for any Oracle component that uses the Oracle Wallet Manager, including the Oracle Database with the Advanced Security Option (ASO), Oracle Application Server, Oracle HTTP Server (OHS), Oracle Internet Directory (ODI). For production environments, you should purchase a certificate from a well known certificate authority such as Entrust, Thawte, GoDaddy, or VeriSign. Using self-signed certificates provides no protection against man in the middle attacks so they should NOT be used in production environments.

Even though the example is Linux / Unix centric, I also tested this on Windows using CygWin and it worked flawlessly.

From Jim Coulter’s site, with one addition:

  1. Download and unpack the ssl helper scripts named ssl.ca-0.1.tar.gz from the OpenSSL > Contributions page.
  2. Open Oracle Wallet Manager and create a new wallet and certificate request.
  3. Export the certificate request to a file. Give it a .csr extension
  4. Move the certificate request to the directory containing the openSSL certificate authority scripts (e.g. /usr/src/crytpo/openssl/apps/ssl.ca-0.1)
  5. Create a self-signed root certificate by running the new-root-ca.sh script. This will create a file called ca.crt
  6. Create the self-signed server certificate by running the sign-server-cert.sh script, e.g. # sign-server-cert.sh <certificate-request-filename>. This will create a file called <certificate-request-filename>.crt
  7. Import the ca.crt into the Oracle wallet as a trusted certificate. Import the <certificate-request-filename>.crt as a user certificate.
  8. Enable auto-login and save the wallet. It is now ready for use.


Posted in Oracle, Security | 38 Comments »

Fine Grained Auditing

Posted by Tyler Muth on June 26, 2007

Below is a short screencast (code in action, no PowerPoint) of Fine Grained Auditing in the context of Application Express. It’s in response to this article, which only dedicates a single sentence to the topic of trip-wires, but I do credit them for raising the issue. The code is posted below the screencast. Other things to keep in mind:

  • This policy will fire for every insert,update,delete, or select statement for this table, but not every row
  • It would be easy to defeat this policy by setting module yourself and logging in as the APEX user, but the idea is that this is more of a silent alarm meant to catch people who are not aware of this policy, or at least not aware of exactly what it’s looking for
  • It would be a good idea to wrap the CHECK_FOOTPRINT function, making it difficult to see what it is looking for
  • Fine Grained Auditing requires Enterprise Edition (no SE or XE)
  • The FGA_NOTIFY procedure uses UTL_MAIL which means you need to set the initialization parameter SMTP_OUT_SERVER to the servername:port of your email server

ScreenCast Click here for the full-size version [splashcast IOMS3823UM]

-- Make sure you set the spfile parameter SMTP_OUT_SERVER to the
-- name and port of your mail server: yourmailserver.com:25
-- Grant execute on utl_mail to FGA;
-- Grant execute on DBMS_FGA to FGA;
-- Also make sure you change the "sender" and "recipients"
-- parameters in the call to utl_mail

create or replace function check_footprint
return number
    if (sys_context('userenv','session_user') = 'APEX_PUBLIC_USER'
        sys_context('userenv','module') = 'APEX:APPLICATION 106')
        -- Other thoughts:
        -- Business Hours
        -- (to_char(sysdate, 'D') between 2 and 6 and to_char(sysdate, 'HH24') between 8 and 18
        return 1;
        return 0;
    end if;
end check_footprint;

grant execute on check_footprint to public;

create or replace procedure fga_notify (
    schema1 in varchar2,
    table1  in varchar2,
    policy1 in varchar2)
    l_msg   varchar2(32767);

    function t_row(
        p_label     in varchar2,
        p_data      in varchar2)
    return varchar2
        return '<tr><td style="text-align:right;">'||p_label||'</td>'||
    '    <td style="font-weight:bold;">'||p_data||'</td></tr>'||utl_tcp.crlf;
    end t_row;

    l_msg := '<html><head><style type="text/css">body{font-family:helvetica}</style></head><body>'||
    '<table style="boder:0px;">'||
    t_row('Client Info',sys_context('userenv', 'client_info'))||
    t_row('Client Identifier',sys_context('userenv', 'client_identifier'))||
    t_row('IP Address',sys_context('userenv', 'ip_address'))||
    t_row('Auth Type',sys_context('userenv', 'authentication_type'))||
    t_row('Session ID',sys_context('userenv', 'sessionid'))||
    t_row('DB Name',sys_context('userenv', 'db_name'))||
    t_row('Host',sys_context('userenv', 'host'))||
    t_row('OS User',sys_context('userenv', 'os_user'))||
    t_row('External Name',sys_context('userenv', 'external_name'))||
    t_row('Current SQL','<pre> '||sys_context('userenv', 'current_sql')||'</pre>')||

        SENDER      => 'orcl_db@demo1',
        RECIPIENTS  => 'oracle@localhost',
        --CC        => '',
        --BCC       => '',
        SUBJECT     => 'Policy Violation',
        MESSAGE     => l_msg,
        MIME_TYPE   => 'text/html',
        PRIORITY    => 1);
end fga_notify;
show errors
set termout off
(object_schema       => 'FGA',
     object_name         => 'EMP',
     policy_name         => 'CHECK_FOOTPRINT');
set termout on
   (object_schema       => 'FGA',
    object_name         => 'EMP',
    policy_name         => 'ONLY_APEX_APP',
    audit_condition     => 'FGA.CHECK_FOOTPRINT = 0',
    audit_column        => null,
    handler_schema      => 'FGA',
    handler_module      => 'FGA_NOTIFY',
    statement_types     => 'INSERT,UPDATE,DELETE,SELECT',
    ENABLE              => TRUE);
-- Audit entries are stored in sys.dba_fga_audit_trail

Posted in APEX, Oracle, Security | 12 Comments »