From a6e2e94345ae3d153f945dd79f3221630015d826 Mon Sep 17 00:00:00 2001 From: Melanie Date: Sun, 6 Sep 2009 23:05:06 +0100 Subject: Thank you, StrawberryFride, for a hit-and-run patch to add authentication and user services to MSSQL --- OpenSim/Data/MSSQL/MSSQLAuthenticationData.cs | 222 +++++++++++++++++++++++++ OpenSim/Data/MSSQL/MSSQLUserAccountData.cs | 186 +++++++++++++++++++++ OpenSim/Data/MSSQL/Resources/011_UserStore.sql | 5 + 3 files changed, 413 insertions(+) create mode 100644 OpenSim/Data/MSSQL/MSSQLAuthenticationData.cs create mode 100644 OpenSim/Data/MSSQL/MSSQLUserAccountData.cs create mode 100644 OpenSim/Data/MSSQL/Resources/011_UserStore.sql diff --git a/OpenSim/Data/MSSQL/MSSQLAuthenticationData.cs b/OpenSim/Data/MSSQL/MSSQLAuthenticationData.cs new file mode 100644 index 0000000..78fc22c --- /dev/null +++ b/OpenSim/Data/MSSQL/MSSQLAuthenticationData.cs @@ -0,0 +1,222 @@ +/* + * Copyright (c) Contributors, http://opensimulator.org/ + * See CONTRIBUTORS.TXT for a full list of copyright holders. + * + * Redistribution and use in source and binary forms, with or without + * modification, are permitted provided that the following conditions are met: + * * Redistributions of source code must retain the above copyright + * notice, this list of conditions and the following disclaimer. + * * Redistributions in binary form must reproduce the above copyright + * notice, this list of conditions and the following disclaimer in the + * documentation and/or other materials provided with the distribution. + * * Neither the name of the OpenSimulator Project nor the + * names of its contributors may be used to endorse or promote products + * derived from this software without specific prior written permission. + * + * THIS SOFTWARE IS PROVIDED BY THE DEVELOPERS ''AS IS'' AND ANY + * EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED + * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE + * DISCLAIMED. IN NO EVENT SHALL THE CONTRIBUTORS BE LIABLE FOR ANY + * DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES + * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; + * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND + * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT + * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS + * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. + */ + +using System; +using System.Collections; +using System.Collections.Generic; +using System.Data; +using OpenMetaverse; +using OpenSim.Framework; +using System.Data.SqlClient; +using System.Reflection; +using System.Text; + +namespace OpenSim.Data.MSSQL +{ + public class MSSQLAuthenticationData : IAuthenticationData + { + private string m_Realm; + private List m_ColumnNames = null; + private int m_LastExpire = 0; + private string m_ConnectionString; + + public MSSQLAuthenticationData(string connectionString, string realm) + { + m_Realm = realm; + m_ConnectionString = connectionString; + using (SqlConnection conn = new SqlConnection(m_ConnectionString)) + { + conn.Open(); + Migration m = new Migration(conn, GetType().Assembly, "AuthStore"); + m.Update(); + } + } + + public AuthenticationData Get(UUID principalID) + { + AuthenticationData ret = new AuthenticationData(); + ret.Data = new Dictionary(); + + string sql = string.Format("select * from '{0}' where UUID = @principalID", m_Realm); + + using (SqlConnection conn = new SqlConnection(m_ConnectionString)) + using (SqlCommand cmd = new SqlCommand(sql, conn)) + { + cmd.Parameters.AddWithValue("@principalID", principalID.ToString()); + conn.Open(); + using (SqlDataReader result = cmd.ExecuteReader()) + { + if (result.Read()) + { + ret.PrincipalID = principalID; + + if (m_ColumnNames == null) + { + m_ColumnNames = new List(); + + DataTable schemaTable = result.GetSchemaTable(); + foreach (DataRow row in schemaTable.Rows) + m_ColumnNames.Add(row["ColumnName"].ToString()); + } + + foreach (string s in m_ColumnNames) + { + if (s == "UUID") + continue; + + ret.Data[s] = result[s].ToString(); + } + return ret; + } + } + } + return null; + } + + public bool Store(AuthenticationData data) + { + if (data.Data.ContainsKey("UUID")) + data.Data.Remove("UUID"); + + string[] fields = new List(data.Data.Keys).ToArray(); + StringBuilder updateBuilder = new StringBuilder(); + + using (SqlConnection conn = new SqlConnection(m_ConnectionString)) + using (SqlCommand cmd = new SqlCommand()) + { + updateBuilder.AppendFormat("update '{0}' set ", m_Realm); + + bool first = true; + foreach (string field in fields) + { + if (!first) + updateBuilder.Append(", "); + updateBuilder.AppendFormat("'{0}' = @{0}",field); + + first = false; + + cmd.Parameters.AddWithValue("@" + field, data.Data[field]); + } + + updateBuilder.Append(" where UUID = @principalID"); + + cmd.CommandText = updateBuilder.ToString(); + cmd.Connection = conn; + + cmd.Parameters.AddWithValue("@principalID", data.PrincipalID.ToString()); + conn.Open(); + if (cmd.ExecuteNonQuery() < 1) + { + StringBuilder insertBuilder = new StringBuilder(); + + insertBuilder.AppendFormat("insert into '{0}' ('UUID', '", m_Realm); + insertBuilder.Append(String.Join("', '", fields)); + insertBuilder.Append("') values ( @principalID, @"); + insertBuilder.Append(String.Join(", @", fields)); + insertBuilder.Append(")"); + + cmd.CommandText = insertBuilder.ToString(); + + if (cmd.ExecuteNonQuery() < 1) + { + return false; + } + } + } + return true; + } + + public bool SetDataItem(UUID principalID, string item, string value) + { + string sql = string.Format("update '{0}' set '{1}' = @{1} where UUID = @UUID", m_Realm, item); + using (SqlConnection conn = new SqlConnection(m_ConnectionString)) + using (SqlCommand cmd = new SqlCommand(sql, conn)) + { + cmd.Parameters.AddWithValue("@" + item, value); + cmd.Parameters.AddWithValue("@UUID", principalID.ToString()); + conn.Open(); + if (cmd.ExecuteNonQuery() > 0) + return true; + } + return false; + } + + public bool SetToken(UUID principalID, string token, int lifetime) + { + if (System.Environment.TickCount - m_LastExpire > 30000) + DoExpire(); + string sql = "insert into tokens (UUID, token, validity) values (@principalID, @token, date_add(now(), interval @lifetime minute))"; + using (SqlConnection conn = new SqlConnection(m_ConnectionString)) + using (SqlCommand cmd = new SqlCommand(sql, conn)) + { + cmd.Parameters.AddWithValue("@principalID", principalID.ToString()); + cmd.Parameters.AddWithValue("@token", token); + cmd.Parameters.AddWithValue("@lifetime", lifetime.ToString()); + conn.Open(); + + if (cmd.ExecuteNonQuery() > 0) + { + return true; + } + } + return false; + } + + public bool CheckToken(UUID principalID, string token, int lifetime) + { + if (System.Environment.TickCount - m_LastExpire > 30000) + DoExpire(); + string sql = "update tokens set validity = date_add(now(), interval @lifetime minute) where UUID = @principalID and token = @token and validity > now()"; + using (SqlConnection conn = new SqlConnection(m_ConnectionString)) + using (SqlCommand cmd = new SqlCommand(sql, conn)) + { + cmd.Parameters.AddWithValue("@principalID", principalID.ToString()); + cmd.Parameters.AddWithValue("@token", token); + cmd.Parameters.AddWithValue("@lifetime", lifetime.ToString()); + conn.Open(); + + if (cmd.ExecuteNonQuery() > 0) + { + return true; + } + } + return false; + } + + private void DoExpire() + { + string sql = "delete from tokens where validity < now()"; + using (SqlConnection conn = new SqlConnection(m_ConnectionString)) + using (SqlCommand cmd = new SqlCommand(sql, conn)) + { + conn.Open(); + cmd.ExecuteNonQuery(); + } + m_LastExpire = System.Environment.TickCount; + } + } +} diff --git a/OpenSim/Data/MSSQL/MSSQLUserAccountData.cs b/OpenSim/Data/MSSQL/MSSQLUserAccountData.cs new file mode 100644 index 0000000..1520888 --- /dev/null +++ b/OpenSim/Data/MSSQL/MSSQLUserAccountData.cs @@ -0,0 +1,186 @@ +/* + * Copyright (c) Contributors, http://opensimulator.org/ + * See CONTRIBUTORS.TXT for a full list of copyright holders. + * + * Redistribution and use in source and binary forms, with or without + * modification, are permitted provided that the following conditions are met: + * * Redistributions of source code must retain the above copyright + * notice, this list of conditions and the following disclaimer. + * * Redistributions in binary form must reproduce the above copyright + * notice, this list of conditions and the following disclaimer in the + * documentation and/or other materials provided with the distribution. + * * Neither the name of the OpenSimulator Project nor the + * names of its contributors may be used to endorse or promote products + * derived from this software without specific prior written permission. + * + * THIS SOFTWARE IS PROVIDED BY THE DEVELOPERS ''AS IS'' AND ANY + * EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED + * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE + * DISCLAIMED. IN NO EVENT SHALL THE CONTRIBUTORS BE LIABLE FOR ANY + * DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES + * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; + * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND + * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT + * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS + * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. + */ + +using System; +using System.Collections; +using System.Collections.Generic; +using System.Data; +using OpenMetaverse; +using OpenSim.Framework; +using System.Data.SqlClient; +using System.Text; + +namespace OpenSim.Data.MSSQL +{ + public class MSSQLUserAccountData : IUserAccountData + { + private string m_Realm; + private List m_ColumnNames = null; + private int m_LastExpire = 0; + private string m_ConnectionString; + + public MSSQLUserAccountData(string connectionString, string realm) + { + m_Realm = realm; + m_ConnectionString = connectionString; + using (SqlConnection conn = new SqlConnection(m_ConnectionString)) + { + conn.Open(); + Migration m = new Migration(conn, GetType().Assembly, "UserStore"); + m.Update(); + } + } + + public List Query(UUID principalID, UUID scopeID, string query) + { + return null; + } + + public UserAccountData Get(UUID principalID, UUID scopeID) + { + UserAccountData ret = new UserAccountData(); + ret.Data = new Dictionary(); + + string sql = string.Format("select * from '{0}' where UUID = @principalID", m_Realm); + if (scopeID != UUID.Zero) + sql += " and ScopeID = @scopeID"; + + using (SqlConnection conn = new SqlConnection(m_ConnectionString)) + using (SqlCommand cmd = new SqlCommand(sql, conn)) + { + + cmd.Parameters.AddWithValue("@principalID", principalID); + cmd.Parameters.AddWithValue("@scopeID", scopeID); + conn.Open(); + using (SqlDataReader result = cmd.ExecuteReader()) + { + if (result.Read()) + { + ret.PrincipalID = principalID; + UUID scope; + UUID.TryParse(result["ScopeID"].ToString(), out scope); + ret.ScopeID = scope; + + if (m_ColumnNames == null) + { + m_ColumnNames = new List(); + + DataTable schemaTable = result.GetSchemaTable(); + foreach (DataRow row in schemaTable.Rows) + m_ColumnNames.Add(row["ColumnName"].ToString()); + } + + foreach (string s in m_ColumnNames) + { + if (s == "UUID") + continue; + if (s == "ScopeID") + continue; + + ret.Data[s] = result[s].ToString(); + } + return ret; + } + } + } + return null; + } + + public bool Store(UserAccountData data) + { + if (data.Data.ContainsKey("UUID")) + data.Data.Remove("UUID"); + if (data.Data.ContainsKey("ScopeID")) + data.Data.Remove("ScopeID"); + + string[] fields = new List(data.Data.Keys).ToArray(); + + using (SqlConnection conn = new SqlConnection(m_ConnectionString)) + using (SqlCommand cmd = new SqlCommand()) + { + StringBuilder updateBuilder = new StringBuilder(); + updateBuilder.AppendFormat("update '{0}' set ", m_Realm); + bool first = true; + foreach (string field in fields) + { + if (!first) + updateBuilder.Append(", "); + updateBuilder.AppendFormat("'{0}' = @{0}", field); + + first = false; + + cmd.Parameters.AddWithValue("@" + field, data.Data[field]); + } + + updateBuilder.Append(" where UUID = @principalID"); + + if (data.ScopeID != UUID.Zero) + updateBuilder.Append(" and ScopeID = @scopeID"); + + cmd.CommandText = updateBuilder.ToString(); + cmd.Connection = conn; + cmd.Parameters.AddWithValue("@principalID", data.PrincipalID); + cmd.Parameters.AddWithValue("@scopeID", data.ScopeID); + conn.Open(); + + if (cmd.ExecuteNonQuery() < 1) + { + StringBuilder insertBuilder = new StringBuilder(); + insertBuilder.AppendFormat("insert into '{0}' ('UUID', 'ScopeID', '", m_Realm); + insertBuilder.Append(String.Join("', '", fields)); + insertBuilder.Append("') values ( @principalID, @scopeID, @"); + insertBuilder.Append(String.Join(", @", fields)); + insertBuilder.Append(")"); + + cmd.CommandText = insertBuilder.ToString(); + + if (cmd.ExecuteNonQuery() < 1) + { + return false; + } + } + } + return true; + } + + public bool SetDataItem(UUID principalID, string item, string value) + { + string sql = string.Format("update '{0}' set '{1}' = @{1} where UUID = @UUID", m_Realm, item); + using (SqlConnection conn = new SqlConnection(m_ConnectionString)) + using (SqlCommand cmd = new SqlCommand(sql, conn)) + { + cmd.Parameters.AddWithValue("@" + item, value); + cmd.Parameters.AddWithValue("@UUID", principalID); + conn.Open(); + + if (cmd.ExecuteNonQuery() > 0) + return true; + } + return false; + } + } +} diff --git a/OpenSim/Data/MSSQL/Resources/011_UserStore.sql b/OpenSim/Data/MSSQL/Resources/011_UserStore.sql new file mode 100644 index 0000000..5aa064f --- /dev/null +++ b/OpenSim/Data/MSSQL/Resources/011_UserStore.sql @@ -0,0 +1,5 @@ +BEGIN TRANSACTION + +ALTER TABLE users ADD scopeID uniqueidentifier not null default '00000000-0000-0000-0000-000000000000' + +COMMIT -- cgit v1.1