Unable to authenticate with form based authentication using JDBC

[K. Tsang]

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

CREATE DATABASE test;

CREATE  TABLE `test`.`users` (
  `userId` BIGINT NOT NULL AUTO_INCREMENT ,
  `username` VARCHAR(50) NOT NULL ,
  `password` VARCHAR(100) NOT NULL ,
  PRIMARY KEY (`userId`) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;


CREATE  TABLE `test`.`groups` (
  `groupId` BIGINT NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(50) NOT NULL ,
  `description` VARCHAR(100) NULL ,
  PRIMARY KEY (`groupId`) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;


CREATE  TABLE `test`.`users_groups` (
  `userId` BIGINT NOT NULL ,
  `username` VARCHAR(50) NOT NULL ,
  `groupId` BIGINT NOT NULL ,
  INDEX `FK_USERS` (`userId` ASC) ,
  INDEX `FK_GROUPS` (`groupId` ASC) ,
  PRIMARY KEY (`userId`, `groupId`) ,
  CONSTRAINT `FK_USERS`
    FOREIGN KEY (`userId` )
    REFERENCES `test`.`users` (`userId` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `FK_GROUPS`
    FOREIGN KEY (`groupId` )
    REFERENCES `test`.`groups` (`groupId` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;


INSERT INTO `test`.`groups` (`name`, `description`) VALUES ('GROUPA', 'Group A users');
INSERT INTO `test`.`groups` (`name`, `description`) VALUES ('GROUPB', 'Group B users');


INSERT INTO `test`.`users` (`username`, `password`) VALUES ('userA', 'userA');
INSERT INTO `test`.`users` (`username`, `password`) VALUES ('userB', 'userB');


INSERT INTO `test`.`users_groups` (`userId`, `username`, `groupId`) VALUES (1, 'userA', 1);
INSERT INTO `test`.`users_groups` (`userId`, `username`, `groupId`) VALUES (2, 'userB', 2);

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

        <form method="post" action="j_security_check">
            Username <input name="j_username" type="text"><br>
            Password <input name="j_password" type="password"><br>
            <input type="submit" value="Submit">
        </form>

setup security stuff in web.xml

    <security-constraint>
        <display-name>groupA</display-name>
        <web-resource-collection>
            <web-resource-name>groupA</web-resource-name>
            <description>Group A pages</description>
            <url-pattern>/groupA/*</url-pattern>
        </web-resource-collection>
        <auth-constraint>
            <role-name>GROUPA</role-name>
        </auth-constraint>
        <user-data-constraint>
            <transport-guarantee>CONFIDENTIAL</transport-guarantee>
        </user-data-constraint>
    </security-constraint>
    <security-constraint>
        <display-name>groupB</display-name>
        <web-resource-collection>
            <web-resource-name>groupB</web-resource-name>
            <description>Group B pages</description>
            <url-pattern>/groupB/*</url-pattern>
        </web-resource-collection>
        <auth-constraint>
            <role-name>GROUPB</role-name>
        </auth-constraint>
        <user-data-constraint>
            <transport-guarantee>CONFIDENTIAL</transport-guarantee>
        </user-data-constraint>
    </security-constraint>
    <security-constraint>
        <display-name>allGroups</display-name>
        <web-resource-collection>
            <web-resource-name>allGroups</web-resource-name>
            <description>all group pages</description>
            <url-pattern>/bothgroups/*</url-pattern>
        </web-resource-collection>
        <user-data-constraint>
            <transport-guarantee>CONFIDENTIAL</transport-guarantee>
        </user-data-constraint>
    </security-constraint>
    <security-constraint>
        <display-name>https</display-name>
        <web-resource-collection>
            <web-resource-name>https</web-resource-name>
            <description>https access</description>
            <url-pattern>/*</url-pattern>
        </web-resource-collection>
        <user-data-constraint>
            <transport-guarantee>CONFIDENTIAL</transport-guarantee>
        </user-data-constraint>
    </security-constraint>
    <login-config>
        <auth-method>FORM</auth-method>
        <realm-name>test_jdbcRealm</realm-name>
        <form-login-config>
            <form-login-page>/login.jsp</form-login-page>
            <form-error-page>/login.jsp</form-error-page>
        </form-login-config>
    </login-config>
    <security-role>
        <description>Group A users</description>
        <role-name>GROUPA</role-name>
    </security-role>
    <security-role>
        <description>Group B users</description>
        <role-name>GROUPB</role-name>
    </security-role>

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?

[Stefan Evans]

INSERT INTO `test`.`groups` (`name`, `description`) VALUES ('GROUPA', 'Group A users');
INSERT INTO `test`.`groups` (`name`, `description`) VALUES ('GROUPB', 'Group B users');
 
 
INSERT INTO `test`.`users` (`username`, `password`) VALUES ('userA', 'userA');
INSERT INTO `test`.`users` (`username`, `password`) VALUES ('userB', 'userB');
 
 
INSERT INTO `test`.`users_groups` (`userId`, `username`, `groupId`) VALUES (1, 'userA', 1);
INSERT INTO `test`.`users_groups` (`userId`, `username`, `groupId`) VALUES (2, 'userB', 2);

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.

select userId, username, groupId
from users 
left join users_groups on users.userId = users_groups.userId
left groups on user_groups.groupId = groups.groupId

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]

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)

userId,username,groupId,name
1,userA,1,GROUPA
2,userB,2,GROUPB

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]

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]

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]

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]

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]

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]

OK I managed to figure it out somewhat.

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

   //init stuff
     String jaasCtx = props.getProperty(IASRealm.JAAS_CONTEXT_PARAM);
        String dbUser = props.getProperty(PARAM_DB_USER);
        String dbPassword = props.getProperty(PARAM_DB_PASSWORD);
        String dsJndi = props.getProperty(PARAM_DATASOURCE_JNDI);
        String digestAlgorithm = props.getProperty(PARAM_DIGEST_ALGORITHM,
            getDefaultDigestAlgorithm());
        String encoding = props.getProperty(PARAM_ENCODING);
        String charset = props.getProperty(PARAM_CHARSET);
        String userTable = props.getProperty(PARAM_USER_TABLE);
        String userNameColumn = props.getProperty(PARAM_USER_NAME_COLUMN);
        String passwordColumn = props.getProperty(PARAM_PASSWORD_COLUMN);
        String groupTable = props.getProperty(PARAM_GROUP_TABLE);
        String groupNameColumn = props.getProperty(PARAM_GROUP_NAME_COLUMN);
        String groupTableUserNameColumn = props.getProperty(PARAM_GROUP_TABLE_USER_NAME_COLUMN,userNameColumn); // interesting
        cr = Util.getDefaultHabitat().getByContract(ConnectorRuntime.class);

// pwd sql
passwordQuery = "SELECT " + passwordColumn + " FROM " + userTable +
            " WHERE " + userNameColumn + " = ?";

// group sql
        groupQuery = "SELECT " + groupNameColumn + " FROM " + groupTable +
            " WHERE " + groupTableUserNameColumn + " = ? ";  // groupTableUserNameColumn default to userNameColumn if blank or empty

// some lines later when checking password
            statement = connection.prepareStatement(passwordQuery);
            statement.setString(1, username);
            rs = statement.executeQuery();


// some lines later checking groups
            statement =  connection.prepareStatement(groupQuery);
            statement.setString(1, user);
            rs = statement.executeQuery();

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:

CREATE DATABASE test;
 
CREATE  TABLE `test`.`users` (
  `username` VARCHAR(50) NOT NULL ,
  `password` VARCHAR(100) NOT NULL ,
  PRIMARY KEY (`username`) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;
 
 
CREATE  TABLE `test`.`groups` (
  `name` VARCHAR(50) NOT NULL ,
  `description` VARCHAR(100) NULL ,
  PRIMARY KEY (`name`) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;
 
 
CREATE  TABLE `test`.`users_groups` (
  `username` VARCHAR(50) NOT NULL ,
  `groupName` VARCHAR(50) NOT NULL ,
  INDEX `FK_USERS` (`username` ASC) ,
  INDEX `FK_GROUPS` (`groupName` ASC) ,
  PRIMARY KEY (`username`, `groupName`) ,
  CONSTRAINT `FK_USERS`
    FOREIGN KEY (`username` )
    REFERENCES `test`.`users` (`username` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `FK_GROUPS`
    FOREIGN KEY (`groupName` )
    REFERENCES `test`.`groups` (`name` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;
 
 
INSERT INTO `test`.`groups` (`name`, `description`) VALUES ('GROUPA', 'Group A users');
INSERT INTO `test`.`groups` (`name`, `description`) VALUES ('GROUPB', 'Group B users');
 
 
INSERT INTO `test`.`users` (`username`, `password`) VALUES ('userA', 'userA');
INSERT INTO `test`.`users` (`username`, `password`) VALUES ('userB', 'userB');
 
 
INSERT INTO `test`.`users_groups` (`username`, `groupName`) VALUES ('userA', 'GROUPA');
INSERT INTO `test`.`users_groups` (`username`, `groupName`) VALUES ('userB', 'GROUPB');

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]

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]

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 "[e-mail]" 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.