How do I use the CA certificate in an Amazon RDS for Oracle wallet to connect to an SSL or TLS endpoint?

Resolution

Note: If you receive errors when you run AWS Command Line Interface (AWS CLI) commands, then see Troubleshooting errors for the AWS CLI. Also, make sure that you're using the most recent AWS CLI version.

Download the target SSL or TLS endpoint root certificate

Complete the following steps:

1. In your web browser, enter the SSL or TLS endpoint of the web service that you want to access.

2. To view the certificate details in the address bar of your browser, choose the padlock symbol. Or, run a command that's similar to the following one:

   openssl s_client -connect status.aws.amazon.com:443

   Note: Replace status.aws.amazon.com with the URL that you want to access.
   Example output:

   CONNECTED(00000003)
   depth=2 C = US, O = Amazon, CN = Amazon Root CA 1
   verify return:1
   depth=1 C = US, O = Amazon, CN = Amazon RSA 2048 M04
   verify return:1
   depth=0 CN = *.execute-api.ap-northeast-1.amazonaws.com
   verify return:1
   ---
   Certificate chain
    0 s:/CN=*.execute-api.ap-northeast-1.amazonaws.com
      i:/C=US/O=Amazon/CN=Amazon RSA 2048 M04
    1 s:/C=US/O=Amazon/CN=Amazon RSA 2048 M04
      i:/C=US/O=Amazon/CN=Amazon Root CA 1
    2 s:/C=US/O=Amazon/CN=Amazon Root CA 1
      i:/C=US/ST=Arizona/L=Scottsdale/O=Starfield Technologies, Inc./CN=Starfield Services Root Certificate Authority - G2

3. Download the Amazon Root CA 1 certificate from the Amazon Trust Services repository.

Create the Oracle wallet, and then load the SSL or TLS endpoint root certificate

Complete the following steps:

1. Use the orapki utility to create an Oracle wallet.
   Note: The orapki utility is available when you install the Oracle client software. Oracle JDeveloper Studio edition also includes the orapki utility. You must create the wallet in the source database environment.

2. Navigate to the directory where you want to create the wallet. Then, run the following command:

   orapki wallet create -wallet . -auto_login_only

3. To add the certificate to the wallet, run the following command:

   orapki wallet add -wallet . -trusted_cert -cert AmazonRootCA1.pem -auto_login_only

4. To check the wallet's contents, run the following command:

   ls -ltrh cwallet.sso

5. To confirm that you added the certificates, run the following command:

   orapki wallet display -wallet .

Upload the wallet to an Amazon S3 bucket

Create a new Amazon Simple Storage Service (Amazon S3) bucket, or use an existing bucket. Then, use the Amazon S3 console to upload the wallet.

Or, run the following cp AWS CLI command on the client machine where you created the Oracle wallet:

aws s3 cp cwallet.sso s3://example-bucket/

Note: Replace example-bucket with the name of your S3 bucket.

Configure the DB user and permissions

Complete the following steps:

1. As the primary user, use SQL*Plus to connect to the RDS for Oracle DB instance.
2. It's a best practice to store each wallet in its own directory. To create a directory for the wallet, run the following command:

   SQL> exec rdsadmin.rdsadmin_util.create_directory('WALLET');

   Note: Replace WALLET with the name that you want for your wallet's directory.
3. To grant the necessary permissions to the DB user that you want to use the UTL_HTTP package, run the following command:

   SQL> define user='app_user';
   SQL> exec rdsadmin.rdsadmin_util.grant_sys_object('DBA_DIRECTORIES', UPPER('&user'));
   SQL> exec rdsadmin.rdsadmin_util.grant_sys_object('UTL_HTTP', UPPER('&user'));
   SQL> exec rdsadmin.rdsadmin_util.grant_sys_object('UTL_FILE', UPPER('&user'));
   SQL> BEGIN
     execute immediate 'GRANT READ, WRITE ON DIRECTORY WALLET TO ' || UPPER('&user');
   END;
   /
   SQL> BEGIN
     DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
       host       => 'status.aws.amazon.com',
       ace        => xs$ace_type(
         privilege_list => xs$name_list('resolve'),
         principal_name => UPPER('&user'),
         principal_type => xs_acl.ptype_db
       )
     );
   END;
   /
   SQL> BEGIN
     DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
       host       => 'status.aws.amazon.com',
       lower_port => 443,
       upper_port => 443,
       ace        => xs$ace_type(
                       privilege_list => xs$name_list('http'),
                       principal_name => UPPER('&user'),
                       principal_type => xs_acl.ptype_db
                     )
     );
   END;
   /

   Note: Replace app_user with the name of the RDS DB user that you want to run UTL_HTTP commands. Also, replace WALLET with the name of your wallet's directory. If the preceding command results in PLS-00201, then set the principal_type parameter to 2.

