Tyler Muth’s Blog

Technology with a focus on Oracle, Application Express and Linux

Archive for the ‘PLSQL’ Category

Logger Project Moved Temporarilly

Posted by Tyler Muth on November 9, 2011

The site samplecode.oracle.com was decommissioned recently. I was hosting a number of projects there including “Logger”, my PL/SQL instrumentation package. Until I find a new home, here’s a temporary link to the latest release (1.4) or if you just want to view the readme use this link. I’ll update this post when I decide on a new home.

This project is still very much alive and I will continue to update it based on your suggestions. I know there are a number of them in my “queue”, I just haven’t had time. My plan is to find a public place to host it such as github where everyone can contribute.

Advertisements

Posted in APEX, Oracle, PLSQL | 26 Comments »

Logger 1.4.0 Released

Posted by Tyler Muth on August 11, 2011

This is just a minor update to logger (project page here) to fix a bug in detecting DB Version 11.2 on RAC.

One important point to note is that in APEX 4.0 (or possibly 4.1), the APEX team removed the “create any context” priv when creating a schema associated with provisioning a workspace. Logger needs this priv. So, this means you won’t be able to simply install logger through the SQL Workshop without granting this priv to the schema owning logger. The biggest impact will be on hosted instances such as apex.oracle.com as you can’t simply ask someone to grant this priv to you (and please don’t try). I will try to see if it’s easy to work around this issue and what the implications are in the next few weeks.

Posted in APEX, Oracle, PLSQL | 3 Comments »

Synonym Switching Technique and Tools

Posted by Tyler Muth on October 11, 2010

AriaA long time ago (about 7 years), in a land far, far away (Reston, VA), I worked in a group that supported an Oracle internal application called “Aria People”. That group became the APEX development team (managed by Mike Hichwa) and at the time reported to Tim Hoechst. Tom Kyte and his team also worked for Tim at the time and the day to day management of Aria was passed around to various members of Mike and Tom’s team. Aria is a simple employee directory that is wildly popular inside Oracle. You can even download a version of Aria here as an APEX packaged application. I’m not sure who’s idea it was (Tim, Tom, Mike…) but Aria used a very effective technique to refresh its data from our global HR database. The application ran (parsed) as one schema, lets call it PEOPLE_PARSE, and that schema had synonyms that pointed to one of two data schemas, PEOPLE_A and PEOPLE_B. PEOPLE_PARSE didn’t actually own any tables or procedures. PEOPLE_A and PEOPLE_B owned all of the tables and procedures that contained the data (simplified explanation). Each night a PL/SQL package would truncate and refresh one of the schemas. Then when it was complete it would drop and recreate all of the synonyms in PEOPLE_PARSE to point to the most recently refreshed schema. In short, the source of data would alternate between PEOPLE_A and PEOPLE_B every night.  This has a couple of key advantages including the ability to truncate and load one schema while running against the other and the ability to switch back to the previous days data schema if anything went wrong. Tim mentioned many times that we should publish this technique in some form or another as it was relatively simple and exceptionally useful. APEX even uses a modified version of this technique for it’s own upgrades, just with public synonyms.

Seven years later, and I now have a current customer that could potentially benefit from this technique, so I decided to create a utility package to automate it. I see two primary scenarios for the schema layout. The 3 schema layout like Aria uses and a 4+ schema layout that introduces a synonym administrator schema.  The latter would be beneficial if you plan to add many new data schemas, such as a new schema each week or month that is a copy of production data, not just flip back and forth between 2 schemas.

Below is a short summary of the package.  Each procedure can be used independently, such as for 1-time operations or you can use the switch_synonyms procedure which calls all of the other procedures.

create or replace package synonym_util
as
procedure drop_user_views ...
procedure drop_views_for_target ...
procedure drop_user_synonyms ...
procedure drop_synonyms_for_target ...
procedure create_views_for_target ...
procedure grant_object_privs ...
procedure revoke_object_privs ...
procedure create_synonyms ...
function  get_object_owner_for_synonym ...
procedure switch_synonyms ...

Scenario 1

