Using Active Directory to control Authentication and Authorisation to Apex

 

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)

 

apex1

The choose authentication schemes

apex2

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

apex3

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

apex4

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


apex2


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


apex5


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


apex6


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


apex65


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


apex7


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)


apex8





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


16 comments:

  1. There is a nasty flaw with the above - please read my other post to make sure you have closed the security hole!

    http://dbaharrison.blogspot.de/2014/07/when-clever-security-ends-up-as-no.html

    ReplyDelete
  2. HI,

    I am trying to implement this at a workspace application level, not able to make it run.
    Can someone please assist.

    Followed this blog as well as this one

    http://www.grassroots-oracle.com/2013/09/using-ldap-to-authenticate-your-apex.html

    Thanks
    Rahul

    ReplyDelete
    Replies
    1. Hi Rahul,
      I think i need a bit more to go on that 'it doesnt work' :-)

      The most likely cause of the problem is i think -

      1) Database Network ACL is blocking the ldap port
      2) incorrect ldap details entered in APEX

      Did you get the plsql to work OK - that needs to function before you even think about Apex

      Cheers,
      Rich

      Delete
  3. thank you for this post...it was very helpful.

    ReplyDelete
  4. Amazing blog. If only all blogs explained so well. You helped me so much. Thank you for taking the time to make this.

    ReplyDelete
  5. Hi Richard!
    I have tried the settings which you gave me on the link. But I ran into a problem. I can authenticate now from AD. I set up the ldap_auth function but when I run it, it gives me "network access denied by access control list (ACL)". I have tried the PL/SQL test codes, they run fine. Only this function gives back this error. I am running on 4.0.2.00.08.

    ReplyDelete
    Replies
    1. Hi,
      Did you do the ACL tests as a 'normal' user - if you run it as SYS it bypasses all the security anyway.

      Try creating a basic user and then run the plsql as that user.

      Cheers,
      Rich

      Delete
  6. Hi,
    I have implemented your solution.But it is not working as i have slightly different situation. I am asked to authenticate users under group A.This group a has various groups inside it say B,C,D and the user is memeber of these child group.I need all the users to be validated who are in B,C,D.and i can not put B,C,D's name in the string match as there may be new child group added to A.
    Please suggest how i can proceed.

    Regards
    Adrita

    ReplyDelete
  7. Hi Adrita,
    Sorry somehow this comment passed me by. I think what you want to do is possible (if i understand it correctly) - it just needs some extra effort to search for all the children of the parent group - and for all thos e groups then search for memebership of the login.

    It's not a huge amount of code i don't think but long enough that i can't really spend the time on it for you.

    You'd need to start with a different variation on dbms_ldap.search i think to identify the groups

    and then loop through the groups using the code you already have above.

    There may also be a way to just identify child memberhsip directly via some sort of different search - but I'm not sure.

    Cheers,
    Rich

    ReplyDelete
  8. Hi,

    I implemented your solution but have trouble passing username & password.

    As recommended I use
    - v('APP_USER') and
    - application item

    The problem is always I log out the authorization schema is executed (it looks like) and the following error will be thrown:

    "Error processing authorization.

    ORA-31202: DBMS_LDAP: LDAP client/server error: Invalid credentials. 80090308: LdapErr: DSID-0C0903A9, comment: AcceptSecurityContext error, data 57, v1db1"

    Can you help please?

    Thanks Reinhard




    ReplyDelete
  9. Hi Reinhard,
    I did a quick google on that error message and there is very little on it.

    Is there anything unusual about the dn - very long or something? (see this link https://support.microsoft.com/en-gb/kb/974522)

    Does it contain double quotes?

    Have you tried hardcoding it with username/password to check nothing is going wrong there?

    Regards,
    Rich

    ReplyDelete
    Replies
    1. Hi Rich,

      Thanks for you reply.

      Our domain setup does not look like unusal.

      Username/password hardcoded works fine:

      RETURN ldap_auth('','');

      Username/password from login page failed:

      RETURN ldap_auth(v('APP_USER',:V_PASSWORD_AUTHTEMP);

      no idea why.

      Used username/password do not contain double quotes...

      Please help.

      Regards,
      Reinhard

      Delete
  10. Hi reinhard - not sure if it's just a type in your last post - but the line

    RETURN ldap_auth(v('APP_USER',:V_PASSWORD_AUTHTEMP);

    should be

    RETURN ldap_auth(v('APP_USER'),:V_PASSWORD_AUTHTEMP);

    Cheers,
    Rich

    ReplyDelete
    Replies
    1. Hi Rich,

      This was just a typo. sorry

      It looks like that the authorization is also verified when logging out. But by that time the variables are already reset to NULL. I workarounded this by checking of V_PASSWORD_AUTHTEMP and V_USERNAME_AUTHTEMP (new) to NULL. Then it's fine.

      But how have you implemented 'reconnect' in case of not authorized? I found here (https://community.oracle.com/thread/967588) an idea but it does not work so far for me

      Thanks Reinhard

      Delete
  11. This comment has been removed by the author.

    ReplyDelete
  12. Hi ,

    I have apex URL integrated within my application.

    URL like : https://slms.ABCDEF.com/apex/f?p=107:3:::::P_USER:c2FyZ2JlaGU=

    Here from the other application we are passing :P_USER which is the user name of the current user .
    I want to use that :P_USER variable value in my apex report for user authentication and other query operations . Please guide me how i can use the :P_USER value passed from the URL in APEX authentications .

    Issues I am facing
    -------------------
    1. I can't able to use the :P_USER variable value in APEX to authenticate the user so the report is open for all now .(For now i am using apex authentication for temporary).

    2. If somebody copy the URL and paste in other browser then the APEX report UI opens with out any security . How to prevent the copy paste URL working for customer ?

    3. What are the other security method i can apply in the APEX by using the variable passed in URL ? :P_USER .

    4. How i can achieve the SSO (Single sign on) APEX . We have integrated the APEX URL in other application and we have a requirement to implement the SSO . But i am new to apex and i have no idea how to implement . Could you please guide me on solving these issues .

    Please send some steps how to do that in my email: gyanabehera2013@gmail.com .

    Thank you in advance .

    Regards
    Gyana

    ReplyDelete