LDAP Authentication

Source: http://vanbortel.blogspot.in/2012/08/apex-and-acl-trouble-ora-31204.html

Today, I had to resolve an issue with an APEX LDAP call, that succeeded in the developer database, but failed in test with an ORA-31204: DBMS_LDAP: PL/SQL - Invalid LDAP Session.
As this concerned 11G databases, my initial thought was: "Is an ACL defined?" On the other hand, I would have expected an error like "Access denied by ACL".
Anyway, check acl's:

column host format a30
column acl format a40
set pages 66 lines 132
select host, lower_port, upper_port, acl from dba_network_acls;

This resulted in to different lists; development had to more entries than test, and yes, test lacked an ACL where the LDAP server was mentioned. Checking the principals:

col principal for a30
select acl, principal from dba_network_acl_privileges;

This revealed APEX040100 was not in the list. The following code creates an ACL, adds the resolve privilege, and adds the LDAP server to this ACL:

begin
dbms_network_acl_admin.create_acl (
acl => 'ldap.xml',
description => 'Allow ldap queries',
principal => 'APEX_040100',
is_grant => TRUE,
privilege => 'connect'
);
end;
/


begin
dbms_network_acl_admin.add_privilege (
acl => 'ldap.xml',
principal => 'APEX_040100',
is_grant => TRUE,
privilege => 'resolve'
);
end;
/

begin
dbms_network_acl_admin.assign_acl(
acl => 'ldap.xml',
host => 'ldap.home.local'
);
end;
/

commit;

After that, not only the APEX user APEX_040100 is listed as principal for the newly created ACL, also the LDAP query now succeeds.



-------------------------------------------------------------
Source :http://dbaharrison.blogspot.in/2013/11/using-active-directory-to-control.html

I’m still quite inexperienced when it comes to using Apex and am just teaching myself as i go along with various requirements that come up. One of the commonest things asked has been wanting to authenticate (check username/password) and authorise (check which groups people are in and what they can do in the app) against the central ldap service (in our case Active Directory) rather than having to maintain a separate username/password and separate group definitions and mappings.

After much googling i couldn’t really find exactly what I wanted to achieve – in the first instance to only allow users who were members of a certain group to be able to access the application. After reading many examples of how people had tried to do this with some very clever pl/sql i started to go down the route of having a completely custom scheme in place to handle this but that rapidly got quite messy and didn't seem like it was going to do what i wanted.

So here is what i actually ended up doing – which to mean seems pretty neat and simple. Hopefully I’ve not made some glaring mistake with the internals of how this works (or left some gaping security hole). Any Apex experts out there it would be great to get some feedback either way…….

So first up i just create an application as normal using the default scheme for authentication and no authorisation scheme – the defaults. You can of course just use an application you already have as this will likely be what is configured here too.

Once you have your application the first thing to do is change the authorization scheme in use to another default one from oracle “LDAP Directory”

In my test application called FOUTH (don’t ask how that name was chosen…..) the first thing to do is click on the shared components link (the compass type thing in the middle)

The choose authentication schemes

Then click on create and go into the wizard – the one i created is called ldap auth

The key parts to fill in are shown in the screenshot below

Host – this is the active directory server name – this could be a single server if thats all you have or it can be a special dns name that can connect to any of your domain controllers

Port – generally this will always be 389 if the defaults are being used

Distinguished Name String – this confused me initially but is actually pretty simple – all it needs to contain us YOUR-DOMAIN\%LDAP_USER%. Your domain should be easy to find as that's the domain you are logging in to windows. %LDAP_USER% is a special variable that Apex understands containing the value you fill in on the login page of the application

You then need to make this the current authentication scheme for the application – this will happen by default if it’s a new one.

At this point the app is set up to use ldap but will likely not work for 2 main reasons:

1. Your firewall is not open from the database server to your domain controller on port 389

2. The Network ACL additional security that got added in Oracle 11 is blocking you

Point one should be easy to test using telnet (in the example below i connected OK – if it just hangs or you don;t get this message then the firewall is closed)

[oracle@server]:DB:/oracle/home/oracle# telnet domain-controller 389
Trying x.x.x.x...
Connected to domain-controller Escape character is '^]'.
^]
telnet> quit
Connection closed.

Point two is also relatively easy to test using pl/sql – the example below which i pinched from another blogger (sorry couldn’t find the page again to credit you)

