/*
* Created on Jun 29, 2005
*
* Copyright 2005 CafeSip.org
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*
*/
package org.cafesip.jiplet.realms;
import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import javax.sip.header.AuthorizationHeader;
import org.cafesip.jiplet.JipletException;
import org.cafesip.jiplet.JipletLogger;
import org.cafesip.jiplet.Pair;
import org.cafesip.jiplet.Realm;
/**
* @author Amit Chatterjee
*
*/
public class JdbcRealm extends BaseRealm implements Realm
{
private String driver;
private String url;
private String user;
private String password;
private Connection connection;
/**
* A constructor for this class.
*
*
*/
public JdbcRealm()
{
}
/*
* @see org.cafesip.jiplet.Realm#init(java.lang.String, java.io.File,
* java.util.HashMap, boolean)
*/
public void init(String name, File rootDir, HashMap params, boolean defaultRealm)
throws JipletException
{
super.init(name, rootDir, params, defaultRealm);
driver = (String) params.get("jdbc-driver");
if (driver == null)
{
throw new JipletException(
"No JDBC driver specified as realm parameter");
}
url = (String) params.get("jdbc-url");
if (driver == null)
{
throw new JipletException(
"No JDBC URL specified as realm parameter");
}
String param = (String) params.get("jdbc-user");
if (param != null)
{
user = param;
}
param = (String) params.get("jdbc-password");
if (param != null)
{
password = param;
}
try
{
Class.forName(driver).newInstance();
connection = DriverManager.getConnection(url, user, password);
createTables();
}
catch (Exception e)
{
throw new JipletException(e);
}
}
private void createTables() throws SQLException
{
JipletLogger.info("Creating tables for JDBC realm " + getRealmName());
String sql = "create table if not exists users ("
+ " user_name varchar(15) not null primary key,"
+ " user_pass varchar(15) not null" + ")";
Statement st = connection.createStatement();
st.executeUpdate(sql);
st.close();
sql = "create table if not exists user_roles ("
+ " user_name varchar(15) not null,"
+ " role_name varchar(15) not null,"
+ " primary key (user_name, role_name)" + ")";
st = connection.createStatement();
st.executeUpdate(sql);
st.close();
}
/*
* @see org.cafesip.jiplet.Realm#destroy()
*/
public void destroy()
{
try
{
connection.close();
}
catch (SQLException e)
{
; // to bad!
}
}
private void freshenConnection()
throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException
{
try
{
Statement st = connection.createStatement();
st.executeUpdate("select 1");
st.close();
}
catch (SQLException e)
{
// if we come here, it is most liely because the connection is closed because of inactivity
Class.forName(driver).newInstance();
connection = DriverManager.getConnection(url, user, password);
}
}
/*
* @see org.cafesip.jiplet.Realm#authenticate(java.lang.String,
* javax.sip.header.AuthorizationHeader)
*/
public synchronized String[] authenticate(String method, AuthorizationHeader header)
{
try
{
if (header.getOpaque().equals(getOpaque()) == false)
{
if (JipletLogger.isDebugEnabled() == true)
{
JipletLogger.debug("Received a SIP request with invalid OPAQUE value");
}
return null;
}
String name = header.getUsername();
freshenConnection();
String sql = "select users.user_pass, user_roles.role_name" +
" from users, user_roles" +
" where users.user_name = user_roles.user_name" +
" and users.user_name = ? ";
PreparedStatement ps = connection.prepareStatement(sql);
ps.setString(1, name);
ResultSet rs = ps.executeQuery();
boolean found = false;
String password = null;
ArrayList roles = new ArrayList();
while (rs.next() == true)
{
found = true;
password = rs.getString(1);
roles.add(rs.getString(2));
}
if (found == false)
{
if (JipletLogger.isDebugEnabled() == true)
{
JipletLogger.debug("Received an authentication request from an unknown user : " + name);
}
return null;
}
// the user is found, verify if the response matches
String c_response = MessageDigestAlgorithm.calculateResponse(header.getAlgorithm(),
name, getRealmName(), password,
header.getNonce(), header.getCNonce(),
header.getCNonce(), method, header.getURI().toString(),
null, header.getQop());
if (c_response.equals(header.getResponse()) == false)
{
if (JipletLogger.isDebugEnabled() == true)
{
JipletLogger.debug("Authentication failure for user " + name);
}
return null;
}
if (JipletLogger.isDebugEnabled() == true)
{
JipletLogger.debug("User " + name + " has been authenticated. ");
}
rs.close();
ps.close();
String[] r = new String[roles.size()];
roles.toArray(r);
return r;
}
catch (Exception e)
{
JipletLogger.fatal("Exception occured while authenticating user. Exception: "
+ e.getClass().getName() + ": " + e.getMessage() + "\n"
+ JipletLogger.getStackTrace(e));
return null;
}
}
/*
* @see org.cafesip.jiplet.Realm#addUser(java.lang.String, java.lang.String,
* java.lang.String[])
*/
public synchronized boolean addUser(String user, String password, String[] roles)
throws UnsupportedOperationException
{
try
{
freshenConnection();
connection.setAutoCommit(false);
String sql = "insert into users (user_name, user_pass) values (?, ?)";
PreparedStatement ps = connection.prepareStatement(sql);
ps.setString(1, user);
ps.setString(2, password);
int count = ps.executeUpdate();
if (count == 0)
{
connection.rollback();
return false;
}
ps.close();
for (int i = 0; i < roles.length; i++)
{
sql = "insert into user_roles (user_name, role_name) values(?,?)";
ps = connection.prepareStatement(sql);
ps.setString(1, user);
ps.setString(2, roles[i]);
count = ps.executeUpdate();
if (count == 0)
{
connection.rollback();
return false;
}
ps.close();
}
connection.commit();
return true;
}
catch (Exception e)
{
JipletLogger.error("An error occured while provisioning user " + user
+ "\n" + e.getMessage() + "\n" + JipletLogger.getStackTrace(e));
try
{
connection.rollback();
}
catch (SQLException e1)
{
JipletLogger.error("An error occured while rolling back for the JDBC realm");
}
return false;
}
finally
{
try
{
connection.setAutoCommit(true);
}
catch (SQLException e)
{
JipletLogger.error("An error occured while turning off auto-commit for the JDBC realm");
}
}
}
/*
* @see org.cafesip.jiplet.Realm#modifyUser(java.lang.String,
* java.lang.String, java.lang.String[])
*/
public synchronized boolean modifyUser(String user, String password, String[] roles)
throws UnsupportedOperationException
{
try
{
freshenConnection();
connection.setAutoCommit(false);
if ((password != null) && (password.length() > 0))
{
String sql = "update users set user_pass = ? where user_name = ?";
PreparedStatement ps = connection.prepareStatement(sql);
ps.setString(1, password);
ps.setString(2, user);
int count = ps.executeUpdate();
if (count == 0)
{
connection.rollback();
return false;
}
ps.close();
}
if (roles != null)
{
// first delete old roles
String sql = "delete from user_roles where user_name = ?";
PreparedStatement ps = connection.prepareStatement(sql);
ps.setString(1, user);
int count = ps.executeUpdate();
if (count == 0)
{
connection.rollback();
return false;
}
ps.close();
for (int i = 0; i < roles.length; i++)
{
sql = "insert into user_roles (user_name, role_name) values(?,?)";
ps = connection.prepareStatement(sql);
ps.setString(1, user);
ps.setString(2, roles[i]);
count = ps.executeUpdate();
if (count == 0)
{
connection.rollback();
return false;
}
ps.close();
}
}
connection.commit();
return true;
}
catch (Exception e)
{
JipletLogger.error("An error occured while provisioning user " + user
+ "\n" + e.getMessage() + "\n" + JipletLogger.getStackTrace(e));
try
{
connection.rollback();
}
catch (SQLException e1)
{
JipletLogger.error("An error occured while rolling back for the JDBC realm");
}
return false;
}
finally
{
try
{
connection.setAutoCommit(true);
}
catch (SQLException e)
{
JipletLogger.error("An error occured while turning off auto-commit for the JDBC realm");
}
}
}
/*
* @see org.cafesip.jiplet.Realm#deleteUser(java.lang.String)
*/
public synchronized boolean deleteUser(String user) throws UnsupportedOperationException
{
try
{
freshenConnection();
connection.setAutoCommit(false);
String sql = "delete from users where user_name = ?";
PreparedStatement ps = connection.prepareStatement(sql);
ps.setString(1, user);
int count = ps.executeUpdate();
if (count == 0)
{
connection.rollback();
return false;
}
ps.close();
sql = "delete from user_roles where user_name = ?";
ps = connection.prepareStatement(sql);
ps.setString(1, user);
count = ps.executeUpdate();
if (count == 0)
{
connection.rollback();
return false;
}
ps.close();
connection.commit();
return true;
}
catch (Exception e)
{
JipletLogger.error("An error occured while provisioning user " + user
+ "\n" + e.getMessage() + "\n" + JipletLogger.getStackTrace(e));
try
{
connection.rollback();
}
catch (SQLException e1)
{
JipletLogger.error("An error occured while rolling back for the JDBC realm");
}
return false;
}
finally
{
try
{
connection.setAutoCommit(true);
}
catch (SQLException e)
{
JipletLogger.error("An error occured while turning off auto-commit for the JDBC realm");
}
}
}
/*
* @see org.cafesip.jiplet.Realm#getRoles(java.lang.String)
*/
public synchronized String[] getRoles(String user)
{
try
{
String sql = "select role_name from user_roles where user_name = ?";
PreparedStatement ps = connection.prepareStatement(sql);
ps.setString(1, user);
ResultSet rs = ps.executeQuery();
ArrayList list = new ArrayList();
while (rs.next() == true)
{
list.add(rs.getString(1));
}
rs.close();
if (list.size() == 0)
{
return null;
}
String[] roles = new String[list.size()];
list.toArray(roles);
return roles;
}
catch (SQLException e)
{
JipletLogger.error("An error occured while getting user information " + user
+ "\n" + e.getMessage() + "\n" + JipletLogger.getStackTrace(e));
return null;
}
}
/*
* @see org.cafesip.jiplet.Realm#supportsProvisioning()
*/
public boolean supportsProvisioning()
{
return true;
}
/*
* @see org.cafesip.jiplet.realms.BaseRealm#getUserInformation(java.lang.String)
*/
public Pair getUserInformation(String user) throws Exception
{
freshenConnection();
String sql = "select users.user_pass, user_roles.role_name" +
" from users, user_roles" +
" where users.user_name = user_roles.user_name" +
" and users.user_name = ? ";
PreparedStatement ps = connection.prepareStatement(sql);
ps.setString(1, user);
ResultSet rs = ps.executeQuery();
boolean found = false;
String password = null;
ArrayList roles = new ArrayList();
while (rs.next() == true)
{
found = true;
password = rs.getString(1);
roles.add(rs.getString(2));
}
if (found == false)
{
return null;
}
String[] r = new String[roles.size()];
roles.toArray(r);
return new Pair(password, roles);
}
}