web app authentication: users from oracle database.

[Urh Srecnik]

Hi,

I've built a sample web application (jsp/struts). It is currently using form based authentication (j_security_check).
Now, I'd like it to use users from Oracle database. I know about SQL Authenticator, but I'd like to authenticate against actual database users (users created by "create user identified by ..." and not users from some table in my schema).

* Can this be achieved by correctly configuring form based authentication (if yes, how?).
* What approach would you recommend for that kind of authentication?

btw, I'd like to deploy that sample application to WebLogic 10.3 server.

[Deepak Bala]

You want to authenticate against the users that login to the database instead of users that are available in a table, correct ?

[Urh Srecnik]

correct, yes...

[Andy Kay]

Urh,

When you say authenticate against actual database users but not users from some database tables. The statement is kind of confusing.

However, If I understand you correctly, to achieve login authentication against data base, you will need to configure RDBMS authentication providers. There are three ways you can configure in weblogic:

1. SQL authentication provider - uses data base with read/write access.
2. Read only SQL authentication provider - all above with only read access
3. Custom RDBMS authenticator - you will need to write some custom plug-in code with your custom sql schema.

Hope the above information helps.

Andy

[Urh Srecnik]

Sorry for confusing statement.

1. and 2.: SQL Authentication provider requires me to issue something like "create table myusers". That is not what I want.
I'm not really sure about Custom RDBMS authenticator, however i don't want to modify any schema.

I want to authenticate against the usernames/passwords you use when connecting via JDBC for example or when you connect to the database using a tool like SQL*Plus.

[Deepak Bala]

Urh Srecnik wrote:Sorry for confusing statement.

1. and 2.: SQL Authentication provider requires me to issue something like "create table myusers". That is not what I want.
I'm not really sure about Custom RDBMS authenticator, however i don't want to modify any schema.

I want to authenticate against the usernames/passwords you use when connecting via JDBC for example or when you connect to the database using a tool like SQL*Plus.

Understood. I have never done this before. You should be able to get the list of all users using the all_users or dba_users table. Passwords are encrypted with some sort of salt, so getting this will be impossible without knowing the underlying implementation (at least to my knowledge).

A hack solution, is to get the user name and password from the user and try to establish a JDBC connection with the credentials. If it succeeds then you have the correct user name and password. Else the login was incorrect.

[Urh Srecnik]

Thanks for your answer.

"Hack solution" is what I was hoping to avoid

[Tim Holloway]

Unless the app is a DBA app, I generally don't recommend authenticating using database credentials. It makes maintenance of the securtity system a DBA's job, and most organizations of any size have the DBA and the security administrator as 2 different people, frequently in different departments. It makes the security auditors happier, for one thing.

However, reading security credentials is even less desirable. First, because it means you may have to widen the access rules just to do it, and secondly because the most secure systems never volunteer anything.

You can do a simple database connect attempt to check for the existence of a valid userid/password combination. For checking additional rights in Oracle, often a simple select from DUAL is sufficient. For full read/write verification, consider adding a table that holds something like the timestamp the user last logged in and do an insert/replace. If it fails, they're not authorized. I leave the role determination up to you.

Expect to have to code your own security manager module, however, since the stock modules expect that if a database is involved, it has userid/password and role tables. Which would be maintained by the security officer, not the DBA.

[Urh Srecnik]

I'm not sure I understand. I mean, it certainly does make sense, but:

Many companies which use Oracle Forms applications (those apps seem to require database authentication, i'm not sure) are trying to deploy new Java applications. Most of the companies I know don't have LDAP (or similar). They simply use database accounts and expects for new applications to do the same. Forms developers also seem to believe that there is no need for administrator to go trough all the trouble setting up different authentication mechanisms and that java developer should simply find a way around that.

And in fact, simply trying to connect to database approach works fine, but it just feels wrong

I wonder how others solved this, since I'm pretty sure I'm not the only java developer facing this problem.

[Tim Holloway]

I'm out of practice on Oracle Forms, but a desktop app frequently does expect individual database accounts per user.

Webapps usually don't. For one thing, a webapp potentially has many, many more users. What DBA would want to manage Amazon.com that way? Even with fewer users, there's another reason. You can't use database connection pooling when each and every user has a discrete connection thanks to having discrete credentials and privileges. And creating connections is expensive, which is why connection pooling is so popular.

So typically, a webapp would have a database account whose rights were the greatest-common-denominator of all the rights required by its users. The app itself (and in J2EE, the security framework) then assumes the duty of making the finer security restrictions for each user.

And in fact, simply trying to connect to database approach works fine, but it just feels wrong

Ah, but it's altogether right. Remember, the best security systems never volunteer. If you return a message saying "Invalid Password", you've just cut the invader's work in half, since he now knows he's got a valid userid and doesn't need to guess userids, just passwords. If you do a query that returns a password back from the database server, a network sniffer can intercept and record it. If you only send a connection request and get back a pass/fail indication, that cuts the intelligence that can be sniffed down even further. Plus, it's less overhead.

I think LDAP (at least Active Directory) is more common than you think. And in AD shops, you already have someone managing network logins, so it's not a big jump to having the same person(s) maintain security profiles in the same LDAP database - typically attached to their network identities.