Este contenido no está disponible en el idioma seleccionado

Trabajamos constantemente para que el contenido esté disponible en el idioma seleccionado. Gracias por tu paciencia.

How do I connect to an SSL/TLS endpoint using the CA certificate bundle in Amazon RDS Oracle wallet?

13 minutos de lectura

I have an Amazon Relational Database Services (Amazon RDS) DB instance running Oracle. I want to securely communicate to external SSL/TLS endpoints from my DB instance using Oracle wallet.

Short description

You can use utilities, such as UTL_HTTP, to connect to remote web server endpoints from your Amazon RDS Oracle DB instance. To do this securely, you can use an Oracle wallet. An Oracle wallet is a container used to store the root and intermediate certificates needed to access the SSL/TLS endpoint of a web service.

Resolution

Connect to external SSL/TLS endpoints from your instance using Oracle wallet

1. In your web browser, open the URL (SSL/TLS endpoint of the web service) that you want to access.

2. View the certificate details in the address bar of your browser by clicking on the padlock symbol. You can also view the certificate details by running a command similar to the following on the command line of your local workstation.

$ openssl s_client -connect status.aws.amazon.com:443
CONNECTED(00000004)
depth=2 C = US, O = Amazon, CN = Amazon Root CA 1
verify return:1
depth=1 C = US, O = Amazon, OU = Server CA 1B, CN = Amazon
verify return:1
depth=0 CN = status.aws.amazon.com
verify return:1
---
Certificate chain
 0 s:CN = status.aws.amazon.com
   i:C = US, O = Amazon, OU = Server CA 1B, CN = Amazon
 1 s:C = US, O = Amazon, OU = Server CA 1B, CN = Amazon
   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 s:C = US, ST = Arizona, L = Scottsdale, O = "Starfield Technologies, Inc.", CN = Starfield Services Root Certificate Authority - G2
   i:C = US, O = "Starfield Technologies, Inc.", OU = Starfield Class 2 Certification Authority
---

Note: Be sure to replace status.aws.amazon.com with the URL of your choice.

3. Download the relevant root and intermediate certificates listed from the certificate provider. You can see from the stack trace that you need the certificates Amazon Root CA 1 and Starfield Services Root Certificate Authority - G2. You can download the certificates from Amazon trust services repository. If the certificate is available in pem format, then no further action is required.

4. If the certificate isn't available to download in pem format, download the certificate in DER/CRT format. Then, convert the downloaded certificate into pem format by running commands similar to the following on the command line of your local workstation:

$ openssl x509 -inform der -in AmazonRootCA1.cer -outform pem -out AmazonRootCA1.pem
$ openssl x509 -inform der -in SFSRootCAG2.cer -outform pem -out SFSRootCAG2.pem

5. Create a wallet using the orapki utility. The orapki utility is available when you install the appropriate oracle client software. The wallet must be created in the source database environment. The Oracle JDeveloper Studio edition also includes the orapki utility. There are different parameters to choose when you create the wallet. For example, you can use the parameter auto_login_only to create an auto login wallet (cwallet.sso) that does not need a password to open. You can modify or delete the wallet without using a password. File system permissions provide the necessary security for auto login wallets. For more information on other available options to create a wallet, see Oracle documentation for Managing Oracle wallets with orapki utility. Browse to the directory where you want to create the wallet. Then, run a command similar to the following on the command line of your local workstation.

>cd /app/client/wallet
>orapki wallet create -wallet . -auto_login_only

6. Add the two certificates to the wallet by running commands similar to the following on the command line of your local workstation:

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

7. List the contents of the wallet by running the following command on the command line of your local workstation. Verify the contents to be sure that you added the certificates.

>ls -ltrh cwallet.sso
-rw------- 1 user1 Domain Users 2.4K Apr 29 2020 cwallet.sso

>orapki wallet display -wallet .
Oracle PKI Tool Release 18.0.0.0.0 - Production
Version 18.1.0.0.0
Copyright (c) 2004, 2017, Oracle and/or its affiliates. All rights reserved.

Requested Certificates:
User Certificates:
Trusted Certificates:
Subject:        CN=Amazon Root CA 1,O=Amazon,C=US
Subject:        CN=Starfield Services Root Certificate Authority - G2,O=Starfield Technologies\, Inc.,L=Scottsdale,ST=Arizona,C=US

8. Connect to the Amazon RDS Oracle DB instance using SQL*Plus as the master user. Grant the necessary privileges to the database user who will use the UTL_HTTP package by running a command similar to the following:

SQL> define user='app_user';
SQL>  BEGIN rdsadmin.rdsadmin_util.grant_sys_object('DBA_DIRECTORIES', UPPER('&user')); END;
  2  /
old   1:  BEGIN rdsadmin.rdsadmin_util.grant_sys_object('DBA_DIRECTORIES', UPPER('&user')); END;
new   1:  BEGIN rdsadmin.rdsadmin_util.grant_sys_object('DBA_DIRECTORIES', UPPER('app_user')); END;