Lets run through Scenario 1 to provide you with an example:

  1. Install the synonym_util package in all 3 schemas
  2. Run the included grants_minimal.sql as sys to give PARSE_SCHEMA just enough privs to function.
  3. Run:
    data_a.synonym_util.grant_object_privs(p_grantee => ‘PARSE_SCHEMA’, p_objects_owner => ‘DATA_A’);
    data_b.synonym_util.grant_object_privs(p_grantee => ‘PARSE_SCHEMA’, p_objects_owner => ‘DATA_B’);
  4. You can now drop the package from DATA_A and DATA_B as we only needed it there for the grants.
  5. Run the following block as PARSE_SCHEMA:
  6. -- The first time you run this it will create synonyms pointed to p_first_schema.
    -- Each subsequent run will look at where the “EMPLOYEES” synonym is pointing,
    -- then flip the synonyms to the other schema.
    synonym_util.switch_synonyms(
    	p_first_schema        => 'DATA_A',
    	p_second_schema       => 'DATA_B',
    	p_synonym_owner       => 'PARSE_SCHEMA',
    	p_reference_synonym   => 'EMPLOYEES');

     

Scenario 2

Lets run through Scenario 2 to provide you with an example of using a SYNONYM_ADMIN schema:

  1. Install the synonym_util package in the SYNONYM_ADMIN schema.
  2. As sys, run the included grants_super_user.sql file.  Warning, you are giving SYNONYM_ADMIN a lot of power, so use with caution.
  3. Run the following code as synonym_admin:
  4. set serveroutput on
    
    begin
        dbms_output.put_line('Old Schema: '||
            synonym_util.get_object_owner_for_synonym(
                p_synonym            => 'EMPLOYEES',
                p_synonym_owner        => 'PARSE_SCHEMA')
        );
    
        synonym_util.switch_synonyms(
            p_first_schema        => 'DATA_A',
            p_second_schema        => 'DATA_B',
            p_synonym_owner        => 'PARSE_SCHEMA',
            p_reference_synonym    => 'EMPLOYEES',
            p_create_views        => 'YES',
            p_object_types      => 'TABLE:VIEW:MATERIALIZED VIEW:FUNCTION:PROCEDURE:PACKAGE:TYPE',
            p_include_grants    => 'YES',
            p_grant_types        => 'SELECT:INSERT:UPDATE:DELETE:EXECUTE'
            );
    
        dbms_output.put_line('New Schema: '||
            synonym_util.get_object_owner_for_synonym(
                p_synonym            => 'EMPLOYEES',
                p_synonym_owner        => 'PARSE_SCHEMA')
        );
    end;
    /

Notes

  • There are schema restriction globals in the package body.  If you plan on using Scenario 2, I strongly suggest you set those globals to restrict use of this utility.
  • This is a “definers rights” package. When owned by a powerful user it can be a security risk.
  • You can download the full package here: synonym_util.zip (5 kb)

Package Spec (for preview)

