What Need To Do On Server?

infestationwatchΛογισμικό & κατασκευή λογ/κού

28 Οκτ 2013 (πριν από 3 χρόνια και 5 μήνες)

64 εμφανίσεις


1

How To Implement Tomcat JDBC Realm

Yue Ji

August 15, 2005




What Need To Do On Server?


1.

Create SPECIAL_USER_ROLES table on LIBSYS.


Each user(Netid) can have multi ple roles, and each role can have multi pl e
user(Netid) also.

Go

http://magellan.library.yale.edu:8085/SpecialUserRoles

to access this table.




2.

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 generati ng 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(o
perator.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 c
at_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, 'c
atstaff' as role_name


from cat_operator, cat_profile, operator


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


2


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


and cat_operator.cat_profile_id = cat_profile.c
at_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.operat
or_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_p
rofile_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, rol
e_name from libsys.special_user_roles



Note: Make updates in “sql_

tomcat_user_rol es_vw” file.




3.

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.


















3



What Need To Do On Application?


1.

Remove VoyAuth filter
.


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

if you have voyauth filter in your applicati ons.


2.

Update web.xml to work for JDBC realm.


Here is web.xml of Currency Estimator(Cu
rrencyJSP) as an exampl e.

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"


"http://java.sun.com/dtd/web
-
app_2_3.dtd">

<web
-
app>


<filter>

<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>https://secure.its.yale.edu/cas/login</param
-
value>


<description/>


</init
-
param>


<init
-
param>


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


<param
-
value>https://secure.its.yale.edu/cas/s
erviceValidate</param
-
value>


<description/>


</init
-
param>


<init
-
param>


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


<param
-
value>https://secure.its.yale.edu/cas/proxyValidate</param
-
value>


<description/>


</init
-
param>


<init
-
param>


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


<param
-
value/>




<description/>


</init
-
param>


<init
-
param>


<!
--

*** Param below should allow specifying

server only, and CASFilter.getService would fill in URI
--
>


<!
--

*** This would be for web
-
apps that shouldn't point the filter to a particular resource to return to
--
>


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


<!
--
param
-
value>magellan.library.yale.edu:8085</param
-
value
--
>


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


<description/>


</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>


4


<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</loca
tion>


</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.js
p</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/Display
Error.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>


<e
rror
-
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>


5


</web
-
resource
-
collection>


<auth
-
constraint>


<role
-
name>acqsta
ff</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>

<fo
rm
-
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</descr
iption>


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


</security
-
role>

</web
-
app>


3.

Update context.xml to work for JDBC realm.


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

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


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

<Context path="/CurrencyJSP">

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


timestamp="true"/>


<Resou
rce auth="Container" name="jdbc/ReportsDB" type="javax.sql.DataSource"/>


<ResourceParams name="jdbc/ReportsDB">


<!
--

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.
--
>


<parameter>


<name>maxActive</name>


6


<value>100</value>


</parameter>


<!
--

Maximum number of idle DB connections to retain in pool. Set to 0 for no limit.
--
>


<parameter>


<name>maxIdle</name>


<value>30</value>


</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.
--
>


<parameter>


<name>maxWait</name>


<value>10000</value>


</parameter>


<!
--

Oracle DB username and password for DB connections
--
>


<parameter>


<name>username</name>



<value>lsoprog</value>


</parameter>


<parameter>


<name>password</name>


<value>xxxxxxxx</value>


</parameter>


<!
--

Class name for Oracle JDBC driver
--
>


<parameter>


<name>driverClassName</name>


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


</parameter>


<!
--

The JDBC connection url for connecting to Reports DB
--
>


<!
--

use parameter name "driverName" for Tomcat 4.06 and below


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


<!
--

use parameter name "url"
for Tomcat 4.1 and above
--
>


<parameter>


<name>url</name>





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


</parameter>


</ResourceParams>




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


connec
tionName="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_na
me"


userRoleTable="tomcat_user_roles_vw"


userTable="tomcat_users_vw"/>



</Context>


4.

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.


<!
--

* Copyright 2005 Yale University


* Author: Yue Ji


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


* LoginForm.jsp
--
>

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


7

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

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

<%@
taglib prefix="sql" uri="http://java.sun.com/jstl/sql" %>

<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).to
String(); %>


<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("getRecord
Query");

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


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


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


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

}

else {


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


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


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

} %>

<div style="visibility:hidden;">



<form name="myform" method="POST" action="j_security_check">

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


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


</form>

</div>

<script language="Javascript">


<!
--

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>


5.

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.


<!
--

* Copyright 2005 Yale University


* 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>


8

<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.




6.

Copy DisplayError403.jsp the application /error directory.


Here is the source code of DisplayError403.jsp.

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


<%
--

* Copyright 2005 Yale University




* @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>


<meta http
-
equiv="Content
-
Type" content="text/h
tml; charset=iso
-
8859
-
1">

</head>

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


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

<center>

<% String statusCodeString = "";

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

statusCodeString = (String) status_code.toString();

statusCodeString = statusCodeString.substring(0,3);

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


<font color="#0080ff" size=5 face="Arial, Helve
tica, sans
-
serif">


<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 th
e 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 th
ere is only the user(Netid) in tomcat_users_vw,
but no role for this user(Netid) in tomcat_user_roles_vw.



7.

Useful lib jar files.


Go to CVS TomcatReal mDoc/lib to check out lib jar files for your CAS(casclient.j ar)

and Oracle(classes12.j ar) operati ons.


Check out CurrencyJSP from CVS as tomcat JDBC realm example.




9

8.

Features of realm constraint jsp.


(1). If the jsp page is not in the <security
-
constrai nt> 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 continuall y checked by CAS. However, for Yale University Library, we
want to authenti cate eve
ryone 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
-
constrai nt>, LoginForm.jsp/real m will run first, it will have null pointer
error by not having netid from CAS. T
he solution for this problem is: don’t use
constraint jsp page(s) as the first execute page, instead, always using another
non
-
constrai nt 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 th
e other jsp. For exampl e,
constraint jsp executes SQL query to get result set, then passes the result set
using session to another jsp that generates excel sheet.



9.

Login error message explanation.


Don't forget first to check that the individual reporting

the error (to their supervisor?)
actually *should* have access to the designated applicati on 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 modi fied the profile tables.
(From Jeff email)


In order to track web applicati on'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 messag
e is from LoginError.html.



Three possible reasons for this error:


o

No database connecti on to tomcat views.

Solution: Go to context.xml, check:

-

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



-

<Real m className=>... part, if t
he datasource is correct.


-

If the datasource is correct in context.xml, login tomcat GUI with admi n to


check if datasource is correct there.


o

Security role doesn't map correctly.

Solution: Go to web.xml, check:


10


-

<security
-
constrai
nt>... 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.


o

No login informati on in both tomcat JDBC realm two views, tomcat_users_vw
and tomcat_us
er_rol es_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_rol es table.



(2). Error message: Apo
logies
--

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:


o

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

o

This netid does exist in Voyager operator table, but it doesn't exist in LIBSYS
special_user_rol es table.


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