DECLARE
  l_retval      PLS_INTEGER;
  l_retval2     PLS_INTEGER;
  l_session     dbms_ldap.session;
  l_ldap_host   VARCHAR2(256);
  l_ldap_port   VARCHAR2(256);
  l_ldap_user   VARCHAR2(256);
  l_ldap_passwd VARCHAR2(256);
  l_ldap_base   VARCHAR2(256);
BEGIN
  l_retval                := -1;
  dbms_ldap.use_exception := TRUE;
  l_ldap_host             := 'domain-controller';
  l_ldap_port             := '389';
 l_ldap_user             := 'DOMAIN\USERNAME';
  l_ldap_passwd           := 'Password';
  l_session               := dbms_ldap.init(l_ldap_host, l_ldap_port);
  l_retval                := dbms_ldap.simple_bind_s(l_session,
                                                     l_ldap_user,
                                                     l_ldap_passwd);
  dbms_output.put_line('Return value: ' || l_retval);
  l_retval2 := dbms_ldap.unbind_s(l_session);
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line(rpad('ldap session ', 25, ' ') || ': ' ||
                         rawtohex(substr(l_session, 1, 8)) ||
                         '(returned from init)');
    dbms_output.put_line('error: ' || SQLERRM || ' ' || SQLCODE);
    dbms_output.put_line('user: ' || l_ldap_user);
    dbms_output.put_line('host: ' || l_ldap_host);
    dbms_output.put_line('port: ' || l_ldap_port);
    l_retval := dbms_ldap.unbind_s(l_session);
END;

This will check that you can authenticate using pl/sql to ad.

If you do get problems with ACL’s then the following code with open up this access ( again shamelessly pinched – sorry)

DECLARE
  l_acl       VARCHAR2(100) := 'ldapacl.xml';
  l_desc      VARCHAR2(100) := 'LDAP Authentication for domain controller’;
  l_principal VARCHAR2(30) := 'APEX_040200'; 
  l_host VARCHAR2(100) := 'domain-controller’;
BEGIN

  dbms_network_acl_admin.create_acl(l_acl,
                                    l_desc,
                                    l_principal,
                                    TRUE,
                                    'connect'); -- Now grant privilege to resolve DNS names.   
  dbms_network_acl_admin.add_privilege(l_acl, l_principal, TRUE, 'resolve'); -- Specify which hosts this ACL applies to.   
  dbms_network_acl_admin.assign_acl(l_acl, l_host);
  COMMIT;
END;

This allows APEX_040200 to connect and resolve and port on domain-controller – slight overkill for what we actually need and could easily be tied down but it shows the principle. This needs to be run as a DBA account (or someone that has the privileges on the dbms_network_acl packages).

So at this point i was feeling pretty pleased with myself – the login page to the app now checks my username/password against AD and only lets me in if they are OK – brilliant.

However…. it also means that any user with a windows login can access the app which is likely not what i want – only certain groups should be given access.

This is where things got more tricky (at least at first) until i took a step back and understood what the authorisation scheme was all about. It’s exactly what i want to do – protect the app (or even at a more granular level if need be) based on some group membership. Perfect – so how do i check against an ldap group – not so easy…..

The first issue was even being able to check group membership using pl/sql – handily i found another blog which did exactly what i wanted with a neat bit of code. Yet again i lost the original source for this (i’m making a habit of this – sorry again…).

The code below searches for a certain string in the group membership attributes of the user:

create or replace function ldap_auth (p_username  varchar2,p_password  varchar2)
is
  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_attr_name   varchar2(256 );
  l_vals        dbms_ldap.string_collection;
  l_ber_element dbms_ldap.ber_element;
  ldap_host     varchar2(256) := 'domain-controller';
  ldap_port     varchar2(256) := '389'; -- default port
  ldap_base     varchar2(256) := 'your-ldap-base';
  l_dn_prefix   varchar2(100) := 'YOURDOMAIN\'; -- domain, like 'USERS\'
  l_not_authenticated varchar2(100) := 'Incorrect username and/or password';
  l_not_authorized    varchar2(100) := 'Not authorized for this application';
  l_authed      boolean;
  l_memberof    dbms_ldap.string_collection;
  
