Local passwordless OS authentification

[pepinno]

Is it possible to achieve the same behivor as a local passwordless OS authentification ?

Here is an example of a local os connection with username "oracle".
sqlplus is not asking me for any password.

[oracle@orsni401:~]$ export ORACLE_HOME=/u01/app/19.0.0/oracle/db_1
[oracle@orsni401:~]$ export ORACLE_SID=LABOAAD1
[oracle@orsni401:~]$ sqlplus /
OPS$ORACLE@LABOAAD1> select username from dba_users where username like 'OPS$ORACLE';
USERNAME
------------
OPS$ORACLE

OPS$ORACLE@LABOAAD1> SELECT SYS_CONTEXT ('USERENV', 'SESSION_USER') FROM DUAL;
SYS_CONTEXT('USERENV','SESSION_USER')
--------------
OPS$ORACLE

I would like to achieve the same thing with python, with running the python script as oracle user context, on the local server, without the need to provide a password when doing "oracledb.connect".

But it looks like the API can only achieve this with a net tns alias.

Here is what I tried with a net alias, but it is not working.

[oracle@orsni401~]$ cat test.py

import oracledb
oracledb.init_oracle_client(lib_dir="/u01/app/19.0.0/oracle/db_1/lib", config_dir="/u01/app/19.0.0/oracle/db_1/network/admin")
connection = oracledb.connect(dsn="(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=orsni401)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=LABOAAD)))")
cursor = connection.cursor()
cursor.execute("select sysdate from dual")
print(cursor.fetchall())

Traceback (most recent call last):
  File "test.py", line 4, in <module>
    connection = oracledb.connect(dsn="(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=orsni401)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=LABOAAD)))")
  File "/opt/rh/rh-python38/root/usr/local/lib64/python3.8/site-packages/oracledb/connection.py", line 1012, in connect
    return conn_class(dsn=dsn, pool=pool, params=params, **kwargs)
  File "/opt/rh/rh-python38/root/usr/local/lib64/python3.8/site-packages/oracledb/connection.py", line 138, in __init__
    impl.connect(params_impl, pool_impl)
  File "src/oracledb/impl/thick/connection.pyx", line 385, in oracledb.thick_impl.ThickConnImpl.connect
  File "src/oracledb/impl/thick/utils.pyx", line 410, in oracledb.thick_impl._raise_from_odpi
  File "src/oracledb/impl/thick/utils.pyx", line 400, in oracledb.thick_impl._raise_from_info
oracledb.exceptions.DatabaseError: ORA-01017: invalid username/password; logon denied

Here are my OS settings :

OPS$ORACLE@LABOAAD1> show parameter remote_os_authent;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
remote_os_authent                    boolean     TRUE
OPS$ORACLE@LABOAAD1> show parameter os_authent_prefix

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
os_authent_prefix                    string      ops$

Also, this is not working :

[LABOAAD1 oracle@orsni401:~]$ sqlplus "/@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=orsni401)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=LABOAAD)))"
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Oct 12 14:38:58 2022
Version 19.16.0.0.0
Copyright (c) 1982, 2022, Oracle.  All rights reserved.
ERROR:
ORA-01017: invalid username/password; logon denied
Enter user-name:

[cjbj]

In your call to init_oracle_client():

• don't pass lib_dir because it doesn't do what you think on Linux, and could lead to problems if the value is different to that in LD_LIBRARY_PATH (or via ldconfig)
• there's no need to pass config_dir because you are setting it to default location

But, having said that, sort out your issue with SQL*Plus first. Are you using a PDB? (And I formally have to advise against setting remote_os_authent to true because of the security issues)

[pepinno]

I made a successful local OS authentification with env variables like this :

import oracledb
import os

oracledb.init_oracle_client()

os.environ['ORACLE_SID'] = 'LABOAAD1'
print(os.environ.get('ORACLE_SID'))
connection = oracledb.connect()
cursor = connection.cursor()
cursor.execute("SELECT SYS_CONTEXT ('USERENV', 'SESSION_USER')  FROM DUAL")
print(cursor.fetchall())

os.environ['ORACLE_SID'] = 'LABOBBD1'
print(os.environ.get('ORACLE_SID'))
connection2 = oracledb.connect()
cursor = connection2.cursor()
cursor.execute("SELECT SYS_CONTEXT ('USERENV', 'SESSION_USER')  FROM DUAL")
print(cursor.fetchall())

[cjbj]

Glad you solved it. Thanks for sharing the info.