• Post Reply Bookmark Topic Watch Topic
  • New Topic

Unable to authenticate with form based authentication using JDBC

 
K. Tsang
Bartender
Posts: 3604
16
Firefox Browser Java Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello ranchers,

I have testing form-based authentication and found that I can't do the authentication. Here is my setup
Setup database (MySQL) with users and groups tables


Setup JDBC realm in app server (Glassfish) (refer to attach image)


Setup WAR project
create jsp groupA/index.jsp (for group A users)
create jsp groupB/index.jsp (for group B users)
create jsp bothgroups/index.jsp (for groups A and B users)
create login page

setup security stuff in web.xml


Deploy the WAR and run it ...
when access groupA/index.jsp -> login page prompted -> enter userA/userA -> error in server.log

Severe: jdbcrealm.invaliduserreason
Warning: WEB9102: Web Login Failed: com.sun.enterprise.security.auth.login.common.LoginException: Login failed: Security Exception


I don't know is it my web.xml or the DB table setup or the server JDBC realm setup that is wrong/incorrect. Any ideas?

Screen Shot 2015-06-14 at 14.43.11 pm.png
[Thumbnail for Screen Shot 2015-06-14 at 14.43.11 pm.png]
Glassfish JDBC realm
 
Stefan Evans
Bartender
Posts: 1822
10
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator


One thing I notice here.
You have assumed that the userA belongs to userId 1 and group 1.

How about we just check that is the case? You don't specify an id when you insert the values, so the database may have given them another id.
You can check if any rows are returned by this query.

os maybe even just:
select * from users to manually check the ids.


Is there any more to that error message?
A caused by?



 
K. Tsang
Bartender
Posts: 3604
16
Firefox Browser Java Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks Stefan for your reply.

After some playing around, I found my JDBC config missed the URL. Once I fixed that, I got a new error.

Severe: SEC1111: Cannot load group for JDBC realm user <user>

When I did the sql join I got (CSV format)


So userA is clearly in groupA and userB in groupB.

There is no cause by or stacktrace even I turned on logging for all events.

I think it's the server config more than my app web.xml or db tables
 
Tim Holloway
Bartender
Posts: 18408
58
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Actually, have you checked to see if the Glassfish userid itself has been granted access to those tables from the host that you're running Glassfish on?

I'm not sure that the security violation was a failure for the userID and password to match. To me it reads more like the userID and password couldn't be checked at all because Glassfish couldn't open a database connection.

MySQL security is fairly stringent by default.
 
K. Tsang
Bartender
Posts: 3604
16
Firefox Browser Java Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Tim Holloway wrote:Actually, have you checked to see if the Glassfish userid itself has been granted access to those tables from the host that you're running Glassfish on?

I'm not sure that the security violation was a failure for the userID and password to match. To me it reads more like the userID and password couldn't be checked at all because Glassfish couldn't open a database connection.

MySQL security is fairly stringent by default.


You mean the JDBC user to connect to MySQL? I doubt that's an issue cos I'm using root.
 
Tim Holloway
Bartender
Posts: 18408
58
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Root gets no special privileges that way.

In the RedHat-related distros, the default is that root cannot access anything unless it's used on the same machine that mysql is running on. They also have an app that's designed to ensure that root has a secure password that's supposed to be run after installation.
 
K. Tsang
Bartender
Posts: 3604
16
Firefox Browser Java Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
OK found some interesting thing. I was using GF 3.x before, then I thought maybe GF 4 will do the trick. So set up GF 4 blabla

I still get the error but now the cause is much clear.

Severe: jdbcrealm.grouperror
Fine: Cannot load group
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'username' in 'where clause'

Then I thought what the ... unknown column username?? what table is it querying? Then I got the hold of the JDBCRealm source and I will see what went wrong.

More on this later.
 
Tim Holloway
Bartender
Posts: 18408
58
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I think you may be confusing "username" the actual name of the user with "username" the user ID.

The Realm could care less about the user's actual name. It just pairs userid and password and userid and group ID(s).

Incidentally, I don't recommend doing things with upper and lower case in databases. There are no consistent rules for when the DBMS gets picky about capitalization. It's much safer to name the column "user_id". Inasmuch as there's any method to the madness, you can usually define a column using lower case in a DBMS and reference it using either lower or upper case, but if you define a column name with uppercase in it, all bets are off as to how it will be handled.
 
K. Tsang
Bartender
Posts: 3604
16
Firefox Browser Java Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
OK I managed to figure it out somewhat.

From the JDBCRealm source, there are only 2 sql namely:


Now I noticed 2 things:
No SQL joins
parameters are strings

Given these 2 facts, I realized that I can't use an (auto-increment) ID and expects the server to map find the "group" name and/or username

To summarize the DB tables look like:



As for GF JDBC realm

User Table = users
User Name Column = username
Password Column = password
Group Table = users_groups
Group Table User Name Column = username (if blank, same as User Name Column)
Group Name Column = groupName
 
K. Tsang
Bartender
Posts: 3604
16
Firefox Browser Java Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Regarding using auto-increment ID, the users table can use.

The groups table CANNOT, such that the group name is the PK

The users_groups table contains 2 strings eg username and group name
 
Tim Holloway
Bartender
Posts: 18408
58
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I think I need to explain.

The setup expects 2 tables. One has the userid/password and one has the userid/groupid. Whatever other stuff you may wish to put into them is up to you, but that's all the container security system is interested in.

The "userid" is the EXACT text that you would enter in as a login ID on the login form or pop-up dialog. So, whether it's "mtsinc" or "timh@mousetech.com" or even "Tim Holloway", whatever you log in with, that's what the security system needs. Auto-increment IDs are generally not what you'd want there. The userid also MUST be unique in the user/password table.

The group table allows a 1-to-many mapping for rolenames, pairing the userID (login ID) with role names. You can use the same user and role ("group") tables for multiple apps, as long as any role names that the apps have have the same meaning. In other words, if you have a role named "administrator", then every app that's got an "administrator" role in its web.xml and shares that role table will have that user pegged as an administrator. If that's a problem, define something like "app1-administrator". You can map logical (in-app) role names to physical (role table) names using web.xml. The role table does NOT have unique simple keys, but every userid/roleid pairing should be unique.

There is, as you've noted, no join. What happens is that when you log in, a UserPrincipal object gets constructed and attached to your session, where it can them be populated into incoming HttpServletRequest objects, along with the login userid accessible via getRemoteUser(). The authentication code simply checks to see if "COUNT(*) FROM user_password WHERE user_id = ? AND password = ?" is greater than zero (ideally, it's 1). If that query fails, then login fails. Otherwise, a "SELECT rolename FROM user_roles WHERE user_id=?" is executed to retrieve and build the list of allowed roles for the user. That's done at login time because a user's roles cannot be changed while a user is logged in or potential security leaks could develop.

Just in case you're confused, I've used generic table and column names for my example above. The actual table and column names come from your security configuration dialog.

So the password check (authentication) and the retrieval of roles (authorization) are 2 independent functions, using 2 independent tables, despite the fact that both use a common userID. You can, of course, add to the capabilities of your webapp using the userID (via getRemoteUser) to look up additional user account properties (via application database logic, LDAP, or whatever) within the webapp as you see fit.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!