Remote OS authentication does not work with Oracle 21c PDB

Question

I have a problem with Oracle 21c PDB connection. I have created an OS user which should be able to login to the Pluggable Database with...

sqlplus /

...command. However it does not work on the way how I have configured.

Details

OS: Rocky Linux release 8.8 (Green Obsidian)

Oracle version: Oracle 21c

CDB: iqlink2c

PDB: iqlink2

Below you may find the steps...

1.) I have created the orasync OS (Linux - Rocky 8) user with the following parameters in the .cshrc file...

setenv ORACLE_BASE /opt/oracle
setenv ORACLE_HOME $ORACLE_BASE/product/21c/dbhome_1
setenv ORACLE_SID iqlink2
setenv ORACLE_TERM xsun5
setenv NLS_LANG AMERICAN_AMERICA.WE8ISO8859P1
setenv PATH ${PATH}:${ORACLE_HOME}/bin
setenv EDITOR /bin/vi

2.) I logged in to the Pluggable Database (iqlink2) and I have created the orasync DB user with the following statement...

create user orasync
identified externally
default tablespace COMPANY
temporary tablespace TEMP
quota unlimited on COMPANY
quota unlimited on COMPANY_INDX
/

...and granted dba privileges...

GRANT dba TO orasync
/

3.) I logged into the Container Database (iqlink2c) and I checked the os_authent_prefix and remote_os parameters...

SQL> SHOW PARAMETERS os_authent_prefix;

NAME                     TYPE    VALUE
------------------------------------ ----------- ------------------------------
os_authent_prefix            string  ops$
SQL> SHOW PARAMETERS remote_os;

NAME                     TYPE    VALUE
------------------------------------ ----------- ------------------------------
remote_os_roles              boolean     FALSE
SQL> 

NOTE: I know that the remote_os_authent parameter should be TRUE, but it's not available anymore with Oracle 21c at all. At this point I wonder if the remote OS login even possible with Oracle 21c PDB?!

4.) I removed the os_authent_prefix value, then restarted the DB...

[oracle@company02 ~]$ sqlplus sys@iqlink2c as sysdba

SQL*Plus: Release 21.0.0.0.0 - Production on Sat Nov 11 00:52:45 2023
Version 21.3.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.

Enter password: 

Connected to:
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0

SQL> alter system set os_authent_prefix='' scope=SPFILE;

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup; 
ORACLE instance started.

Total System Global Area 4294967152 bytes
Fixed Size          9695088 bytes
Variable Size        3388997632 bytes
Database Buffers      872415232 bytes
Redo Buffers           23859200 bytes
Database mounted.
Database opened.
SQL> show parameter os_authent_prefix;              

NAME                     TYPE    VALUE
------------------------------------ ----------- ------------------------------
os_authent_prefix            string

5.) I logged in with the orasync user and tried to log in to the Pluggable Database...

[orasync@company02 ~]$ sqlplus /

SQL*Plus: Release 21.0.0.0.0 - Production on Sat Nov 11 01:04:49 2023
Version 21.3.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.

ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory
Additional information: 4775
Additional information: 1962365431
Process ID: 0
Session ID: 0 Serial number: 0

Enter user-name:

...and I have alsotried with...

[orasync@company02 ~]$ sqlplus /@iqlink2

SQL*Plus: Release 21.0.0.0.0 - Production on Sat Nov 11 00:47:53 2023
Version 21.3.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.

**ERROR:
ORA-01017: invalid username/password; logon denied**

Enter user-name:

At this point I don't really understand why does not work, because I get answer for tnsping...

[orasync@company02 ~]$ tnsping iqlink2

TNS Ping Utility for Linux: Version 21.0.0.0.0 - Production on 11-NOV-2023 01:08:00

Copyright (c) 1997, 2021, Oracle.  All rights reserved.

Used parameter files:
/opt/oracle/product/21c/dbhome_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = iqlinkxg02)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = iqlink2)))
OK (0 msec)
[orasync@company02 ~]$

...and I can also log in with an other DB user from the same OS user...

[orasync@company02 ~]$ sqlplus sys1@iqlink2

SQL*Plus: Release 21.0.0.0.0 - Production on Sat Nov 11 01:09:18 2023
Version 21.3.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.

Enter password: 
Last Successful login time: Sat Nov 11 2023 01:03:42 +01:00

Connected to:
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0

SQL>

---

The first thing what I tried to solve this was that to remove the ops$ value of the os_authent_prefix parameter, but in this case I got the ORA-01017: invalid username/password; logon denied error.

An other thing what I tried was that I configured an Oracle 19c client on the server and modified the .cshrc file of the orasync user accordingly. The configuration was OK, because I could log in to the DB with an other DB user, but I was still getting the ORA-01017: invalid username/password; logon denied error.

On the top of that I tried to add the setenv TWO_TASK $ORACLE_SID parameter to the .cshrc file, but it's just a formal change actually, did not help.

Any help would be much appreciated!

Answer 1

remote_os_authentication was removed with good reason. It was a MAJOR security vulerability. Any client - even a hacker - with a local user sharing the same name as a database user could connect to the database. This is an authentication architecture that should never, never, never be used.

That said, a PDB cannot be connected to directly in the way you are describing. Only a CDB can be connected to with IPC-type connections. For a PDB you must specify a TNS alias and connect through the network listener. You can then use an Oracle Wallet on the client to hold username/password credentials and achieve the same effect as an OS authenticated IPC connection.

See documentation here: https://www.oracle.com/technetwork/database/security/twp-db-security-secure-ext-pwd-stor-133399.pdf

and here: https://docs.oracle.com/en/database/oracle/oracle-database/19/dbseg/configuring-authentication.html#GUID-3EA07020-A9F3-4FF9-9518-E1AEA3BDDBBE

The basic steps are:

1. create a wallet
2. add username/password credentials to the wallet
3. configure sqlnet.ora to point to the wallet
4. connect using something like sqlplus /@pdb_tns_alias

Also note: Oracle server isn't supported on your Linux distro; for correct functionality you should use Oracle Linux.

Answer 2

If you want your new OS user (orasync) can use Oracle OS authentication, then you have to modify orasync's OS membership to be a member of Oracle dba group (usually dba, using #usermod). When sitting at OS level, you can not directly connect to Pluggable database using OS authentication (because it just leave you at root database).

The workaround is to append tnsnames.ora file with Net Service Name of your PDB (if not exist). Then using this syntax:

[orasync@company02 ~]$ sqlplus orasync/<password>@<Net_service_name_of_iqlink2>

Note: this walkaround does not require OS membership modification! Good luck!