A short note on ACL's inside the database

Back in 11g oracle introduced the concept of access control lists (ACL's) to restrict what network ports could be opened from within the database - personally i think this was solving a problem that wasn't really there but I'm sure there are cases where it is useful and access does need to be very tightly controlled. It's pretty much caused nothing but hassle for me though....

Anyway it caught me out again this week- though it wasn't obvious that this was the problem. For a lot of our Apex applications we use ldap to control authentication (see post about that here ) on one system this was not working and i started to look into why.

The basic test i always do is to confirm that the ldap authentication works from plsql before delving into apex - so i run the following code block

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-name-here';
  l_ldap_port             := '389';
 l_ldap_user             := 'domain\user';
  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;
/

If that returns 0 (with serveroutput on of course) then ldap is OK.

In this case the plsql worked fine so why was apex not working?

Initially i thought maybe it's some issue with Apex itself (as we were using a newer version of Apex) but after a lot of headscratching i realised the problem...

The test above i ran as SYS (as I'm lazy and i have an alias set up to just type s to log me on) - however there is a caveat when this plsql block is run as sys - acl's don't apply..... so the code will work regardless

It's only when you run it as a non sys user (even if they have DBA rights it is ok - it's just sys is special) then you see the issue:


DECLARE
*
ERROR at line 1:
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.DBMS_LDAP_API_FFI", line 25
ORA-06512: at "SYS.DBMS_LDAP", line 48
ORA-06512: at line 17

So one to remember (and I'm sure it is documented) - Network ACL's do not apply to the SYS user.....

As soon as we set the ACL up correctly then apex started to work fine......