Connect to Oracle using SQLDeveloper using OS Authentication on a remote machine

Question

I reach my database server from my MacBook via SSH. You can login into this database as by using OS authentication.

MacBook:~ mangeles$ ssh [email protected]
[email protected]'s password: 
Last login: Thu May 21 11:45:14 2015 from 172.31.12.51
[oracle@bd ~]$ . oraenv
ORACLE_SID = [MF] ? PROD
[oracle@bd ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Thu May 21 11:46:17 2015
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> 

I'm able to connect to the database using SQLDeveloper but I have to provide a username and a password. I can connect to all the databases as a SYS (through SSH) but I don't have the SYS password of all the databases and changing the password is not an option.

I want to know how I can use the OS Authentication in my SQLDev to login as I do in my terminal, without entering a password.

This is the error i get

Answer 1

These are the default values related to this:

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
os_authent_prefix string ops$
remote_os_authent boolean FALSE

Set remote_os_authent to true:

alter system set remote_os_authent=true scope=spfile sid='*';
shutdown immediate
startup

Create a database user with your OS username, prefixed with the above value:

create user ops$mangeles identified externally;
grant create session to ops$mangeles;

This way you can log in without a password because of the remote OS authentication. The problem is, anyone can create an OS user named mangeles on any machine, and they will be able to log in without providing a password.

SYSDBA will not work with this type of authentication, if you try grant sysdba to ops$mangeles, you will get ORA-01997: GRANT failed: user 'OPS$MANGELES' is identified externally.

• Colin 't Hart: Setting remote_os_authent to TRUE is a very bad idea and your sentences on this should be bold and in red :-) And the parameter is deprecated in 11g and higher.

Answer 2

When you login in the terminal, Oracle lets you in because the user oracle on the server 10.10.1.64 is a member of the OSDBA group on that machine.

When you connect from SQL Developer on your laptop, it's a remote connection so OS Authentication isn't normally possible. You might be able to use SSH Authentication, see https://docs.oracle.com/cd/E39885_01/appdev.40/e38414/intro.htm#CHDEHFFB Maybe it's configurable when you click on the "Advanced..." button?