create or replace package synonym_util
authid definer
as
    -- Drop all views for a given user (p_view_owner)
    procedure drop_user_views(
        p_view_owner     in varchar2 default user);

    -- Drop all views for a given user (p_view_owner) that reference objects in
    -- a target schema (p_target_owner)
    procedure drop_views_for_target(
        p_target_owner    in varchar2,
        p_view_owner     in varchar2 default user);

    -- Drop all synonyms for a given user
    procedure drop_user_synonyms(p_synonym_owner in varchar2 default user);

    -- Drop synonyms for a given user (p_synonym_owner) that point to a "target" schema (p_target_owner)
    procedure drop_synonyms_for_target(
        p_target_owner  in varchar2,
        p_synonym_owner in varchar2 default user,
        p_drop_views    in varchar2 default 'YES' -- YES | NO
        );

    -- Create views in a schema (p_view_owner) that point to objects in a
    -- target schema (p_target_owner).  You can pass in a colon separated list
    -- of object types: 'TABLE:VIEW:MATERIALIZED VIEW'
    procedure create_views_for_target(
        p_target_owner    in varchar2,
        p_view_owner     in varchar2 default user,
        p_object_types  in varchar2 default 'TABLE:VIEW:MATERIALIZED VIEW');

    -- Grant object privs from p_objects_owner to p_grantee.
    -- Package must be owned by p_objects_owner or by a user that has system privs
    procedure grant_object_privs(
        p_grantee        in varchar2,
        p_objects_owner    in varchar2 default user,
        p_object_types    in varchar2 default 'TABLE:VIEW:MATERIALIZED VIEW:FUNCTION:PROCEDURE:PACKAGE:TYPE',
        p_grants        in varchar2 default 'SELECT:INSERT:UPDATE:DELETE:EXECUTE');

    procedure revoke_object_privs(
        p_grantee        in varchar2,
        p_objects_owner    in varchar2 default user);

    -- Create synonyms in a schema (p_synonym_owner) that point to all of the objects
    -- in a target schema (p_objects_owner).
    -- By default it will create VIEWS instead of synonms for TABLES, VIEWS and MATERIALIZED views
    -- as the describe of the view is much more useful than the describe of the synonym.
    -- You can optionionally choose which object types to reference.
    procedure create_synonyms(
        p_objects_owner in varchar2,
        p_synonym_owner in varchar2 default user,
        p_create_views    in varchar2 default 'YES', -- YES | NO
        p_object_types  in varchar2 default 'TABLE:VIEW:MATERIALIZED VIEW:FUNCTION:PROCEDURE:PACKAGE:TYPE');

    -- For a given synonym and synonym owner, return the schema / owner that the synonym points to
    function get_object_owner_for_synonym(
        p_synonym            in varchar2,
        p_synonym_owner        in varchar2 default user)
        return varchar2;

    -- This is the rollup of all or most of the other procedures in this package. It's meant for situations where you
    -- want to switch back and forth between 2 object owner schemas from a synonym schema
    procedure switch_synonyms(
        p_first_schema        in varchar2,
        p_second_schema        in varchar2,
        p_synonym_owner        in varchar2,
        p_reference_synonym    in varchar2,
        p_create_views        in varchar2 default 'YES', -- YES | NO
        p_object_types      in varchar2 default 'TABLE:VIEW:MATERIALIZED VIEW:FUNCTION:PROCEDURE:PACKAGE:TYPE',
        p_include_grants    in varchar2 default 'NO', -- YES | NO
        p_grant_types        in varchar2 default 'SELECT:INSERT:UPDATE:DELETE:EXECUTE' -- 'SELECT:INSERT:UPDATE:DELETE:EXECUTE'
        );

end synonym_util;
/
show errors
scenario_one

Posted in Oracle, PLSQL | 12 Comments »

Logger 1.3.0 Released

Posted by Tyler Muth on April 16, 2010

I just posted version 1.3.0 of Logger. This version includes the following:

  • Fixed major flaw in time calculation used in time_start/time_stop
  • Changed implementation of LOG_APEX_ITEMS to use the APEX views so explicit privs on wwv_flow_data are not required.

Enjoy!

Posted in PLSQL | 8 Comments »

Logger 1.2.2 Released

Posted by Tyler Muth on February 19, 2010

I justed commited Logger 1.2.2 which addresses several bugs in the previous release. The change log at the bottom of the project page has more details. Thanks to John Flack and Bill Wheeling for taking the time to find and “log” these issues.

Posted in PLSQL | 6 Comments »

Logger 1.2.0 Released

Posted by Tyler Muth on November 23, 2009

I just posted the latest version of logger, my PL/SQL logging utility to https://logger.samplecode.oracle.com/.  There are a number of new features as well as bug fixes, many of which were suggested by other people in the comments of my previous blog post on logger.  You can view the new features and fixes in the “Change Log” section at the bottom of the logger home page. Thanks again for your feedback.

On a related note, to post to the discussion forums for logger you have to request the role of “observer” for the project.  I’ve set this to automatically approve everyone so it’s instantaneous.  It’s not my choice and I’m trying to get this speed-bump removed, but for now it’s just the way it is… sorry.  I have an Announcements forum which is locked-down so only I can post to.  My thought was that if you were interested you could subscribe to that Discussion forum to receive emails when a new version is released or a major bug is identified, yet not get a bunch of “noise” from other people posting questions.

Posted in PLSQL | 3 Comments »

Logger, A PL/SQL Logging and Debugging Utility

Posted by Tyler Muth on November 3, 2009

I’ve been gradually building out a logging utility for PL/SQL over the last year or so. It’s been a huge help on some complicated projects, especially in APEX Applications with a lot of AJAX. I’m sure most people reading this have been stuck on a problem, created a logging table, and sprinkled a few inserts in their code to see what’s going on and when. It’s amazing how much faster you resolve these issues if the table is already there and the inserts are now just a short procedure call. Anyone that’s ever used Firebug probably can’t imagine it without console.log() or console.dir(). Yes, you can use dbms_output.put_line or htp.p, but those fall over pretty quick in a web environment with HTTP Posts or worse, a lot of AJAX Posts / Gets. Additionally, if you’re a Tom Kyte groupie, you’ve probably heard him talk about the value of instrumentation more than once.

