DBA Hub

📋Steps in this guide1/6

Oracle Application Express (APEX) LDAP Authentication

Use DBMS_LDAP to perform LDAP authentication from APEX.

oracle miscconfigurationintermediate
by OracleDba
15 views
1

Create User

We need a user to hold the authentication function. Notice the user doesn't have privilege, as nothing should ever need to connect to it.

Code/Command (click line numbers to comment):

1
2
3
4
5
CREATE USER apex_ldap_auth IDENTIFIED BY NeverLogIn123
  DEFAULT TABLESPACE APEX
  QUOTA UNLIMITED ON APEX;

ALTER USER apex_ldap_auth ACCOUNT LOCK;
2

Network ACL

From Oracle 11g onward, access to network services is controlled using Access Control Lists (ACLs). You will need to create an ACL to allow access to the LDAP server. An example of an ACL is shown below.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
BEGIN
  DBMS_NETWORK_ACL_ADMIN.create_acl (
    acl          => 'ldap_acl_file.xml', 
    description  => 'ACL to grant access to LDAP server',
    principal    => 'APEX_LDAP_AUTH',
    is_grant     => TRUE, 
    privilege    => 'connect',
    start_date   => SYSTIMESTAMP,
    end_date     => NULL);

  DBMS_NETWORK_ACL_ADMIN.assign_acl (
    acl         => 'ldap_acl_file.xml',
    host        => 'ldap.example.com', 
    lower_port  => 389,
    upper_port  => NULL);

  DBMS_NETWORK_ACL_ADMIN.assign_acl (
    acl         => 'ldap_acl_file.xml',
    host        => 'ldaps.example.com', 
    lower_port  => 686,
    upper_port  => NULL);

  COMMIT;
END;
/
3

Authentication Function

The function is created in the schema. It accepts the username and password and returns a boolean of TRUE if the authentication is successful or FALSE if not. You will need to adjust the LDAP settings before using it. The previous function only checks LDAP authentication. If you need to authorize against a specific LDAP group as well, you will need to add a little more code, such as the following. Thanks the Roel Hartman for his blog post on this subject, which saved me a lot of time. Make sure the relevant workspace user can execute the procedure. For example, if my workspace user were called MY_WORKSPACE_USER, I would do the following.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
CREATE OR REPLACE FUNCTION apex_ldap_auth.ldap_auth(
  p_username IN VARCHAR2,
  p_password IN VARCHAR2
)
RETURN BOOLEAN IS
  l_ldap_host     VARCHAR2(256) := 'ldap.example.com';
  l_ldap_port     VARCHAR2(256) := '389';
  l_ldap_base     VARCHAR2(256) := 'dc=adf,dc=example,dc=com';
  l_dn_prefix     VARCHAR2(100) := 'ADF\'; -- Amend as desired'.

  l_retval        PLS_INTEGER;
  l_session       DBMS_LDAP.session;
BEGIN
  IF p_username IS NULL OR p_password IS NULL THEN
    RAISE_APPLICATION_ERROR(-20000, 'Credentials must be specified.');
  END IF;

  -- Choose to raise exceptions.
  DBMS_LDAP.use_exception := TRUE;
  
  -- Connect to the LDAP server.
  l_session := DBMS_LDAP.init(hostname => l_ldap_host,
                              portnum  => l_ldap_port);
  
  l_retval := DBMS_LDAP.simple_bind_s(ld     => l_session,
                                      dn     => l_dn_prefix || p_username,
                                      passwd => p_password);

  l_retval := DBMS_LDAP.unbind_s(l_session); 

  -- No exceptions mean you are authenticated.
  RETURN TRUE;
EXCEPTION
  WHEN OTHERS THEN
    -- Exception means authentication failed. 
    l_retval := DBMS_LDAP.unbind_s(ld => l_session);
    APEX_UTIL.set_custom_auth_status(p_status => 'Incorrect username and/or password');
    RETURN FALSE;    
END;
/

CREATE OR REPLACE FUNCTION apex_ldap_auth.ldap_auth(
  p_username IN VARCHAR2,
  p_password IN VARCHAR2
)
RETURN BOOLEAN IS
  l_ldap_host     VARCHAR2(256) := 'ldap.example.com';
  l_ldap_port     VARCHAR2(256) := '389';
  l_ldap_base     VARCHAR2(256) := 'dc=adf,dc=example,dc=com';
  l_dn_prefix     VARCHAR2(100) := 'ADF\'; -- Amend as desired'.
  l_auth_group    VARCHAR2(100) := 'MY_APP_LDAP_GROUP'; -- Amend as desired'.

  l_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_ber_element   DBMS_LDAP.ber_element;
  l_vals          DBMS_LDAP.string_collection;
  l_ok            BOOLEAN;
