From 89813b9e5bbd8c9d3fcea71a8436255208c2782d Mon Sep 17 00:00:00 2001 From: David Sommerseth Date: Sun, 13 Sep 2009 22:07:39 +0200 Subject: Moved user account functions into administration/useraccount.c --- database/sqlite/CMakeLists.txt | 1 + database/sqlite/administration.c | 496 ------------------------ database/sqlite/administration/useraccount.c | 554 +++++++++++++++++++++++++++ 3 files changed, 555 insertions(+), 496 deletions(-) create mode 100644 database/sqlite/administration/useraccount.c (limited to 'database') diff --git a/database/sqlite/CMakeLists.txt b/database/sqlite/CMakeLists.txt index 9875b5b..ff55765 100644 --- a/database/sqlite/CMakeLists.txt +++ b/database/sqlite/CMakeLists.txt @@ -53,6 +53,7 @@ IF(ADMIN_ENABLED) administration/blacklist.c administration/usercerts.c administration/configuration.c + administration/useraccount.c ) ENDIF(ADMIN_ENABLED) diff --git a/database/sqlite/administration.c b/database/sqlite/administration.c index a27c220..ab544ce 100644 --- a/database/sqlite/administration.c +++ b/database/sqlite/administration.c @@ -63,7 +63,6 @@ #include "sqlite.h" -#define FMAP_USERS /**< fieldmapping.h: Include declaration of tbl_sqlite_users */ #define FMAP_CERTS /**< fieldmapping.h: Include declaration of tbl_sqlite_certs */ #define FMAP_ADMINACCESS /**< fieldmapping.h: Include declaration of tbl_sqlite_eurephiaadmacc */ #define FMAP_LASTLOG /**< fieldmapping.h: Include declaration of tbl_sqlite_lastlog */ @@ -389,501 +388,6 @@ int eDBadminLogout(eurephiaCTX *ctx, const char *sessionkey) { } -/** - * @copydoc eDBadminGetUserList() - */ -xmlDoc *eDBadminGetUserList(eurephiaCTX *ctx, const char *sortkeys) { - xmlDoc *userlist = NULL; - xmlNode *root_n = NULL, *user_n = NULL; - dbresult *res = NULL; - char *dbsort = NULL, tmp[34]; - int i = 0; - - DEBUG(ctx, 20, "Function call: eDBadminGetUserList(ctx, '%s')", sortkeys); - assert((ctx != NULL) && (ctx->dbc != 0)); - - if( (ctx->context_type != ECTX_ADMIN_CONSOLE) && (ctx->context_type != ECTX_ADMIN_WEB) ) { - eurephia_log(ctx, LOG_CRITICAL, 0, - "eurephia admin function call attempted with wrong context type"); - return NULL; - } - - // Convert the input sort keys to the proper database field names - dbsort = eDBmkSortKeyString(tbl_sqlite_users, sortkeys); - - // Query database for all users - res = sqlite_query(ctx, - "SELECT username, activated, deactivated, last_accessed, uid" - " FROM openvpn_users " - "ORDER BY %s", (sortkeys != NULL ? dbsort : "uid")); - if( res == NULL ) { - eurephia_log(ctx, LOG_ERROR, 0, "Error querying the user database"); - return NULL; - } - - // Prepare a list with all users - memset(&tmp, 0, 34); - eurephiaXML_CreateDoc(ctx, 1, "userlist", &userlist, &root_n); - snprintf(tmp, 32, "%i", sqlite_get_numtuples(res)); - xmlNewProp(root_n, (xmlChar *)"usercount", (xmlChar *)tmp); - - // Register all records - for( i = 0; i < sqlite_get_numtuples(res); i++ ) { - user_n = xmlNewChild(root_n, NULL, (xmlChar *)"user", NULL); - sqlite_xml_value(user_n, XML_ATTR, "uid", res, i, 4); - sqlite_xml_value(user_n, XML_NODE, "username", res, i, 0); - sqlite_xml_value(user_n, XML_NODE, "activated", res, i, 1); - sqlite_xml_value(user_n, XML_NODE, "deactivated", res, i, 2); - sqlite_xml_value(user_n, XML_NODE, "last_accessed", res, i, 3); - } - sqlite_free_results(res); - - // Return a user list - return userlist; -} - - -/** - * Internal function. Adds a child node named \ to an xmlNode containing a flag value - * - * @param node xmlNode pointer where to add the new flag - * @param flagname String containing a name of the flag - * @param flagged Is the flag set or not. The tag will only be added if the flag is set - * - * @return Returns the \c flagged value - */ -inline int xml_set_flag(xmlNode *node, char *flagname, int flagged) { - if( flagged ) { - xmlNewChild(node, NULL, (xmlChar *) "flag", (xmlChar *) flagname); - } - return flagged; -} - - -/** - * @copydoc eDBadminGetUserInfo() - */ -xmlDoc *eDBadminGetUserInfo(eurephiaCTX *ctx, int infoType, xmlDoc *srch) { - dbresult *uinf = NULL, *qres = NULL; - eDBfieldMap *uinfo_map = NULL; - int flag = 0, uid = 0; - char *username = NULL; - - xmlDoc *doc = NULL; - xmlNode *root_n = NULL, *info_n = NULL, *fieldmap = NULL; - - DEBUG(ctx, 20, "Function call: eDBadminGetUserUserInfo(ctx, %i, {xmlDoc})", infoType); - assert( (ctx != NULL) && (srch != NULL) ); - - if( (ctx->context_type != ECTX_ADMIN_CONSOLE) && (ctx->context_type != ECTX_ADMIN_WEB) ) { - eurephia_log(ctx, LOG_CRITICAL, 0, - "eurephia admin function call attempted with wrong context type"); - return NULL; - } - - fieldmap = eurephiaXML_getRoot(ctx, srch, "fieldMapping", 1); - uinfo_map = eDBxmlMapping(ctx, tbl_sqlite_users, "u", fieldmap); - - // Query the database, find the user defined in the user map - uinf = sqlite_query_mapped(ctx, SQL_SELECT, - "SELECT u.username, u.activated, u.deactivated, u.last_accessed, u.uid," - " (bl.username IS NOT NULL), opensess, logincount," - " (at.attempts > 0)" - " FROM openvpn_users u" - " LEFT JOIN openvpn_blacklist bl USING(username)" - " LEFT JOIN openvpn_attempts at ON(at.username = u.username)" - " LEFT JOIN (SELECT uid, count(*) AS logincount " - " FROM openvpn_lastlog" - " GROUP BY uid) lc" - " ON (lc.uid = u.uid)" - " LEFT JOIN (SELECT uid, count(*) > 0 AS opensess" - " FROM openvpn_lastlog" - " WHERE sessionstatus = 2" - " GROUP BY uid) os" - " ON (os.uid = u.uid)", - NULL, uinfo_map, NULL); - - if( uinf == NULL ) { - eurephia_log(ctx, LOG_ERROR, 0, "Error querying the database for a user"); - return 0; - } - eDBfreeMapping(uinfo_map); - - switch( sqlite_get_numtuples(uinf) ) { - case 0: - sqlite_free_results(uinf); - return 0; // No user found - - case 1: - uid = atoi_nullsafe(sqlite_get_value(uinf, 0, 4)); - username = sqlite_get_value(uinf, 0, 0); - - eurephiaXML_CreateDoc(ctx, 1, "user", &doc, &root_n); - sqlite_xml_value(root_n, XML_NODE, "username", uinf, 0, 0); - sqlite_xml_value(root_n, XML_ATTR, "uid", uinf, 0, 4); - - if( (infoType & USERINFO_user) == USERINFO_user ) { - info_n = xmlNewChild(root_n, NULL, (xmlChar *) "flags", NULL); - - // set DEACTIVATED flag, if deactivated field is not NULL - xml_set_flag(info_n, "DEACTIVATED", (sqlite_get_value(uinf, 0, 2) != NULL)); - - // set BLACKLISTED flag, if username is found in blacklist table - xml_set_flag(info_n, "BLACKLISTED", (atoi_nullsafe(sqlite_get_value(uinf, 0, 5))==1)); - - // set OPENSESSION flag, if user has a lastlog entry with sessionstatus == 2 - xml_set_flag(info_n, "OPENSESSION", (atoi_nullsafe(sqlite_get_value(uinf, 0, 6))==1)); - - // set ERRATTEMPT flag, if user has an entry in attempts log with attemtps > 0 - xml_set_flag(info_n, "ERRATTEMPT", (atoi_nullsafe(sqlite_get_value(uinf, 0, 8))==1)); - - // set NEVERUSED flag, if login count == 0 and last_accessed == NULL - flag = xml_set_flag(info_n, "NEVERUSED", ((atoi_nullsafe(sqlite_get_value(uinf,0, 7))==0) - && (sqlite_get_value(uinf, 0, 3) == NULL))); - - // set RSETLASTUSED flag, if login count == 0 and last_accessed == NULL - xml_set_flag(info_n, "RSETLASTUSED", !flag && (sqlite_get_value(uinf,0,3)) == NULL); - - // set RSETLOGINCNT flag, if login count == 0 and last_accessed != NULL - xml_set_flag(info_n, "RSETLOGINCNT", ((atoi_nullsafe(sqlite_get_value(uinf,0, 7))==0) - && (sqlite_get_value(uinf,0,3)) != NULL)); - - sqlite_xml_value(root_n, XML_NODE, "activated", uinf, 0, 1); - sqlite_xml_value(root_n, XML_NODE, "deactivated", uinf, 0, 2); - info_n = sqlite_xml_value(root_n, XML_NODE, "last_accessed", uinf, 0, 3); - sqlite_xml_value(info_n, XML_ATTR, "logincount", uinf, 0, 7); - } - - if( (infoType & USERINFO_certs) == USERINFO_certs ) { - // Extract certificate info - qres = sqlite_query(ctx, - "SELECT depth, digest, common_name, organisation, email, " - " c.registered, c.certid, uc.accessprofile, access_descr," - " fw_profile" - " FROM openvpn_certificates c" - " LEFT JOIN openvpn_usercerts uc ON (c.certid = uc.certid)" - " LEFT JOIN openvpn_accesses a " - " ON (uc.accessprofile = a.accessprofile)" - " WHERE uid = '%i' ORDER BY c.certid DESC", uid); - - info_n = xmlNewChild(root_n, NULL, (xmlChar *) "certificates", NULL); - if( (qres != NULL) && (sqlite_get_numtuples(qres) > 0) ) { - int i; - xmlNode *cert, *acpr; - xmlChar *tmp = NULL; - - for( i = 0; i < sqlite_get_numtuples(qres); i++ ) { - cert = xmlNewChild(info_n, NULL, (xmlChar *) "certificate", NULL); - - sqlite_xml_value(cert, XML_ATTR, "certid", qres, 0, 6); - sqlite_xml_value(cert, XML_ATTR, "depth", qres, 0, 0); - sqlite_xml_value(cert, XML_ATTR, "registered", qres, 0, 5); - sqlite_xml_value(cert, XML_NODE, "digest", qres, 0, 1); - - tmp = (xmlChar *)sqlite_get_value(qres, 0, 2); - xmlReplaceChars(tmp, '_', ' '); - xmlNewChild(cert, NULL, (xmlChar *) "common_name", tmp); - - tmp = (xmlChar *)sqlite_get_value(qres, 0, 3); - xmlReplaceChars(tmp, '_', ' '); - xmlNewChild(cert, NULL, (xmlChar *) "organisation", tmp); - - sqlite_xml_value(cert, XML_NODE, "email", qres, 0, 4); - - acpr = sqlite_xml_value(cert, XML_NODE, "access_profile", qres, 0, 8); - sqlite_xml_value(acpr, XML_ATTR, "accessprofile", qres, 0, 7); - sqlite_xml_value(acpr, XML_ATTR, "fwdestination", qres, 0, 9); - } - } - - if( qres != NULL ) { - sqlite_free_results(qres); - } - } - - if( (infoType & USERINFO_lastlog) == USERINFO_lastlog ) { - int i = 0; - xmlNode *lastl = NULL, *sess = NULL, *tmp1 = NULL, *tmp2 = NULL; - xmlChar *tmp = NULL; - - qres = sqlite_query(ctx, - "SELECT llid, ll.certid, protocol, remotehost, remoteport, macaddr," - " vpnipaddr, vpnipmask, sessionstatus, sessionkey," - " login, logout, session_duration, session_deleted," - " bytes_sent, bytes_received, uicid, accessprofile," - " access_descr, fw_profile, depth, digest," - " common_name, organisation, email" - " FROM openvpn_lastlog ll" - " LEFT JOIN openvpn_usercerts USING (uid, certid)" - " LEFT JOIN openvpn_accesses USING (accessprofile)" - " LEFT JOIN openvpn_certificates cert ON (ll.certid = cert.certid)" - " WHERE uid = '%i' ORDER BY login, logout", uid); - - if( qres == NULL ) { - eurephia_log(ctx, LOG_ERROR, 0, "Quering the lastlog failed"); - xmlFreeDoc(doc); - return NULL; - } - - lastl = xmlNewChild(root_n, NULL, (xmlChar *) "lastlog", NULL); - for( i = 0; i < sqlite_get_numtuples(qres); i++ ) { - - sess = xmlNewChild(lastl, NULL, (xmlChar*) "session", NULL); - sqlite_xml_value(sess, XML_ATTR, "llid", qres, i, 0); - xmlNewProp(sess, (xmlChar *) "session_status", - (xmlChar *)SESSION_STATUS[atoi_nullsafe(sqlite_get_value(qres, i, 8))]); - sqlite_xml_value(sess, XML_ATTR, "session_duration", qres, i, 12); - sqlite_xml_value(sess, XML_NODE, "sessionkey", qres, i, 9); - sqlite_xml_value(sess, XML_NODE, "login", qres, i, 10); - sqlite_xml_value(sess, XML_NODE, "logout", qres, i, 11); - sqlite_xml_value(sess, XML_NODE, "session_closed", qres, i, 13); - - tmp1 = xmlNewChild(sess, NULL, (xmlChar *) "connection", NULL); - sqlite_xml_value(tmp1, XML_ATTR, "bytes_sent", qres, i, 14); - sqlite_xml_value(tmp1, XML_ATTR, "bytes_received", qres, i, 15); - sqlite_xml_value(tmp1, XML_NODE, "protocol", qres, i, 2); - sqlite_xml_value(tmp1, XML_NODE, "remote_host", qres, i, 3); - sqlite_xml_value(tmp1, XML_NODE, "remote_port", qres, i, 4); - sqlite_xml_value(tmp1, XML_NODE, "vpn_macaddr", qres, i, 5); - sqlite_xml_value(tmp1, XML_NODE, "vpn_ipaddr" , qres, i, 6); - sqlite_xml_value(tmp1, XML_NODE, "vpn_netmask", qres, i, 7); - - tmp1 = xmlNewChild(sess, NULL, (xmlChar *) "certificate", NULL); - sqlite_xml_value(tmp1, XML_ATTR, "certid", qres, i, 1); - sqlite_xml_value(tmp1, XML_ATTR, "uicid", qres, i, 16); - sqlite_xml_value(tmp1, XML_ATTR, "depth", qres, i, 20); - sqlite_xml_value(tmp1, XML_NODE, "digest", qres, i, 21); - - tmp = (xmlChar *)sqlite_get_value(qres, 0, 22); - xmlReplaceChars(tmp, '_', ' '); - xmlNewChild(tmp1, NULL, (xmlChar *) "common_name", tmp); - - tmp = (xmlChar *)sqlite_get_value(qres, 0, 23); - xmlReplaceChars(tmp, '_', ' '); - xmlNewChild(tmp1, NULL, (xmlChar *) "organisation", tmp); - - sqlite_xml_value(tmp1, XML_NODE, "email", qres, i, 24); - - tmp2 = sqlite_xml_value(tmp1, XML_NODE, "access_profile", qres, i, 18); - sqlite_xml_value(tmp2, XML_ATTR, "accessprofile", qres, i, 17); - sqlite_xml_value(tmp2, XML_ATTR, "fwdestination", qres, i, 19); - } - sqlite_free_results(qres); - } - - if( (infoType & USERINFO_attempts) == USERINFO_attempts ) { - xmlNode *atmpt = NULL; - - qres = sqlite_query(ctx, - "SELECT attempts, registered, last_attempt, atpid" - " FROM openvpn_attempts " - " WHERE username = '%q'", username); - - if( (qres == NULL) || (sqlite_get_numtuples(qres) > 1) ) { - eurephia_log(ctx, LOG_ERROR, 0, "Quering for login attempts failed"); - sqlite_free_results(qres); - xmlFreeDoc(doc); - return NULL; - } - - atmpt = xmlNewChild(root_n, NULL, (xmlChar *) "attempts", NULL); - if( sqlite_get_numtuples(qres) == 1 ) { - sqlite_xml_value(atmpt, XML_ATTR, "atpid", qres, 0, 3); - sqlite_xml_value(atmpt, XML_ATTR, "attempts", qres, 0, 0); - sqlite_xml_value(atmpt, XML_NODE, "first_attempt", qres, 0, 1); - sqlite_xml_value(atmpt, XML_NODE, "last_attempt", qres, 0, 2); - } - sqlite_free_results(qres); - } - - if( (infoType & USERINFO_blacklist) == USERINFO_blacklist ) { - xmlNode *atmpt = NULL; - - qres = sqlite_query(ctx, - "SELECT registered, last_accessed, blid" - " FROM openvpn_blacklist " - " WHERE username = '%q'", username); - - if( (qres == NULL) || (sqlite_get_numtuples(qres) > 1) ) { - eurephia_log(ctx, LOG_ERROR, 0, "Quering blacklist log failed"); - sqlite_free_results(qres); - xmlFreeDoc(doc); - return NULL; - } - - atmpt = xmlNewChild(root_n, NULL, (xmlChar *) "blacklist", NULL); - if( sqlite_get_numtuples(qres) == 1 ) { - sqlite_xml_value(atmpt, XML_ATTR, "blid", qres, 0, 2); - sqlite_xml_value(atmpt, XML_NODE, "blacklisted", qres, 0, 0); - sqlite_xml_value(atmpt, XML_NODE, "last_accessed", qres, 0, 1); - } - sqlite_free_results(qres); - } - - sqlite_free_results(uinf); - return doc; - default: - sqlite_free_results(uinf); - eurephia_log(ctx, LOG_ERROR, 0, "Too many user records was found."); - return NULL; - } -} - - -/** - * @copydoc eDBadminAddUser() - */ -int eDBadminAddUser(eurephiaCTX *ctx, xmlDoc *userinfo) { - dbresult *res = NULL; - xmlNode *usrinf_n = NULL; - eDBfieldMap *usrinf_map = NULL; - int uid = 0; - - DEBUG(ctx, 20, "Function call: eDBadminAddUser(ctx, xmlDoc)"); - assert( (ctx != NULL) && (userinfo != NULL) ); - - if( (ctx->context_type != ECTX_ADMIN_CONSOLE) && (ctx->context_type != ECTX_ADMIN_WEB) ) { - eurephia_log(ctx, LOG_CRITICAL, 0, - "eurephia admin function call attempted with wrong context type"); - return 0; - } - - // Get the add_user node, and then find the fieldMapping node - usrinf_n = eurephiaXML_getRoot(ctx, userinfo, "add_user", 1); - if( usrinf_n == NULL ) { - eurephia_log(ctx, LOG_ERROR, 0, "Could not find proper add user XML document"); - return 0; - } - usrinf_n = xmlFindNode(usrinf_n, "fieldMapping"); - if( usrinf_n == NULL ) { - eurephia_log(ctx, LOG_ERROR, 0, "Could not find proper add user XML document"); - return 0; - } - - // Get a proper field mapping to be used by the database - usrinf_map = eDBxmlMapping(ctx, tbl_sqlite_users, NULL, usrinf_n); - assert( usrinf_map != NULL ); - - // Register the user - res = sqlite_query_mapped(ctx, SQL_INSERT, "INSERT INTO openvpn_users", usrinf_map, NULL, NULL); - if( res == NULL ) { - eurephia_log(ctx, LOG_FATAL, 0, "Could not register the new user account"); - uid = -1; - } else { - uid = res->last_insert_id; - } - sqlite_free_results(res); - eDBfreeMapping(usrinf_map); - - return uid; -} - - -/** - * @copydoc eDBadminUpdateUser() - */ -int eDBadminUpdateUser(eurephiaCTX *ctx, const int uid, xmlDoc *userinfo) { - dbresult *uinf = NULL; - xmlDoc *srch_xml = NULL; - xmlNode *root_n = NULL, *srch_n = NULL, *values_n = NULL; - eDBfieldMap *value_map = NULL, *srch_map = NULL; - xmlChar *xmluid = 0; - - DEBUG(ctx, 20, "Function call: eDBadminUpdateUser(ctx, %i, xmlDoc)", uid); - assert( (ctx != NULL) && (userinfo != NULL) ); - - if( (ctx->context_type != ECTX_ADMIN_CONSOLE) && (ctx->context_type != ECTX_ADMIN_WEB) ) { - eurephia_log(ctx, LOG_CRITICAL, 0, - "eurephia admin function call attempted with wrong context type"); - return 0; - } - - // Get the update_user node - root_n = eurephiaXML_getRoot(ctx, userinfo, "update_user", 1); - if( root_n == NULL ) { - eurephia_log(ctx, LOG_ERROR, 0, "Could not find proper XML element for user update"); - return 0; - } - - // Double check that we are going to update the right user - xmluid = (xmlChar *)xmlGetAttrValue(root_n->properties, "uid"); - if( atoi_nullsafe((char *)xmluid) != uid ) { - eurephia_log(ctx, LOG_ERROR, 0, "Mismatch between uid given as parameter and uid in XML"); - return 0; - } - - // Grab the fieldMapping node and create a eDBfieldMap structure for it - values_n = xmlFindNode(root_n, "fieldMapping"); - value_map = eDBxmlMapping(ctx, tbl_sqlite_users, NULL, values_n); - - // Create an eDBfieldMap structure for the srch_map (used for WHERE clause) - eurephiaXML_CreateDoc(ctx, 1, "fieldMapping", &srch_xml, &srch_n); - xmlNewProp(srch_n, (xmlChar *) "table", (xmlChar *) "users"); - xmlNewChild(srch_n, NULL, (xmlChar *) "uid", xmluid); // Add uid as the only criteria - srch_map = eDBxmlMapping(ctx, tbl_sqlite_users, NULL, srch_n); - assert( srch_map != NULL ); - - // UPDATE the database - uinf = sqlite_query_mapped(ctx, SQL_UPDATE, "UPDATE openvpn_users", value_map, srch_map, NULL); - - if( uinf == NULL ) { - eurephia_log(ctx, LOG_ERROR, 0, "Error querying the database for a user"); - return 0; - } - sqlite_free_results(uinf); - - eDBfreeMapping(srch_map); - eDBfreeMapping(value_map); - xmlFreeDoc(srch_xml); - - return 1; -} - -/** - * @copydoc eDBadminDeleteUser() - */ -int eDBadminDeleteUser(eurephiaCTX *ctx, const int uid, xmlDoc *userinfo) { - dbresult *res = NULL; - xmlNode *usrinf_n = NULL; - char *uid_str = NULL; - int rc = 0; - - DEBUG(ctx, 20, "Function call: eDBadminDeleteUser(ctx, %i, xmlDoc)", uid); - assert( (ctx != NULL) && (userinfo != NULL) ); - - if( (ctx->context_type != ECTX_ADMIN_CONSOLE) && (ctx->context_type != ECTX_ADMIN_WEB) ) { - eurephia_log(ctx, LOG_CRITICAL, 0, - "eurephia admin function call attempted with wrong context type"); - return 0; - } - - // Get the delete_user node - usrinf_n = eurephiaXML_getRoot(ctx, userinfo, "delete_user", 1); - if( usrinf_n == NULL ) { - eurephia_log(ctx, LOG_ERROR, 0, "Could not find proper delete user XML document"); - return 0; - } - - // Get the uid from the XML and compare it with the uid in the function argument - uid_str = xmlGetAttrValue(usrinf_n->properties, "uid"); - if( (uid_str == NULL) || (atoi_nullsafe(uid_str) != uid) ) { - eurephia_log(ctx, LOG_ERROR, 0, "Could not find proper delete user XML document. (uid mismatch)"); - return 0; - } - - // Delete the user - res = sqlite_query(ctx, "DELETE FROM openvpn_users WHERE uid = '%i'", uid); - if( res == NULL ) { - eurephia_log(ctx, LOG_FATAL, 0, "Could not delete the user account"); - rc = 0; - } else { - rc = 1; - } - sqlite_free_results(res); - return rc; -} - - /** * @copydoc eDBadminGetCertificateInfo() */ diff --git a/database/sqlite/administration/useraccount.c b/database/sqlite/administration/useraccount.c new file mode 100644 index 0000000..15ca660 --- /dev/null +++ b/database/sqlite/administration/useraccount.c @@ -0,0 +1,554 @@ +/* administration.c -- Functions for user account management + * + * GPLv2 only - Copyright (C) 2008, 2009 + * David Sommerseth + * + * This program is free software; you can redistribute it and/or + * modify it under the terms of the GNU General Public License + * as published by the Free Software Foundation; version 2 + * of the License. + * + * This program is distributed in the hope that it will be useful, + * but WITHOUT ANY WARRANTY; without even the implied warranty of + * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + * GNU General Public License for more details. + * + * You should have received a copy of the GNU General Public License + * along with this program; if not, write to the Free Software + * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA. + * + */ + +/** + * @file useraccount.c + * @author David Sommerseth + * @date 2009-09-13 + * + * @brief Functions for user account management + * + */ + +#include +#include +#include + +#include + +#include + +#include +#include +#include +#include +#include +#include +#include +#include + +#ifndef DRIVER_MODE +#define DRIVER_MODE +#endif +#include + +#include "../sqlite.h" + +#define FMAP_USERS /**< fieldmapping.h: Include declaration of tbl_sqlite_users */ +#include "../fieldmapping.h" + + +void xmlReplaceChars(xmlChar *str, char s, char r); + + +/** + * @copydoc eDBadminGetUserList() + */ +xmlDoc *eDBadminGetUserList(eurephiaCTX *ctx, const char *sortkeys) { + xmlDoc *userlist = NULL; + xmlNode *root_n = NULL, *user_n = NULL; + dbresult *res = NULL; + char *dbsort = NULL, tmp[34]; + int i = 0; + + DEBUG(ctx, 20, "Function call: eDBadminGetUserList(ctx, '%s')", sortkeys); + assert((ctx != NULL) && (ctx->dbc != 0)); + + if( (ctx->context_type != ECTX_ADMIN_CONSOLE) && (ctx->context_type != ECTX_ADMIN_WEB) ) { + eurephia_log(ctx, LOG_CRITICAL, 0, + "eurephia admin function call attempted with wrong context type"); + return NULL; + } + + // Convert the input sort keys to the proper database field names + dbsort = eDBmkSortKeyString(tbl_sqlite_users, sortkeys); + + // Query database for all users + res = sqlite_query(ctx, + "SELECT username, activated, deactivated, last_accessed, uid" + " FROM openvpn_users " + "ORDER BY %s", (sortkeys != NULL ? dbsort : "uid")); + if( res == NULL ) { + eurephia_log(ctx, LOG_ERROR, 0, "Error querying the user database"); + return NULL; + } + + // Prepare a list with all users + memset(&tmp, 0, 34); + eurephiaXML_CreateDoc(ctx, 1, "userlist", &userlist, &root_n); + snprintf(tmp, 32, "%i", sqlite_get_numtuples(res)); + xmlNewProp(root_n, (xmlChar *)"usercount", (xmlChar *)tmp); + + // Register all records + for( i = 0; i < sqlite_get_numtuples(res); i++ ) { + user_n = xmlNewChild(root_n, NULL, (xmlChar *)"user", NULL); + sqlite_xml_value(user_n, XML_ATTR, "uid", res, i, 4); + sqlite_xml_value(user_n, XML_NODE, "username", res, i, 0); + sqlite_xml_value(user_n, XML_NODE, "activated", res, i, 1); + sqlite_xml_value(user_n, XML_NODE, "deactivated", res, i, 2); + sqlite_xml_value(user_n, XML_NODE, "last_accessed", res, i, 3); + } + sqlite_free_results(res); + + // Return a user list + return userlist; +} + + +/** + * Internal function. Adds a child node named \ to an xmlNode containing a flag value + * + * @param node xmlNode pointer where to add the new flag + * @param flagname String containing a name of the flag + * @param flagged Is the flag set or not. The tag will only be added if the flag is set + * + * @return Returns the \c flagged value + */ +inline int xml_set_flag(xmlNode *node, char *flagname, int flagged) { + if( flagged ) { + xmlNewChild(node, NULL, (xmlChar *) "flag", (xmlChar *) flagname); + } + return flagged; +} + + +/** + * @copydoc eDBadminGetUserInfo() + */ +xmlDoc *eDBadminGetUserInfo(eurephiaCTX *ctx, int infoType, xmlDoc *srch) { + dbresult *uinf = NULL, *qres = NULL; + eDBfieldMap *uinfo_map = NULL; + int flag = 0, uid = 0; + char *username = NULL; + + xmlDoc *doc = NULL; + xmlNode *root_n = NULL, *info_n = NULL, *fieldmap = NULL; + + DEBUG(ctx, 20, "Function call: eDBadminGetUserUserInfo(ctx, %i, {xmlDoc})", infoType); + assert( (ctx != NULL) && (srch != NULL) ); + + if( (ctx->context_type != ECTX_ADMIN_CONSOLE) && (ctx->context_type != ECTX_ADMIN_WEB) ) { + eurephia_log(ctx, LOG_CRITICAL, 0, + "eurephia admin function call attempted with wrong context type"); + return NULL; + } + + fieldmap = eurephiaXML_getRoot(ctx, srch, "fieldMapping", 1); + uinfo_map = eDBxmlMapping(ctx, tbl_sqlite_users, "u", fieldmap); + + // Query the database, find the user defined in the user map + uinf = sqlite_query_mapped(ctx, SQL_SELECT, + "SELECT u.username, u.activated, u.deactivated, u.last_accessed, u.uid," + " (bl.username IS NOT NULL), opensess, logincount," + " (at.attempts > 0)" + " FROM openvpn_users u" + " LEFT JOIN openvpn_blacklist bl USING(username)" + " LEFT JOIN openvpn_attempts at ON(at.username = u.username)" + " LEFT JOIN (SELECT uid, count(*) AS logincount " + " FROM openvpn_lastlog" + " GROUP BY uid) lc" + " ON (lc.uid = u.uid)" + " LEFT JOIN (SELECT uid, count(*) > 0 AS opensess" + " FROM openvpn_lastlog" + " WHERE sessionstatus = 2" + " GROUP BY uid) os" + " ON (os.uid = u.uid)", + NULL, uinfo_map, NULL); + + if( uinf == NULL ) { + eurephia_log(ctx, LOG_ERROR, 0, "Error querying the database for a user"); + return 0; + } + eDBfreeMapping(uinfo_map); + + switch( sqlite_get_numtuples(uinf) ) { + case 0: + sqlite_free_results(uinf); + return 0; // No user found + + case 1: + uid = atoi_nullsafe(sqlite_get_value(uinf, 0, 4)); + username = sqlite_get_value(uinf, 0, 0); + + eurephiaXML_CreateDoc(ctx, 1, "user", &doc, &root_n); + sqlite_xml_value(root_n, XML_NODE, "username", uinf, 0, 0); + sqlite_xml_value(root_n, XML_ATTR, "uid", uinf, 0, 4); + + if( (infoType & USERINFO_user) == USERINFO_user ) { + info_n = xmlNewChild(root_n, NULL, (xmlChar *) "flags", NULL); + + // set DEACTIVATED flag, if deactivated field is not NULL + xml_set_flag(info_n, "DEACTIVATED", (sqlite_get_value(uinf, 0, 2) != NULL)); + + // set BLACKLISTED flag, if username is found in blacklist table + xml_set_flag(info_n, "BLACKLISTED", (atoi_nullsafe(sqlite_get_value(uinf, 0, 5))==1)); + + // set OPENSESSION flag, if user has a lastlog entry with sessionstatus == 2 + xml_set_flag(info_n, "OPENSESSION", (atoi_nullsafe(sqlite_get_value(uinf, 0, 6))==1)); + + // set ERRATTEMPT flag, if user has an entry in attempts log with attemtps > 0 + xml_set_flag(info_n, "ERRATTEMPT", (atoi_nullsafe(sqlite_get_value(uinf, 0, 8))==1)); + + // set NEVERUSED flag, if login count == 0 and last_accessed == NULL + flag = xml_set_flag(info_n, "NEVERUSED", ((atoi_nullsafe(sqlite_get_value(uinf,0, 7))==0) + && (sqlite_get_value(uinf, 0, 3) == NULL))); + + // set RSETLASTUSED flag, if login count == 0 and last_accessed == NULL + xml_set_flag(info_n, "RSETLASTUSED", !flag && (sqlite_get_value(uinf,0,3)) == NULL); + + // set RSETLOGINCNT flag, if login count == 0 and last_accessed != NULL + xml_set_flag(info_n, "RSETLOGINCNT", ((atoi_nullsafe(sqlite_get_value(uinf,0, 7))==0) + && (sqlite_get_value(uinf,0,3)) != NULL)); + + sqlite_xml_value(root_n, XML_NODE, "activated", uinf, 0, 1); + sqlite_xml_value(root_n, XML_NODE, "deactivated", uinf, 0, 2); + info_n = sqlite_xml_value(root_n, XML_NODE, "last_accessed", uinf, 0, 3); + sqlite_xml_value(info_n, XML_ATTR, "logincount", uinf, 0, 7); + } + + if( (infoType & USERINFO_certs) == USERINFO_certs ) { + // Extract certificate info + qres = sqlite_query(ctx, + "SELECT depth, digest, common_name, organisation, email, " + " c.registered, c.certid, uc.accessprofile, access_descr," + " fw_profile" + " FROM openvpn_certificates c" + " LEFT JOIN openvpn_usercerts uc ON (c.certid = uc.certid)" + " LEFT JOIN openvpn_accesses a " + " ON (uc.accessprofile = a.accessprofile)" + " WHERE uid = '%i' ORDER BY c.certid DESC", uid); + + info_n = xmlNewChild(root_n, NULL, (xmlChar *) "certificates", NULL); + if( (qres != NULL) && (sqlite_get_numtuples(qres) > 0) ) { + int i; + xmlNode *cert, *acpr; + xmlChar *tmp = NULL; + + for( i = 0; i < sqlite_get_numtuples(qres); i++ ) { + cert = xmlNewChild(info_n, NULL, (xmlChar *) "certificate", NULL); + + sqlite_xml_value(cert, XML_ATTR, "certid", qres, 0, 6); + sqlite_xml_value(cert, XML_ATTR, "depth", qres, 0, 0); + sqlite_xml_value(cert, XML_ATTR, "registered", qres, 0, 5); + sqlite_xml_value(cert, XML_NODE, "digest", qres, 0, 1); + + tmp = (xmlChar *)sqlite_get_value(qres, 0, 2); + xmlReplaceChars(tmp, '_', ' '); + xmlNewChild(cert, NULL, (xmlChar *) "common_name", tmp); + + tmp = (xmlChar *)sqlite_get_value(qres, 0, 3); + xmlReplaceChars(tmp, '_', ' '); + xmlNewChild(cert, NULL, (xmlChar *) "organisation", tmp); + + sqlite_xml_value(cert, XML_NODE, "email", qres, 0, 4); + + acpr = sqlite_xml_value(cert, XML_NODE, "access_profile", qres, 0, 8); + sqlite_xml_value(acpr, XML_ATTR, "accessprofile", qres, 0, 7); + sqlite_xml_value(acpr, XML_ATTR, "fwdestination", qres, 0, 9); + } + } + + if( qres != NULL ) { + sqlite_free_results(qres); + } + } + + if( (infoType & USERINFO_lastlog) == USERINFO_lastlog ) { + int i = 0; + xmlNode *lastl = NULL, *sess = NULL, *tmp1 = NULL, *tmp2 = NULL; + xmlChar *tmp = NULL; + + qres = sqlite_query(ctx, + "SELECT llid, ll.certid, protocol, remotehost, remoteport, macaddr," + " vpnipaddr, vpnipmask, sessionstatus, sessionkey," + " login, logout, session_duration, session_deleted," + " bytes_sent, bytes_received, uicid, accessprofile," + " access_descr, fw_profile, depth, digest," + " common_name, organisation, email" + " FROM openvpn_lastlog ll" + " LEFT JOIN openvpn_usercerts USING (uid, certid)" + " LEFT JOIN openvpn_accesses USING (accessprofile)" + " LEFT JOIN openvpn_certificates cert ON (ll.certid = cert.certid)" + " WHERE uid = '%i' ORDER BY login, logout", uid); + + if( qres == NULL ) { + eurephia_log(ctx, LOG_ERROR, 0, "Quering the lastlog failed"); + xmlFreeDoc(doc); + return NULL; + } + + lastl = xmlNewChild(root_n, NULL, (xmlChar *) "lastlog", NULL); + for( i = 0; i < sqlite_get_numtuples(qres); i++ ) { + + sess = xmlNewChild(lastl, NULL, (xmlChar*) "session", NULL); + sqlite_xml_value(sess, XML_ATTR, "llid", qres, i, 0); + xmlNewProp(sess, (xmlChar *) "session_status", + (xmlChar *)SESSION_STATUS[atoi_nullsafe(sqlite_get_value(qres, i, 8))]); + sqlite_xml_value(sess, XML_ATTR, "session_duration", qres, i, 12); + sqlite_xml_value(sess, XML_NODE, "sessionkey", qres, i, 9); + sqlite_xml_value(sess, XML_NODE, "login", qres, i, 10); + sqlite_xml_value(sess, XML_NODE, "logout", qres, i, 11); + sqlite_xml_value(sess, XML_NODE, "session_closed", qres, i, 13); + + tmp1 = xmlNewChild(sess, NULL, (xmlChar *) "connection", NULL); + sqlite_xml_value(tmp1, XML_ATTR, "bytes_sent", qres, i, 14); + sqlite_xml_value(tmp1, XML_ATTR, "bytes_received", qres, i, 15); + sqlite_xml_value(tmp1, XML_NODE, "protocol", qres, i, 2); + sqlite_xml_value(tmp1, XML_NODE, "remote_host", qres, i, 3); + sqlite_xml_value(tmp1, XML_NODE, "remote_port", qres, i, 4); + sqlite_xml_value(tmp1, XML_NODE, "vpn_macaddr", qres, i, 5); + sqlite_xml_value(tmp1, XML_NODE, "vpn_ipaddr" , qres, i, 6); + sqlite_xml_value(tmp1, XML_NODE, "vpn_netmask", qres, i, 7); + + tmp1 = xmlNewChild(sess, NULL, (xmlChar *) "certificate", NULL); + sqlite_xml_value(tmp1, XML_ATTR, "certid", qres, i, 1); + sqlite_xml_value(tmp1, XML_ATTR, "uicid", qres, i, 16); + sqlite_xml_value(tmp1, XML_ATTR, "depth", qres, i, 20); + sqlite_xml_value(tmp1, XML_NODE, "digest", qres, i, 21); + + tmp = (xmlChar *)sqlite_get_value(qres, 0, 22); + xmlReplaceChars(tmp, '_', ' '); + xmlNewChild(tmp1, NULL, (xmlChar *) "common_name", tmp); + + tmp = (xmlChar *)sqlite_get_value(qres, 0, 23); + xmlReplaceChars(tmp, '_', ' '); + xmlNewChild(tmp1, NULL, (xmlChar *) "organisation", tmp); + + sqlite_xml_value(tmp1, XML_NODE, "email", qres, i, 24); + + tmp2 = sqlite_xml_value(tmp1, XML_NODE, "access_profile", qres, i, 18); + sqlite_xml_value(tmp2, XML_ATTR, "accessprofile", qres, i, 17); + sqlite_xml_value(tmp2, XML_ATTR, "fwdestination", qres, i, 19); + } + sqlite_free_results(qres); + } + + if( (infoType & USERINFO_attempts) == USERINFO_attempts ) { + xmlNode *atmpt = NULL; + + qres = sqlite_query(ctx, + "SELECT attempts, registered, last_attempt, atpid" + " FROM openvpn_attempts " + " WHERE username = '%q'", username); + + if( (qres == NULL) || (sqlite_get_numtuples(qres) > 1) ) { + eurephia_log(ctx, LOG_ERROR, 0, "Quering for login attempts failed"); + sqlite_free_results(qres); + xmlFreeDoc(doc); + return NULL; + } + + atmpt = xmlNewChild(root_n, NULL, (xmlChar *) "attempts", NULL); + if( sqlite_get_numtuples(qres) == 1 ) { + sqlite_xml_value(atmpt, XML_ATTR, "atpid", qres, 0, 3); + sqlite_xml_value(atmpt, XML_ATTR, "attempts", qres, 0, 0); + sqlite_xml_value(atmpt, XML_NODE, "first_attempt", qres, 0, 1); + sqlite_xml_value(atmpt, XML_NODE, "last_attempt", qres, 0, 2); + } + sqlite_free_results(qres); + } + + if( (infoType & USERINFO_blacklist) == USERINFO_blacklist ) { + xmlNode *atmpt = NULL; + + qres = sqlite_query(ctx, + "SELECT registered, last_accessed, blid" + " FROM openvpn_blacklist " + " WHERE username = '%q'", username); + + if( (qres == NULL) || (sqlite_get_numtuples(qres) > 1) ) { + eurephia_log(ctx, LOG_ERROR, 0, "Quering blacklist log failed"); + sqlite_free_results(qres); + xmlFreeDoc(doc); + return NULL; + } + + atmpt = xmlNewChild(root_n, NULL, (xmlChar *) "blacklist", NULL); + if( sqlite_get_numtuples(qres) == 1 ) { + sqlite_xml_value(atmpt, XML_ATTR, "blid", qres, 0, 2); + sqlite_xml_value(atmpt, XML_NODE, "blacklisted", qres, 0, 0); + sqlite_xml_value(atmpt, XML_NODE, "last_accessed", qres, 0, 1); + } + sqlite_free_results(qres); + } + + sqlite_free_results(uinf); + return doc; + default: + sqlite_free_results(uinf); + eurephia_log(ctx, LOG_ERROR, 0, "Too many user records was found."); + return NULL; + } +} + + +/** + * @copydoc eDBadminAddUser() + */ +int eDBadminAddUser(eurephiaCTX *ctx, xmlDoc *userinfo) { + dbresult *res = NULL; + xmlNode *usrinf_n = NULL; + eDBfieldMap *usrinf_map = NULL; + int uid = 0; + + DEBUG(ctx, 20, "Function call: eDBadminAddUser(ctx, xmlDoc)"); + assert( (ctx != NULL) && (userinfo != NULL) ); + + if( (ctx->context_type != ECTX_ADMIN_CONSOLE) && (ctx->context_type != ECTX_ADMIN_WEB) ) { + eurephia_log(ctx, LOG_CRITICAL, 0, + "eurephia admin function call attempted with wrong context type"); + return 0; + } + + // Get the add_user node, and then find the fieldMapping node + usrinf_n = eurephiaXML_getRoot(ctx, userinfo, "add_user", 1); + if( usrinf_n == NULL ) { + eurephia_log(ctx, LOG_ERROR, 0, "Could not find proper add user XML document"); + return 0; + } + usrinf_n = xmlFindNode(usrinf_n, "fieldMapping"); + if( usrinf_n == NULL ) { + eurephia_log(ctx, LOG_ERROR, 0, "Could not find proper add user XML document"); + return 0; + } + + // Get a proper field mapping to be used by the database + usrinf_map = eDBxmlMapping(ctx, tbl_sqlite_users, NULL, usrinf_n); + assert( usrinf_map != NULL ); + + // Register the user + res = sqlite_query_mapped(ctx, SQL_INSERT, "INSERT INTO openvpn_users", usrinf_map, NULL, NULL); + if( res == NULL ) { + eurephia_log(ctx, LOG_FATAL, 0, "Could not register the new user account"); + uid = -1; + } else { + uid = res->last_insert_id; + } + sqlite_free_results(res); + eDBfreeMapping(usrinf_map); + + return uid; +} + + +/** + * @copydoc eDBadminUpdateUser() + */ +int eDBadminUpdateUser(eurephiaCTX *ctx, const int uid, xmlDoc *userinfo) { + dbresult *uinf = NULL; + xmlDoc *srch_xml = NULL; + xmlNode *root_n = NULL, *srch_n = NULL, *values_n = NULL; + eDBfieldMap *value_map = NULL, *srch_map = NULL; + xmlChar *xmluid = 0; + + DEBUG(ctx, 20, "Function call: eDBadminUpdateUser(ctx, %i, xmlDoc)", uid); + assert( (ctx != NULL) && (userinfo != NULL) ); + + if( (ctx->context_type != ECTX_ADMIN_CONSOLE) && (ctx->context_type != ECTX_ADMIN_WEB) ) { + eurephia_log(ctx, LOG_CRITICAL, 0, + "eurephia admin function call attempted with wrong context type"); + return 0; + } + + // Get the update_user node + root_n = eurephiaXML_getRoot(ctx, userinfo, "update_user", 1); + if( root_n == NULL ) { + eurephia_log(ctx, LOG_ERROR, 0, "Could not find proper XML element for user update"); + return 0; + } + + // Double check that we are going to update the right user + xmluid = (xmlChar *)xmlGetAttrValue(root_n->properties, "uid"); + if( atoi_nullsafe((char *)xmluid) != uid ) { + eurephia_log(ctx, LOG_ERROR, 0, "Mismatch between uid given as parameter and uid in XML"); + return 0; + } + + // Grab the fieldMapping node and create a eDBfieldMap structure for it + values_n = xmlFindNode(root_n, "fieldMapping"); + value_map = eDBxmlMapping(ctx, tbl_sqlite_users, NULL, values_n); + + // Create an eDBfieldMap structure for the srch_map (used for WHERE clause) + eurephiaXML_CreateDoc(ctx, 1, "fieldMapping", &srch_xml, &srch_n); + xmlNewProp(srch_n, (xmlChar *) "table", (xmlChar *) "users"); + xmlNewChild(srch_n, NULL, (xmlChar *) "uid", xmluid); // Add uid as the only criteria + srch_map = eDBxmlMapping(ctx, tbl_sqlite_users, NULL, srch_n); + assert( srch_map != NULL ); + + // UPDATE the database + uinf = sqlite_query_mapped(ctx, SQL_UPDATE, "UPDATE openvpn_users", value_map, srch_map, NULL); + + if( uinf == NULL ) { + eurephia_log(ctx, LOG_ERROR, 0, "Error querying the database for a user"); + return 0; + } + sqlite_free_results(uinf); + + eDBfreeMapping(srch_map); + eDBfreeMapping(value_map); + xmlFreeDoc(srch_xml); + + return 1; +} + +/** + * @copydoc eDBadminDeleteUser() + */ +int eDBadminDeleteUser(eurephiaCTX *ctx, const int uid, xmlDoc *userinfo) { + dbresult *res = NULL; + xmlNode *usrinf_n = NULL; + char *uid_str = NULL; + int rc = 0; + + DEBUG(ctx, 20, "Function call: eDBadminDeleteUser(ctx, %i, xmlDoc)", uid); + assert( (ctx != NULL) && (userinfo != NULL) ); + + if( (ctx->context_type != ECTX_ADMIN_CONSOLE) && (ctx->context_type != ECTX_ADMIN_WEB) ) { + eurephia_log(ctx, LOG_CRITICAL, 0, + "eurephia admin function call attempted with wrong context type"); + return 0; + } + + // Get the delete_user node + usrinf_n = eurephiaXML_getRoot(ctx, userinfo, "delete_user", 1); + if( usrinf_n == NULL ) { + eurephia_log(ctx, LOG_ERROR, 0, "Could not find proper delete user XML document"); + return 0; + } + + // Get the uid from the XML and compare it with the uid in the function argument + uid_str = xmlGetAttrValue(usrinf_n->properties, "uid"); + if( (uid_str == NULL) || (atoi_nullsafe(uid_str) != uid) ) { + eurephia_log(ctx, LOG_ERROR, 0, "Could not find proper delete user XML document. (uid mismatch)"); + return 0; + } + + // Delete the user + res = sqlite_query(ctx, "DELETE FROM openvpn_users WHERE uid = '%i'", uid); + if( res == NULL ) { + eurephia_log(ctx, LOG_FATAL, 0, "Could not delete the user account"); + rc = 0; + } else { + rc = 1; + } + sqlite_free_results(res); + return rc; +} -- cgit