Download the wallet from your S3 bucket to your RDS DB instance

Note: You can complete the following procedure with or without Amazon S3 integration.

With Amazon S3 integration

Complete the following steps:

1. Add the Amazon S3 integration option to your DB instance.
2. Download the wallet file from your S3 bucket and upload it to your DB instance. Run the following command in a SQL*Plus session that's connected to your DB instance:

   SQL> SELECT rdsadmin.rdsadmin_s3_tasks.download_from_s3(
             p_bucket_name =>  'example-bucket',
             p_s3_prefix => 'cwallet.sso',
             p_directory_name => 'WALLET') 
             AS TASK_ID FROM DUAL;

   Note: Replace example-bucket with the name of your S3 bucket and WALLET with the name of your wallet's directory.
3. To view the results, run the following command:

   SQL> SELECT text FROM table(rdsadmin.rds_file_util.read_text_file('BDUMP','dbtask-output.log'));

   Note: Replace output with the output from the preceding step.
4. To verify that the wallet downloaded to your DB instance, run the following command:

   SQL> select * from table (rdsadmin.rds_file_util.listdir(p_directory => 'WALLET'));

   Note: Replace WALLET with the name of your wallet's directory.
5. To set the wallet path for UTL_HTTP transactions, run the following command as the RDS DB user that you want to run UTL_HTTP:

   SQL>  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';
   utl_http.set_wallet('file:/' || l_wallet_path);
   END;
   /

   Note: Replace WALLET with the name of your wallet's directory.
6. To check the web service host DNS name resolution, run the following command:

   SQL> SELECT UTL_INADDR.GET_HOST_ADDRESS(host => 'status.aws.amazon.com') FROM DUAL;

   Note: Replace status.aws.amazon.com with the URL that you want to access.
7. To use the Oracle wallet to browse the remote web service URL, run the following command:

   SQL> SELECT utl_http.request('https://status.aws.amazon.com/robots.txt') AS ROBOTS_TXT FROM DUAL;

Without S3 integration

Complete the following steps:

1. To allow the outbound traffic on the Oracle access control list (ACL), run the following command in the SQL*Plus session that's connected to your DB instance:

   SQL> define user='app_user';
   SQL> BEGIN DBMS_NETWORK_ACL_ADMIN.CREATE_ACL
        ( acl => 's3.xml', description => 'AWS S3 ACL', principal => UPPER('&user'), is_grant => TRUE, privilege => 'connect');
        COMMIT;
        END;
        /
   SQL> BEGIN DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL ( acl => 's3.xml', host => '*.amazonaws.com');
        COMMIT;
        END;
        /