BEGIN
  -- Raise exceptions on failure
  dbms_ldap.use_exception := true;
  
  -- Connect to the LDAP server
  l_session := dbms_ldap.init( hostname =>ldap_host 
                             , portnum  => ldap_port );
  
  -- Authenicate the user -- raises an exception on failure
  retval := dbms_ldap.SIMPLE_BIND_S( ld     => l_session 
                                   , dn     => l_dn_prefix || p_username
                                   , passwd => p_password ); 
  -- Once you are here you are authenticated
      
  -- Get all "memberOf" attributes    
  l_attrs(1) := 'memberOf';
  -- Searching for the user info using his samaccount (windows login )
  retval := dbms_ldap.search_s( ld       => l_session 
                              , base     => ldap_base 
                              , scope    => dbms_ldap.SCOPE_SUBTREE
                              , filter   => '(&(objectClass=*)(sAMAccountName=' || p_username || '))'
                              , attrs    => l_attrs
                              , attronly => 0
                              , res      => l_message );
  
  -- There is only one entry but still have to access that
  l_entry := dbms_ldap.first_entry( ld  => l_session 
                                  , msg => l_message );
  
  -- Get the first Attribute for the entry
  l_attr_name := dbms_ldap.first_attribute( ld        => l_session
                                          , ldapentry => l_entry       
                                          , ber_elem  => l_ber_element );


  -- Loop through all "memberOf" attributes  
  while l_attr_name is not null loop


    -- Get the values of the attribute
    l_vals := dbms_ldap.get_values( ld        => l_session
                                  , ldapentry => l_entry 
                                  , attr      => l_attr_name );
    -- Check the contents of the value
    for i in l_vals.first..l_vals.last loop
          l_authed := instr(l_vals(i), 'String to look for') > 0 ;
      exit when l_authed;
    end loop;
    exit when l_authed;    


    l_attr_name := dbms_ldap.next_attribute( ld        => l_session
                                           , ldapentry => l_entry       
                                           , ber_elem  => l_ber_element );
  end loop;


  retval := dbms_ldap.unbind_s( ld => l_session );
  
  if not l_authed
  then -- Although username / password was correct, user isn't authorized for this application
    apex_util.set_custom_auth_status ( p_status => l_not_authorized );
  end if;  


  -- Return Authenticated  
  IF l_authed
    then dbms_output.put_line('OK');
      END IF;
    
--EXCEPTION
--  when others then
--  retval := dbms_ldap.unbind_s( ld => l_session );
  -- Return NOT Authenticated  
  --apex_util.set_custom_auth_status ( p_status => l_not_authenticated );
  --return false;    
END;

The tricky bit i found out here was working out what the ldap base should be – but it was actually quite easy once i discovered the dsquery tool. To use this run the following from a dos prompt:

dsquery user –name your-username

This returns a string with CN=your-username followed by OU=blah blah blah

The ldap base is everything after your-username i.e. (OU=x,DC=y etc)

In the example code above the ldap group membership for username is being compare to the value ‘String to look for’ – if the plsql finds this in the group memberships then the function returns true – this can easily be tested in plsql. The simple code below with print ‘OK’ if the group is found (make sure serveroutput is on if doing this in sqlplus)

declare
  result boolean;
begin
  result := apex_040200.ldap_auth('username', 'password');
  IF result THEN
    dbms_output.put_line('OK');
  END IF;
end;

What i also found useful at this stage was being able to show what groups the user was in – again using the dsquery tool – for example

dsquery * full-username-as-retrieved-by-previous-dsquery-command  -scope base -attr *

Here if you supply the full ldap format name (the one with all the DC,OU etc in) it will return a large amount of output – part of which is the memberOf: section which shows all the groups that user is in

So now we’re looking good – we just need to associate this function with an authorisation scheme which protects the whole application – we’re on the home stretch now…..

So first up we again go to the shared components link

This time choosing authorisation schemes, i created a new one called authorized-ldap-group

I then simply pass the values from the username/password field from the login screen to this function and it should all work – if it fails i get the message shown below. Again nice as it confirms there username/password is valid but not their group membership – that;s much nicer than just saying ‘computer says no’

So lets try it out – i fire up the login screen click the button and it fails – with errors about ldap binds – how can that be!

Well after a bit of head scratching i discovered that there is a clear page cache that gets called in the post process section of the login screen. Aha i thought – I'll remove that and that should fix it….. but no – it still didn't;t work – it seems the values for the username/password are thrown away after the screen anyway – i guess as a security feature.

So how to get round this…..

