Everyone is "admin" role
I am having a problem with roles. A user called tomcat is in a role called admin, but should not be. I can login okay with the tomcat user but, the tomcat user can do everything an admin can do, which is not what I want. I then tried to programmitaclly see if tomcat user is an admin and he was.
JBoss security is setup as follows:
<application-policy name = "express">
<login-module code = "org.jboss.security.auth.spi.DatabaseServerLoginModule"
flag = "required">
<module-option name = "dsJndiName">jdbc/mysql</module-option>
<module-option name = "principalsQuery">
select passwrd from app_user where username=?
</module-option>
<module-option name = "rolesQuery">
select role_name, 'Roles' from user_role where username=?
</module-option>
<module-option name="hashAlgorithm">SHA</module-option>
<module-option name="hashEncoding">base64</module-option>
</login-module>
</application-policy>
When I run the querries in the database workbench they seem to work as they should.
(I tried several combinations of encoding and hash to no avail).
It should be like this:
user tomcat is in the role "user"
user mraible is in the role "admin"
Here is the role table:
CREATE TABLE USER_ROLE
(
ID NUMERIC( 18, 0) NOT NULL,
USER_ID NUMERIC( 18, 0) NOT NULL,
USERNAME VARCHAR( 255) NOT NULL COLLATE NONE,
ROLE_NAME VARCHAR( 255) NOT NULL COLLATE NONE,
PRIMARY KEY (ID)
);
This query
select USER_NAME ROLENAME from USER_ROLE;
outputs this:
USER_NAME ROLENAME
tomcat user
mraible admin
Here is the DDL for the user table:
RECREATE TABLE APP_USER
(
ID NUMERIC( 18, 0) NOT NULL,
USERNAME VARCHAR( 40) NOT NULL COLLATE NONE,
PASSWRD VARCHAR( 150) NOT NULL COLLATE NONE,
FIRSTNAME VARCHAR( 40) NOT NULL COLLATE NONE,
LASTNAME VARCHAR( 40) NOT NULL COLLATE NONE,
EMAIL VARCHAR( 100) COLLATE NONE,
PHONENUMBER VARCHAR( 15) COLLATE NONE,
PASSWORDHINT VARCHAR( 40) COLLATE NONE,
INCREMENTBY FLOAT,
VER INTEGER,
PRIMARY KEY (ID)
);
The above has the following data:
ID,USERNAME,FIRSTNAME,EMAIL
1,"tomcat","Tomcat","matt_raible@yah.com"
2,"mraible","Matt","matt@raible.com"
3,"rick","Rick","rick@arc-mind.com"