2. As the RDS DB user that you want to run UTL_HTTP, create the following procedure in the SQL*Plus session that's connected to your DB instance:

   CREATE OR REPLACE PROCEDURE s3_download_presigned_url (
           p_s3_url IN VARCHAR2,
           p_local_filename IN VARCHAR2,
           p_local_directory IN VARCHAR2
       ) AS
   -- Local variables
       l_req utl_http.req;
       l_fh utl_file.file_type;
       l_resp utl_http.resp;
       l_data raw(32767);
       l_file_size NUMBER;
       l_file_exists BOOLEAN;
       l_block_size BINARY_INTEGER;
       l_http_status NUMBER;
   BEGIN
       -- Do HTTP request
       BEGIN
           l_req := utl_http.begin_request(p_s3_url, 'GET', 'HTTP/1.1');
           l_fh := utl_file.fopen(p_local_directory, p_local_filename, 'wb', 32767);
           l_resp := utl_http.get_response(l_req);
           
           -- If we get HTTP error code, write that instead
           l_http_status := l_resp.status_code;
           IF (l_http_status != 200) THEN
               dbms_output.put_line('WARNING: HTTP response '
                   || l_http_status
                   || ' - ' || l_resp.reason_phrase
                   || '. Details in ' || p_local_filename
               );
           END IF;
   
           -- Loop over response and write to file
           BEGIN
               LOOP
                   utl_http.read_raw(l_resp, l_data, 32766);
                   utl_file.put_raw(l_fh, l_data, true);
               END LOOP;
           EXCEPTION
               WHEN utl_http.end_of_body THEN
                   utl_http.end_response(l_resp);
           END;
   
           -- Get file attributes to see what we did
           utl_file.fgetattr(
               location => p_local_directory,
               filename => p_local_filename,
               fexists => l_file_exists,
               file_length => l_file_size,
               block_size => l_block_size
           );
   
           utl_file.fclose(l_fh);
           dbms_output.put_line('wrote ' || l_file_size || ' bytes');
       EXCEPTION
           WHEN OTHERS THEN
               utl_http.end_response(l_resp);
               utl_file.fclose(l_fh);
               dbms_output.put_line(dbms_utility.format_error_stack());
               dbms_output.put_line(dbms_utility.format_error_backtrace());
               raise;
       END;
   END s3_download_presigned_url;
   /

3. To generate a presigned URL, run the following presign AWS CLI command.

   aws s3 presign s3://example-bucket/cwallet.sso

   Note: Replace example-bucket with the name of your S3 bucket where your wallet is located. By default, the presigned URL is valid for 1 hour.

4. To download your wallet from your S3 bucket, run the following command in the SQL*Plus session that's connected to your DB instance.

   SQL> SET SERVEROUTPUT ON;
   SQL> set define #;
   SQL> BEGIN s3_download_presigned_url( 
    p_s3_url=> 'http://example-bucket.s3.amazonaws.com/cwallet.sso?AWSAccessKeyId=AKIAJWTPIJJA4FAQURFA&Signature=u8ysKQyp4O6ws3Qy5qZak8PfmLE%3D&Expires=1588199836',
    p_local_filename => 'cwallet.sso',
    p_local_directory => 'WALLET' 
   );
   END;
   /

   Note: Replace the value of p_s3_url with the presigned URL that you generated and WALLET with the name of your wallet's directory. Also, replace https with http in your presigned URL before you run the command.

5. To verify that the wallet uploaded to your DB instance, run the following command as the primary user:

   SQL> select * from table (rdsadmin.rds_file_util.listdir(p_directory => 'WALLET'));

   Note: Replace WALLET with the name of your wallet's directory.

6. To set the wallet path for url_http transactions, run the following command as the RDS DB user that you want to run UTL_HTTP:

   SQL>  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';
   utl_http.set_wallet('file:/' || l_wallet_path);
   END;
   /

   Note: Replace WALLET with the name of your wallet's directory.

7. To check the web service host DNS name resolution, run the following command:

   SQL> SELECT UTL_INADDR.GET_HOST_ADDRESS(host => 'status.aws.amazon.com') FROM DUAL;

   Note: Replace status.aws.amazon.com with the URL that you want to access.

8. To use the Oracle wallet to browse the remote web service URL, run the following command:

   SQL> SELECT utl_http.request('https://status.aws.amazon.com/robots.txt') AS ROBOTS_TXT FROM DUAL;

   Note: Replace status.aws.amazon.com with the URL that you want to access.

Troubleshoot errors

You receive the "ORA-28759: failure to open file" error when the wallet that you reference isn't in the location that you specified. Run the following command to list the directories and check the location of the wallet file:

SQL> select directory_name, directory_path from dba_directories where directory_name = 'WALLET';

Note: Replace WALLET with the name of your wallet's directory.

You receive the "ORA-28768: bad magic number" error when you use an AWS Key Management Service (AWS KMS) encrypted S3 bucket. Remove the encryption on your S3 bucket.

You receive the "ORA-12535: TNS: operation timed out" error when your DB instance is in a private subnet. Or, the route table has no route to the internet. Move your DB instance to a public subnet, or add a route to the internet in your route table.