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

Question

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.

Answer 1

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.