Our AD server does not support anonymous binds so i can’t just do an anonymous lookup on it – so i have to authenticate. I need to get the username/password values before they are thrown away.

The username part is easy as this gets set up as a global variable which i can refer to as v(‘APP_USER’), however the password is more tricky – for obvious reasons that is not retained.

My solution to this was to create a temporary global variable populated in the login screen and then checked in the authorisation shared component before being cleaned out. So lets see how i did that.

First up we create a new “application item” – again from the shared components page – not much to specify here – just defaults – and i give it a name of v_password_authtemp

Then in the login process of the login screen – we edit it

And add in some simple code to populate the variable with the password

Now that’s done we have 2 variables – one with the username and one with the password – we now plug those into the authorisation scheme (and also clear out the password straight afterwards)

And now it works! I can only access the application if i have the correct group membership.

Hope this is useful for others too as I couldn’t find a definitive article on how to do this. What looked like something that would be pretty complicated is actually quite simple in reality.

Any feedback much appreciated – and apologies again to the people whose code i pinched and didn't;t credit

-----------------------------------------------------------------
Source : http://itworkedonmymachine.blogspot.in/2012/06/calling-ldap-from-plsql.html

Calling LDAP from PL/SQL

It seems to be a more frequent requirement to interact with LDAP directories from PL/SQL. Either populating records or reading them.

Here is a short test script to debug basic LDAP connection problems from Oracle PL/SQL. I have included some of the common error messages with a brief description below to help work out what is going wrong. 

DECLARE
    v_retval pls_integer;
    v_session dbms_ldap.session;
BEGIN

    dbms_ldap.use_exception := TRUE;
    BEGIN
        v_session := dbms_ldap.init(
                  machine.example.com', -- LDAP hostname
                  389 ); -- ldap port
    EXCEPTION
    WHEN OTHERS THEN
        dbms_output.put_line(
            Error with dbms_ldap.init' ||
            SQLERRM ||'|'||
            SQLCODE);
    END;
    BEGIN
    -- bind with the dn of the user and password
        v_retval := dbms_ldap.simple_bind_s(
                v_session,

                cn=scott,dc=people,dc=example,dc=com',
                'tiger' ); -- password
        dbms_output.put_line('Connected ' || v_retval);
    EXCEPTION
        WHEN OTHERS THEN
            dbms_output.put_line(

                    'Error with simple_bind_s ' ||
                    'SQLERRM: ' || SQLERRM ||' | '||
                    'SQLCODE: ' || SQLCODE ||' | '||
                    'Return Value:' || v_retval);
    END;
    v_retval := dbms_ldap.unbind_s( v_session );
END; 
This script can help discover a number of errors. Here are some of the most common.

ORA-24247: network access denied by access control list (ACL)
The call to dbms_ldap.init failed because the database access control list does not allow access to that destination.

So you can check what ACL exists for the database.
If you have access to the dba views you can use

SELECT host, lower_port, upper_port, acl
FROM dba_network_acls; 

Or as the user trying to connect to LDAP run

SELECT host, lower_port, upper_port, privilege, status
FROM user_network_acl_privileges; 
You may need to get the DBA to create a new ACL for the access to the LDAP directory. They can do this running as sys.
begin
    dbms_network_acl_admin.assign_acl (
        acl => 'acl.xml', -- the filename of the acl
        host => 'machine.example.com', -- LDAP server.
        lower_port => 389, -- LDAP port
        upper_port => 389); -- same LDAP port
end;
/
commit; 
ORA-31204: DBMS_LDAP: PL/SQL - Invalid LDAP Session. 
This will occur if you try to access a session that does not exist. From this test script if the dbms_ldap.init failed then there is no session when it tries to do the dbms_ldap.simple_bind_s. This error will occur when v_session does not have a valid value.

ORA-31202: DBMS_LDAP: LDAP client/server error: DSA is unwilling to perform. unauthenticated bind (DN with no password) disallowed
When user is passed but the password is blank and LDAP does not allow unauthenticated bind

ORA-31202: DBMS_LDAP: LDAP client/server error: Invalid credentials 
Either the user DN or the password is not correct

ORA-31202: DBMS_LDAP: LDAP client/server error: Invalid DN syntax. invalid DN
The string used for the user DN is not a valid format.

Of course there are lots of other things to do with LDAP but this will give a few hints to get started and connected.