Oracle database authentication

zbigniew.czesak · November 3, 2016, 12:42pm

Is there a way to connect to a database using dual authentication feature,

similalar what we have in SQL Deleveloper (advanced proxy) using

jdbc:oracle:oci:@xxxxx…

It is for users defined in a database like:

ALTER USER xxxxxxx GRANT CONNECT THROUGH yyyyyyy AUTHENTICATION REQUIRED;

If it is not posiible, are there plans to implement it in the future?

Thanks

Zbigniew Czesak

JohnDorlon · November 3, 2016, 12:59pm

I notice you mentioned JDBC - Toad does not use JDBC, it uses OCI. So if the OCI can support it, we should be able to support it too. I just did a little reading about this type of authentication. Unless I am missing something, it seems that SQL*Plus does not support it. That doesn’t mean that the OCI doesn’t but it’s not a good sign.

How do you currently connect to the database with this kind of user? Only jdbc?

zbigniew.czesak · November 4, 2016, 8:56am

I connect using Oracle SQL Developer.

First I have to define connection:

Connection Type [Advanced]

then in field:

Custom JDBC URL:

entering (example):

jdbc:oracle:oci:@cleu1.elections.ca

then pressing [Advanced…] button at the bottom,
it opens pop-up with username/password for the xxxxxx user
which is specified as:

ALTER USER xxxxxx
GRANT CONNECT THROUGH yyyyyyy
AUTHENTICATION REQUIRED;

When I already have connection defined as above,
to log in, I enter password only for user yyyyyy
(password for xxxxxx is stored in the Connection Properties)

JohnDorlon · November 4, 2016, 9:43am

Thank you. This is not yet possible in Toad, but I think I can make some changes to support it.

zbigniew.czesak · November 4, 2016, 9:48am

Thank you very much.

Zbigniew

From: John Dorlon [mailto:bounce-jdorlon@toadworld.com]

Sent: November 4, 2016 12:46 PM

To: toadoracle@toadworld.com

Subject: RE: [Toad for Oracle - Discussion Forum] Oracle database authentication

RE: Oracle database authentication

Reply by John Dorlon

Thank you. This is not yet possible in Toad, but I think I can make some changes to support it.

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle Forum notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.

JohnDorlon · November 4, 2016, 2:19pm

setup:

create user app_user identified by app_user;
create user end_user_ar identified by end_user_ar;
grant create session to app_user;
grant create session to end_user_ar;
alter user end_user_ar grant connect through app_user authentication required;

I’ve been working on this, and I’ve come across something that may help you. Unless I’m mistaken, both users need the CREATE SESSION privilege for this kind of proxy session. So can’t you just connect as END_USER_AR as a workaround? I mean, without using app_user at all?

I believe that I can have this kind of proxy session working soon, but I don’t see the value in it. I thought the point of proxy session was to be able to connect as the other user without having to know their password. What am I missing?

zbigniew.czesak · November 7, 2016, 8:12am

Thank you for your response.

I agree with you, I do not see much value in it either.

I was asked to investigate it as some people use it in SQL Developer.

Zbigniew

From: John Dorlon [mailto:bounce-jdorlon@toadworld.com]

Sent: November 4, 2016 5:22 PM

To: toadoracle@toadworld.com

Subject: RE: [Toad for Oracle - Discussion Forum] Oracle database authentication

RE: Oracle database authentication

Reply by John Dorlon

setup:

create user app_user identified by app_user;

create user end_user_ar identified by end_user_ar;

grant create session to app_user;

grant create session to end_user_ar;

alter user end_user_ar grant connect through app_user authentication required;