RE: Oracle / LDAP ACLs

Unfortunately, I’m not the one running the code and I don’t have access to it.

Scott Canaan ‘88
Sr Database Administrator
Information & Technology Services
Finance & Administration
Rochester Institute of Technology
o: (585) 475-7886 | f: (585) 475-7520
srcdco_at_rit.edu<mailto:srcdco_at_rit.edu> | c: (585) 339-8659

CONFIDENTIALITY NOTE: The information transmitted, including attachments, is intended only for the person(s) or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and destroy any copies of this information.

From: Kurt Van Meerbeeck <kurtvm_at_telenet.be> Sent: Wednesday, July 17, 2024 2:57 PM
To: Scott Canaan <srcdco_at_rit.edu>
Cc: Mikhail Velikikh <mvelikikh_at_gmail.com>; Oracle-L Freelists <oracle-l_at_freelists.org> Subject: Re: Oracle / LDAP ACLs

If i'm not mistaken - try setting
ALTER SESSION SET EVENTS = '10937 TRACE NAME CONTEXT FOREVER, LEVEL 4'; in your session and retry.

this will trace the ssl handshake (kind of like java.net.debug=all). It might be :

- missing root or intermediate certs
- ssl protocol too low (for example sslv3<=>tls1.2/tls1.3)
- or an ssl cipher not supported

Van: "Scott Canaan" <dmarc-noreply_at_freelists.org<mailto:dmarc-noreply_at_freelists.org>>
Aan: "Mikhail Velikikh" <mvelikikh_at_gmail.com<mailto:mvelikikh_at_gmail.com>>
Cc: "Oracle-L Freelists" <oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org>>

It seems we got the ACLs figured out. Now we are getting issues with SSL:

ORA-31202: DBMS_LDAP: LDAP client/server error: SSL handshake failed

I was given a cert and I created a wallet for it and did the following:

begin
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE('ldap_acl_file.xml','APEX_210200', true, 'use-client-certificates'); end;
/

begin
DBMS_NETWORK_ACL_ADMIN.ASSIGN_WALLET_ACL('ldap_acl_file.xml','file:/oracle/data/admin/EBSADEVL/wallet/ebsadevl_ldap/'); end;
/

Any ideas where to go from here?

Scott Canaan ‘88
Sr Database Administrator
Information & Technology Services
Finance & Administration
Rochester Institute of Technology
o: (585) 475-7886 | f: (585) 475-7520
srcdco_at_rit.edu<mailto:srcdco_at_rit.edu> | c: (585) 339-8659 CONFIDENTIALITY NOTE: The information transmitted, including attachments, is intended only for the person(s) or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and destroy any copies of this information.

From: Mikhail Velikikh <mvelikikh_at_gmail.com<mailto:mvelikikh_at_gmail.com>> Sent: Monday, July 8, 2024 5:52 PM
To: Scott Canaan <srcdco_at_rit.edu<mailto:srcdco_at_rit.edu>> Cc: Oracle-L Freelists <oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org>> Subject: Re: Oracle / LDAP ACLs

You can set the events below to enable extended debugging of the network ACL component:

alter session set events '10937 level 6:trace[xsacl]'; -- run the problem code causing ORA-24247 -- disable debugging
alter session set events '10937 off:trace[xsacl] off';

The output of event 10937 is discussed in https://berxblog.blogspot.com/2021/05/tracing-network-acls.html

On a different topic, you have not mentioned your Oracle version but the procedures you are using have been deprecated since 12.1: https://docs.oracle.com/database/121/ARPLS/d_networkacl_adm.htm#ARPLS74568

Best regards,
Mikhail Velikikh

On Mon, 8 Jul 2024 at 15:01, Scott Canaan <dmarc-noreply_at_freelists.org<mailto:dmarc-noreply_at_freelists.org>> wrote: I’m trying to allow Oracle to connect to LDAP using ACLs. The user that requested it is still getting “ORA-24247: network access denied by access control list (ACL)”. This is what I’ve tried:

BEGIN
  DBMS_NETWORK_ACL_ADMIN.create_acl (

    acl          => 'ldap_acl_file.xml',
    description  => 'ACL to grant access to LDAP server',
    principal    => 'APEX_EBA',
    is_grant     => TRUE,
    privilege    => 'connect',
    start_date   => SYSTIMESTAMP,
    end_date     => NULL);

    acl         => 'ldap_acl_file.xml',
    host        => 'ldap.rit.edu<http://ldap.rit.edu>',
    lower_port  => 389,
    upper_port  => NULL);

begin
  DBMS_NETWORK_ACL_ADMIN.assign_acl (

    acl         => 'ldap_acl_file.xml',
    host        => 'ldap.rit.edu<http://ldap.rit.edu>',
    lower_port  => 686,
    upper_port  => NULL);

  COMMIT;
END;
/

I don’t see what is wrong here.

Scott Canaan ‘88
Sr Database Administrator
Information & Technology Services
Finance & Administration
Rochester Institute of Technology
o: (585) 475-7886 | f: (585) 475-7520
srcdco_at_rit.edu<mailto:srcdco_at_rit.edu> | c: (585) 339-8659 CONFIDENTIALITY NOTE: The information transmitted, including attachments, is intended only for the person(s) or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and destroy any copies of this information.

--
http://www.freelists.org/webpage/oracle-l