How to Manage Sign-In, Roles, and Authorization from PL/SQL in Oracle APEX

Authentication establishes who the user is; authorization decides what they may do. In Oracle APEX both are declarative, with an authentication scheme and authorization schemes in Shared Components, but behind them sit PL/SQL packages you can call yourself. APEX_AUTHENTICATION signs users in and out, APEX_AUTHORIZATION checks authorization schemes in code, APEX_ACL grants and revokes the roles of Application Access Control, APEX_CUSTOM_AUTH reads session and cookie properties, and APEX_LDAP talks to directories such as Active Directory.

This guide covers all five with tested examples and their real output, including what the login process actually sends to the browser, why authorization results can be stale within one call, and a case-sensitivity trap with role static IDs in APEX 26.1.

Quick Reference

How to Run These Examples

The examples ran in Oracle APEX 26.1 against a test application with ID 200 that uses an Open Door authentication scheme, which accepts any password, so no real credentials appear. Its Application Access Control roles are ADMINISTRATOR, CONTRIBUTOR, READER, and approver, checked by the authorization schemes Administration Rights, Can Approve Orders, Contribution Rights, and Reader Rights. The user ADMIN has the roles ADMINISTRATOR and approver.

Several examples act as a browser request: they set up the web environment of the database session with owa.init_cgi_env, as ORDS does, and then show the HTTP headers APEX wrote. Others create APEX sessions themselves with APEX_SESSION.CREATE_SESSION, as described in the guide to creating APEX sessions and managing session state from PL/SQL. Run them as your workspace schema with server output switched on. The output under each example is exactly what the database printed.

For the declarative side, see the guides to authentication schemes, custom login, and sessions and authorization and application security.

Signing In and Out: APEX_AUTHENTICATION

LOGIN and POST_LOGIN

LOGIN is what the login page's Login process calls. It checks the user name and password with the application's current authentication scheme and, on success, creates the session cookie and redirects to the home page, or to the page the user originally asked for. p_uppercase_username, true by default, stores the name in upper case, and p_set_persistent_auth true keeps the user signed in across browser sessions ("Remember me") if the instance allows it. POST_LOGIN does the same without checking a password, for code that has already verified the user another way.

Syntax:

apex_authentication.login(p_username in varchar2, p_password in varchar2,
    p_uppercase_username in boolean default true, p_set_persistent_auth in boolean default false)
apex_authentication.post_login(p_username in varchar2, p_password in varchar2 default null,
    p_uppercase_username in boolean default true)

IS_AUTHENTICATED and IS_PUBLIC_USER

IS_AUTHENTICATED returns whether the session's user has signed in; IS_PUBLIC_USER returns the opposite, meaning the user is nobody on a public page. The next example shows both, before and after LOGIN.

Example:

declare
    l_page htp.htbuf_arr;
    l_rows integer := 999;
    l_name owa.vc_arr;
    l_val  owa.vc_arr;
    l_text varchar2(32767);
