search

oracle / python-oracledb

Python driver for Oracle Database conforming to the Python DB API 2.0 specification. This is the renamed, new major release of cx_Oracle
https://oracle.github.io/python-oracledb
Other
345 stars 70 forks source link

Certificate error while using TLS and SCAN #415

Open shaunmugam opened 3 weeks ago

shaunmugam commented 3 weeks ago
  1. What versions are you using?

Oracle 19c database oracledb 2.4.1

import oracledb as cx

dsn_tns='(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = tcps)(HOST = cluster-scan)(PORT = 2484))) (CONNECT_DATA = (SERVICE_NAME = db_service))(security=(ssl_server_dn_match=yes)))'

sql = """SELECT sys_context('USERENV', 'NETWORK_PROTOCOL'), ora_database_name FROM dual"""

conn = cx.connect(user='myuser', password='mypass', dsn=dsn_tns) cursor = conn.cursor() result = cursor.execute(sql)

for row in result: print(row)

Output:

PS C:\Users\cn131304\OneDrive - Centene Corporation\Documents\learning\python> & "C:/Program Files/Python311/python.exe" "c:/Users/cn131304/OneDrive - Centene Corporation/Documents/learning/python/ora2.py" Traceback (most recent call last): File "src\oracledb\impl/thin/connection.pyx", line 322, in oracledb.thin_impl.ThinConnImpl._connect_with_address File "src\oracledb\impl/thin/protocol.pyx", line 225, in oracledb.thin_impl.Protocol._connect_phase_one File "src\oracledb\impl/thin/protocol.pyx", line 380, in oracledb.thin_impl.Protocol._connect_tcp File "src\oracledb\impl/thin/transport.pyx", line 244, in oracledb.thin_impl.Transport.negotiate_tls File "C:\Program Files\Python311\Lib\ssl.py", line 517, in wrap_socket return self.sslsocket_class._create( ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "C:\Program Files\Python311\Lib\ssl.py", line 1075, in _create self.do_handshake() File "C:\Program Files\Python311\Lib\ssl.py", line 1346, in do_handshake self._sslobj.do_handshake() ssl.SSLCertVerificationError: [SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed: Hostname mismatch, certificate is not valid for 'cluster_scan'. (_ssl.c:992)

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

Traceback (most recent call last): File "c:\Users\cn131304\OneDrive - Centene Corporation\Documents\learning\python\ora2.py", line 7, in conn = cx.connect(user='a_cn131304', password='%0qwxAwFj3M).Jt9)}2oIz<p', dsn=dsn_tns) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "C:\Users\cn131304\AppData\Roaming\Python\Python311\site-packages\oracledb\connection.py", line 1169, in connect return conn_class(dsn=dsn, pool=pool, params=params, **kwargs) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "C:\Users\cn131304\AppData\Roaming\Python\Python311\site-packages\oracledb\connection.py", line 551, in init impl.connect(params_impl) File "src\oracledb\impl/thin/connection.pyx", line 424, in oracledb.thin_impl.ThinConnImpl.connect File "src\oracledb\impl/thin/connection.pyx", line 420, in oracledb.thin_impl.ThinConnImpl.connect File "src\oracledb\impl/thin/protocol.pyx", line 380, in oracledb.thin_impl.Protocol._connect_tcp File "src\oracledb\impl/thin/connection.pyx", line 361, in oracledb.thin_impl.ThinConnImpl._connect_with_description File "src\oracledb\impl/thin/connection.pyx", line 331, in oracledb.thin_impl.ThinConnImpl._connect_with_address File "C:\Users\cn131304\AppData\Roaming\Python\Python311\site-packages\oracledb\errors.py", line 195, in _raise_err raise error.exc_type(error) from cause oracledb.exceptions.OperationalError: DPY-6005: cannot connect to database (CONNECTION_ID=mF4qb0/Xnv/m66jKv3Lz1w==). [SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed: Hostname mismatch, certificate is not valid for 'cluster_scan'. (_ssl.c:992)

  1. Is it an error or a hang or a crash?

Error

  1. What error(s) or behavior you are seeing?

We only get the error if using the cluster_scan (to which the SSL certificate is issued). The cluster_scan has cnames that are defined as subject alternate names in the certificate request. The code completes successfully if using the cname for cluster_scan.

We are not getting any errors while using JDBC or Oracle thick client configuration using either the cluster_scan or its cnames. So, we know the certificate is valid.

PS C:\Users\cn131304\OneDrive - Centene Corporation\Documents\learning\python> & "C:/Program Files/Python311/python.exe" "c:/Users/cn131304/OneDrive - Centene Corporation/Documents/learning/python/ora2.py" Traceback (most recent call last): File "src\oracledb\impl/thin/connection.pyx", line 322, in oracledb.thin_impl.ThinConnImpl._connect_with_address File "src\oracledb\impl/thin/protocol.pyx", line 225, in oracledb.thin_impl.Protocol._connect_phase_one File "src\oracledb\impl/thin/protocol.pyx", line 380, in oracledb.thin_impl.Protocol._connect_tcp File "src\oracledb\impl/thin/transport.pyx", line 244, in oracledb.thin_impl.Transport.negotiate_tls File "C:\Program Files\Python311\Lib\ssl.py", line 517, in wrap_socket return self.sslsocket_class._create( ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "C:\Program Files\Python311\Lib\ssl.py", line 1075, in _create self.do_handshake() File "C:\Program Files\Python311\Lib\ssl.py", line 1346, in do_handshake self._sslobj.do_handshake() ssl.SSLCertVerificationError: [SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed: Hostname mismatch, certificate is not valid for 'cluster_scan'. (_ssl.c:992)

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

Traceback (most recent call last): File "c:\Users\cn131304\OneDrive - Centene Corporation\Documents\learning\python\ora2.py", line 7, in conn = cx.connect(user='a_cn131304', password='%0qwxAwFj3M).Jt9)}2oIz<p', dsn=dsn_tns) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "C:\Users\cn131304\AppData\Roaming\Python\Python311\site-packages\oracledb\connection.py", line 1169, in connect return conn_class(dsn=dsn, pool=pool, params=params, **kwargs) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "C:\Users\cn131304\AppData\Roaming\Python\Python311\site-packages\oracledb\connection.py", line 551, in init impl.connect(params_impl) File "src\oracledb\impl/thin/connection.pyx", line 424, in oracledb.thin_impl.ThinConnImpl.connect File "src\oracledb\impl/thin/connection.pyx", line 420, in oracledb.thin_impl.ThinConnImpl.connect File "src\oracledb\impl/thin/protocol.pyx", line 380, in oracledb.thin_impl.Protocol._connect_tcp File "src\oracledb\impl/thin/connection.pyx", line 361, in oracledb.thin_impl.ThinConnImpl._connect_with_description File "src\oracledb\impl/thin/connection.pyx", line 331, in oracledb.thin_impl.ThinConnImpl._connect_with_address File "C:\Users\cn131304\AppData\Roaming\Python\Python311\site-packages\oracledb\errors.py", line 195, in _raise_err raise error.exc_type(error) from cause oracledb.exceptions.OperationalError: DPY-6005: cannot connect to database (CONNECTION_ID=mF4qb0/Xnv/m66jKv3Lz1w==). [SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed: Hostname mismatch, certificate is not valid for 'cluster_scan'. (_ssl.c:992)

  1. Does your application call init_oracle_client()?

No. Using thin mode

  1. Include a runnable Python script that shows the problem.

import oracledb as cx

dsn_tns='(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = tcps)(HOST = cluster_scan)(PORT = 2484))) (CONNECT_DATA = (SERVICE_NAME = db_service))(security=(ssl_server_dn_match=yes)))'

sql = """SELECT sys_context('USERENV', 'NETWORK_PROTOCOL'), ora_database_name FROM dual"""

conn = cx.connect(user='myuser', password='mypass', dsn=dsn_tns) cursor = conn.cursor() result = cursor.execute(sql)

for row in result: print(row)

anthony-tuininga commented 3 weeks ago

I don't believe this is a bug.

We only get the error if using the cluster_scan (to which the SSL certificate is issued). The cluster_scan has cnames that are defined as subject alternate names in the certificate request. The code completes successfully if using the cname for cluster_scan.

We are not getting any errors while using JDBC or Oracle thick client configuration using either the cluster_scan or its cnames. So, we know the certificate is valid.

The code is currently making use of Python's SSL context server name validation. The fact that JDBC and Oracle thick client are happy with using the alternative name doesn't really say that Python's approach is wrong! Can you explain further why you believe that Python's SSL context server name validation is incorrect? And why do you not want to use the officially registered name?

shaunmugam commented 3 weeks ago

I am not sure if I explained correctly. With JDBC and thick client, both the registered name and alternate names work

With python oracledb, only alternate names work. Registered name does not work.

On Fri, Nov 1, 2024 at 2:00 PM Anthony Tuininga @.***> wrote:

I don't believe this is a bug.

We only get the error if using the cluster_scan (to which the SSL certificate is issued). The cluster_scan has cnames that are defined as subject alternate names in the certificate request. The code completes successfully if using the cname for cluster_scan.

We are not getting any errors while using JDBC or Oracle thick client configuration using either the cluster_scan or its cnames. So, we know the certificate is valid.

The code is currently making use of Python's SSL context server name validation. The fact that JDBC and Oracle thick client are happy with using the alternative name doesn't really say that Python's approach is wrong! Can you explain further why you believe that Python's SSL context server name validation is incorrect? And why do you not want to use the officially registered name?

— Reply to this email directly, view it on GitHub https://github.com/oracle/python-oracledb/issues/415#issuecomment-2452583866, or unsubscribe https://github.com/notifications/unsubscribe-auth/BMTEBFOVEQJN33BVTQVA7Z3Z6PTYTAVCNFSM6AAAAABRA52U5KVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDINJSGU4DGOBWGY . You are receiving this because you authored the thread.Message ID: @.***>

anthony-tuininga commented 3 weeks ago

This is the relevant Python documentation. When you set ssl_server_dn_match, this enables hostname checking and that is the issue you are running into. The python-oracledb implementation does not do anything more than enable that flag. I understand that the JDBC and thick client do something different. I assume you are asking for thin mode to implement something different? You can also diasble ssl_server_dn_match or create your own ssl_context parameter which does what you wish it to do. If that isn't acceptable, we can make this an enhancement request instead. Thoughts?

shaunmugam commented 3 weeks ago

We can make this an enhancement request. I just did not understand why, with DN match, alternate names would work but not the registered name. In the meantime, I will try to see if adding the registered name as an alternate name would work.

Thank you

On Fri, Nov 1, 2024 at 3:47 PM Anthony Tuininga @.***> wrote:

This is the relevant Python documentation https://docs.python.org/3/library/ssl.html#ssl.SSLContext.check_hostname. When you set ssl_server_dn_match, this enables hostname checking and that is the issue you are running into. The python-oracledb implementation does not do anything more than enable that flag. I understand that the JDBC and thick client do something different. I assume you are asking for thin mode to implement something different? You can also diasble ssl_server_dn_match or create your own ssl_context parameter which does what you wish it to do. If that isn't acceptable, we can make this an enhancement request instead. Thoughts?

— Reply to this email directly, view it on GitHub https://github.com/oracle/python-oracledb/issues/415#issuecomment-2452685777, or unsubscribe https://github.com/notifications/unsubscribe-auth/BMTEBFIIUSLBGCDFVWOA5PLZ6QAGXAVCNFSM6AAAAABRA52U5KVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDINJSGY4DKNZXG4 . You are receiving this because you authored the thread.Message ID: @.***>

cjbj commented 3 weeks ago

@shaunmugam what's the exact 19c DB version you have?

shaunmugam commented 3 weeks ago

19.23.0.0.0

On Fri, Nov 1, 2024 at 4:19 PM Christopher Jones @.***> wrote:

@shaunmugam https://github.com/shaunmugam what's the exact 19c DB version you have?

— Reply to this email directly, view it on GitHub https://github.com/oracle/python-oracledb/issues/415#issuecomment-2452709589, or unsubscribe https://github.com/notifications/unsubscribe-auth/BMTEBFN4OJGDLNCYV4HIRQTZ6QEAHAVCNFSM6AAAAABRA52U5KVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDINJSG4YDSNJYHE . You are receiving this because you were mentioned.Message ID: @.***>

shaunmugam commented 3 weeks ago

Also this was not an issue with cx_Oracle

On Fri, Nov 1, 2024 at 4:28 PM Shanmugam Murugappan < @.***> wrote:

19.23.0.0.0

On Fri, Nov 1, 2024 at 4:19 PM Christopher Jones @.***> wrote:

@shaunmugam https://github.com/shaunmugam what's the exact 19c DB version you have?

— Reply to this email directly, view it on GitHub https://github.com/oracle/python-oracledb/issues/415#issuecomment-2452709589, or unsubscribe https://github.com/notifications/unsubscribe-auth/BMTEBFN4OJGDLNCYV4HIRQTZ6QEAHAVCNFSM6AAAAABRA52U5KVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDINJSG4YDSNJYHE . You are receiving this because you were mentioned.Message ID: @.***>

cjbj commented 3 weeks ago

cx_Oracle and python-oracledb Thick are really the same thing - all the DB connectivity is handled in Oracle Client libraries.

I get your point that if some environments work, other environments might also be expected to. However if those environments have different levels of support for some technologies or options, than it may not be possible.

Let us know what you find about adding the registered name as an alternate name.

We'll also check with the Oracle Net Services team and see what they might suggest.

shaunmugam commented 3 weeks ago

Thank you so much for your patience and your explanation. I will try the other option next week and get back to you with what I find.

On Fri, Nov 1, 2024 at 6:32 PM Christopher Jones @.***> wrote:

cx_Oracle and python-oracledb Thick are really the same thing - all the DB connectivity is handled in Oracle Client libraries.

I get your point that if some environments work, other environments might also be expected to. However if those environments have different levels of support for some technologies or options, than it may not be possible.

Let us know what you find about adding the registered name as an alternate name.

We'll also check with the Oracle Net Services team and see what they might suggest.

— Reply to this email directly, view it on GitHub https://github.com/oracle/python-oracledb/issues/415#issuecomment-2452787056, or unsubscribe https://github.com/notifications/unsubscribe-auth/BMTEBFOKYE2RGYZYDSF4K7LZ6QTSXAVCNFSM6AAAAABRA52U5KVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDINJSG44DOMBVGY . You are receiving this because you were mentioned.Message ID: @.***>

cjbj commented 3 weeks ago

@shaunmugam to help us understand user requirements, I would be interested to know your specific reason(s) for preferring Thin mode over Thick mode. I am imagining you have a large Oracle environment. In particular do you not want to use AQ or Application Continuity/TAC, which aren't available in Thin mode? Or do you already use Thick mode where it has features you need?

shaunmugam commented 3 weeks ago

Hello,

We have a large Oracle environment. We have hundreds of applications

connecting to these and each development team pick a tool of our choice. We do want to standardize what these teams use to connect to our DBs. But unfortunately we do not have that standard yet and so we have many ways that apps connect to our DBs. We do want to use AC at a future date but we don’t have it now

On Fri, Nov 1, 2024 at 8:15 PM Christopher Jones @.***> wrote:

@shaunmugam https://github.com/shaunmugam to help us understand user requirements, I would be interested to know your specific reason(s) for preferring Thin mode over Thick mode. I am imagining you have a large Oracle environment. In particular do you not want to use AQ or Application Continuity/TAC, which aren't available in Thin mode? Or do you already use Thick mode where it has features you need?

— Reply to this email directly, view it on GitHub https://github.com/oracle/python-oracledb/issues/415#issuecomment-2452827760, or unsubscribe https://github.com/notifications/unsubscribe-auth/BMTEBFPKDZBR42NQTC4XSSLZ6Q7UHAVCNFSM6AAAAABRA52U5KVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDINJSHAZDONZWGA . You are receiving this because you were mentioned.Message ID: @.***>

anthony-tuininga commented 2 weeks ago

I have pushed a patch that adds this support and have initated a build from which you can download pre-built development wheels once it completes. You can also build from source if you prefer. If you can test your scenario and confirm the patch works as expected, that would be appreciated!

shaunmugam commented 2 weeks ago

Hello,

I will check with our dev team and have them apply this patch and test.

Thanks, Shan

On Fri, Nov 8, 2024 at 9:52 AM Anthony Tuininga @.***> wrote:

I have pushed a patch that adds this support and have initated a build https://github.com/oracle/python-oracledb/actions/runs/11747035390 from which you can download pre-built development wheels once it completes. You can also build from source https://python-oracledb.readthedocs.io/en/latest/user_guide/installation.html#installing-from-source-code if you prefer. If you can test your scenario and confirm the patch works as expected, that would be appreciated!

— Reply to this email directly, view it on GitHub https://github.com/oracle/python-oracledb/issues/415#issuecomment-2465429489, or unsubscribe https://github.com/notifications/unsubscribe-auth/BMTEBFIWJSAK4TVUQC33KYDZ7T25PAVCNFSM6AAAAABRA52U5KVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDINRVGQZDSNBYHE . You are receiving this because you were mentioned.Message ID: @.***>

cjbj commented 4 days ago

@shaunmugam any update?

shaunmugam commented 3 days ago

Hello Christopher,

We have limitations on how we can patch python on our company machines.

I have requested a development person to check this. I will follow-up today.

We did try adding the SCAN name as a SAN and it did not work. But I

have not tried the patch yet.

Thanks, Shan

On Sun, Nov 24, 2024 at 8:52 PM Christopher Jones @.***> wrote:

@shaunmugam https://github.com/shaunmugam any update?

— Reply to this email directly, view it on GitHub https://github.com/oracle/python-oracledb/issues/415#issuecomment-2496821655, or unsubscribe https://github.com/notifications/unsubscribe-auth/BMTEBFPG3LAVPZ2LWJ2YLDD2CKUJHAVCNFSM6AAAAABRA52U5KVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDIOJWHAZDCNRVGU . You are receiving this because you were mentioned.Message ID: @.***>