Now that you know the “why”, lets talk about what Logger is.  It’s just a couple of tables and a PL/SQL package that make all of this easier for you.  I’ll give you a brief summary of features here, but for full details you should go to the project page at https://logger.samplecode.oracle.com.  So, the super short demo is:

logger@orcl> exec logger.log('hello world');

PL/SQL procedure successfully completed.

logger@orcl> select * from logger_logs_terse;

     ID LOGGER_LEVEL TIME_AGO             TEXT
------- ------------ -------------------- ---------------------
     48           16 35 seconds ago       hello world

Features Include:

  • Easily Enable / Disable all logging
  • Minimal Overhead.  There’s even a NO-OP version of the package that you can use for production if you’re really paranoid. It’s just a stub of the logger package that doesn’t write to or depend on any tables (or any other objects).
  • Automatically purges debug messages older than 7 days.  This is completely configurable
  • Ability to time blocks of code
  • Log sys_context(‘userenv’,”) variables
  • Log APEX Item Names and Values
  • Automatically captures key session information such as module, action, client_identifier including APEX session ID, timestamp, call stack.

I’d like to thank all of the community members that tested and contributed ideas for logger including:

I would love to get more feedback from the community as well.  You can either comment here, or use one of the 3 discussion forums I created for logger listed on the project page just above the download link.

The Fastest Way to Store / Retrieve a Cross Session Variable

Posted in APEX, Oracle, PLSQL | Tagged: , , | 44 Comments »

The Fastest Way to Store / Retrieve a Cross Session Variable

Posted by Tyler Muth on October 20, 2009

I’m working on a logging / debugging utility in my spare time that allows a developer to set the logging level. Currently I’m using Conditional Compilation to completely remove all logging code as this was the fastest way I could think of to do it. After talking it over with Tom Kyte, he brought up a very important point that since this technique relies on recompiling the logging package, this will invalidate any code that calls it which could be undesirable (potentially disastrous) in a production environment. His solution? Use Function Result Cache if the logger is installed in an 11g Database which should be plenty fast and not require recompilation to change the logging level.

If I’ve learned anything from Tom it’s the concept of “prove it”, so I set out to find the quickest way to store a cross session variable that will be heavily used in an application. In addition, was function result cache really fast enough given the huge benefit of the solution?

Update: John Scott posted a great suggestion in the comments of using a Global Application Context as there is no Database version / edition dependency issue and it does not require recompilation.  I added it to the test and it appears to be the new winner!  Thanks John!

I’ll post the results first since the test code is a bit lengthy. Each result is the time it took to check the variable 100,000 times.:

----------------------------------------------------------------
Simple Loop:                                     0.00247 seconds
No-op Procedure Call:                            0.06028 seconds
Procedure Call with simple IF-THEN:              0.05350 seconds
Procedure Call with Package Globals IF-THEN:     0.05019 seconds
Procedure with standard select:                  8.50581 seconds
Procedure with Query Result Cache:               9.50794 seconds
Function Result Cache:                           0.75690 seconds
Package Globals:                                 0.06700 seconds
Global Application Context:                      0.33737 seconds

As you can see, Function Result Cache is about 14 times slower than the current solution which results in a NO OP.  However, if we step back and look at this objectively, it’s still REALLY fast and as Tom said, “faster than fast enough”.  If we divide 0.7 by 100,000 we get the time per call to this function which is 0.000007.  Lets say we’re using this in an APEX environment and we call the logging package 100 times per page view which in my opinion is very generous.  Lets also assume our average page view time is 0.3 seconds.   (0.0007 / 0.3 ) * 100 = 0.23% overhead added to our application. In my opinion, 5% overhead would be a fair price to pay for an application that is instrumented with debug code.  This way when (not if) something goes wrong, the time to diagnose the problem should be substantially less. 0.23% isn’t even worth talking about.  The benefit of instrumentation FAR outweighs the overhead.

Here’s my test script (11g Database only):

set serveroutput on

-- grant create any context to user;

create or replace context test_global_ctx using set_global_ctx accessed globally;

create or replace function time_diff(
    p_start    in  timestamp,
    p_end      in  timestamp)
return varchar2
is
    l_return number;
