Authenticate User from Java application using JDBC

Primary tabs

Hello everyone,

I'm trying to authenticate a user(Health Share clinician) from a Java Application. 

I 'm already connected to Caché and able to run SQL commands.

My question is: How can I authenticate a user using only SQL? In fact, what I want is verify if the users exists in the base and if the given password is the same used in Health Share.

There is a column 'password' in Security.users table but I'm not able to see its content, even so, I don't know which hash function to use to compare with.

Answers

I did not show how you made connection to the Caché, and looks like, your Java application has hardcoded login password which used to connect to server. 

so, I may only suggest, that you use code like this

Class.forName ("com.intersys.jdbc.CacheDriver").newInstance();
CacheDataSource ds = new CacheDataSource();
ds.setURL("jdbc:Cache://127.0.0.1:1972/SAMPLES");
Connection dbconn = ds.getConnection("_SYSTEM","SYS");

Last string is Caché login password, so you should use here user's login and password. Any works from your application should be with user's login.

So I have to open a connection with caché using the clinician credentials? 

Yes, you still have to ask user for login password, and should check it in Caché, so, you just should try to connect with user's login/pass, and so, you can validate him.

Hello, I'm back.

I tryied, but as our clinicians aren't database users they aren't able to connect with caché.

Considering I'm connected with the database and able to run SQL commands, is there a way of run a SQL command that matches the user name and password? 

PS.: I don't know the hash function.

I don't understand what you mean when you say that your clinicians aren't database users, but it sounds like you need to give them access to the %Service_SQL service.

When you use

Connection dbconn = ds.getConnection("_SYSTEM","SYS");

"_SYSTEM" is a database user.

An clinician 'dr Thomas' isn't a database user therefore the connection won't  establish.

I really appreciate to do the user validation by myself.

In other words:

Get the user password, encode using the hash function and match with the database one.