0

I'm developing a java web application which uses Oracle. Web app uses Glassfish AS and connects to Oracle through a configured connection pool.

The customer requirements for performing user authentication is to try to create oracle session under user login/password provided, and if that succeeds, user is successfully logged on.

The first idea comes into my mind is write a function that takes login/password, perform hashing and compare hash with stored one. But I'm afraid, if I do that, I have some pitfalls like hash algorithm can change anytime, oracle account can be locked so I have to perform additional checks and so on.

Could anyone advice me reliable way to check oracle user authentification except of creating session to Oracle?

Kerb
  • 1,138
  • 2
  • 20
  • 39
  • Do you mean that only if the user can connect to the DB, he will be authorized to the web application??? or do you mean that after he was authenticated in the web-app he will connect to the DB using his own user and not the one that the connection pool is using ? it sounds from your question that you're mixing authentication and authorization plus doing it in the wrong place ... – A.B.Cade Jan 16 '13 at 09:19
  • Actually I meant authenticating user (updated the post) by creating an oracle session. After the oracle session is successfully created I consider that this user actually has a Oracle account in the db. After that I just close and forget this oracle session, and user works with web app (which using database pool). – Kerb Jan 16 '13 at 11:52
  • You say *perform hashing and compare hash with stored one* do you mean that you're going to grant select on `DBA_USERS` view to the application user (the one used with the connection pool) ??? doesn't sound right ... – A.B.Cade Jan 16 '13 at 12:57

3 Answers3

0

I wouldn't do it on the first place.
I'd do the authentication in the web-application using one of the many tools that exists out there.
More than that, I'd make the connection pool's db user - a proxy user and never give or check the DB password of the users in the application (as described in this asktom post).

But since The customer requirements for performing user authentication is to try to create oracle session, then why not just do that?

Getting access to the password in DBA_USERS view from the web-application sounds like a very bad idea...

A.B.Cade
  • 16,735
  • 1
  • 37
  • 53
  • 1
    I agree, that access to `DBA_USERS` from the web application user isn't a good idea, but it could be done as stored procedure in another oracle schema, so that web app user only has access to execute `OTHER_SCHEMA.AUTHENTICATE_USER`. We used oracle proxy user authentication, but in our case (we use JPA in our app) it brought more problems than advantages. – Kerb Jan 16 '13 at 14:59
  • No doubt this is much better, yet I still hate the idea of someone else then dba has access to the passwords... But still, why not just open a session? isn't that the customer's idea ? having the DB authentication mechanism do it for you ? (not that I think it's a good idea) – A.B.Cade Jan 16 '13 at 15:06
  • Now I have to store jdbc url in two places: 1. glassfish pool configuration 2. inside a `.properties` file in an application war. The customer has test and production environment, and of course there are two different oracle instances. So, before deploying war to production an administrator has to change configuration inside war file by hand, and I guess this approach is far from perfect. – Kerb Jan 16 '13 at 15:14
  • 1
    Then hold it in the DB instead of a `.properties` file – A.B.Cade Jan 16 '13 at 15:18
0

I have another (very bad) idea for you-

You can create a function in plsql that dynamycally creates a DBLink to the user.
Then you'll try to (dynamycally again) query DUAL table using that dblink,
if it works - return ok else return not ok.
Afterwards drop (dynamycally again) the dblink.

should look something like this anonymous block-

DECLARE
  res NUMBER := 1;
BEGIN
  EXECUTE IMMEDIATE 'create database link temp_dblink connect to user identified by password using ''dbname''';

  BEGIN
    EXECUTE IMMEDIATE 'select 1 from dual@temp_dblink';

  EXCEPTION
    WHEN others THEN
      res := 0;
  END;

  ROLLBACK;
  dbms_session.close_database_link('temp_dblink');

  EXECUTE IMMEDIATE 'drop database link temp_dblink';
END;

This of course can't be taken "as is",
you need to bind the "username" and "password",
need to make the name of tmp_dblink different for every user/try (web-session),
and more...

Also note that this may leave unwanted dblinks in your db and so on

I wouldn't recommend using this solution
(I wrote it more for the sport of it...)

A.B.Cade
  • 16,735
  • 1
  • 37
  • 53
  • I'm trying one more idea - to find out path to glassfish configuration file, then parse it and get the jdbc url. I didn't want to bind to Glassfish environment variables like *current running Glassfish instance root* to make application more portable, but it seems to me it will be the most appropriate solution. – Kerb Jan 16 '13 at 17:02
  • Are you sure that parsing glassfish configuration file is better than a one time query to the DB to get the jdbc url? BTW, if you have a tnsnames.ora file - parsing it might be easier – A.B.Cade Jan 16 '13 at 17:30
  • You also [can't use bind variables with DDL](http://stackoverflow.com/a/5538183/266304), which makes this slightly worse than you already thought it was *8-) – Alex Poole Jan 16 '13 at 18:03
  • I've found that glassfish instance (*domain*) configuration stored in just one file *$GLASSFISH_HOME/domains/domain1/domain.xml*. Since I already have utility class for xml parsing it's couple lines of code to get jdbc from the xml. I'll post my code in answer below. – Kerb Jan 16 '13 at 18:04
0

I've solved the problem by parsing glassfish domain configuration file $GLASSFISH_ROOT/domains/domain1/domain.xml with the following code:

 private String getGlassfishConfigParameterJdbcUrl() {
    String instanceRoot = System.getProperty("com.sun.aas.instanceRootURI");
    URI configUri = null;
    try {
        configUri = new URI(instanceRoot);

        File configFile = new File(configUri.getPath(), "config" + File.separator + "domain.xml");
        XmlNode node = XmlNode.fromFile(configFile);
        return node.getNode("domain/resources/jdbc-connection-pool[@name=\"GLASSFISH_DB_POOL_NAME\"]/property[@name=\"URL\"]").getAttribute("value");
    } catch (URISyntaxException e) {
        throw new RuntimeException(e);
    }
}

XmlNode is my utility class for xml parsing.

Kerb
  • 1,138
  • 2
  • 20
  • 39