begin
    -- a browser request to the lab's login page, as ORDS sets it up
    l_name(1) := 'REQUEST_CHARSET'; l_val(1) := 'AL32UTF8';
    l_name(2) := 'SCRIPT_NAME';     l_val(2) := '/ords';
    l_name(3) := 'SERVER_NAME';     l_val(3) := 'localhost';
    l_name(4) := 'REQUEST_PROTOCOL'; l_val(4) := 'http';
    owa.init_cgi_env(4, l_name, l_val);
    htp.init;
    apex_session.create_session(p_app_id => 200, p_page_id => 9999, p_username => 'nobody');
    dbms_output.put_line('public user: ' || case when apex_authentication.is_public_user then 'yes' else 'no' end
                      || ', authenticated: ' || case when apex_authentication.is_authenticated then 'yes' else 'no' end);

    -- what the login page's process does: authenticate with the current scheme
    -- (the lab's "Open Door" accepts any password), then redirect to the home page
    apex_authentication.login(p_username => 'kim.lee', p_password => 'any password');

    dbms_output.put_line('user: ' || apex_application.g_user
                      || ', authenticated: ' || case when apex_authentication.is_authenticated then 'yes' else 'no' end);
    owa.get_page(l_page, l_rows);
    for i in 1 .. l_rows loop
        l_text := l_text || l_page(i);
    end loop;
    for h in (select column_value as line from table(apex_string.split(l_text, chr(10)))
               where regexp_like(column_value, '^(Set-Cookie|Location|Status)')) loop
        dbms_output.put_line(regexp_replace(regexp_replace(h.line, '=ORA_WWV-[^;]+', '=ORA_WWV-...'), 'session=\d+', 'session=...'));
    end loop;
end;
/

Output:

public user: yes, authenticated: no
user: KIM.LEE, authenticated: yes
Set-Cookie:ORA_WWV_APP_200=ORA_WWV-...; path=/ords/; samesite=lax; HttpOnly
Location:/ords/r/apexbook/api-lab/home?session=...
Status:302

The login produced exactly three things for the browser: an HttpOnly session cookie, a redirect to the home page, and a 302 status. The user name was stored in upper case. POST_LOGIN is the hook for flows such as a second factor, where your code verifies the user and then completes the login; see email OTP verification after login in Oracle APEX for one such flow.

LOGOUT

Ends the session and redirects to the authentication scheme's logout URL, or for single sign-on schemes, the provider's. The navigation bar's Sign Out link calls it as the URL apex_authentication.logout?p_app_id=...&p_session_id=.... p_session_id and p_app_id default to the current ones.

Syntax:

apex_authentication.logout(p_session_id in number default null, p_app_id in number default null)

Remembering the User: GET_LOGIN_USERNAME_COOKIE, SEND_LOGIN_USERNAME_COOKIE, and Persistent Authentication

The login page's Remember username option stores the user name in a cookie. SEND_LOGIN_USERNAME_COOKIE sets it, but only with p_consent true; without consent it empties an existing cookie. GET_LOGIN_USERNAME_COOKIE reads it to prefill the user name field. PERSISTENT_COOKIES_ENABLED returns whether the instance allows persistent cookies at all, and PERSISTENT_AUTH_ENABLED whether Persistent Authentication, meaning "Remember me" sign-in without a password for some days, is enabled. REMOVE_PERSISTENT_AUTH removes a user's persistent logins on all browsers and ends their sessions, and REMOVE_CURRENT_PERSISTENT_AUTH removes the current browser's.

Syntax:

apex_authentication.send_login_username_cookie(p_username in varchar2,
    p_cookie_name in varchar2 default c_default_username_cookie, p_consent in boolean default false)
apex_authentication.get_login_username_cookie(p_cookie_name in varchar2 default c_default_username_cookie) return varchar2
apex_authentication.persistent_cookies_enabled | persistent_auth_enabled return boolean
apex_authentication.remove_persistent_auth(p_username in varchar2)
apex_authentication.remove_current_persistent_auth

Plug-in Callbacks: GET_CALLBACK_URL, CALLBACK, CALLBACK2, SAML_CALLBACK, and SAML_METADATA

These serve authentication plug-ins that send the user to an external login page. GET_CALLBACK_URL returns the URL the provider should redirect back to, carrying values p_x01 to p_x10 for the plug-in. CALLBACK, or CALLBACK2 as an alternative URL for providers that reject the first, is that URL's procedure; it runs the plug-in's Ajax function with the parameters the provider sends, such as code, state, and error. The built-in Social Sign-In scheme uses them for OAuth2 and OpenID Connect. SAML_CALLBACK receives the SAML assertion of the SAML Sign-In scheme, and SAML_METADATA returns the application's SAML metadata for registering it with the identity provider.

This example simulates a request that carries the cookie of an earlier login:

Example:

declare
    l_page htp.htbuf_arr;
    l_rows integer := 999;
    l_name owa.vc_arr;
    l_val  owa.vc_arr;
    l_text varchar2(32767);
begin
    -- a request that brings the cookie of an earlier login
    l_name(1) := 'REQUEST_CHARSET'; l_val(1) := 'AL32UTF8';
    l_name(2) := 'HTTP_COOKIE';     l_val(2) := 'LOGIN_USERNAME_COOKIE=kim.lee';
    l_name(3) := 'SCRIPT_NAME';     l_val(3) := '/ords';
    l_name(4) := 'SERVER_NAME';     l_val(4) := 'localhost';
    l_name(5) := 'REQUEST_PROTOCOL'; l_val(5) := 'http';
    l_name(6) := 'SERVER_PORT';     l_val(6) := '8080';
    l_name(7) := 'HTTP_HOST';       l_val(7) := 'localhost:8080';
    owa.init_cgi_env(7, l_name, l_val);
    htp.init;
    apex_session.create_session(p_app_id => 200, p_page_id => 9999, p_username => 'nobody');

    -- the login page's "Remember username": prefill the user name ...
    dbms_output.put_line('remembered: ' || apex_authentication.get_login_username_cookie);
    -- ... and store it again after a login (only with the user's consent)
    apex_authentication.send_login_username_cookie(p_username => 'kim.lee', p_consent => true);

    dbms_output.put_line('persistent auth:    ' || case when apex_authentication.persistent_auth_enabled then 'on' else 'off' end);
    dbms_output.put_line('persistent cookies: ' || case when apex_authentication.persistent_cookies_enabled then 'on' else 'off' end);
    dbms_output.put_line('callback: ' || regexp_replace(regexp_replace(apex_authentication.get_callback_url(p_x01 => 'orbit'),
                            'p_session_id=\d+', 'p_session_id=...'), 'p_ajax_identifier=[^&]+', 'p_ajax_identifier=...'));

    owa.get_page(l_page, l_rows);
    for i in 1 .. l_rows loop
        l_text := l_text || l_page(i);
    end loop;
    dbms_output.put_line(regexp_replace(regexp_substr(l_text, 'Set-Cookie:[^' || chr(10) || ']+'), 'expires=[^;]+', 'expires=(in 6 months)'));
end;
/

Output:

remembered: kim.lee
persistent auth:    off
persistent cookies: on
callback: http://localhost:8080/ords/apex_authentication.callback?p_session_id=...&p_app_id=200&p_ajax_identifier=...&p_page_id=9999&p_x01=orbit
Set-Cookie:LOGIN_USERNAME_COOKIE=kim.lee; expires=(in 6 months); samesite=lax; HttpOnly

The consent requirement is deliberate: remembering a user name is a cookie that needs the user's permission in many jurisdictions, so tie p_consent to a checkbox the user ticks.

Checking Rights: APEX_AUTHORIZATION

IS_AUTHORIZED, HAS_ACCESS, and RESET_CACHE

IS_AUTHORIZED checks an authorization scheme by name, and HAS_ACCESS by static ID, which is the one to use in code because names can be translated or renamed. Like the declarative checks, they cache the result according to the scheme's Evaluation Point: once per session, once per page view, once per request, or always. RESET_CACHE clears the cached results, for example after changing a user's roles in the same session.

Syntax:

apex_authorization.is_authorized(p_authorization_name in varchar2) return boolean
apex_authorization.has_access(p_static_id in varchar2) return boolean
apex_authorization.reset_cache

Before this example ran, KIM.LEE's roles were set to READER only. It checks every scheme for ADMIN, then for KIM.LEE in the same database call:

Example:

declare
    procedure show(p_label varchar2) is
    begin
        dbms_output.put(rpad(p_label, 16));
        for a in (select authorization_scheme_name as name, static_id from apex_application_authorization
                   where application_id = 200 order by 1) loop
            dbms_output.put(case when apex_authorization.is_authorized(p_authorization_name => a.name)
                                 then 'yes ' else 'no  ' end);
        end loop;
        dbms_output.put_line('/ approve by static ID: ' ||
            case when apex_authorization.has_access(p_static_id => 'can-approve-orders') then 'yes' else 'no' end);
    end;
begin
    dbms_output.put_line(rpad(' ', 16) || 'Adm Apr Con Rea');
    apex_session.create_session(p_app_id => 200, p_page_id => 1, p_username => 'ADMIN');
    show('ADMIN');
    apex_session.delete_session;

    apex_session.create_session(p_app_id => 200, p_page_id => 1, p_username => 'KIM.LEE');
    show('KIM.LEE cached');    -- results cached per page view come from ADMIN's request
    apex_authorization.reset_cache;
    show('KIM.LEE reset');
    apex_session.delete_session;
end;
/

Output:

                Adm Apr Con Rea
ADMIN           yes yes yes yes / approve by static ID: yes
KIM.LEE cached  yes yes yes yes / approve by static ID: yes
KIM.LEE reset   no  no  no  yes / approve by static ID: no

The middle line is the trap. The cache belongs to the database session's request, not to the APEX session, so after switching sessions in one call, KIM.LEE got ADMIN's cached answers until RESET_CACHE. In a normal page request this does not arise, but in jobs and scripts that loop over users, reset the cache for each one. In PL/SQL conditions inside an application, prefer HAS_ACCESS with a static ID over repeating the role logic by hand.

ENABLE_DYNAMIC_GROUPS

Enables groups for the current session that need not exist in the workspace, for example groups from an LDAP directory or from an HTTP header set by a single sign-on server, so that Is In Role or Group schemes can check them. Call it in the authentication scheme's post-authentication procedure; called later, in an existing session, it has no effect.

Syntax:

apex_authorization.enable_dynamic_groups(p_group_names in apex_t_varchar2)

Application Roles: APEX_ACL

Application Access Control assigns roles to the users of an application, and Is In Role or Group authorization schemes check them. APEX_ACL changes the assignments, just as the application's Manage User Access page does. The subprograms take p_application_id (the current application by default) and p_user_name (the current user for the checks), and roles either by ID or by static ID. They work in an APEX session of the application, or outside one after apex_util.set_workspace.

Before this example ran, all of KIM.LEE's roles were removed. It runs outside an APEX session after setting the workspace.

Example:

declare
    l_role_id number;
    procedure show(p_label varchar2) is
        l_roles varchar2(400);
    begin
        select listagg(role_static_id, ', ') within group (order by role_static_id) into l_roles
          from apex_appl_acl_user_roles where application_id = 200 and user_name = 'KIM.LEE';
        dbms_output.put_line(rpad(p_label, 14) || nvl(l_roles, '-'));
    end;
begin
    -- outside an APEX session: set the workspace
    apex_util.set_workspace('APEXBOOK');

    apex_acl.add_user_role(p_application_id => 200, p_user_name => 'kim.lee', p_role_static_id => 'READER');
    select role_id into l_role_id from apex_appl_acl_roles
     where application_id = 200 and role_static_id = 'approver';
    apex_acl.add_user_role(p_application_id => 200, p_user_name => 'KIM.LEE', p_role_id => l_role_id);
    show('added');

    dbms_output.put_line('any role:     ' || case when apex_acl.has_user_any_roles(p_application_id => 200,
        p_user_name => 'KIM.LEE') then 'yes' else 'no' end);
    dbms_output.put_line('READER:       ' || case when apex_acl.has_user_role(p_application_id => 200,
        p_user_name => 'KIM.LEE', p_role_static_id => 'READER') then 'yes' else 'no' end);
    dbms_output.put_line('approver:     ' || case when apex_acl.has_user_role(p_application_id => 200,
        p_user_name => 'KIM.LEE', p_role_static_id => 'approver') then 'yes' else 'no' end);

    -- replace all roles with this list (static IDs or role IDs)
    apex_acl.replace_user_roles(p_application_id => 200, p_user_name => 'KIM.LEE',
                                p_role_static_ids => apex_t_varchar2('CONTRIBUTOR', 'READER'));
    show('replaced');

    -- would setting ADMIN's roles to just READER take ADMINISTRATOR away?
    select role_id into l_role_id from apex_appl_acl_roles
     where application_id = 200 and role_static_id = 'READER';
    dbms_output.put_line('removes admin: ' || case when apex_acl.is_role_removed_from_user(p_application_id => 200,
        p_user_name => 'ADMIN', p_role_static_id => 'ADMINISTRATOR', p_role_ids => apex_t_number(l_role_id))
        then 'yes' else 'no' end);

    apex_acl.remove_user_role(p_application_id => 200, p_user_name => 'KIM.LEE', p_role_static_id => 'READER');
    show('removed');
    apex_acl.remove_all_user_roles(p_application_id => 200, p_user_name => 'KIM.LEE');
    show('all removed');
end;
/

Output:

added         READER, approver
any role:     yes
READER:       yes
approver:     no
replaced      CONTRIBUTOR, READER
removes admin: yes
removed       CONTRIBUTOR
all removed   -

User names are not case-sensitive, so kim.lee and KIM.LEE are the same user. Role static IDs are upper-cased, though: a role whose static ID contains lower case letters, such as approver here, cannot be found by static ID. ADD_USER_ROLE by static ID raises ORA-01403 for it, and HAS_USER_ROLE returned no even though the role was granted by ID a moment earlier. Give roles upper-case static IDs, or work with role IDs. IS_ROLE_REMOVED_FROM_USER is worth using on any page that lets administrators edit roles, so nobody can lock themselves out.

Sessions and Cookies: APEX_CUSTOM_AUTH

APEX_CUSTOM_AUTH is the older API for custom authentication; most of its login functions predate authentication schemes, but its session and cookie functions remain useful.

This example needs a session of application 200, page 1, for the user KIM.LEE.

Example:

declare
    l_cookie varchar2(200);
    l_path   varchar2(200);
    l_domain varchar2(200);
    l_secure boolean;
begin
    dbms_output.put_line('user:            ' || apex_custom_auth.get_username || ' / ' || apex_custom_auth.get_user);
    dbms_output.put_line('session:         ' || case when apex_custom_auth.get_session_id = v('APP_SESSION') then 'APP_SESSION' end);
    dbms_output.put_line('session exists:  ' || case when apex_custom_auth.session_id_exists then 'yes' else 'no' end);
    dbms_output.put_line('session valid:   ' || case when apex_custom_auth.is_session_valid then 'yes' else 'no' end);
    dbms_output.put_line('workspace:       ' || case when apex_custom_auth.get_security_group_id = v('WORKSPACE_ID')
                                                     then 'WORKSPACE_ID' end);
    dbms_output.put_line('next session ID: ' || case when apex_custom_auth.get_next_session_id > 0 then 'a new number' end);
    dbms_output.put_line('item P1_X:       ' || case when apex_custom_auth.application_page_item_exists('P1_X') then 'yes' else 'no' end);
    dbms_output.put_line('page public:     ' || case when apex_custom_auth.current_page_is_public then 'yes' else 'no' end);

    apex_custom_auth.get_cookie_props(p_app_id => 200, p_cookie_name => l_cookie, p_cookie_path => l_path,
                                      p_cookie_domain => l_domain, p_secure => l_secure);
    dbms_output.put_line('cookie:          ' || l_cookie || ', secure ' || case when l_secure then 'yes' else 'no' end);

    dbms_output.put_line('LDAP DN:         ' || apex_custom_auth.ldap_dnprep(p_username => 'kim.lee'));

    apex_custom_auth.set_user(p_user => 'KIM.LEE.ADMIN');       -- changes APP_USER of this session
    dbms_output.put_line('APP_USER now:    ' || v('APP_USER'));
end;
/

Output:

user:            KIM.LEE / KIM.LEE
session:         APP_SESSION
session exists:  yes
session valid:   yes
workspace:       WORKSPACE_ID
next session ID: a new number
item P1_X:       no
page public:     no
cookie:          ORA_WWV_APP_200, secure no
LDAP DN:         kim_lee
APP_USER now:    KIM.LEE.ADMIN

SET_USER changes APP_USER for the rest of the session, which is powerful and should never be driven by user input. APEX_CUSTOM_AUTH.LOGOUT is deprecated; use APEX_AUTHENTICATION.LOGOUT. For a custom authentication scheme built on these pieces, see creating custom authentication in Oracle APEX.

Directories: APEX_LDAP

APEX_LDAP checks passwords and reads users and groups in an LDAP directory, such as Active Directory or Oracle Internet Directory, using DBMS_LDAP. Each function takes the directory's host, port, and SSL mode (p_use_ssl is 'N', 'Y', or 'A' for SSL with one-way authentication, which needs the server's certificate in a wallet), a search base, and either a user and password to bind with or a Web Credential in p_credential_static_id. The test environment has no directory, so these are not run here.

Searching a directory from SQL:

select dn, name, val
  from table(apex_ldap.search(p_host => 'ldap.example.com', p_port => 636, p_use_ssl => 'Y',
                              p_credential_static_id => 'ldap-service-account',
                              p_search_base => 'ou=people,dc=example,dc=com',
                              p_search_filter => 'mail=kim.lee@example.com',
                              p_attribute_names => 'cn,title,memberOf'))

A common pattern is to call MEMBER_OF in the post-authentication procedure and pass the result to APEX_AUTHORIZATION.ENABLE_DYNAMIC_GROUPS, so authorization schemes can use the directory's groups directly. The bind password lives in a Web Credential, as covered in the guide to calling REST APIs with APEX_WEB_SERVICE and APEX_CREDENTIAL.

Conclusion

APEX_AUTHENTICATION signs users in and out with the current authentication scheme, writing an HttpOnly session cookie and a redirect, remembers user names only with consent, and serves the callbacks of authentication plug-ins and SAML. APEX_AUTHORIZATION checks authorization schemes by name or, better, by static ID, with a cache that RESET_CACHE clears when a script switches users. APEX_ACL grants, replaces, and revokes application roles; use upper-case role static IDs in 26.1. APEX_CUSTOM_AUTH reads and sets session, user, and cookie properties, and APEX_LDAP authenticates against and searches a directory, feeding its groups into authorization through ENABLE_DYNAMIC_GROUPS.