begin
    l_return :=  extract(second from (p_end-p_start))+
           (extract(minute from (p_end-p_start))*60)+
           (extract(hour from (p_end-p_start))*60*60);
           
    return to_char(l_return,'9990D00000');
end time_diff;
/


drop table some_table;

create table some_table(
	object_name	varchar2(30),
	object_owner varchar2(30)
)
/

create unique index some_tab_index on some_table(object_name,object_owner)
/

insert into some_table
    select level object_name, level object_owner
      from dual
   connect by level <= 20;

begin     
    dbms_stats.gather_table_stats(
        ownname => user,
        tabname  => 'SOME_TABLE',
        estimate_percent => 100);
end;
/

create or replace package globals_test
as

	g_one	constant varchar2(255)   := 'One';
	g_two	constant varchar2(255)   := 'Two';
	g_three	constant varchar2(255)   := 'Three';
	
	
end globals_test;
/
show errors

create or replace procedure test1
is
begin
	null;
end;
/

create or replace procedure test1_2
is
begin
	if 1 = 1 then
		null;
	end if;
end;
/


create or replace procedure test1_3
is
begin
	if globals_test.g_one = 'One' then
		null;
	end if;
end;
/

create or replace procedure test2(p_name in varchar2)
is
	l_owner varchar2(30);
begin
	select object_owner into l_owner from some_table where object_name = p_name;
end;
/

create or replace procedure test3(p_name in varchar2)
is
	l_owner varchar2(30);
begin
	for c1 in (select /*+ result_cache */ object_owner from some_table where object_name = p_name)
    loop
        l_owner := c1.object_owner;
    end loop; --c1
end;
/

create or replace function test4(p_name in varchar2)
	return varchar2
	result_cache
	relies_on(some_table)
is
	l_owner varchar2(30);
begin
	for c1 in (select object_owner from some_table where object_name = p_name)
    loop
        l_owner := c1.object_owner;
    end loop; --c1
	
	return l_owner;
end;
/


create or replace procedure test5
is
	l_dummy varchar2(30);
begin
	l_dummy := globals_test.g_one;
end;
/


create or replace procedure set_global_ctx(
    p_var   in varchar2)
is
begin
    dbms_session.set_context(  
        namespace  => 'test_global_ctx', 
        attribute  => 'foo', 
        value      => p_var);
end;
/


accept DUMMY prompt "Ready to run tests which will take a while.  Press ENTER to continue..."
prompt  
prompt  

declare
    l_start     timestamp;
	l_object_name	varchar2(30);
	l_return varchar2(30);
	l_loop_size	pls_integer := 100000;
    
    function format_title(p_title in varchar2)
    return varchar2
    is
    begin
        return rpad(p_title,45);
    end format_title;
begin
	l_start := current_timestamp;
	for i in 1..l_loop_size 
	loop
		null;
	end loop; --i
	dbms_output.put_line('----------------------------------------------------------------');
	dbms_output.put_line(format_title('Simple Loop: ')||
                           time_diff(l_start,current_timestamp)|| ' seconds');
						   
						   
	l_start := current_timestamp;
	for i in 1..l_loop_size 
	loop
		test1;
	end loop; --i
	dbms_output.put_line(format_title('No-op Procedure Call: ')||
                           time_diff(l_start,current_timestamp)|| ' seconds');
						   
	l_start := current_timestamp;
	for i in 1..l_loop_size 
	loop
		test1_2;
	end loop; --i
	dbms_output.put_line(format_title('Procedure Call with simple IF-THEN: ')||
                           time_diff(l_start,current_timestamp)|| ' seconds');
						   
	l_start := current_timestamp;
	for i in 1..l_loop_size 
	loop
		test1_3;
	end loop; --i
	dbms_output.put_line(format_title('Procedure Call with Package Globals IF-THEN: ')||
                           time_diff(l_start,current_timestamp)|| ' seconds');
						   
	
	select object_name into l_object_name from some_table where rownum = 1; 
	l_start := current_timestamp;
	for i in 1..l_loop_size 
	loop
		test2(l_object_name);
	end loop; --i
	dbms_output.put_line(format_title('Procedure with standard select: ')||
                           time_diff(l_start,current_timestamp)|| ' seconds');
						   
	l_start := current_timestamp;
	for i in 1..l_loop_size 
	loop
		test3(l_object_name);
	end loop; --i
	dbms_output.put_line(format_title('Procedure with Query Result Cache: ')||
                           time_diff(l_start,current_timestamp)|| ' seconds');
						   
	l_start := current_timestamp;
	for i in 1..l_loop_size 
	loop
		l_return := test4(l_object_name);
	end loop; --i
	dbms_output.put_line(format_title('Function Result Cache: ')||
                           time_diff(l_start,current_timestamp)|| ' seconds');
						   
	l_start := current_timestamp;
	for i in 1..l_loop_size 
	loop
		test5;
	end loop; --i
	dbms_output.put_line(format_title('Package Globals: ')||
                           time_diff(l_start,current_timestamp)|| ' seconds');	
    
    set_global_ctx('bar');
    l_start := current_timestamp;
    for i in 1..l_loop_size 
	loop
		l_return :=  sys_context('test_global_ctx using','foo');
	end loop; --i
	dbms_output.put_line(format_title('Global Application Context: ')||
                           time_diff(l_start,current_timestamp)|| ' seconds');	
