Need Support with connection string for CMAN connection to DB (Source_route=yes) in connection string

[sme-eprince]

We use a OCM which is running in my jump server, it is like the gateway for our DB which is in another VCN in OCI.
We want to secure the DB and make sure an extra layer of security with the Jump server authentication.

Configuration is like below

Client machine ---> Jump Server ---> Database
CMAN is in Jump Server -----> Database

We basically open a Terminal connection in SSH (Port 22) and create a tunnel to port 1480 of OCM which will forward the connection to Database

ssh -i user@jumpserver -L 127.0.0.1:1481:jumpserver:1480

connection string will be then like below (Note: we make the address list with 1 host as local machine with the same port as local tunnel port opened in above step)

(description=(address_list=(address=(protocol=tcp)(port=1481)(host=127.0.0.1))(address=(protocol=tcp)(port=1521)(host=Test_DB_Scan_Server_Name)))(connect_data=(service_name=ORA_TEST))(source_route=yes))

I am able to connect from SQL Developer which is using Thin driver and it works good.

I discussed this issue in discussion already (#77)

`import oracledb
import os

oracledb.defaults.config_dir = "/Users/eprince/PycharmProjects/flaskProject2/"
un = "SCOTT"
pw = "pwd"

c_dsn = "(DESCRIPTION=(RETRY_DELAY=2)(TRANSPORT_CONNECT_TIMEOUT=100 ms)(FAILOVER=on)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1481))(ADDRESS=(PROTOCOL=tcp)(HOST=db_scan_server_url)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=ORA_TEST_DB))(source_route=yes))"

with oracledb.connect(user=un,password=pw,dsn=c_dsn) as connection:
with connection.cursor() as cursor:
sql = "select * from employee"
for r in cursor.execute(sql):
print(r)`

[anthony-tuininga]

I discussed this internally and it looks like there is another solution that you might be able to use -- depending on what verson of CMAN you are using. If you implement the next_hop parameter of cman.ora as shown in the documentation then you can just use a regular connect string instead and the client doesn't need to know about the hops that are taking place on the server side. It just needs to know the first CMAN to connect to.

[sme-eprince]

Hi,
This is a new configuration change and doing this will be difficult in production environment with multiple servers in it.
Is there any other way or code fix possible for this

[anthony-tuininga]

You can use thick mode, of course, by calling oracledb.init_oracle_client() before making any connection to the database; otherwise, you'll have to wait for us to correct the code and add the necessary support. I'm not sure how long that will take, though!

[sme-eprince]

Thick drive is not directly supported in Mac M1 since driver is not supporting arm64 architecture. I have to install Rosetta2 and even with that it's very difficult to get it working. It will be very helpful if you could change the code for thin drive approach else coding for oracle with mac M1 becomes a nightmare. It will be nice if you could prioritise this issue

[anthony-tuininga]

@cjbj is the one that prioritizes, so I'll see what he has to say. I appreciate the difficulty you're in, though, so will see what I am able to do!

[sme-eprince]

Just for a tip notice, in cx_Oracle package for Python the same connection string with source_route=yes is working fine

[anthony-tuininga]

Yes, cx_Oracle is the same as python-oracledb thick mode -- so not surprising that works! Are you able to work with cx_Oracle? If so, you should be able to use thick mode of python-oracledb, too.

[sme-eprince]

No, Actually I tried it in Windows which is working good.
Only way Oracle connection working from Mac M1 is SQL Developer which is using a JAR file.
So I thought same functionality will be repeated in oracledb , which is not working

Since we don't see any light for cx_oracle to work in Mac M1 the only way is to solve oracledb package

[anthony-tuininga]

Understood. Thanks for the clarification.

[anthony-tuininga]

@sme-eprince, I have just pushed code that adds support for this configuration -- at least the configuration that I had available to me! Can you try your configuration and let me know if it works for you, too? Thanks!

[anthony-tuininga]

This is part of oracledb 1.2.0 which was just released.

[sme-eprince]

Hi Anthony,

Excellent it works for me as well. This is really good news, I can start removing a lot of dependency now