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.
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!
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
The basic steps are:
sqlplus /@pdb_tns_aliasAlso note: Oracle server isn't supported on your Linux distro; for correct functionality you should use Oracle Linux.