public void UpdateUser(int userid, string name, string password, UserAuthType typ)
{
using (NpgsqlConnection conn = PostgreSQLConn.CreateConnection(_ConnectionString))
{
using (NpgsqlCommand cmd = conn.CreateCommand())
{
string sCommand = "UPDATE \"UserProfiles\" ";
sCommand = sCommand + " SET username='******',";
if (typ == UserAuthType.ListAuthentication)
{
sCommand = sCommand + " password='',";
}
else
{
sCommand = sCommand + " password=md5('" + password + "'),";
}
sCommand = sCommand + " user_type='" + typ.ToString() + "'";
sCommand = sCommand + " WHERE id=" + userid;
cmd.CommandText = sCommand;
cmd.ExecuteNonQuery();
}
}
Refresh();
}
private int FindAC_ID_Type(string type, string permissionName, string roleId, bool bIsGroup)
{
using (NpgsqlConnection conn = PostgreSQLConn.CreateConnection(_ConnectionString))
{
string XXX_AccessControlList;
string XXX_RoleId;
int ac_id;
if (bIsGroup)
{
XXX_AccessControlList = "UserGroups_AccessControlList";
XXX_RoleId = "groups_id";
}
else
{
XXX_AccessControlList = "UserProfiles_AccessControlList";
XXX_RoleId = "users_id";
}
//Suche entsprechende ac_id
using (NpgsqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText =
string.Format("SELECT al.id FROM \"AccessControlList\" AS al INNER JOIN \"{0}\" AS ua ON ua.acl_id = al.id ", XXX_AccessControlList) +
"INNER JOIN \"Permissions\" AS pe ON al.permissions_id = pe.id INNER JOIN \"TypeDefinitions\" AS td ON pe.types_id = td.id INNER JOIN \"ObjectList\" AS ol ON ol.id=al.object_id " +
string.Format("WHERE ua.{0} = {1} AND pe.name = '{2}' AND td.clr_name='{3}' AND ol.locator='DUMMYOBJECT'",
XXX_RoleId, roleId, permissionName, type);
object result = cmd.ExecuteScalar();
ac_id = (result == null || result is System.DBNull) ? -1 : (int)result;
//ac_id = (int)cmd.ExecuteScalar();
}
return(ac_id);
}
}
public void RemoveEntry(int id)
{
using (NpgsqlConnection conn = PostgreSQLConn.CreateConnection(_ConnectionString))
{
//Lösche nun noch in der UserGroups_AccessControlList
using (NpgsqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = string.Format("DELETE FROM \"ObjectList\" WHERE id={0}", id);
cmd.ExecuteNonQuery();
}
}
}
public void RemoveGroup(int id)
{
using (NpgsqlConnection conn = PostgreSQLConn.CreateConnection(_ConnectionString))
{
using (NpgsqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "DELETE From \"UserGroups\" WHERE id =" + id;
cmd.ExecuteNonQuery();
}
}
Refresh();
}
public void RemoveEntry(int user_id, int acl_id)
{
using (NpgsqlConnection conn = PostgreSQLConn.CreateConnection(_ConnectionString))
{
//Lösche nun noch in der UserGroups_AccessControlList
using (NpgsqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = string.Format("DELETE FROM \"UserProfiles_AccessControlList\" WHERE users_id={0} AND acl_id={1} ", user_id, acl_id);
cmd.ExecuteNonQuery();
}
}
}
public void RemoveEntriesByGroupId(int groupid)
{
using (NpgsqlConnection conn = PostgreSQLConn.CreateConnection(_ConnectionString))
{
using (NpgsqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "DELETE From \"UserProfiles_UserGroups\" WHERE groups_id =" + groupid;
cmd.ExecuteNonQuery();
}
}
Refresh();
}
public void CloseSession(int userId)
{
using (NpgsqlConnection conn = PostgreSQLConn.CreateConnection(_ConnectionString))
{
using (NpgsqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "UPDATE \"UserSessions\" " +
"SET logout_time = now() " +
"WHERE logout_time > now()";
cmd.ExecuteNonQuery();
}
}
}
public void AddEntry(int group_id, int acl_id)
{
using (NpgsqlConnection conn = PostgreSQLConn.CreateConnection(_ConnectionString))
{
using (NpgsqlCommand cmd = conn.CreateCommand())
{
string sCommand = string.Format("INSERT INTO \"UserGroups_AccessControlList\" (groups_id,acl_id) VALUES({0},{1})",
group_id, acl_id);
cmd.CommandText = sCommand;
cmd.ExecuteNonQuery();
}
}
}
public int AddEntry(int object_id, int permission_id, bool access)
{
using (NpgsqlConnection conn = PostgreSQLConn.CreateConnection(_ConnectionString))
{
using (NpgsqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "SELECT \"InsertAclEntry\"(:object_id, :permission_id, :access);";
cmd.Parameters.Add("object_id", object_id);
cmd.Parameters.Add("permission_id", permission_id);
cmd.Parameters.Add("access", access);
object result = cmd.ExecuteScalar();
return((result == null || result is System.DBNull) ? -1 : (int)result);
}
}
}
public bool?_GetPermissionOfUserObjectPermissionList(string locator, string type, string permissionName, string userId)
{
using (NpgsqlConnection conn = PostgreSQLConn.CreateConnection(_ConnectionString))
{
using (NpgsqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "SELECT al.access FROM \"AccessControlList\" AS al INNER JOIN \"UserProfiles_AccessControlList\" AS ua ON ua.acl_id = al.id " +
"INNER JOIN \"Permissions\" AS pe ON al.permissions_id = pe.id INNER JOIN \"TypeDefinitions\" AS td ON pe.types_id = td.id INNER JOIN \"ObjectList\" AS ol ON ol.id=al.object_id " +
string.Format("WHERE ua.users_id = {0} AND pe.name = '{1}' AND ol.locator = '{2}' AND td.clr_name='{3}'", userId, permissionName, locator, type);
object result = cmd.ExecuteScalar();
return((result == null || result is System.DBNull) ? (bool?)null : (bool?)result);
}
}
}
public void RemoveEntriesByUserId(int userid)
{
using (NpgsqlConnection conn = PostgreSQLConn.CreateConnection(_ConnectionString))
{
using (NpgsqlCommand cmd = conn.CreateCommand())
{
string sCommand = "DELETE FROM \"UserProfiles_UserGroups\" ";
sCommand = sCommand + " WHERE users_id='" + userid.ToString() + "'";
cmd.CommandText = sCommand;
cmd.ExecuteNonQuery();
}
}
Refresh();
}
public void AddEntry(int userid, int groupid)
{
using (NpgsqlConnection conn = PostgreSQLConn.CreateConnection(_ConnectionString))
{
using (NpgsqlCommand cmd = conn.CreateCommand())
{
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "\"AddGroupToUser\"";
cmd.Parameters.Add("uid", userid);
cmd.Parameters.Add("gid", groupid);
PostgreSQLConn.ExecuteNonQuery(cmd);
}
}
Refresh();
}
public bool _GetDefaultPermissionOfType(string type, string permissionName)
{
bool defaultRes = false;
using (NpgsqlConnection conn = PostgreSQLConn.CreateConnection(_ConnectionString))
{
using (NpgsqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "SELECT pe.default FROM \"Permissions\" AS pe INNER JOIN \"TypeDefinitions\" AS td ON pe.types_id = td.id WHERE td.clr_name = :clr_name AND pe.name = :permission";
cmd.Parameters.Add("clr_name", type);
cmd.Parameters.Add("permission", permissionName);
defaultRes = PostgreSQLConn.ExecuteScalar <bool>(cmd).Value;
}
}
return(defaultRes);
}
public void UpdateGroup(int groupid, string name)
{
using (NpgsqlConnection conn = PostgreSQLConn.CreateConnection(_ConnectionString))
{
using (NpgsqlCommand cmd = conn.CreateCommand())
{
string sCommand = "UPDATE \"UserGroups\" ";
sCommand = sCommand + " SET name='" + name + "'";
sCommand = sCommand + " WHERE id=" + groupid;
cmd.CommandText = sCommand;
cmd.ExecuteNonQuery();
}
}
Refresh();
}
public bool?_GetPermissionOfUserTypePermissionList(string type, string permissionName, string userId)
{
using (NpgsqlConnection conn = PostgreSQLConn.CreateConnection(_ConnectionString))
{
using (NpgsqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "SELECT al.access FROM \"AccessControlList\" AS al INNER JOIN \"UserProfiles_AccessControlList\" AS ua ON ua.acl_id = al.id " +
"INNER JOIN \"Permissions\" AS pe ON al.permissions_id = pe.id INNER JOIN \"TypeDefinitions\" AS td ON pe.types_id = td.id INNER JOIN \"ObjectList\" AS ol ON ol.id=al.object_id " +
"WHERE ua.users_id = :user_id AND pe.name = :permission AND ol.locator = 'DUMMYOBJECT' AND td.clr_name = :clr_name";
cmd.Parameters.Add("user_id", userId);
cmd.Parameters.Add("permission", permissionName);
cmd.Parameters.Add("clr_name", type);
object result = cmd.ExecuteScalar();
return((result == null || result is System.DBNull) ? (bool?)null : (bool?)result);
}
}
}
public void _ResetUserObjectPermission(string locator, string type, string userId)
{
int type_id = Adapters.TypeDefinitionsAdapter.GetIdByTypeName(type);
//suche alle acl_id Einträge in UserGroups_AccessControlList mit groups_id
//lösche alle Einträge in der AccessControlList mit id == acl_id eines Typs
int user_id = int.Parse(userId);
List <int> acl_ids = new List <int>();
List <int> object_ids = new List <int>();
using (NpgsqlConnection conn = PostgreSQLConn.CreateConnection(_ConnectionString))
{
using (NpgsqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "SELECT upacl.acl_id,acl.object_id FROM \"UserProfiles_AccessControlList\" as upacl " +
"INNER JOIN \"AccessControlList\" AS acl ON acl.id=upacl.acl_id " +
"INNER JOIN \"Permissions\" AS pe ON pe.id=acl.permissions_id " +
"INNER JOIN \"ObjectList\" AS ol ON ol.id=acl.object_id " +
"WHERE upacl.users_id=:users_id AND pe.types_id=:type_id AND ol.locator=:locator";
cmd.Parameters.Add("users_id", user_id);
cmd.Parameters.Add("type_id", type_id);
cmd.Parameters.Add("locator", locator);
NpgsqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
acl_ids.Add((int)dr["acl_id"]);
object_ids.Add((int)dr["object_id"]);
}
}
}
foreach (int object_id in object_ids)
{
Adapters.ObjectListAdapter.RemoveEntry(object_id);
}
foreach (int acl_id in acl_ids)
{
Adapters.UserProfiles_AccessControlListAdapter.RemoveEntry(user_id, acl_id);
}
foreach (int acl_id in acl_ids)
{
Adapters.AccessControlListAdapter.RemoveEntryById(acl_id);
}
}
public int AddEntry(string locator)
{
using (NpgsqlConnection conn = PostgreSQLConn.CreateConnection(_ConnectionString))
{
using (NpgsqlCommand cmd = conn.CreateCommand())
{
string sCommand = string.Format("INSERT INTO \"ObjectList\" (locator,parent_id) VALUES('{0}','{1}') RETURNING id",
locator, 0);
cmd.CommandText = sCommand;
object result = cmd.ExecuteScalar();
if (result == null || result is System.DBNull)
{
return(-1);
}
return((int)result);
}
}
}
public int GetIdByLocator(string locator)
{
using (NpgsqlConnection conn = PostgreSQLConn.CreateConnection(_ConnectionString))
{
//Lösche nun noch in der UserGroups_AccessControlList
using (NpgsqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = string.Format("SELECT id FROM \"ObjectList\" WHERE locator='{0}'", locator);
NpgsqlDataReader dr = cmd.ExecuteReader();
object result = null;
if (dr.Read())
{
result = dr["id"];
}
return((result == null || result is System.DBNull) ? -1 : (int)result);
}
}
}
public int AddGroup(string name)
{
using (NpgsqlConnection conn = PostgreSQLConn.CreateConnection(_ConnectionString))
{
using (NpgsqlCommand cmd = conn.CreateCommand())
{
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "\"InsertUserGroups\"";
cmd.Parameters.Add("group_name", name);
object result = cmd.ExecuteScalar();
if (result == null || result is System.DBNull)
{
return(-1);
}
Refresh();
return(Convert.ToInt32(result));
}
}
}
public void Refresh()
{
this.Clear();
using (NpgsqlConnection conn = PostgreSQLConn.CreateConnection(_ConnectionString))
{
using (NpgsqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "SELECT users_id,groups_id FROM \"UserProfiles_UserGroups\";";
NpgsqlDataReader reader = PostgreSQLConn.ExecuteReader(cmd);
while (reader.Read())
{
UserProfile_UserGroups entry = new UserProfile_UserGroups();
entry.UserId = Convert.ToInt32(reader["users_id"]);
entry.GroupId = Convert.ToInt32(reader["groups_id"]);
this.Add(entry);
}
}
}
}
public void Refresh()
{
this.Clear();
using (NpgsqlConnection conn = PostgreSQLConn.CreateConnection(_ConnectionString))
{
using (NpgsqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "SELECT id, name FROM \"UserGroups\";";
NpgsqlDataReader reader = PostgreSQLConn.ExecuteReader(cmd);
while (reader.Read())
{
UserGroup userGroup = new UserGroup();
userGroup.Id = Convert.ToInt32(reader["id"]);
userGroup.Name = reader["name"].ToString();
this.Add(userGroup.Id, userGroup);
}
}
}
}
public void Refresh()
{
this.Clear();
using (NpgsqlConnection conn = PostgreSQLConn.CreateConnection(_ConnectionString))
{
using (NpgsqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "SELECT id,name, types_id,\"Permissions\".default FROM \"Permissions\";";
NpgsqlDataReader reader = PostgreSQLConn.ExecuteReader(cmd);
while (reader.Read())
{
Permission info = new Permission();
info.Id = Convert.ToInt32(reader["id"]);
info.PermissionName = reader["name"].ToString();
info.TypeId = Convert.ToInt32(reader["types_id"]);
info.DefaultAccess = Convert.ToBoolean(reader["default"]);
this.Add(info.Id, info);
}
}
}
}
public List <ISession> GetOpenSessions()
{
Sessions.Clear();
ISession session;
using (NpgsqlConnection conn = PostgreSQLConn.CreateConnection(_ConnectionString))
{
using (NpgsqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "SELECT t1.user_id, t2.username, t1.login_time, t1.logout_time from \"UserSessions\" as t1, \"UserProfiles\" as t2 " +
"WHERE logout_time > now() " +
"AND t1.user_id = t2.id";
NpgsqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
session = new Session((int)dr["user_id"], (string)dr["username"], (DateTime)dr["login_time"], (DateTime)dr["logout_time"]);
Sessions.Add(session);
}
}
}
return(Sessions);
}
public void Refresh()
{
this.Clear();
using (NpgsqlConnection conn = PostgreSQLConn.CreateConnection(_ConnectionString))
{
using (NpgsqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "SELECT id, username, enabled, user_type FROM \"UserProfiles\";";
NpgsqlDataReader reader = PostgreSQLConn.ExecuteReader(cmd);
while (reader.Read())
{
UserProfile userProfile = new UserProfile();
userProfile.Id = Convert.ToInt32(reader["id"]);
userProfile.UserName = reader["username"].ToString();
userProfile.Enabled = Convert.ToBoolean(reader["enabled"]);
userProfile.Type = (UserAuthType)Enum.Parse(typeof(UserAuthType), reader["user_type"].ToString());
this.Add(userProfile.Id, userProfile);
}
}
}
}
public int AddUser(string account, string password, UserAuthType type)
{
using (NpgsqlConnection conn = PostgreSQLConn.CreateConnection(_ConnectionString))
{
using (NpgsqlCommand cmd = conn.CreateCommand())
{
//CFI: Do not use CommandType.StoredProcedure as this will convert \ -> \\
cmd.CommandText = "SELECT * FROM \"InsertUserProfile\"(:p_username, :p_password, :p_local_directory_id, :p_user_type)";
cmd.Parameters.Add("p_username", account);
cmd.Parameters.Add("p_password", password);
cmd.Parameters.Add("p_local_directory_id", string.Empty);
cmd.Parameters.Add("p_user_type", type.ToString());
object result = cmd.ExecuteScalar();
if (result == null || result is System.DBNull)
{
return(-1);
}
Refresh();
return(Convert.ToInt32(result));
}
}
}
public void Refresh()
{
this.Clear();
using (NpgsqlConnection conn = PostgreSQLConn.CreateConnection(_ConnectionString))
{
using (NpgsqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "SELECT id, clr_name, parent_id FROM \"TypeDefinitions\";";
NpgsqlDataReader reader = PostgreSQLConn.ExecuteReader(cmd);
while (reader.Read())
{
TypeDefinition info = new TypeDefinition();
info.Id = Convert.ToInt32(reader["id"]);
info.ClrName = reader["clr_name"].ToString();
object result = reader["parent_id"];
info.ParentId = (result is DBNull || result == null) ? -1 : Convert.ToInt32(result);
this.Add(info.Id, info);
}
}
}
}
