Configuring UTL_HTTP access using certificates and an Oracle wallet

Place the root and client certificates in a single directory, and then change into this directory.

Convert the .p12 client certificate to the Java keystore.

The following example converts the client certificate named client_certificate.p12 to the Java keystore named client_keystore.jks. The keystore is then included in the Oracle wallet. The keystore password is P12PASSWORD.

orapki wallet pkcs12_to_jks -wallet ./client_certificate.p12 -jksKeyStoreLoc ./client_keystore.jks -jksKeyStorepwd P12PASSWORD

Create a directory for your Oracle wallet that is different from the certificate directory.

The following example creates the directory /tmp/wallet.

mkdir -p /tmp/wallet

Create an Oracle wallet in your wallet directory.

The following example sets the Oracle wallet password to P12PASSWORD, which is the same password used by the Java keystore in a previous step. Using the same password is convenient, but not necessary. The -auto_login parameter turns on the automatic login feature, so that you don’t need to specify a password every time you want to access it.

orapki wallet create -wallet /tmp/wallet -pwd P12PASSWORD -auto_login

Add the Java keystore to your Oracle wallet.

The following example adds the keystore client_keystore.jks to the Oracle wallet named /tmp/wallet. In this example, you specify the same password for the Java keystore and the Oracle wallet.

orapki wallet jks_to_pkcs12 -wallet /tmp/wallet -pwd P12PASSWORD -keystore ./client_keystore.jks -jkspwd P12PASSWORD

Add the root certificate for your target website to the Oracle wallet.

The following example adds a certificate named Root_CA.cer.

orapki wallet add -wallet /tmp/wallet -trusted_cert -cert ./Root_CA.cer -pwd P12PASSWORD

Add any intermediate certificates.

The following example adds a certificate named Intermediate.cer. Repeat this step as many times as need to load all intermediate certificates.

orapki wallet add -wallet /tmp/wallet -trusted_cert -cert ./Intermediate.cer -pwd P12PASSWORD

Confirm that your newly created Oracle wallet has the required certificates.

orapki wallet display -wallet /tmp/wallet -pwd P12PASSWORD

Complete the prerequisites for Amazon S3 integration with Oracle, and add the S3_INTEGRATION option to your Oracle DB instance. Ensure that the IAM role for the option has access to the Amazon S3 bucket you are using.

For more information, see Amazon S3 integration.

Log in to your DB instance as the master user, and then create an Oracle directory to hold the Oracle wallet.

The following example creates an Oracle directory named WALLET_DIR.

EXEC rdsadmin.rdsadmin_util.create_directory('WALLET_DIR');

For more information, see Creating and dropping directories in the main data storage space.

Upload the Oracle wallet to your Amazon S3 bucket.

You can use any supported upload technique.

If you're re-uploading an Oracle wallet, delete the existing wallet. Otherwise, skip to the next step.

The following example removes the existing wallet, which is named cwallet.sso.

EXEC UTL_FILE.FREMOVE ('WALLET_DIR','cwallet.sso');

Download the Oracle wallet from your Amazon S3 bucket to the Oracle DB instance.

The following example downloads the wallet named cwallet.sso from the Amazon S3 bucket named my_s3_bucket to the DB instance directory named WALLET_DIR.

SELECT rdsadmin.rdsadmin_s3_tasks.download_from_s3(
      p_bucket_name    =>  'my_s3_bucket', 
      p_s3_prefix      =>  'cwallet.sso', 
      p_directory_name =>  'WALLET_DIR') 
   AS TASK_ID FROM DUAL;

(Optional) Download a password-protected Oracle wallet.

Download this wallet only if you want to require a password for every use of the wallet. The following example downloads password-protected wallet ewallet.p12.

SELECT rdsadmin.rdsadmin_s3_tasks.download_from_s3(
      p_bucket_name    =>  'my_s3_bucket', 
      p_s3_prefix      =>  'ewallet.p12', 
      p_directory_name =>  'WALLET_DIR') 
   AS TASK_ID FROM DUAL;

Check the status of your DB task.

Substitute the task ID returned from the preceding steps for dbtask-1234567890123-4567.log in the following example.

SELECT TEXT FROM TABLE(rdsadmin.rds_file_util.read_text_file('BDUMP','dbtask-1234567890123-4567.log'));

Check the contents of the directory that you're using to store the Oracle wallet.

SELECT * FROM TABLE(rdsadmin.rds_file_util.listdir(p_directory => 'WALLET_DIR'));

For more information, see Listing files in a DB instance directory.

Log in your RDS for Oracle DB instance as the master user.

If you don't want to configure an existing database user, create a new user. Otherwise, skip to the next step.

The following example creates a database user named my-user.

CREATE USER my-user IDENTIFIED BY my-user-pwd;
GRANT CONNECT TO my-user;

Grant permission to your database user on the directory containing your Oracle wallet.

The following example grants read access to user my-user on directory WALLET_DIR.

GRANT READ ON DIRECTORY WALLET_DIR TO my-user;

Grant permission to your database user to use the UTL_HTTP package.

The following PL/SQL program grants UTL_HTTP access to user my-user.

BEGIN 
  rdsadmin.rdsadmin_util.grant_sys_object('UTL_HTTP', UPPER('my-user')); 
  END;
/

Grant permission to your database user to use the UTL_FILE package.

The following PL/SQL program grants UTL_FILE access to user my-user.

BEGIN 
  rdsadmin.rdsadmin_util.grant_sys_object('UTL_FILE', UPPER('my-user')); 
  END;
/

Log in your RDS for Oracle DB instance as the master user.

