How to Implement Tomcat JDBC Realm

How To Implement Tomcat JDBC Realm

Yue Ji

August 15, 2005

What Need To Do On Server?

Create SPECIAL_USER_ROLES table on LIBSYS.

Each user(Netid) can have multiple roles, and each role can have multiple user(Netid) also.

Go access this table.

Generate two views - tomcat_users_vw and tomcat_user_roles_vw on LIBSYS.

(1). SQL query of generating tomcat_users_vw:

select operator_id as user_name, 'DUMMY' as user_pass from operator

UNION

select user_name, 'DUMMY' as user_pass from special_user_roles

(2).SQL query of generating tomcat_user_roles_vw:

select acq_operator.operator_id as user_name, acq_profile.acq_profile_name as role_name

from acq_operator, acq_profile

where acq_operator.acq_profile_id = acq_profile.acq_profile_id

UNION

select operator.operator_id as user_name, 'acqstaff' as role_name

from acq_operator, acq_profile, operator

where ((upper(operator.last_name) not like '%HOURLY%')

and (upper(operator.first_name) not like '%STUDENT%'))

and acq_operator.acq_profile_id = acq_profile.acq_profile_id

and acq_operator.operator_id = operator.operator_id

UNION

select operator.operator_id as user_name, 'acqstudent' as role_name

from acq_operator, acq_profile, operator

where ((upper(operator.last_name) like '%HOURLY%')

or (upper(operator.first_name) like '%STUDENT%'))

and acq_operator.acq_profile_id = acq_profile.acq_profile_id

and acq_operator.operator_id = operator.operator_id

UNION

select cat_operator.operator_id as user_name, cat_profile.cat_profile_name as role_name

from cat_operator, cat_profile

where cat_operator.cat_profile_id = cat_profile.cat_profile_id

UNION

select operator.operator_id as user_name, 'catstaff' as role_name

from cat_operator, cat_profile, operator

where ((upper(operator.last_name) not like '%HOURLY%')

and (upper(operator.first_name) not like '%STUDENT%'))

and cat_operator.cat_profile_id = cat_profile.cat_profile_id

and cat_operator.operator_id = operator.operator_id

UNION

select operator.operator_id as user_name, 'catstudent' as role_name

from cat_operator, cat_profile, operator

where ((upper(operator.last_name) like '%HOURLY%')

or (upper(operator.first_name) like '%STUDENT%'))

and cat_operator.cat_profile_id = cat_profile.cat_profile_id

and cat_operator.operator_id = operator.operator_id

UNION

select circ_operator.operator_id as user_name, circ_profile.circ_profile_name as role_name

from circ_operator, circ_profile

where circ_operator.circ_profile_id = circ_profile.circ_profile_id

UNION

select operator.operator_id as user_name, 'circstaff' as role_name

from circ_operator, circ_profile, operator

where ((upper(operator.last_name) not like '%HOURLY%')

and (upper(operator.first_name) not like '%STUDENT%'))

and circ_operator.circ_profile_id = circ_profile.circ_profile_id

and circ_operator.operator_id = operator.operator_id

UNION

select operator.operator_id as user_name, 'circstudent' as role_name

from circ_operator, circ_profile, operator

where ((upper(operator.last_name) like '%HOURLY%')

or (upper(operator.first_name) like '%STUDENT%'))

and circ_operator.circ_profile_id = circ_profile.circ_profile_id

and circ_operator.operator_id = operator.operator_id

UNION

select user_name, role_name from libsys.special_user_roles

Note: Make updates in “sql_ tomcat_user_roles_vw” file.

There is no any manual changes in server.xml. JDBC realm will be implemented in each application’s context.xml. Tomcat will make link automatically.

What Need To Do On Application?

Remove VoyAuth filter.

Remove VoyAuth filter and related code from web.xml and JSPs

if you have voyauth filter in your applications.

Update web.xml to work for JDBC realm.

Here is web.xml of Currency Estimator(CurrencyJSP) as an example.

Don’t copy from here. Copy from web.xml.CurrencyJSP for better format.

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE web-app

PUBLIC "-//Sun Microsystems, Inc.//DTD Web Application 2.3//EN"

<web-app>

<filter-name>CASFilter</filter-name>

<filter-class>edu.yale.its.tp.cas.client.filter.CASFilter</filter-class>

<init-param>

<param-name>edu.yale.its.tp.cas.client.filter.loginUrl</param-name>

<param-value>

</init-param>

<init-param>

