Access Webservices from Oracle Database (using PFX Certificate & Basic Header Auth)

HowTo: Access Webservices from Oracle Database (using PFX Certificate & Basic Header Auth):

Step 1: Create Wallet

-- CA certificates extracted from Mozilla
curl --remote-name --time-cond cacert.pem https://curl.se/ca/cacert.pem

orapki wallet create -wallet /opt/oracle/admin/XE/MY_WALLET_NAME -pwd MY_WALLET_PWD -auto_login
orapki wallet add -wallet /opt/oracle/admin/XE/MY_WALLET_NAME -cert /tmp/cacert.pem -trusted_cert -pwd MY_WALLET_PWD 

-- ROOT certificate extracted from Browser of target
orapki wallet add -wallet /opt/oracle/admin/XE/MY_WALLET_NAME -cert /tmp/MY_ROOT_CA.pem -trusted_cert -pwd MY_WALLET_PWD

-- PFX certificate 
orapki wallet import_pkcs12 -wallet /opt/oracle/admin/XE/MY_WALLET_NAME -pkcs12file /tmp/MY_PFX.p12 -pkcs12pwd 'MY_PFX_PWD' -pwd MY_WALLET_PWD

-- Show wallet
orapki wallet display -wallet /opt/oracle/admin/XE/MY_WALLET_NAME -pwd MY_WALLET_PWD

Step 2: Create ACLs

begin
	--dbms_network_acl_admin.drop_acl(acl => 'MY_ACL.xml');
	dbms_network_acl_admin.create_acl
	(
		acl         => 'MY_ACL.xml',
		description => 'Description of MY_ACL',
		principal   => 'apex_220200',
		is_grant    => true,
		privilege   => 'connect'
	);

	dbms_network_acl_admin.assign_acl (
		acl         => 'MY_ACL.xml',
		host        => 'MY_HTTPS_HOST',
		lower_port  => 443,
		upper_port  => 443
	);    

	--dbms_network_acl_admin.drop_acl(acl => 'MY_WALLET_ACL.xml');
	dbms_network_acl_admin.create_acl
	(
		acl         => 'MY_WALLET_ACL.xml',
		description => 'Description of MY_WALLET_ACL',
		principal   => 'apex_220200',
		is_grant    => true,
		privilege   => 'use-client-certificates'
	);

	dbms_network_acl_admin.assign_wallet_acl
	(
		acl         => 'MY_WALLET_ACL.xml',
		wallet_path => 'file://opt/oracle/admin/XE/MY_WALLET_NAME'  --HINT: Two Slashes (!)
	);

	commit;
end;

--select * from dba_network_acls;
--select * from dba_network_acl_privileges;  
--select * from dba_wallet_acls;

Step 3: Call Webservice - with APEX_WEB_SERVICE.MAKE_REQUEST

declare
	l_clob    clob;  
begin
	apex_web_service.g_request_headers.delete();
	
	l_clob := apex_web_service.make_rest_request
	(
		p_url           => 'MY_HTTPS_HOST',
		p_http_method   => 'GET',
		p_wallet_path   => 'file://opt/oracle/admin/XE/MY_WALLET_NAME',  --HINT: Two Slashes (!)
		p_wallet_pwd    => 'MY_WALLET_PWD',
		p_scheme        => 'Basic',
		p_username      => 'MY_BASIC_HEADER_USER', --optional, if http basic authentication
		p_password      => 'MY_BASIC_HEADER_PWD'	 --optional, if http basic authentication
	);
				
	dbms_output.put_line(l_clob);
end;   

Quellen: 

CA certificates extracted from Mozilla

ORDS, APEX and secure REST APIs (Part 1 – secure the API)

ORDS, APEX and secure REST APIs (Part 2 – call the API)