end;
/

Posted in Oracle, PLSQL | Tagged: , , | 28 Comments »

“Pretty” Date Format Function

Posted by Tyler Muth on June 26, 2009

I’m not sure who originally wrote this function (probably Tom Kyte), but I needed it yesterday and couldn’t find it. Thankfully Chris Beck tracked it down in some other code I wrote.  I made a few enhancements, but the bulk of the function is the same.  The output is almost identical to the “SINCE” date format available in Application Express.  So, if you pass in a date in the past to this function, the result will be similar to “25 seconds ago”, “1 minute ago”, “5 months ago”, etc.

Function

create or replace function date_text_format (p_date in date)
return varchar2
as
	x	varchar2(255);
begin
	x := 	case
				when sysdate-p_date < 1/1440
					then round(24*60*60*(sysdate-p_date)) || ' seconds'
				when sysdate-p_date < 1/24
					then round(24*60*(sysdate-p_date)) || ' minutes'
				when sysdate-p_date < 1
					then round(24*(sysdate-p_date)) || ' hours'
				when sysdate-p_date < 14
					then trunc(sysdate-p_date) || ' days'
				$IF $$BRITISH $THEN
					when mod(trunc(sysdate-p_date),14) = 0
						then trunc(sysdate-p_date) / 14 || ' fortnights'
				$END
				when sysdate-p_date < 60
					then trunc((sysdate-p_date)/7) || ' weeks'
				when sysdate-p_date < 365
					then round(months_between(sysdate,p_date)) || ' months'
				else round(months_between(sysdate,p_date)/12,1) || ' years'
		   end;
	x:= regexp_replace(x,'(^1 &#91;&#91;:alnum:&#93;&#93;{4,10})s','\1');
	x:= x || ' ago';
	return x;
end date_text_format;
/&#91;/sourcecode&#93;
<h3>Examples</h3>
select date_text_format(sysdate - 3/86400) the_date from dual;
select date_text_format(sysdate - 5/1440) the_date from dual;
select date_text_format(sysdate - 1/24) the_date from dual;
select date_text_format(sysdate - 3.141549) the_date from dual;
select date_text_format(sysdate - 15) the_date from dual;
select date_text_format(sysdate - 120) the_date from dual;
select date_text_format(sysdate - 365) the_date from dual;
--------------------------------------------------------------------
3 seconds ago
5 minutes ago
1 hour ago
3 days ago
2 weeks ago
4 months ago
1 year ago

Posted in Oracle, PLSQL, Uncategorized | Tagged: | 12 Comments »

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
is

	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
is
	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
	as
		l_ldap_user   varchar2(256) := NULL;
		l_ldap_pwd    varchar2(256) := NULL;
	begin
		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 );
	end;

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

	procedure handle_error(p_message in varchar2)
	as
	begin
		do_close;

		$IF $$logger $THEN
			logger.log_error(p_message);
		$END

		RAISE_APPLICATION_ERROR (-20001,p_message);
	end;

begin
	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 );
	else
		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);

		begin
      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
        do_close;
        return false;
      end if;

		exception when others then
			do_close;
			raise;
		end;

    dbms_ldap.use_exception := true;
	do_close;
	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
		do_close;
		raise;
		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
declare
	l_return boolean;
begin
	l_return := ldap_authenticate(
					p_username	=> 'first.last',
					p_password	=> '');

	if l_return = true then
		dbms_output.put_line('Authenticated');
	else
		dbms_output.put_line('Fail!');
	end;
end;
/

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