Passing Oracle configuration parameters, e.g. oracle.net.encryption_client

[aleybovich]

@tgulacsi

Is there a way to pass client oracle parameters to this driver? I need to force NNE encryption and for that I need to set oracle.net.encryption_client=required on the client somehow, but I don't see a way to do that in this driver. An ideas?

[tgulacsi]

Just set it in sqlnet.ora (https://docs.oracle.com/cd/B28359_01/network.111/b28530/asoappa.htm#g638255).

But if you find how to set it in OCI or ODPI-C, we can incorporate it.

[aleybovich]

Just set it in sqlnet.ora (https://docs.oracle.com/cd/B28359_01/network.111/b28530/asoappa.htm#g638255). But if you find how to set it in OCI or ODPI-C, we can incorporate it.

Creating sqlnet.ora and providing TNS_ADMIN env variable did work, thank you!

Still, having the ability to provide config values programmatically is beneficial for example in the case of running go code in an AWS lambda.

[tgulacsi]

Sure, but I don't know other way than sqlnet.ora! (JDBC is cheating: they've reimplemented the SQL*NET protocol, so don't have to use the same libclntsh.so (OCI shared lib)).

Maybe @cjbj knows better.

[cjbj]

I'm personally in favor of being able to set things programmatically. Most recently we got OCI_ATTR_CALL_TIMEOUT implemented, which can be used instead of the Oracle Net SQLNET.RECV_TIMEOUT and SQLNET.SEND_TIMEOUT options. I know this is one out of many options.

Regarding encryption, don't you want to set SQLNET.ENCRYPTION_SERVER = required on the database server side, so every client uses it? I have some notes at https://oracle.github.io/node-oracledb/doc/api.html#securenetwork

[aleybovich]

@cjbj We can't set it to required on the server for historic reasons - a bunch of legacy apps that aren't maintained any more will stop working, so we have to force it in the client. And again, our current problem got solved by adding sqlnet.ora and specifying TNS_ADMIN path, but that won't work for serverless apps running on AWS or Azure. So if you know how to incorporate that feature on the drive, that would be beneficial for the users.

[cjbj]

@aleybovich If there was a way, I would have shared it.

[krismohan]

@aleybovich Do you mean it is not possible to configure sqlnet.ora if the apps are running on AWS or Azure? Is it because of no access to shell? How about other environment variables (like NLS_LANG)? Is it not possible to set them when needed?

[aleybovich]

@krismohan in theory, you can probably deploy sqlnet.ora with your go lambda to AWS and specify TNS_ADMIN=./ as env variable, but I haven't tried that. Our lambda that requires encrypted traffic with Oracle is written in C# and ODP.NET driver has a property exposed for setting client encryption parameter

[cjbj]

One tip: You can create a subdirectory network/admin (relative to the directory that libclntsh is in) and put your Oracle Net and oraacess.xml files there. This is a default location and no TNS_ADMIN variable is necessary. The 18.3 Instant Client packages actually pre-create this directory for you (except on Windows, for reasons not worth getting into - but you can create it easily enough).

[dinvlad]

Is it possible to check that sqlnet.ora was picked up correctly, prior to opening the connection? We could simply check that sqlnet.ora file exists under TNS_ADMIN path and contains the right string (or alternatively, create that file from our code), but is there a better way?

Related, does goracle "pick up" settings from sqlnet.ora (and similar config files) when we import it, or does it only happen when we call sql.Open() method?

EDIT: The following snippet seems to do the job:

os.Setenv("TNS_ADMIN", ".")
ora := []byte("sqlnet.encryption_client=required")
err = ioutil.WriteFile("sqlnet.ora", ora, 0644)
...

EDIT2: Added TNS_ADMIN to the snippet (previously, it was picking up sqlnet.ora from my Oracle network/admin folder..)

Thanks!

[tgulacsi]

The ODPI guys know better, but goracle does nothing with TNS_ADMIN, and calls dpiContext_create first when the first connection is acquired.

goracle could check tnsnames.ora or sqlnet.ora under $TNS_ADMIN, but what to look for? You don't need a tnsnames.ora to set most connection params (just use what tnsping prints).

[dinvlad]

Hmm, yeah maybe the snipped above is sufficient then. Since OCI pretty much requires one to set native encryption through sqlnet.ora, there isn’t much else we can do (i.e. I wish we could just set those programmatically through "connection parameters").

Btw @aleybovich, I believe "create the file from the code" approach also works for Lambda, since they make /tmp available to the app. Perhaps that's how they do it in C# as well.

[cjbj]

FWIW there is an extended 'Easy Connect Plus' syntax in 19c, see Understanding the Easy Connect Naming Method but it supports mostly tnsnames.ora settings and just a few sqlnet.ora settings. And 19c is only available in the Cloud and a few other places, so there is no Instant Client 19c yet.