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

11 minutos de lectura
0

I want to securely communicate to external SSL/TLS endpoints from my Amazon Relational Database Service (Amazon RDS) for an Oracle DB instance with an Oracle wallet.

Resolution

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

Download the target SSL/TLS endpoint root and intermediate certificates

  1. In your web browser, open the URL (SSL/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 similar to the following one on the command line of your local workstation:

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

    $ openssl s_client -connect status.aws.amazon.com:443
    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 M01
    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, CN = Amazon RSA 2048 M01
     1 s:C = US, O = Amazon, CN = Amazon RSA 2048 M01
       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
  3. Download the relevant root and intermediate certificates listed from the certificate provider. In this example, the stack trace indicates that you need the certificates Amazon Root CA 1 and Amazon RSA 2048 M01. Download the certificates from Amazon Trust Services repository.

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

    $ openssl x509 -inform der -in "Amazon Root CA 1.der" -outform pem -out "Amazon Root CA 1.pem"
    $ openssl x509 -inform der -in "Amazon RSA 2048 M01.der" -outform pem -out "Amazon RSA 2048 M01.pem"

Create the Oracle wallet, and then load the SSL/TLS endpoint root and intermediate certificates

  1. Use the orapki utility to create an Oracle wallet. 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. There are several parameters available to choose from when you create the wallet. For example, use auto_login_only to create an auto login wallet (cwallet.sso) that doesn't need a password to open.

  3. Navigate to the directory where you want to create the wallet. Then, run the following command on the command line of your local workstation:

    cd /app/client/wallet>
    orapki wallet create -wallet . -auto_login_only
  4. Add the two certificates to the wallet:

    orapki wallet add -wallet . -trusted_cert -cert "Amazon Root CA 1.pem" -auto_login_only
    orapki wallet add -wallet . -trusted_cert -cert "Amazon RSA 2048 M01.pem" -auto_login_only
  5. Verify the wallet contents, and then confirm that you added the certificates:

    >ls -ltrh cwallet.sso
    -rw------- 1 oracle oinstall 2.3K Aug 13 11:39 cwallet.sso
    
    >orapki wallet display -wallet .Oracle PKI Tool Release 19.0.0.0.0 - Production
    Version 19.3.0.0.0
    Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights reserved.
    
    Requested Certificates:
    User Certificates:
    Trusted Certificates:
    Subject:        CN=Amazon RSA 2048 M01,O=Amazon,C=US
    Subject:        CN=Amazon Root CA 1,O=Amazon,C=US

Upload the wallet to an S3 bucket

Create a new Amazon Simple Storage Service (Amazon S3) bucket, or use an existing bucket, and then upload the wallet. If you use the AWS CLI, then run the following command on the client machine where you created the Oracle wallet. Or, upload the wallet from the Amazon S3 console.

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

Note: Replace wallet4321 with the name of your S3 bucket.

Configure the database user and permissions

  1. Use SQL*Plus as the primary user to connect to the RDS for Oracle DB instance.

  2. Grant the necessary privileges to the database user that you want to be able to use the UTL_HTTP package:

    SQL> define 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.

    Note: Replace app_user with the name of the RDS database user that you want to be able to run UTL_HTTP commands.

  3. Create a directory for the wallet:

    Note: It's a best practice to store each wallet in its own directory.

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

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

Note: You can complete this step with or without Amazon S3 integration.

Use Amazon S3 integration

  1. Use Amazon S3 integration to download the wallet from an S3 bucket to your DB instance.

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

    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
    1691935899410-1902
  3. To view the results, use the task-id from the previous step to display the task's output file.

    SQL> SELECT text FROM table(rdsadmin.rds_file_util.read_text_file('BDUMP','dbtask-1691935899410-1902.log'));
    OUTPUT
    2023-08-13 14:11:39.497 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.
    2023-08-13 14:11:39.552 UTC [INFO ] The task successfully listed the Amazon S3 objects for AWS Region us-east-1, bucket name wallet4321, and prefix cwallet.sso.
    2023-08-13 14:11:39.568 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.
    2023-08-13 14:11:39.719 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.
    2023-08-13 14:11:39.720 UTC [INFO ] The task finished successfully.
  4. Verify that the wallet downloaded to your DB instance:

    SQL> select * from table (rdsadmin.rds_file_util.listdir(p_directory => 'WALLET'));
    FILENAME            TYPE         FILESIZE MTIME               
    ------------------- ---------- ---------- --------------------
    01/                 directory        4096 13-AUG-2023 14:11:39
    cwallet.sso         file             2349 13-AUG-2023 14:11:39
  5. Set the wallet path for UTL_HTTP transactions:

    SQL>  DECLAREl_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.
  6. Verify the web service host DNS name resolution:

    SQL> SELECT UTL\_INADDR.GET\_HOST\_ADDRESS(host => 'status.aws.amazon.com') FROM DUAL

    Use the Oracle wallet to browse the remote web service URL:

    SQL> SELECT utl_http.request('https://status.aws.amazon.com/robots.txt') AS ROBOTS_TXT FROM DUAL;
    ROBOTS_TXT
    --------------------------------------------------------------------------------
    {"message":"Not Found"}

Without S3 integration

  1. To allow the outbound traffic on Oracle's 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;
         /
    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. Create the following procedure in the SQL*Plus session that's connected to your DB instance:

    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. To generate a presigned URL, use the AWS CLI to run the following command. The presigned URL is valid for an hour, by default.

    >aws s3 presign s3://wallet4321/cwallet.sso
    
    https://wallet4321.s3.amazonaws.com/cwallet.sso?AWSAccessKeyId=AKIAJWTPIJJA4FAQURFA&Signature=u8ysKQyp4O6ws3Qy5qZak8PfmLE%3D&Expires=1588199836
  4. To download the wallet from the S3 bucket, run the s3_download_presigned_url procedure in the SQL*Plus session that's connected to your DB instance. Update the following input parameters before you run the s3_download_presigned_url procedure:

    p_s3 url - Enter the presigned URL that you generated

    p_local_filename - Enter the name of the wallet file

    p_local_directory - Enter the name of the directory that you created on your DB instance to store the wallet

    p_wallet_directory - Enter S3_SSL_WALLET . The DB instance uses this directory to store the wallet that contains the Amazon S3 web service certificates.

    See the following example command that uses HTTP to access the S3 web service. Replace https with http in the presigned URL before you run the command:

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

    See the following example command that uses HTTPS to access the Amazon S3 web service.

    Note: To use HTTPS, you must store the Amazon S3 web service wallet in the DB instance directory S3_SSL_WALLET.

    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 downloaded to the RDS for Oracle DB instance:

    SQL> select * from table (rdsadmin.rds_file_util.listdir(p_directory => 'WALLET'));
    FILENAME            TYPE         FILESIZE MTIME               
    ------------------- ---------- ---------- --------------------
    01/                 directory        4096 13-AUG-2023 15:15:39
    cwallet.sso         file             2349 13-AUG-2023 15:15:39
  6. Set the wallet path for url_http transactions:

    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:

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

    Use the Oracle wallet to browse the remote web service URL:

    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 receive this error if the wallet that you reference isn't in the location that you specified. List the directories to confirm the location of the wallet file:

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 receive this error if the S3 bucket and its contents are encrypted with AWS Key Management Service (AWS KMS). Remove the encryption on the bucket objects.

ORA-12535: TNS: operation timed out

You might receive this error because the instance is in a private subnet. Or, the route table has no route out to the internet.

OFICIAL DE AWS
OFICIAL DE AWSActualizada hace 24 días