Tim has written an excellent blog post on how to connect your APEX application with Azure SSO. I used this article as a base with my work, with a few modifications.
You can also set Authentication provider to OpenID Connect Provider, then you only have to supply one Azure SSO configuration URL, everything else will be automatically configured. Documentation is here. You can configure like that:
- Authentication provider: OpenID Connect Provider
- Discovery URL: https://login.microsoftonline.com/your_Azure_AD_tenant_UUID_here/v2.0/.well-known/openid-configuration
For Oracle Wallet setup, you can use my solution to automatically convert Linux system trusted certificates to Oracle Wallet format.
Another requirement for me was to make some Azure user group membership available for the APEX application. One option to query this from APEX is to make a post authentication call to Azure GraphQL endpoint /me/memberOf. For this to work, Azure administrator needs to grant your application User.Read privilege at minimum. Then /me/memberOf will list you only the group object ID-s that the logged in user is a member, but no group names nor other information (if you require to see group names, then your application also needs Group.Read.All permission, but for my case it required approvals and more red tape that I really did not want to go through).
The solution below is to create APEX post authentication procedure that will store the Azure enabled roles in APEX user session collection APP_USER_ENABLED_ROLES. Afterwards you can use the collection in APEX application as you see fit, also use it in APEX authorization schemes.
If you need to make HTTPS requests (or other TLS secured network requests) from Oracle database, you also need a wallet containing the certification authority (CA) certificates you trust. In todays distributed web world it is quite painful to keep updated. But operating systems and popular web browsers already come with bundled trust lists – the certificate authorities the OS vendor has considered trustworthy according to their policies. Wouldn’t it be nice to make it usable for Oracle Database?
Enterprise linuxes have package ca-certificates that contain the CA-s Mozilla has deemed trustworthy, this is available in file /etc/pki/ca-trust/extracted/pem/tls-ca-bundle.pem
Oracle Wallet is basically PKCS#12 container, so we can just use standard openssl command to generate it.
I tested it with Oracle Database 19.18 and it accepted the generated file without any problems.
I’ve been using the old good Radius authentication protocol to authenticate database accounts (created for humans) with Active Directory credentials. It may sound strange use case, specially since Oracle also advertises its own Active Directory integration (Centrally Managed Users) and also there is Kerberos. I’ve had the following problems with them:
- CMU – in order to use Active Directory passwords, AD schema needs to be modified and AD filter installed on AD side. I think the latter removes this feature from consideration.
- Kerberos – passwordless login is very tempting and if you get it running on Oracle side – definetly a feature to consider. But deploying at scale and maintaining it is a nightmare, almost impossible to automate.
Radius on the other hand – Windows domain controllers have Radius server built in and it is also very easy to deploy at large scale and maintain on Oracle database side.
Configure database server
First add the following to database sqlnet.ora. File /u01/app/oracle/radius.key is a simple text file containing just the Radius secret. 10.0.0.1 and 10.0.0.2 are my Radius servers running on Windows, both using port 1812.
# Radius authentication settings SQLNET.RADIUS_AUTHENTICATION_PORT = 1812 SQLNET.RADIUS_SECRET=/u01/app/oracle/radius.key SQLNET.RADIUS_AUTHENTICATION = 10.0.0.1 SQLNET.RADIUS_ALTERNATE = 10.0.0.2
In the database itself set parameter os_authent_prefix to empty string:
alter system set os_authent_prefix='' scope=spfile;
And create the database users IDENTIFIED EXTERNALLY, and database username must match WInodws AD username.
CREATE USER ilmker IDENTIFIED EXTERNALLY;
Configure the client
The bad thing with Radius authenticated users is that the database client must also support Radius. Oracle thick driver supports it just fine, also JDBC thin driver.
When using Oracle thick client (Instatnt client), turn on Radius authentication by adding it to sqlnet.ora:
$ cat ORACLE_CLIENT_HOME/network/admin/sqlnet.ora SQLNET.AUTHENTICATION_SERVICES=(RADIUS)
After that you can use this client to log into the database using both database authenticated users and Radius authenticated users.
JDBC thin driver is a little bit trickier (tested using 21c JDBC driver)
To use Radius add the following Java VM option – but the problem with that is that you cannot use database authenticated users after turning on this option.
If you want to use it with SQL Developer, add the following to product.conf file
As mentioned earlier this would disable database authenticated accounts, so in case of SQL Developer changing product.conf is not desirable.
Since 19c JDBC thin driver, it is also possible to change Java properties within the connection string using EasyConnect syntax:
One bug that I discovered in JDBC thin driver support for Radius (and Oracle is still working on it) – if you use Radius together with TCPS and database server has also enabled Oracle Native Encryption – you will get the following error from JDBC driver IO Error: Checksum fail
This is rather strange error, since when using TCPS – Oracle Native Encryption should be turned off automatically, but this error comes from Native encryption checksumming. To get around it, have to disable Native Encryption checksumming from the client side – which can also be done from inside the connection string.
We have hundreds of developers who need access hundreds of application schemas, deployed to multiple locations. Data is sensitive and there is a requirement that each human access must be done via a personal database account that has access to only allowed application schemas. That has always been a struggle for me how to manage all these access privileges in a nice and easy way. Since many many many different databases are involved, database itself cannot be the source of truth for the access privileges and so far we have just synchronised the access privileges from the source of truth system to all individual databases.
I think there is a better way now – Secure Application Roles.
The idea behind them is very flexible – there is no need to grant the individual roles to database users, the users need to execute a procedure (which will do all necessary validations) and then enable the role(s) for the user session.
Lets first set up a common SECURITY_MANAGER schema, that will contain all our security related code and logging.
create user security_manager no authentication default tablespace users quota 1g on users; grant create table, create procedure to security_manager; create table security_manager.allowed_grants ( db_username varchar2(128) not null, db_role varchar2(128) not null, primary key (db_username,db_role) ) organization index; create table security_manager.role_grant_log ( grant_time timestamp default sys_extract_utc(systimestamp) not null, db_username varchar2(128) not null, granted_role varchar2(128) not null, is_allowed number(1) not null, comments varchar2(1000), client_host varchar2(200), client_ip varchar2(50), unified_audit_session_id varchar2(100) );
Here are two helper programs, since ACTIVATE_ROLE procedure below will need to read and write to tables in SECURITY_MANAGER schema and I do not want to grant access to these tables to users directly.
-- The job of the following procedure is just logging the grant request CREATE OR REPLACE PROCEDURE security_manager.log_role_grant (p_requested_role role_grant_log.granted_role%TYPE , p_is_allowed role_grant_log.is_allowed%TYPE , p_comments role_grant_log.comments%TYPE) ACCESSIBLE BY (activate_role) IS PRAGMA AUTONOMOUS_TRANSACTION; BEGIN INSERT INTO role_grant_log (db_username, granted_role, is_allowed, comments , client_host, client_ip, unified_audit_session_id) VALUES (SYS_CONTEXT('USERENV','SESSION_USER') , p_requested_role, p_is_allowed, SYS_CONTEXT('USERENV','HOST') , SYS_CONTEXT('USERENV','IP_ADDRESS') , SYS_CONTEXT('USERENV','UNIFIED_AUDIT_SESSIONID')); COMMIT; END; / -- The following function just check the master autorisation table if -- user is allowed to activate the role or not CREATE OR REPLACE FUNCTION security_manager.is_role_allowed (p_username allowed_grants.db_username%TYPE , p_requested_role role_grant_log.granted_role%TYPE) RETURN boolean ACCESSIBLE BY (activate_role) IS v_is_role_allowed NUMBER; BEGIN SELECT COUNT(*) INTO v_is_role_allowed FROM allowed_grants WHERE db_username = p_username AND db_role = UPPER(p_requested_role); RETURN v_is_role_allowed = 1; END; /
Now the security code itself. The procedure below just checks from a simple table, if the logged in user is allowed to activate the requested role or not and also logs the request. In real life it can be much more complex – the code could make a REST call to external autorisation system and ofcourse logging should be much more detailed.
NB! The procedure must be declared AUTHID CURRENT_USER – using invokers rights.
CREATE OR REPLACE PROCEDURE security_manager.activate_role (p_requested_role allowed_grants.db_role%TYPE , p_comments role_grant_log.comments%TYPE) AUTHID CURRENT_USER IS v_activated_roles VARCHAR2(4000); BEGIN -- Check if users is allowd to activate the requested role IF NOT is_role_allowed(SYS_CONTEXT('USERENV','SESSION_USER'), p_requested_role) THEN log_role_grant(upper(p_requested_role), 0, p_comments); raise_application_error(-20000, 'You are not allowed to activate the requested role.'); END IF; -- Query all roles that are currently active for the session and append the requested role to that list SELECT listagg(role, ',') WITHIN GROUP (ORDER BY role) INTO v_activated_roles FROM ( SELECT role FROM session_roles UNION SELECT upper(p_requested_role) FROM dual ); -- Activate all roles log_role_grant(upper(p_requested_role), 1, p_comments); DBMS_SESSION.SET_ROLE(v_activated_roles); END; /
Now I create the role itself and grant the role read access to one application table. Here IDENTIFIED USING clause does the magic – it tells Oracle that sec_app_role_test1 role can only be enabled by security_manager.activate_role procedure.
CREATE ROLE sec_app_role_test1 IDENTIFIED USING security_manager.activate_role; GRANT READ ON app1.t1 TO sec_app_role_test1;
And my developer personal account is called ILMKER and this account only needs execute privileges on my security package. In real life you would grant this execute to a common role that all developers have (in my case that custom role is called PERSONAL_ACCOUNT).
GRANT execute ON security_manager.activate_role TO ilmker;
By default ILMKER user cannot access table APP1.T1.
ILMKER SQL> SELECT * FROM session_roles; ROLE ----------------- PERSONAL_ACCOUNT ILMKER SQL> SELECT * FROM app1.t1; SQL Error: ORA-00942: table or view does not exist
Lets test using my developer account ILMKER… first I try to request a role that I do not have been granted access to. No luck, I get the exception “ORA-20000: You are not allowed to activate the requested role.”
SQL> SELECT * FROM session_roles; ROLE -------------------- PERSONAL_ACCOUNT SQL> exec security_manager.activate_role('sec_app_role_test1', 'Jira ref: ABC-490'); ORA-20000: You are not allowed to activate the requested role. SQL> SELECT * FROM session_roles; ROLE -------------------- PERSONAL_ACCOUNT SQL> SELECT * FROM app1.t1; SQL Error: ORA-00942: table or view does not exist
After security administrator grants me the role – inserts a row to security_manager.allowed_grants table for this example and NOT executing Oracle GRANT command.
insert into security_manager.allowed_grants (db_username, db_role) values ('ILMKER', upper('sec_app_role_test1')); commit;
I ask my developer to run again.
SQL> SELECT * FROM session_roles; ROLE -------------------- PERSONAL_ACCOUNT SQL> exec security_manager.activate_role('sec_app_role_test1', 'Jira ref: ABC-490'); PL/SQL procedure successfully completed. SQL> SELECT * FROM session_roles; ROLE -------------------- PERSONAL_ACCOUNT SEC_APP_ROLE_TEST1 SQL> SELECT * FROM app1.t1; no rows selected
The developer is happy now! Role was activated in the developer session and developer was able to read the application table. All requests were also logged by SECURITY_MANAGER schema.
SQL> select * from security_manager.role_grant_log; GRANT_TIME DB_USERNAME GRANTED_ROLE CLIENT_HOST CLIENT_IP UNIFIED_AUDIT_SESSION_ID IS_ALLOWED COMMENTS 2023-05-12 15:05:28,478732000 ILMKER SEC_APP_ROLE_TEST1 STH-FVFFV04QQ05R 10.88.17.241 3428220339 0 Jira ref: ABC-490 2023-05-12 15:10:41,923908000 ILMKER SEC_APP_ROLE_TEST1 STH-FVFFV04QQ05R 10.88.17.241 3428220339 1 Jira ref: ABC-490
I think this is a powerful feature to control access to data based on much more complex criteria than just DBA executing GRANT commands. Before enabling the role code can make a REST query to external autorisation system, check the client host IP, check the client authentication method, enable detailed unified auditing policies. Possibilities are endless!
Lets say your company has an automated system that propagates and renews TLS certificates for each server automatically, for example using NDES. And you want to use the same files also for your Oracle database TCPS protocol connections.
For Oracle database the database server user certificate needs to be placed inside Oracle wallet and the default workflow for Oracle wallet is that you create the private key and certificate request inside Oracle wallet and then use the exported certificate request to request a certificate from your certificate authority and then you import that certificate to the wallet.
But you have already issued certificate and private key as separate files, like many popular open source tools like it. It is possible to create Oracle wallet from these.
We have two files.
- Private key – /etc/pki/tls/private/servername.key
- Issued certificate – /etc/pki/tls/private/servername.crt
We also need the certificate authority certificates (all of them, the full chain) who issued your server certificate. You can usually download them from your CA webpage or there also might be URL embedded in the certificate itself.
I’m using orapki executable from 19c installation.
First create an empty Oracle wallet.
# orapki wallet create -wallet /oracle/wallet/location -pwd oracle_wallet_password
Then add all the certificate authority certificates to the wallet
# You can check the certificate if it has links to issuer server certificates embedded. You need to repeat that until there is no output (you have reached the root) and download each file along the way. Usually there are about 2-3 of them. # openssl x509 -in /etc/pki/tls/private/servername.crt -inform pem -noout -issuer -ext authorityInfoAccess # orapki wallet add -wallet /oracle/wallet/location -pwd oracle_wallet_password -trusted_cert -cert /tmp/ca_certificate_intermediate.cer # orapki wallet add -wallet /oracle/wallet/location -pwd oracle_wallet_password -trusted_cert -cert /tmp/ca_certificate_root.cer
A quick sanity check, the server certificate you have should have TLS Web Server Authentication extended key usage set. If it is not, ask your CA to reissue the certificate. Without it your clients might get an error like this: IO error: extended key usage does not permit use for TLS server authentication
# openssl x509 -ext extendedKeyUsage -in /etc/pki/tls/private/servername.crt -noout X509v3 Extended Key Usage: TLS Web Client Authentication, TLS Web Server Authentication
If the private key is unencrypted, then you first need to encrypt it in PKCS#8 format – into a separate temporary file. oraplki cannot import unencrypted private key.
# Check first, if the key in unencrypted # head -1 /etc/pki/tls/private/servername.key -----BEGIN PRIVATE KEY----- # export wallet_build_pass="long_key_encryption_password" # openssl pkcs8 -topk8 -in /etc/pki/tls/private/servername.key -out /tmp/encrypted.key -passout env:wallet_build_pass # Lets check that the resulting file has encrypted key # # head -1 /tmp/encrypted.key -----BEGIN ENCRYPTED PRIVATE KEY-----
Now you can import the private key and certificate into Oracle wallet.
# orapki wallet import_private_key -wallet /oracle/wallet/location -pwd oracle_wallet_password -pvtkeyfile /tmp/encrypted.key -pvtkeypwd long_key_encryption_password -cert /etc/pki/tls/private/servername.crt
Code – to automatically renew Oracle wallet. NB! read and modify the code according to your needs.