<param-name>edu.yale.its.tp.cas.client.filter.validateUrl</param-name>

<param-value>

</init-param>

<init-param>

<param-name>edu.yale.its.tp.cas.client.filter.authorizedProxy</param-name>

<param-value>

</init-param>

<init-param>

<param-name>edu.yale.its.tp.cas.client.filter.renew</param-name>

<param-value/>

</init-param>

<init-param>

<param-name>edu.yale.its.tp.cas.client.filter.serverName</param-name>

<param-value>localhost:8084</param-value>

</init-param>

</filter>

<filter-mapping>

<filter-name>CASFilter</filter-name>

<url-pattern>/*</url-pattern>

</filter-mapping>

<session-config>

<session-timeout>30</session-timeout>

</session-config>

<welcome-file-list>

<welcome-file>index.jsp</welcome-file>

<welcome-file>index.html</welcome-file>

<welcome-file>index.htm</welcome-file>

</welcome-file-list>

<error-page>

<error-code>400</error-code>

<location>/error/DisplayError.jsp</location>

</error-page>

<error-page>

<error-code>401</error-code>

<location>/error/DisplayError.jsp</location>

</error-page>

<error-page>

<error-code>403</error-code>

<location>/error/DisplayError403.jsp</location>

</error-page>

<error-page>

<error-code>404</error-code>

<location>/error/DisplayError.jsp</location>

</error-page>

<error-page>

<error-code>406</error-code>

<location>/error/DisplayError.jsp</location>

</error-page>

<error-page>

<error-code>408</error-code>

<location>/error/DisplayError.jsp</location>

</error-page>

<error-page>

<error-code>500</error-code>

<location>/error/DisplayError.jsp</location>

</error-page>

<error-page>

<error-code>501</error-code>

<location>/error/DisplayError.jsp</location>

</error-page>

<error-page>

<error-code>502</error-code>

<location>/error/DisplayError.jsp</location>

</error-page>

<error-page>

<error-code>503</error-code>

<location>/error/DisplayError.jsp</location>

</error-page>

<error-page>

<error-code>504</error-code>

<location>/error/DisplayError.jsp</location>

</error-page>

<error-page>

<error-code>505</error-code>

<location>/error/DisplayError.jsp</location>

</error-page>

<error-page>

<exception-type>java.lang.Exception</exception-type>

<location>/error/DisplayError.jsp</location>

</error-page>

<error-page>

<exception-type>javax.servlet.ServletException</exception-type>

<location>/error/DisplayError.jsp</location>

</error-page>

<security-constraint>

<web-resource-collection>

<web-resource-name>Authorize Currency Estimator main page</web-resource-name>

<url-pattern>/src/CurrencyWebList.jsp</url-pattern>

</web-resource-collection>

<auth-constraint>

<role-name>acqstaff</role-name>

</auth-constraint>

</security-constraint>

<security-constraint>

<web-resource-collection>

<web-resource-name>Authorize Currency Estimator main page </web-resource-name>

<url-pattern>/src/CurrencyWebList.jsp</url-pattern>

</web-resource-collection>

<auth-constraint>

<role-name>catstaff</role-name>

</auth-constraint>

</security-constraint>

<security-constraint>

<web-resource-collection>

<web-resource-name>Authorize Currency Estimator main page </web-resource-name>

<url-pattern>/src/CurrencyWebList.jsp</url-pattern>

</web-resource-collection>

<auth-constraint>

<role-name>circstaff</role-name>

</auth-constraint>

</security-constraint>

<login-config>

<auth-method>FORM</auth-method>

<form-login-config>

<form-login-page>/LoginForm.jsp</form-login-page>

<form-error-page>/LoginError.html</form-error-page>

</form-login-config>

</login-config>

<security-role>

<description>Acquisition staff</description>

<role-name>acqstaff</role-name>

</security-role>

<security-role>

<description>Catalog staff</description>

<role-name>catstaff</role-name>

</security-role>

<security-role>

<description>Circulation staff</description>

<role-name>circstaff</role-name>

</security-role>

</web-app>

Update context.xml to work for JDBC realm.

Here is context.xml of Currency Estimator(CurrencyJSP) as an example.

Don’t copy from here. Copy from context.xml.CurrencyJSP for better format.

<?xml version="1.0" encoding="UTF-8"?>

<Logger className="org.apache.catalina.logger.FileLogger" prefix="CurrencyJSP." suffix=".log"

timestamp="true"/>

<!-- Maximum number of DB connections in pool. Make sure you

configure your oracle sql max_connections large enough to handle

all of your db connections. Set to 0 for no limit. -->

<name>maxActive</name>

</parameter>

<name>maxIdle</name>

</parameter>

<!-- Maximum time to wait for a DB connection to become available,

here setting up as 10 seconds. An Exception is thrown if this

timeout is exceeded. Set to -1 to wait indefinitely. -->

<name>maxWait</name>

</parameter>

<name>username</name>

<value>lsoprog</value>

</parameter>

<name>password</name>

<value>xxxxxxxx</value>

</parameter>

<name>driverClassName</name>

<value>oracle.jdbc.driver.OracleDriver</value>

</parameter>

<!-- use parameter name "driverName" for Tomcat 4.06 and below

<parameter<name>driverName</name> -->

<value>jdbc:oracle:thin:@magellan.library.yale.edu:1521:LIBR</value>

</parameter>

</ResourceParams>

<Realm className="org.apache.catalina.realm.JDBCRealm"

connectionName="lsoprog"

connectionPassword="xxxxxxxx"

connectionURL="jdbc:oracle:thin:@magellan.library.yale.edu:1521:LIBR"

debug="99"

driverName="oracle.jdbc.driver.OracleDriver"

roleNameCol="role_name"

userCredCol="user_pass"

userNameCol="user_name"

userRoleTable="tomcat_user_roles_vw"

userTable="tomcat_users_vw"/>

</Context>

Copy LoginForm.jsp to the application web root.

Here is the source code of LoginForm.jsp.

Don’t copy from here. Copy from LoginForm.jsp.txt for better format.

* Author: Yue Ji

* Created on June 7, 2005, 4:39 PM

* LoginForm.jsp -->

<%@ page contentType="text/html"%>

<%@ page pageEncoding="UTF-8"%>

<%@ page import="javax.servlet.jsp.jstl.sql.Result" %>

<%@ taglib prefix="sql" uri=" %>

<html>

<head<title>Tomcat Realm Login Page</title</head>

<body>

<% String CAS_FILTER_USER = "edu.yale.its.tp.cas.client.filter.user";

String userName = session.getAttribute(CAS_FILTER_USER).toString(); %>

<sql:query var="getRecordQuery" dataSource="jdbc/ReportsDB">

select FIRST_NAME, LAST_NAME from operator

where operator_id = '<% out.print(userName); %>'

</sql:query>

<% Result rs = (Result)pageContext.findAttribute("getRecordQuery");

if (rs.getRowCount() == 0) {

request.getSession().setAttribute("OpFirstName", userName);

request.getSession().setAttribute("OpLastName", "Special User");

request.getSession().setAttribute("netid", userName);

}

else {

request.getSession().setAttribute("OpFirstName", rs.getRows()[0].get("FIRST_NAME"));

request.getSession().setAttribute("OpLastName", rs.getRows()[0].get("LAST_NAME"));

request.getSession().setAttribute("netid", userName);

} %>

Username: <input type="text" name="j_username">

Password: <input type="password" name="j_password">

</form>

</div>

<!-- Hide from older browsers

document.myform.j_username.value='<% out.print(userName); %>';

document.myform.j_password.value="DUMMY";

document.myform.submit();

// end hiding contents -->

</script>

</body>

</html>

Copy LoginError.html to the application web root.

Here is the source code of LoginError.html.

Don’t copy from here. Copy from LoginError.html.txt for better format.

* Author: Yue Ji

* Created on June 7, 2005, 4:40 PM

* LoginError.html -->

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">

<html>

<head>

<title>Login Error - neither user_name nor role_name exists in tomcat realm's two views.</title>

</head>

<body background="../image/background.gif" vlink="#8080ff" alink="#8080ff" link="#8080ff"

topmargin=0 leftmargin=0 marginwidth=0 marginheight=0>

<font color="#0080ff" size=5 face="Arial, Helvetica, sans-serif"<b>Apologies -- you currently don't have access to this application.</b<p>

<font size=3>Please contact your supervisor <br>

and ask that he/she contact the Integrated Systems and Programming group at<br>

<a class="Footer" href="../error/WriteEmailForm.jsp" target=new>Integrated Systems &

Programming</a</font</FONT>

</body>

</html>

Note: This page will be executed if there is no authorized data in both two tomcat J DBC views – tomcat_users_vw, and tomcat_user_roles_vw.

Copy DisplayError403.jsp the application /error directory.

Here is the source code of DisplayError403.jsp.

Don’t copy from here. Copy from DisplayError403.jsp.txt for better format.

* @Author Yue Ji

* Created on May 3, 2005 3:17 PM

* DisplayError403.jsp --%>

<%@ page isErrorPage="true" %>

<%@ page import="java.util.*,java.io.*" %>

<html>

<head>

<title>Error - user_name exists in tomcat_users_vw, but role_name does not exist in

tomcat_user_roles_vw.</title>

</head>

<body background="../image/background.gif" vlink="#8080ff" alink="#8080ff" link="#8080ff"

topmargin=0 leftmargin=0 marginwidth=0 marginheight=0>

<% String statusCodeString = "";

Object status_code = request.getAttribute("javax.servlet.error.status_code");

statusCodeString = (String) status_code.toString();

statusCodeString = statusCodeString.substring(0,3);

if (statusCodeString.equals("403")) { %>

<b>Apologies -- you currently don't have a role with access to this application.</b<p>

<font size=3>Please contact your supervisor <br>

and ask that he/she contact the Integrated Systems and Programming group at<br>

<a class="Footer" href="../error/WriteEmailForm.jsp" target=new>Integrated Systems &

Programming</a</font</FONT>

<% } %>

</center>

</body>

</html>

Note: This page will be executed if there is only the user(Netid) in tomcat_users_vw, but no role for this user(Netid) in tomcat_user_roles_vw.

Useful lib jar files.

Go to CVS TomcatRealmDoc/lib to check out lib jar files for your CAS(casclient.jar)

and Oracle(classes12.jar) operations.

Check out CurrencyJSP from CVS as tomcat JDBC realm example.

Features of realm constraint jsp.

(1). If the jsp page is not in the <security-constraint> of web.xml, this page will

not be checked by realm. It will be just checked by CAS.

(2). If the jsp page is in the <security-constraint> of web.xml, this page will be

checked by realm first through LoginForm.jsp. If it passes the login page, then it will be continually checked by CAS. However, for Yale University Library, we want to authenticate everyone through CAS first, so LoginForm.jsp is designed to assume CAS already happened and carried its netid. If the jsp page is in the <security-constraint>, LoginForm.jsp/realm will run first, it will have null pointer error by not having netid from CAS. The solution for this problem is: don’t use constraint jsp page(s) as the first execute page, instead, always using another non-constraint page to be executed first. In this way netid from CAS can pass over to constraint page, then realm login page can have netid to be checked in tomcat realm views.

(3). If constraint jsp page includes process of generating Microsoft Excel Spread

Sheet, FireFox will not be able to download this excel sheet. The solution is: move out the generating excel sheet process to the other jsp. For example, constraint jsp executes SQL query to get result set, then passes the result set using session to another jsp that generates excel sheet.

Don't forget first to check that the individual reporting the error (to their supervisor?) actually *should* have access to the designated application or role. It is also possible for timing to play a role if the user has just recently changed status and the business office has not modified the profile tables. (From Jeff email)

In order to track web application's login error easier and more clearly, here are possible error types and solutions.

(1). Error message:Apologies -- you currently don't have access to this

application.

This error message is from LoginError.html.

Three possible reasons for this error:

No database connection to tomcat views.

Solution: Go to context.xml, check:

- <ResourceParams name=>... part, if the datasource is correct.

- <Realm className=>... part, if the datasource is correct.

- If the datasource is correct in context.xml, login tomcat GUI with admin to

check if datasource is correct there.

Security role doesn't map correctly.

Solution: Go to web.xml, check:

- <security-constraint>... part, if the role name is correct and if the jsp

page is what you want to constraint.

- <security-role>... part, if the role name is correct.

No login information in both tomcat JDBC realm two views, tomcat_users_vw and tomcat_user_roles_vw.

This is caused by: this netid doesn't exist in Voyager operator table and doesn't exist in LIBSYS special_user_roles table.

Solution: Add this netid in either Voyager operator table or LIBSYS special_user_roles table.

(2). Error message: Apologies -- you currently don't have a role with access to this

application.

This error message is from DisplayError403.jsp.

Two possible reasons for this error:

This netid does exist in Voyager operator table and/or LIBSYS special_user_roles table, but it doesn't exist in Voyager one/some profile table(s).
This netid does exist in Voyager operator table, but it doesn't exist in LIBSYS special_user_roles table.

Solution: Add this netid into the table(s) that are related with your application's role(s).