PL/SQL procedure successfully completed.

SQL> BEGIN rdsadmin.rdsadmin_util.grant_sys_object('UTL_HTTP', UPPER('&user')); END;
  2  /
old   1: BEGIN rdsadmin.rdsadmin_util.grant_sys_object('UTL_HTTP', UPPER('&user')); END;
new   1: BEGIN rdsadmin.rdsadmin_util.grant_sys_object('UTL_HTTP', UPPER('app_user')); END;

PL/SQL procedure successfully completed.

SQL> BEGIN rdsadmin.rdsadmin_util.grant_sys_object('UTL_FILE', UPPER('&user')); END;
  2  /
old   1: BEGIN rdsadmin.rdsadmin_util.grant_sys_object('UTL_FILE', UPPER('&user')); END;
new   1: BEGIN rdsadmin.rdsadmin_util.grant_sys_object('UTL_FILE', UPPER('app_user')); END;

PL/SQL procedure successfully completed.

9. In the SQL*Plus session connected to the RDS DB instance, create a directory for the wallet by running a command similar to the following:
Note: It's a best practice to store each wallet in its own directory.

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

10. Create a new Amazon Simple Storage Service (Amazon S3) bucket, or use an existing bucket to upload the wallet. If you have the AWS Command Line Interface (AWS CLI) installed and configured, then run the following command on the client machine where you created the Oracle wallet. You can also upload the wallet from the Amazon S3 console.

>aws s3 cp cwallet.sso s3://wallet4321/

Note: If you receive errors when running AWS CLI commands, make sure that you’re using the most recent version of the AWS CLI.

11. Download the wallet from the S3 bucket onto the RDS DB instance. You can do this either with or without Amazon S3 integration.

Download the wallet from an S3 bucket to an RDS instance using Amazon S3 integration

To download the wallet from an S3 bucket using Amazon S3 integration, see Amazon S3 integration.

1. Run the following command to download the wallet file from an S3 bucket to the RDS instance directory:

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

2. View the results by displaying the task's output file using the task-id from the previous step to confirm successful download.

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

OUTPUT
2020-04-30 20:33:03.452 UTC [INFO ] This task is about to list the Amazon S3 objects for AWS Region us-east-1, bucket name wallet4321, and prefix cwallet.sso.
2020-04-30 20:33:03.526 UTC [INFO ] The task successfully listed the Amazon S3 objects for AWS Region us-east-1, bucket name wallet4321, and prefix cwallet.sso.
2020-04-30 20:33:03.544 UTC [INFO ] This task is about to download the Amazon S3 object or objects in /rdsdbdata/userdirs/01 from bucket name wallet4321 and key cwallet.sso.
2020-04-30 20:33:03.734 UTC [INFO ] The task successfully downloaded the Amazon S3 object or objects from bucket name wallet4321 with key cwallet.sso to the location /rdsdbdata/userdirs/01.
2020-04-30 20:33:03.734 UTC [INFO ] The task finished successfully.

3. Verify that the wallet is downloaded onto the DB instance:

SQL> select * from table (rdsadmin.rds_file_util.listdir(p_directory => 'WALLET'));
FILENAME  TYPE         FILESIZE   MTIME
------------------------------------------------------------
01/              directory        4096      29-APR-20

cwallet.sso file                  2405     29-APR-20

4. In the SQL*Plus session connected to the RDS DB instance set the wallet path for utl_http transactions by running a command similar to the following:

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;
/
PL/SQL procedure successfully completed.

5. Verify the web service host DNS name resolution by running a command similar the following:

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

6. Browse the remote web service URL successfully using Oracle wallet by running a command similar to the following:

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

ROBOTS_TXT
--------------------------------------------------------------------------------
User-agent: *
Allow: /

For more information on using utl_http with an Oracle instance, see Configuring outbound network access on your Oracle DB instance.

Download wallet from an S3 bucket to an RDS instance without using Amazon S3 integration in the RDS option group

1. In the SQL*Plus session connected to the RDS DB instance, allow the outbound traffic on Oracle’s ACL (using the user you defined) by running a command similar to the following:

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;
     /
old   3: ( acl => 's3.xml', description => 'AWS S3 ACL', principal => UPPER('&user'), is_grant => TRUE, privilege => 'connect');
new   3: ( acl => 's3.xml', description => 'AWS S3 ACL', principal => UPPER('app_user'), is_grant => TRUE, privilege => 'connect');

PL/SQL procedure successfully completed.

SQL> BEGIN DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL ( acl => 's3.xml', host => '*.amazonaws.com');
     COMMIT;
     END;
     /
PL/SQL procedure successfully completed.

2. In the SQL*Plus session connected to the RDS DB instance, create the following procedure:

