Connect to Oracle Cloud Database with python oracledb-CodePudding

I'm trying to connect to a newly created database in Oracle Cloud (https://cloud.oracle.com/db/adb/)

I've copied the connection string from DB Connection > Connection Strings > (One of the three listed.)

Which looks a little like this:

(description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=adb.uk-london-1.oraclecloud.com))(connect_data=(service_name=abc123xyzredacted.adb.oraclecloud.com))(security=(ssl_server_cert_dn="CN=adwc.eucom-central-1.oraclecloud.com, OU=Oracle BMCS FRANKFURT, O=Oracle Corporation, L=Redwood City, ST=California, C=US")))

I'm authenticating using the "ADMIN" account that was created at DB creation along with its password.

Running the test.py script found here: https://python-oracledb.readthedocs.io/en/latest/user_guide/installation.html#quickstart

import oracledb
import os

un = os.environ.get('PYTHON_USERNAME')
pw = os.environ.get('PYTHON_PASSWORD')
cs = os.environ.get('PYTHON_CONNECTSTRING')

with oracledb.connect(user=un, password=pw, dsn=cs) as connection:
    with connection.cursor() as cursor:
        sql = """select sysdate from dual"""
        for r in cursor.execute(sql):
            print(r)

I get

% python test.py
Traceback (most recent call last):
  File "src/oracledb/impl/thin/connection.pyx", line 227, in oracledb.thin_impl.ThinConnImpl._connect_with_address
  File "src/oracledb/impl/thin/crypto.pyx", line 125, in oracledb.thin_impl.get_ssl_socket
  File "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/ssl.py", line 500, in wrap_socket
    return self.sslsocket_class._create(
  File "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/ssl.py", line 1040, in _create
    self.do_handshake()
  File "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/ssl.py", line 1309, in do_handshake
    self._sslobj.do_handshake()
ssl.SSLError: [SSL: SSLV3_ALERT_HANDSHAKE_FAILURE] sslv3 alert handshake failure (_ssl.c:1129)

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/test.py", line 15, in <module>
    with oracledb.connect(user=un, password=pw, dsn=cs) as connection:
  File ".venv/lib/python3.9/site-packages/oracledb/connection.py", line 1000, in connect
    return conn_class(dsn=dsn, pool=pool, params=params, **kwargs)
  File ".venv/lib/python3.9/site-packages/oracledb/connection.py", line 128, in __init__
    impl.connect(params_impl)
  File "src/oracledb/impl/thin/connection.pyx", line 345, in oracledb.thin_impl.ThinConnImpl.connect
  File "src/oracledb/impl/thin/connection.pyx", line 163, in oracledb.thin_impl.ThinConnImpl._connect_with_params
  File "src/oracledb/impl/thin/connection.pyx", line 129, in oracledb.thin_impl.ThinConnImpl._connect_with_description
  File "src/oracledb/impl/thin/connection.pyx", line 247, in oracledb.thin_impl.ThinConnImpl._connect_with_address
  File ".venv/lib/python3.9/site-packages/oracledb/errors.py", line 103, in _raise_err
    raise exc_type(_Error(message)) from cause
oracledb.exceptions.OperationalError: DPY-6005: cannot connect to database. Connection failed with "[SSL: SSLV3_ALERT_HANDSHAKE_FAILURE] sslv3 alert handshake failure (_ssl.c:1129)"

Am I using the wrong connection string? Should I create another user?

Additional:

I've just found this: https://blogs.oracle.com/opal/post/easy-way-to-connect-python-applications-to-oracle-autonomous-databases

So I now have "Network" showing:

Access Type: Allow secure access from specified IPs and VCNs
Access Control List: Enabled Mutual TLS (mTLS)
Authentication: Not Required

Where my IP address is entered in Access Control List.

CodePudding user response：

You have:

Access Control List: Enabled Mutual TLS (mTLS)

which seems incorrect.

This is what my cloud console shows when I have 1-way (aka walletless) authentication enabled:

Network
Access Type: Allow secure access from specified IPs and VCNs
Access Control List: Enabled
Mutual TLS (mTLS) Authentication: Not Required

When you copy the connection string from the cloud console, make sure to select the correct TLS (not mTLS) value in the dropdown box just above, because the connection string changes.

Check your current IP address is in the ACL list, because IPs addresses are often not static !