JDBC Realm

[bjornn]

Hi all,

Following some hints, tips, articles I'm trying to develop a small sample using JAAS & JBoss.

I think I have understood the concepts, but when I start to code, things became hard... hehehe.

Maybe I have forgot something... I don't know exactaly.

According what I have read, I proceed like this:

Step 1: Declare a new application policy in login-config.xml at conf dir:

 <application-policy name = "jaas-webapp-domain">
 <authentication>
 <login-module code = "org.jboss.security.auth.spi.DatabaseServerLoginModule"
 flag = "required">
 <module-option name = "unauthenticatedIdentity">pirata</module-option>
 <module-option name = "dsJndiName">jdbc/jaasDS</module-option>
 <module-option name = "principalsQuery">SELECT PASSWORD FROM USERS WHERE LOGIN=?</module-option>
 <module-option name = "rolesQuery">SELECT LOGIN, 'Roles' FROM ROLES WHERE LOGIN=?</module-option>
 </login-module>
 </authentication>
 </application-policy>

Step 2: Modify web.xml file:

 <security-constraint>
 <web-resource-collection>
 <web-resource-name>página dos pebas</web-resource-name>
 <url-pattern>/peba/*</url-pattern>
 </web-resource-collection>
 <auth-constraint>
 <role-name>peba</role-name>
 </auth-constraint>
 </security-constraint>

 <security-role>
 <role-name>peba</role-name>
 </security-role>

 <login-config>
 <auth-method>BASIC</auth-method>
 <realm-name>jaas-webapp-realm</realm-name>
 </login-config>

Step 3: add jboss-web.xml inside WEB-INF dir. Like this:

<?xml version="1.0" encoding="UTF-8"?>
 <jboss-web>
 <security-domain>java:/jaas/jaas-webapp-domain</security-domain>
 </jboss-web>

I have tried to write de sql to query principals in a WRONG WAY (i.e. missing table name after from keywork, missing where keyword) just to see if jboss would complain, tell me that something was wrong.... But nothing happened. I think it isn't called...

IE shows me the login window, I try 3 times, and I get a forbidden error... but no sql error appears in jboss log...

Maybe tag X in file Y must have the same value as tag Z in file W and I didn't realize...

What I'm doing wrong? should I find some reference to my security domain(java:/jaas/jaas-webapp-domain ) in jmx-console? I didn't found. My datasources are there... I found them...

I hope that you can understand me e send me some help.
Thanks in advance.

Excuse me for any english mistakes.

[j2ee_junkie]

bjornn,

So far all your config looks good. The only thing that looks odd is your queries. These must be made specific to your database. Specifically, your query...

<module-option name = "rolesQuery">SELECT LOGIN, 'Roles' FROM ROLES WHERE LOGIN=?</module-option>

just returns the login name. Is this correct? Other than checking these queries, you will want to look at trace logging output of the login event to see what is going on.

good luck, griffith.

[bjornn]

I have copied the query from login-config.xml.... from the policy "JBossMQ, and just make the necessary modifications.... Both policies(mine and jbossmq) are in the same file, under conf dir.

The JBossMQ policy looks like this:

 <!-- Security domain for JBossMQ -->
 <application-policy name = "jbossmq">
 <authentication>
 <login-module code = "org.jboss.security.auth.spi.DatabaseServerLoginModule"
 flag = "required">
 <module-option name = "unauthenticatedIdentity">guest</module-option>
 <module-option name = "dsJndiName">java:/DefaultDS</module-option>
 <module-option name = "principalsQuery">SELECT PASSWD FROM JMS_USERS WHERE USERID=?</module-option>
 <module-option name = "rolesQuery">SELECT ROLEID, 'Roles' FROM JMS_ROLES WHERE USERID=?</module-option>
 </login-module>
 </authentication>
 </application-policy>

But wait... maybe i'm making a mistake... my tables contains these fields:
user's table: login, password
role's table: login(from user's table), role_name, role_group(I don't know the propose of this field, but in jboss wiki it seems mandatory to me)

By looking the declaration of jbossmq policy, my role query must retrive the id of the role(login?!) and a static text 'roles'. Is this right? wrong? why?

If my query looks bad, how it could be? Maybe a select *?

Thanks.

[zurchman]

Debugging authentication can be tricky. If you think JDBC is tricky, try LDAP.

First, a point and a question:

1) The JBoss Security documentation calls for the Data source to be defined as java:/jaasDS

2) Do you have this datasource defined and is it visible in the jmx-console?
You can find samples for your database in docs/examples/jca.

Once the database is visible to JBoss, you will have to enable TRACE logging in conf/log4j.xml in order to diagnose authentication activity. You might have to change the "Threshold" for the log messages. Something like this should do it:



<appender-ref ref="FILE"/>


Let us know when you get it working.

[j2ee_junkie]

bjornn,

Your problem then is your query. As I already mentioned, the sql configured in your security-domain must be specific for you database. For more details about the DatabaseServerLoginModule see http://wiki.jboss.org/wiki/Wiki.jsp?page=DatabaseServerLoginModule.

cgriffith

[zurchman]

Oops.

Forgot to escape the xml for conf/log4j.xml. (Haven't posted here for a while).

 <category name="org.jboss.security.auth">
 <priority value="TRACE" class="org.jboss.logging.XLevel"/>
 <appender-ref ref="FILE"/>
 </category>

[bjornn]

Hi j2ee_junkie, Hi zurchman.

Follow me:

"j2ee_junkie" wrote:
bjornn,

Your problem then is your query. As I already mentioned, the sql configured in your security-domain must be specific for you database. For more details about the DatabaseServerLoginModule see http://wiki.jboss.org/wiki/Wiki.jsp?page=DatabaseServerLoginModule.

cgriffith

I change my query to retrieve all columns of the user's and role's tables. look:

 <application-policy name = "jaas-webapp-domain">
 <authentication>
 <login-module code = "org.jboss.security.auth.spi.DatabaseServerLoginModule"
 flag = "required">
 <module-option name = "unauthenticatedIdentity">pirata</module-option>
 <module-option name = "dsJndiName">jdbc/jaasDS</module-option>
 <module-option name = "principalsQuery">SELECT LOGIN, PASSWORD FROM USERS WHERE LOGIN=?</module-option>
 <module-option name = "rolesQuery">SELECT LOGIN, ROLE_NAME, ROLE_GROUP FROM ROLES WHERE LOGIN=?</module-option>
 </login-module>
 </authentication>
 </application-policy>

Question: How jboss know what column represents the password, for example? How should it know? Same for role name... Maybe i'm missing some step in configuration...

I have modified my log4j.xml and get it when I try to login for the first time:

2006-08-02 16:20:35,141 TRACE [org.jboss.security.auth.login.XMLLoginConfigImpl] Begin getAppConfigurationEntry(jaas-webapp-domain), size=9
2006-08-02 16:20:35,141 TRACE [org.jboss.security.auth.login.XMLLoginConfigImpl] Begin getAppConfigurationEntry(jaas-webapp-domain), size=9
2006-08-02 16:20:35,141 TRACE [org.jboss.security.auth.login.XMLLoginConfigImpl] End getAppConfigurationEntry(jaas-webapp-domain), authInfo=AppConfigurationEntry[]:
[0]
LoginModule Class: org.jboss.security.auth.spi.DatabaseServerLoginModule
ControlFlag: LoginModuleControlFlag: required
Options:name=rolesQuery, value=SELECT LOGIN, ROLE_NAME, ROLE_GROUP FROM ROLES WHERE LOGIN=?
name=principalsQuery, value=SELECT LOGIN, PASSWORD FROM USERS WHERE LOGIN=?
name=unauthenticatedIdentity, value=pirata
name=dsJndiName, value=jdbc/jaasDS

2006-08-02 16:20:35,141 TRACE [org.jboss.security.auth.login.XMLLoginConfigImpl] End getAppConfigurationEntry(jaas-webapp-domain), authInfo=AppConfigurationEntry[]:
[0]
LoginModule Class: org.jboss.security.auth.spi.DatabaseServerLoginModule
ControlFlag: LoginModuleControlFlag: required
Options:name=rolesQuery, value=SELECT LOGIN, ROLE_NAME, ROLE_GROUP FROM ROLES WHERE LOGIN=?
name=principalsQuery, value=SELECT LOGIN, PASSWORD FROM USERS WHERE LOGIN=?
name=unauthenticatedIdentity, value=pirata
name=dsJndiName, value=jdbc/jaasDS

2006-08-02 16:20:35,141 TRACE [org.jboss.security.auth.spi.DatabaseServerLoginModule] initialize
2006-08-02 16:20:35,141 TRACE [org.jboss.security.auth.spi.DatabaseServerLoginModule] initialize
2006-08-02 16:20:35,157 TRACE [org.jboss.security.auth.spi.DatabaseServerLoginModule] Saw unauthenticatedIdentity=pirata
2006-08-02 16:20:35,157 TRACE [org.jboss.security.auth.spi.DatabaseServerLoginModule] Saw unauthenticatedIdentity=pirata
2006-08-02 16:20:35,157 TRACE [org.jboss.security.auth.spi.DatabaseServerLoginModule] DatabaseServerLoginModule, dsJndiName=jdbc/jaasDS
2006-08-02 16:20:35,157 TRACE [org.jboss.security.auth.spi.DatabaseServerLoginModule] DatabaseServerLoginModule, dsJndiName=jdbc/jaasDS
2006-08-02 16:20:35,157 TRACE [org.jboss.security.auth.spi.DatabaseServerLoginModule] principalsQuery=SELECT LOGIN, PASSWORD FROM USERS WHERE LOGIN=?
2006-08-02 16:20:35,157 TRACE [org.jboss.security.auth.spi.DatabaseServerLoginModule] principalsQuery=SELECT LOGIN, PASSWORD FROM USERS WHERE LOGIN=?
2006-08-02 16:20:35,157 TRACE [org.jboss.security.auth.spi.DatabaseServerLoginModule] rolesQuery=SELECT LOGIN, ROLE_NAME, ROLE_GROUP FROM ROLES WHERE LOGIN=?
2006-08-02 16:20:35,157 TRACE [org.jboss.security.auth.spi.DatabaseServerLoginModule] rolesQuery=SELECT LOGIN, ROLE_NAME, ROLE_GROUP FROM ROLES WHERE LOGIN=?
2006-08-02 16:20:35,157 TRACE [org.jboss.security.auth.spi.DatabaseServerLoginModule] login
2006-08-02 16:20:35,157 TRACE [org.jboss.security.auth.spi.DatabaseServerLoginModule] login
2006-08-02 16:20:35,157 TRACE [org.jboss.security.auth.spi.DatabaseServerLoginModule] abort
2006-08-02 16:20:35,157 TRACE [org.jboss.security.auth.spi.DatabaseServerLoginModule] abort
2006-08-02 16:20:39,105 TRACE [org.jboss.security.auth.login.XMLLoginConfigImpl] Begin getAppConfigurationEntry(jaas-webapp-domain), size=9
2006-08-02 16:20:39,105 TRACE [org.jboss.security.auth.login.XMLLoginConfigImpl] Begin getAppConfigurationEntry(jaas-webapp-domain), size=9
2006-08-02 16:20:39,105 TRACE [org.jboss.security.auth.login.XMLLoginConfigImpl] End getAppConfigurationEntry(jaas-webapp-domain), authInfo=AppConfigurationEntry[]:
[0]
LoginModule Class: org.jboss.security.auth.spi.DatabaseServerLoginModule
ControlFlag: LoginModuleControlFlag: required
Options:name=rolesQuery, value=SELECT LOGIN, ROLE_NAME, ROLE_GROUP FROM ROLES WHERE LOGIN=?
name=principalsQuery, value=SELECT LOGIN, PASSWORD FROM USERS WHERE LOGIN=?
name=unauthenticatedIdentity, value=pirata
name=dsJndiName, value=jdbc/jaasDS

2006-08-02 16:20:39,105 TRACE [org.jboss.security.auth.login.XMLLoginConfigImpl] End getAppConfigurationEntry(jaas-webapp-domain), authInfo=AppConfigurationEntry[]:
[0]
LoginModule Class: org.jboss.security.auth.spi.DatabaseServerLoginModule
ControlFlag: LoginModuleControlFlag: required
Options:name=rolesQuery, value=SELECT LOGIN, ROLE_NAME, ROLE_GROUP FROM ROLES WHERE LOGIN=?
name=principalsQuery, value=SELECT LOGIN, PASSWORD FROM USERS WHERE LOGIN=?
name=unauthenticatedIdentity, value=pirata
name=dsJndiName, value=jdbc/jaasDS

2006-08-02 16:20:39,105 TRACE [org.jboss.security.auth.spi.DatabaseServerLoginModule] initialize
2006-08-02 16:20:39,105 TRACE [org.jboss.security.auth.spi.DatabaseServerLoginModule] initialize
2006-08-02 16:20:39,105 TRACE [org.jboss.security.auth.spi.DatabaseServerLoginModule] Saw unauthenticatedIdentity=pirata
2006-08-02 16:20:39,105 TRACE [org.jboss.security.auth.spi.DatabaseServerLoginModule] Saw unauthenticatedIdentity=pirata
2006-08-02 16:20:39,105 TRACE [org.jboss.security.auth.spi.DatabaseServerLoginModule] DatabaseServerLoginModule, dsJndiName=jdbc/jaasDS
2006-08-02 16:20:39,105 TRACE [org.jboss.security.auth.spi.DatabaseServerLoginModule] DatabaseServerLoginModule, dsJndiName=jdbc/jaasDS
2006-08-02 16:20:39,105 TRACE [org.jboss.security.auth.spi.DatabaseServerLoginModule] principalsQuery=SELECT LOGIN, PASSWORD FROM USERS WHERE LOGIN=?
2006-08-02 16:20:39,105 TRACE [org.jboss.security.auth.spi.DatabaseServerLoginModule] principalsQuery=SELECT LOGIN, PASSWORD FROM USERS WHERE LOGIN=?
2006-08-02 16:20:39,105 TRACE [org.jboss.security.auth.spi.DatabaseServerLoginModule] rolesQuery=SELECT LOGIN, ROLE_NAME, ROLE_GROUP FROM ROLES WHERE LOGIN=?
2006-08-02 16:20:39,105 TRACE [org.jboss.security.auth.spi.DatabaseServerLoginModule] rolesQuery=SELECT LOGIN, ROLE_NAME, ROLE_GROUP FROM ROLES WHERE LOGIN=?
2006-08-02 16:20:39,105 TRACE [org.jboss.security.auth.spi.DatabaseServerLoginModule] login
2006-08-02 16:20:39,105 TRACE [org.jboss.security.auth.spi.DatabaseServerLoginModule] login
2006-08-02 16:20:39,105 TRACE [org.jboss.security.auth.spi.DatabaseServerLoginModule] abort
2006-08-02 16:20:39,105 TRACE [org.jboss.security.auth.spi.DatabaseServerLoginModule] abort

These are aditional information that I got from the moment that I change log4j.xml.

Maybe I should take the source to debug...

Excuse me for boring...

[bjornn]

hi,

I have tried to create a new file named sample-login-config.xml and put it into /conf dir.

I have created a service file named sample-login-config-service.xml mapping the new config file and put it into /depoy dir.

Nothing happened.... I don't know what to do...

Am I supposed to find any reference to java:/jaas in jmx-console? Because I didn't found any.... My Datasource is listed in jmx-console as it would be

My security domain is java:/jaas/jaas-webapp-domain and I think it would appear in jmx-console.

Please help me.

Thanks.

[zurchman]

I suspect that you do not have the datasource defined correcly: the examples would have you use java:/jaasDS.

And if there's no reference to jaasDS in jmx-console, you probably have not deployed it correctly.

Read over my earlier post for details.

[bjornn]

Hi zurchman,

My datasource has some problemas, like as mismatch in url connection... but I was guess that jboss would complain... I work hard to discover it.

But I can't solve the problem yet. Now, after just one attempt I get a 403 error. The server log print this lines below just after my login fail:

2006-08-03 16:55:54,983 DEBUG [org.jboss.security.plugins.JaasSecurityManagerService] Created securityMgr=org.jboss.security.plugins.JaasSecurityManager@182b9b5
2006-08-03 16:55:54,983 DEBUG [org.jboss.security.plugins.JaasSecurityManager.JaasSampleDBRealm] CachePolicy set to: org.jboss.util.TimedCachePolicy@16e46f5
2006-08-03 16:55:54,983 DEBUG [org.jboss.security.plugins.JaasSecurityManagerService] setCachePolicy, c=org.jboss.util.TimedCachePolicy@16e46f5
2006-08-03 16:55:54,983 DEBUG [org.jboss.security.plugins.JaasSecurityManagerService] Added JaasSampleDBRealm, org.jboss.security.plugins.SecurityDomainContext@f4a376 to map
2006-08-03 16:55:54,999 DEBUG [org.jboss.resource.adapter.jdbc.local.LocalManagedConnectionFactory] Using properties: {user=sa, password=--hidden--}
2006-08-03 16:55:54,999 DEBUG [org.jboss.resource.adapter.jdbc.local.LocalManagedConnectionFactory] Checking driver for URL: jdbc:hsqldb:hsql://localhost:9001/jaas
2006-08-03 16:55:54,999 DEBUG [org.jboss.resource.adapter.jdbc.local.LocalManagedConnectionFactory] Driver already registered for url: jdbc:hsqldb:hsql://localhost:9001/jaas
2006-08-03 16:55:55,014 DEBUG [org.jboss.resource.adapter.jdbc.local.LocalManagedConnectionFactory] Using properties: {user=sa, password=--hidden--}
2006-08-03 16:55:55,077 DEBUG [org.jboss.resource.adapter.jdbc.local.LocalManagedConnectionFactory] Using properties: {user=sa, password=--hidden--}
2006-08-03 16:55:55,077 DEBUG [org.jboss.resource.adapter.jdbc.local.LocalManagedConnectionFactory] Using properties: {user=sa, password=--hidden--}
2006-08-03 16:55:55,092 DEBUG [org.jboss.resource.adapter.jdbc.local.LocalManagedConnectionFactory] Using properties: {user=sa, password=--hidden--}

these lines are just after:

2006-08-03 16:53:16,587 INFO [org.jboss.system.server.Server] JBoss (MX MicroKernel) [4.0.2 (build: CVSTag=JBoss_4_0_2 date=200505022023)] Started in 34s:170ms

Why does jboss try to "connect" four times using LocalManagedConnectionFactory(last 4 lines above)? Do you think I still have some problem with my datasource?

Thanks again!

[j2ee_junkie]

bjornn,

You are making more work for yourself. I already told you that your query is not correct and pointed you to documentation on the LM. If the info you posted about your database is correct, and assuming your table names are USERS and ROLES, your queries should be as so...

...
<module-option name = "principalsQuery">SELECT PASSWORD FROM USERS WHERE LOGIN=?</module-option>
<module-option name = "rolesQuery">SELECT ROLE_NAME, 'Roles' FROM ROLES WHERE LOGIN=?</module-option>
...

The example for jms is just that, an example. You must customize the queries to match your database. Also, since you have gone down the road of changing your datasource, make sure your datasource points to your database.

cgriffith

[zurchman]

"bjornn" wrote:
Do you think I still have some problem with my datasource?

I think so, but it looks like you're trying to use Hypersonic for your user/password tables - which I've never done.

You might try this change and see what happens:

<module-option name = "dsJndiName">java:/DefaultDS</module-option>