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); } } } }