BEGIN
  IF p_username IS NULL OR p_password IS NULL THEN
    RAISE_APPLICATION_ERROR(-20000, 'Credentials must be specified.');
  END IF;

  -- Choose to raise exceptions.
  DBMS_LDAP.use_exception := TRUE;
  
  -- Connect to the LDAP server.
  l_session := DBMS_LDAP.init(hostname => l_ldap_host,
                              portnum  => l_ldap_port);
  
  l_retval := DBMS_LDAP.simple_bind_s(ld     => l_session,
                                      dn     => l_dn_prefix || p_username,
                                      passwd => p_password);
                                      
  -- No exceptions mean you are authenticated. Now check if authorized.

  -- Get all "memberOf" attributes    
  l_attrs(1) := 'memberOf';

  -- Searching for the user info using his samaccount (windows login)
  l_retval := DBMS_LDAP.search_s(ld       => l_session,
                                 base     => l_ldap_base,
                                 scope    => DBMS_LDAP.scope_subtree,
                                 filter   => '(&(objectClass=*)(sAMAccountName=' || p_username || '))',
                                 attrs    => l_attrs,
                                 attronly => 0,
                                 res      => l_message);
  
  -- Get the first and only entry.
  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
      -- Check the user is a member of the required group. 
      l_ok := INSTR(UPPER(l_vals(i)), l_auth_group) > 0 ;
      EXIT WHEN l_ok;
    END LOOP;
    EXIT WHEN l_ok;    

    l_attr_name := DBMS_LDAP.next_attribute(ld        => l_session,
                                            ldapentry => l_entry,       
                                            ber_elem  => l_ber_element );
  END LOOP;

  l_retval := dbms_ldap.unbind_s(ld => l_session);
 
  IF NOT l_ok THEN
    APEX_UTIL.set_custom_auth_status (p_status => 'You are not in the correct LDAP group to use this application.');
  END IF;  

  -- Return authentication + authorization result.  
  RETURN l_ok;
EXCEPTION
  WHEN OTHERS THEN
    -- Exception means authentication failed. 
    l_retval := DBMS_LDAP.unbind_s(ld => l_session);
    APEX_UTIL.set_custom_auth_status (p_status => 'Incorrect username and/or password');
    RETURN FALSE;    
END;
/

GRANT EXECUTE ON apex_ldap_auth.ldap_auth TO my_workspace_user;
4

Test It

Regardless of the type of authentication function you use, you can test it using the following code.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SET SERVEROUTPUT ON
DECLARE
  l_result BOOLEAN;
BEGIN
  l_result := apex_ldap_auth.ldap_auth('myuser', 'mypassword');
  IF l_result THEN
    DBMS_OUTPUT.put_line('OK');
  ELSE
    DBMS_OUTPUT.put_line('NOT OK');
  END IF;
END;
/
OK

PL/SQL procedure successfully completed.

SQL>
5

Create Custom Authentication Scheme

With the authentication function in place, you can create a custom authentication scheme as follows. - Application > Shared Components > Authentication Schemes - Click the "Create" button. - Select the "Based on a pre-configured scheme from gallery" option and click the "Next" button. - Enter a name, select the Scheme Type of "Custom" and an Authentication Function Name of "apex_ldap_auth.ldap_auth". - Click the "Create Authentication Scheme" option. - Run the application and test the authentication.
6

LDAPS Support

The package supports LDAP over SSL (LDAPS), so this code can easily be adjusted to work with LDAPS using the method described here . The example below is a repeat of the first example, but this time using LDAPS. Notice the call, specifying a wallet location and wallet password. The wallet must already exist in this directory, and it must contain the root certificate of the LDAPS server as a trusted certificate. You can read more about wallets here . For more information see: Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
CREATE OR REPLACE FUNCTION apex_ldap_auth.ldap_auth(
  p_username IN VARCHAR2,
  p_password IN VARCHAR2
)
RETURN BOOLEAN IS
  l_ldap_host     VARCHAR2(256) := 'ldaps.example.com';
  l_ldap_port     VARCHAR2(256) := '636';
  l_ldap_base     VARCHAR2(256) := 'dc=adf,dc=example,dc=com';
  l_dn_prefix     VARCHAR2(100) := 'ADF\'; -- Amend as desired'.

  l_retval        PLS_INTEGER;
  l_session       DBMS_LDAP.session;
BEGIN
  IF p_username IS NULL OR p_password IS NULL THEN
    RAISE_APPLICATION_ERROR(-20000, 'Credentials must be specified.');
  END IF;

  -- Choose to raise exceptions.
  DBMS_LDAP.use_exception := TRUE;
  
  -- Connect to the LDAP server.
  l_session := DBMS_LDAP.init(hostname => l_ldap_host,
                              portnum  => l_ldap_port);
l_retval := DBMS_LDAP.open_ssl(ld              => l_session,
                                 sslwrl          => 'file:/home/oracle/wallet',
                                 sslwalletpasswd => 'WalletPassword',
                                 sslauth         => 2);
l_retval := DBMS_LDAP.simple_bind_s(ld     => l_session,
                                      dn     => l_dn_prefix || p_username,
                                      passwd => p_password);
                                      
  l_retval := DBMS_LDAP.unbind_s(l_session); 

  -- No exceptions mean you are authenticated.
  RETURN TRUE;
EXCEPTION
  WHEN OTHERS THEN
    -- Exception means authentication failed. 
    l_retval := DBMS_LDAP.unbind_s(ld => l_session);
    APEX_UTIL.set_custom_auth_status(p_status => 'Incorrect username and/or password');
    RETURN FALSE;    
END;
/

Comments (0)

Please to add comments

No comments yet. Be the first to comment!