Tomcat: autenticazione base con MySql

Autenticazione centralizzata in Tomcat utilizzando MySql

---

Librerie Apache aggiuntive per far funzionare l'utenticazione:

commons-dbcp-1.4.jar
commons-pool-1.6.jar
mysql-connector-java-5.1.9.jar

da installare in jre/lib/ext agganciata a Tomcat

Sul file server.xml:

<GlobalNamingResources>....
	<Resource name="jdbc/utenti"	        auth="Container"
	        type="javax.sql.DataSource"
	        factory="org.apache.commons.dbcp.BasicDataSourceFactory"
	        maxWait="30000"
	        maxActive="15"
	        maxIdle="2"
	        username="root"
	        password="1latte81"
	        validationQuery="SELECT 1"
	        timeBetweenEvictionRunsMillis="60000"
	        testWhileIdle="true"
	        driverClassName="com.mysql.jdbc.Driver"
        	url="jdbc:mysql://127.0.0.1:3306/sso"/>
....
</GlobalNamingResources ><Engine name="Catalina" defaultHost="localhost">....
	<Realm className="org.apache.catalina.realm.DataSourceRealm"                dataSourceName="jdbc/utenti"
                userTable="user" userNameCol="username" userCredCol="password"
                userRoleTable="user_roles" roleNameCol="usergroup"/>
....
</Engine>

Ricordatevi di commentare la voce:

<Realm className="org.apache.catalina.realm.UserDatabaseRealm" resourceName="UserDatabase"/>

Creare un database su Mysql:

CREATE DATABASE 'sso';
USE DATABASE 'sso';

CREATE TABLE `user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `ver` timestamp NULL DEFAULT NULL,
  `username` varchar(20) NOT NULL,
  `password` varchar(250) NOT NULL,
  `description` varchar(250) DEFAULT NULL,
  UNIQUE KEY `id` (`id`),
  UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;


CREATE TABLE `usergroup` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `ver` timestamp NULL DEFAULT NULL,
  `groupname` varchar(50) DEFAULT NULL,
  `description` varchar(250) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`),
  UNIQUE KEY `group_name` (`groupname`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;


CREATE TABLE `user_roles` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `ver` timestamp NULL DEFAULT NULL,
  `username` varchar(20) NOT NULL,
  `usergroup` varchar(50) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;


INSERT INTO `user` (`id`, `ver`, `username`, `password`, `description`) VALUES
(1, '2012-11-24 13:28:17', 'admin1', 'admin1', NULL),
(2, '2012-11-24 12:29:01', 'user1', 'user1', NULL),
(3, '2012-11-24 13:35:03', 'admin', 'admin', NULL);


INSERT INTO `usergroup` (`id`, `ver`, `groupname`, `description`) VALUES
(1, '2012-11-24 12:30:00', 'Application1_administrator', NULL),
(2, '2012-11-24 12:30:00', 'Application2_administrator', NULL);


INSERT INTO `user_roles` (`id`, `ver`, `username`, `usergroup`) VALUES
(1, '2012-11-24 13:28:34', 'admin1', 'Application1'),
(2, '2012-11-24 13:18:07', 'user1', 'Application1'),
(3, '2012-11-24 13:18:07', 'user1', 'Application2'),
(4, '2012-11-24 13:35:15', 'admin', 'manager');    

Nella propria Web Application modificare il file web.xml:

<security-constraint>    <web-resource-collection>        <web-resource-name>Application1sso</web-resource-name>        <url-pattern>/*</url-pattern>    </web-resource-collection>    <auth-constraint>        <role-name>Application1</role-name>    </auth-constraint></security-constraint><login-config>    <auth-method>BASIC</auth-method>    <realm-name>Application1sso</realm-name></login-config><security-role>    <description>Gruppo Utenti abilitati</description>    <role-name>Application1</role-name></security-role>