Tuesday, 15 September 2015

APEX 5 Change Workspace Authentication

APEX has provided the ability to authenticate your application users against an LDAP server for quite some time.

APEX 5 now provides us the ability to change how we log into the development builder itself, and it's surprisingly easy.

Recently I modified our APEX 5 sandpit to authenticate against LDAP, so we can use our Windows passwords logging into the APEX Development Builder - one less password to manage.

Database ACL

I didn't need to apply an ACL to get this working in 11g APEX 4.2.1, but I did in 12c & 11g APEX 5 instances.

  l_acl       VARCHAR2(100) := 'ldapacl.xml';
  l_desc      VARCHAR2(100) := 'LDAP Authentication for SAGE';
  l_principal VARCHAR2(30)  := 'APEX_050000'; -- upper case
  l_host      VARCHAR2(100) := 'your-ldap-domain.com.au';
  -- Create the new ACL.
  -- Also, provide one starter privilege, granting the schema the privilege to connect.
  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);


You can check what you have already with
select * from dba_network_acl_privileges where acl like '%ldap%'

Instance Security

First log into the INTERNAL workspace and head to Instance Security settings. Under the authentication section you'll see a list of available schemes, all you need to do is change the 'current' scheme.
Instance Security Settings

Scheme Settings

The declarative settings are just like those for your application. Getting the LDAP string correct for your environment can be tricky.

At our site I had one that worked for some of us, but not others. Then I found since we're using a Microsoft LDAP we can use the simple format of domain\%LDAP_USER%
LDAP authentication attributes

Note the message on the right hand side.
NOTE: Even for external authentication schemes (e.g.HTTP Header Variable), you have to make sure that users exist as developers or administrators in the workspaces. Otherwise, Application Express will not be able to verify which workspace a user is allowed to work in.
Now the login process authenticates against LDAP, but it still uses APEX user settings to determine authorisation.
ie - what type of user are they? administrator, developer etc.

Internal Workspace Users

Since level of access is still deferred to APEX accounts, don't forget to define workspace users specifically for the INTERNAL workspace that match the LDAP username.
INTERNAL Workspace user list
It's unlikely you will have an ADMIN account in your LDAP server, which is a good thing.

Rolling Back

If you leave your INTERNAL session logged in you can use another browser (or private session) to test authentication and revert the current scheme if necessary.

Alternatively, as described by the warning when applying the scheme change, you restore the authentication from SQL Developer using an API.

You'll need administration privileges:
exec apex_instance_admin.set_parameter('APEX_BUILDER_AUTHENTICATION','APEX')

Workspace Logon Page

Don't forget to remind your developers what's going on, perhaps via a login message.

APEX 5 login message

The login message here is a running joke with our transient DBAs.

I've drafted a post where you can include JavaScript in the login message to make modifications to the login page, just like in prior versions.


Anonymous said...

Will using LDAP login credentials allow a single user to access multiple workspaces without having to create the same user in multiple workspaces?

Mike said...

I am trying to get LDAP authentication to work in one of my Apex applications. The pre-configured, LDAP Directory authentication scheme authenticates a user (me) with Active Directory credentials IF I convert %LDAP_USER% from network login name to FirstName LastName format in the DN String.

If I build on this, however, looking for a way around the login user not knowing their exact LDAP DN (am I Mike or Michael? was my name updated with my marital status change?), I am using DBMS_LDAP.init in a pre-authentication procedure (before the .simple_bind_s). It fails with ORA-24247.

Presumably, I may need my DBA to run your network_acl procedures - but why, if the built-in authentication is succeeding without it?

Mike said...

In the built-in 'LDAP Directory' authentication scheme, setting the DN string to MQ\%LDAP_USER% was successful. MQ is our domain name. DUH! Thanks to a post somewhere from 2008.

I no longer need an answer to my earlier question (but someone else may).

Scott Wesley said...

@Mike: Glad I could be your rubber duck. Don't know the answer to the other question, don't know enough about DN strings.

@Anonymous: As stated, the user record needs to exist for each relevant workspace, otherwise it's open slather for anybody with LDAP credentials.

Mike said...

Scott, this is working nicely at the Application level. And my DBA and I are playing with it as you described - at the Instance level. It was very simple to do in a Microsoft Active Directory environment.

The only request from the DBA is the ability to control this at the Workspace level, but we did not see that available. We also have multiple DBA's who would prefer NOT to login with their "normal" AD credentials to do administrative work. One option, of course, would be to create an Apex administrative/dba user in Active Directory, but those admins may frown on that.

There is also the reset script as a back door of sorts.

Royan said...

Hi Scott ,

Thank you for the very informative post.

I have 2 queries, and seek your suggestions for it.

1) If we use LDAP authentication for Workspace, How I can we restrict only the developers to Login to the App Builder Workspace. We do not want all the user accounts in LDAP to be able to login to APEX workspace.

2) As we have Custom Authentication Scheme for Applications built on APEX ( Where we can give a PL/SQL function returning TRUE or FALSE ), is there something Similar we can do for the Development Builder Workspace login.

Thank You..

Scott Wesley said...

Thanks, Royan.

1) This statement still applies:
"Since level of access is still deferred to APEX accounts"

Authentication will let you in the gate. If someone without a relevant user within APEX tries to log in, they will be denied.
If they have end-user rights only, the will receive a message saying "Restricted End User Access - since you are not a developer in this workspace, you can only change your password on this page."

2) I don't think custom authentication is an available scheme for APEX builder (see screenshot 1)