SQL> CREATE OR REPLACE PROCEDURE s3_download_presigned_url (
        p_s3_url IN VARCHAR2,
        p_local_filename IN VARCHAR2,
        p_local_directory IN VARCHAR2,
        p_wallet_directory IN VARCHAR2 DEFAULT NULL
    ) AS
-- Local variables
    l_req utl_http.req;
    l_wallet_path VARCHAR2(4000);
    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;
-- User-defined exceptions
    e_https_requires_wallet EXCEPTION;
    e_wallet_dir_invalid EXCEPTION;
    e_http_exception EXCEPTION;
BEGIN
    -- Validate input
    IF (regexp_like(p_s3_url, '^https:', 'i') AND
        p_wallet_directory IS NULL) THEN
        raise e_https_requires_wallet;
    END IF;
    -- Use wallet, if specified
    IF (p_wallet_directory IS NOT NULL) THEN
        BEGIN
                   SELECT directory_path INTO l_wallet_path 
                   FROM dba_directories 
                   WHERE upper(directory_name)= upper(p_wallet_directory);
                   utl_http.set_wallet('file:' || l_wallet_path);
        EXCEPTION
            WHEN NO_DATA_FOUND
                THEN raise e_wallet_dir_invalid;
        END;
    END IF;

    -- 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;
EXCEPTION

    WHEN e_https_requires_wallet THEN
        dbms_output.put_line('ERROR: HTTPS requires a valid wallet location');
    WHEN e_wallet_dir_invalid THEN
        dbms_output.put_line('ERROR: wallet directory not found');
    WHEN others THEN
        raise;
END s3_download_presigned_url;

3. Generate an S3 presigned URL by running a command similar to the following:
Note: To run this command, you need the AWS CLI to be installed and configured on the client machine. The presigned URL is valid for an hour by default. For more information, see AWS CLI reference for presign.

>aws s3 presign s3://wallet4321/cwallet.sso

https://wallet4321.s3.amazonaws.com/cwallet.sso?AWSAccessKeyId=AKIAJWTPIJJA4FAQURFA&Signature=u8ysKQyp4O6ws3Qy5qZak8PfmLE%3D&Expires=1588199836

4. In the SQL*Plus session connected to the RDS DB instance, run the s3_download_presigned_url procedure to download the wallet from the S3 bucket onto the RDS DB instance. Be sure to update the input parameters of the procedure as follows:
Note: You can download the wallet from the S3 bucket onto the RDS DB instance using http or https.

p_s3_url with the S3 presigned URL that was generated
p_local_filename with the name of the wallet file
p_local_directory with the name of the directory created on the RDS Oracle instance to store the wallet
p_wallet_directory with S3_SSL_WALLET. This directory is used by the RDS Oracle instance to store the wallet that contains the certificates for the S3 web service.

To use the S3 web service using HTTP, you can use the following example code:

Note: Replace "http" with https" in the S3 presigned URL that was previously generated.

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

To use the S3 web service using HTTPS, you can use the following example code:

Note: Storing the S3 web service wallet in the RDS instance directory S3_SSL_WALLET is a pre-requisite for using the HTTPS.

SQL> exec rdsadmin.rdsadmin_util.create_directory('S3_SSL_WALLET');
SQL> SET SERVEROUTPUT ON;
SQL> set define #;
SQL> BEGIN s3_download_presigned_url(
      p_s3_url=> 'https://wallet4321.s3.amazonaws.com/cwallet.sso?AWSAccessKeyId=AKIAJWTPIJJA4FAQURFA&Signature=u8ysKQyp4O6ws3Qy5qZak8PfmLE%3D&Expires=1588199836',
      p_local_filename => 'cwallet.sso',
      p_local_directory => 'WALLET',
      p_wallet_directory => 'S3_SSL_WALLET'
     );
     END;
    /

5. Verify that the wallet is downloaded on to the DB instance:

SQL> select * from table (rdsadmin.rds_file_util.listdir(p_directory => 'WALLET'));
FILENAME         TYPE         FILESIZE   MTIME
------------------------------------------------------------
01/              directory     4096      29-APR-20

cwallet.sso      file          2405      29-APR-20

6. In the SQL*Plus session connected to the RDS DB instance, set the wallet path for utl_http transactions by running a command similar to the following:

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;
/
PL/SQL procedure successfully completed.

7. Verify the web service host DNS name resolution by running a command similar the following:

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

8. Browse the remote web service URL successfully using Oracle wallet by running a command similar to the following:

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

Common errors

ORA-28759: failure to open file

You might receive this error if the wallet you reference is not in the location you specified. You can confirm the location of the wallet file by listing the directories:

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

DIRECTORY_NAME               DIRECTORY_PATH
--------------------------------------------------------------------------------
WALLET                       /rdsdbdata/userdirs/01

ORA-28768: bad magic number

You might receive this error if the S3 bucket and its contents are encrypted using AWS Key Management Service (AWS KMS). To resolve this error, remove the encryption on the bucket objects.

ORA-12535: TNS: operation timed out

You might receive this error under the following conditions: