Q:

Is there a bug in org.apache.catalina.realm.JDBCRealm?

http://www.docjar.com/html/api/org/apache/catalina/realm/JDBCRealm.java.html
To use this JDBCRealm we need two tables, user and user_role. 
user table - user_id, user_name, user_password
user_role table - user_id, role_id, user_name

Why user_name is required in user_role table when user_id is there as foreign key. The JDBCRealm could have used a join query to extract roles or a direct query as well if it stores the user_id from the previous query.

A:

Here's Tomcat 6 documentation for JDBCRealm.    
user_id is NOT required in either table, nor is role_id. users table should have user_name and user_password columns; roles table should have user_name and role_name columns. Tables are linked via user_name.    
The reason it's done this way (string rather then numeric ids) is because Principal had name as a String and isUserInRole() call takes role name as String as well.

