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.

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.

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?