Create a Host Access Control Entry (ACE) for your user and the target website on a secure port.

The following example configures my-user to access secret.encrypted-website.com on secure port 443.

BEGIN
  DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
    host       => 'secret.encrypted-website.com', 
    lower_port => 443,
    upper_port => 443,
    ace        => xs$ace_type(privilege_list => xs$name_list('http'),
                              principal_name => 'my-user',
                              principal_type => xs_acl.ptype_db)); 
                           -- If the program unit results in PLS-00201, set
                           -- the principal_type parameter to 2 as follows:
                           -- principal_type => 2));
END;
/

principal_type => 2));

For more information, see Configuring Access Control for External Network Services in the Oracle Database documentation.

(Optional) Create an ACE for your user and target website on the standard port.

You might need to use the standard port if some web pages are served from the standard web server port (80) instead of the secure port (443).

BEGIN
  DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
    host       => 'secret.encrypted-website.com', 
    lower_port => 80,
    upper_port => 80,
    ace        => xs$ace_type(privilege_list => xs$name_list('http'),
                              principal_name => 'my-user',
                              principal_type => xs_acl.ptype_db)); 
                           -- If the program unit results in PLS-00201, set
                           -- the principal_type parameter to 2 as follows:
                           -- principal_type => 2));
END;
/

Confirm that the access control entries exist.

SET LINESIZE 150
COLUMN HOST FORMAT A40
COLUMN ACL FORMAT A50

SELECT HOST, LOWER_PORT, UPPER_PORT, ACL
  FROM DBA_NETWORK_ACLS
ORDER BY HOST;

Grant permission to your database user to use the UTL_HTTP package.

The following PL/SQL program grants UTL_HTTP access to user my-user.

BEGIN 
  rdsadmin.rdsadmin_util.grant_sys_object('UTL_HTTP', UPPER('my-user')); 
  END;
/

Confirm that related access control lists exist.

SET LINESIZE 150
COLUMN ACL FORMAT A50
COLUMN PRINCIPAL FORMAT A20
COLUMN PRIVILEGE FORMAT A10

SELECT ACL, PRINCIPAL, PRIVILEGE, IS_GRANT,
       TO_CHAR(START_DATE, 'DD-MON-YYYY') AS START_DATE,
       TO_CHAR(END_DATE, 'DD-MON-YYYY') AS END_DATE
  FROM DBA_NETWORK_ACL_PRIVILEGES
ORDER BY ACL, PRINCIPAL, PRIVILEGE;

Grant permission to your database user to use certificates for client authentication and your Oracle wallet for connections.

DECLARE
  l_wallet_path all_directories.directory_path%type;
BEGIN
  SELECT DIRECTORY_PATH 
    INTO l_wallet_path 
    FROM ALL_DIRECTORIES
   WHERE UPPER(DIRECTORY_NAME)='WALLET_DIR';
  DBMS_NETWORK_ACL_ADMIN.APPEND_WALLET_ACE(
    wallet_path => 'file:/' || l_wallet_path,
    ace         =>  xs$ace_type(privilege_list => xs$name_list('use_client_certificates'),
                                principal_name => 'my-user',
                                principal_type => xs_acl.ptype_db));
END;
/

Log in your RDS for Oracle DB instance as a database user with UTL_HTTP permissions.

Confirm that a connection to your target website can resolve the host address.

The following example gets the host address from secret.encrypted-website.com.

SELECT UTL_INADDR.GET_HOST_ADDRESS(host => 'secret.encrypted-website.com')
  FROM DUAL;

Test a failed connection.

The following query fails because UTL_HTTP requires the location of the Oracle wallet with the certificates.

SELECT UTL_HTTP.REQUEST('secret.encrypted-website.com') FROM DUAL;

Test website access by using UTL_HTTP.SET_WALLET and selecting from DUAL.

DECLARE
  l_wallet_path all_directories.directory_path%type;
BEGIN
  SELECT DIRECTORY_PATH
    INTO l_wallet_path 
    FROM ALL_DIRECTORIES
   WHERE UPPER(DIRECTORY_NAME)='WALLET_DIR';
  UTL_HTTP.SET_WALLET('file:/' || l_wallet_path);
END;
/

SELECT UTL_HTTP.REQUEST('secret.encrypted-website.com') FROM DUAL;

(Optional) Test website access by storing your query in a variable and using EXECUTE IMMEDIATE.

DECLARE
  l_wallet_path all_directories.directory_path%type;
  v_webpage_sql VARCHAR2(1000);
  v_results     VARCHAR2(32767);
BEGIN
  SELECT DIRECTORY_PATH
    INTO l_wallet_path 
    FROM ALL_DIRECTORIES
   WHERE UPPER(DIRECTORY_NAME)='WALLET_DIR';
  v_webpage_sql := 'SELECT UTL_HTTP.REQUEST(''secret.encrypted-website.com'', '''', ''file:/' ||l_wallet_path||''') FROM DUAL';
  DBMS_OUTPUT.PUT_LINE(v_webpage_sql);
  EXECUTE IMMEDIATE v_webpage_sql INTO v_results;
  DBMS_OUTPUT.PUT_LINE(v_results);
END;
/

(Optional) Find the file system location of your Oracle wallet directory.

SELECT * FROM TABLE(rdsadmin.rds_file_util.listdir(p_directory => 'WALLET_DIR'));

Use the output from the previous command to make an HTTP request. For example, if the directory is rdsdbdata/userdirs/01, run the following query.

SELECT UTL_HTTP.REQUEST('https://secret.encrypted-website.com/', '', 'file://rdsdbdata/userdirs/01') 
FROM   DUAL;