path: root/database/sqlite/administration/useraccount.c

/* administration.c  --  Functions for user account management
 *
 *  GPLv2 only - Copyright (C) 2008 - 2010
 *               David Sommerseth <dazo@users.sourceforge.net>
 *
 *  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 <dazo@users.sourceforge.net>
 * @date   2009-09-13
 *
 * @brief  Functions for user account management
 *
 */

#include <string.h>
#include <unistd.h>
#include <assert.h>

#include <libxml/tree.h>

#include <sqlite3.h>

#include <eurephia_nullsafe.h>
#include <eurephia_context.h>
#include <eurephia_log.h>
#include <eurephia_xml.h>
#include <eurephia_values.h>
#include <eurephiadb_session_struct.h>
#include <eurephiadb_mapping.h>
#include <passwd.h>

#ifndef DRIVER_MODE
#define DRIVER_MODE
#endif
#include <eurephiadb_driver.h>

#include "../sqlite.h"

#define FMAP_USERS              /**< fieldmapping.h: Include declaration of tbl_sqlite_users */
#define FMAP_ADMINACCESS        /**< fieldmapping.h: Include declaration of tbl_sqlite_eurephiaadmacc */
#include "../fieldmapping.h"


/**
 * Internal function.  Adds a child node named \<flag\> 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
 */
static inline int xml_set_flag(xmlNode *node, char *flagname, int flagged) {
        if( flagged ) {
                xmlNewChild(node, NULL, (xmlChar *) "flag", (xmlChar *) flagname);
        }
        return flagged;
}


/**
 * Internal function.  Retrieves information about user accounts
 *
 * @param ctx         eurephiaCTX
 * @param infoType    Flags defining which information to be included in the result
 * @param uinfo_map   eDBfieldMap containing information needed for the new user account
 * @param sortkeys    String containing list of fields defining data sorting
 *
 * @return Returns an eurephia ResultMsg XML document with status of the operation.  On fatal errors,
 *         NULL is returned.
 */
static xmlDoc *useracc_view(eurephiaCTX *ctx, unsigned int infoType,
                            eDBfieldMap *uinfo_map, const char *sortkeys)
{
        dbresult *uinf = NULL, *qres = NULL;
        unsigned int flag = 0, uid = 0, recid = 0;
        char *username = NULL;
        xmlDoc *doc = NULL;
        xmlNode *root_n = NULL, *info_n = NULL;

        DEBUG(ctx, 20, "Function call: eDBadminGetUserUserInfo(ctx, %i, {xmlDoc})", infoType);
        assert( ctx != 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;
        }

        // Query the database, find the user defined in the user map
        uinf = sqlite_query_mapped(ctx, SQL_SELECT,
                                   "SELECT users.username, users.activated, users.deactivated,"
                                   "       users.last_accessed, users.uid,"
                                   "       (bl.username IS NOT NULL), opensess, logincount,"
                                   "       (at.attempts > 0)"
                                   "  FROM openvpn_users users"
                                   "  LEFT JOIN openvpn_blacklist bl USING(username)"
                                   "  LEFT JOIN openvpn_attempts at ON(at.username = users.username)"
                                   "  LEFT JOIN (SELECT uid, count(*) AS logincount "
                                   "               FROM openvpn_lastlog"
                                   "              GROUP BY uid) lc"
                                   "         ON (lc.uid = users.uid)"
                                   "  LEFT JOIN (SELECT uid, count(*) > 0 AS opensess"
                                   "               FROM openvpn_lastlog"
                                   "              WHERE sessionstatus = 2"
                                   "              GROUP BY uid) os"
                                   "         ON (os.uid = users.uid)",
                                   NULL, uinfo_map, sortkeys);

        if( uinf == NULL ) {
                eurephia_log(ctx, LOG_ERROR, 0, "Error querying the database for a user");
                return eurephiaXML_ResultMsg(ctx, exmlERROR, NULL, "Failed to query the user database");
        }

        eurephiaXML_CreateDoc(ctx, 1, "UserAccount", &doc, &root_n);
        xmlNewProp(root_n, (xmlChar *) "mode", (xmlChar *) "view");

        for( recid = 0; recid < sqlite_get_numtuples(uinf); recid++ ) {
                xmlNode *user_n = xmlNewChild(root_n, NULL, (xmlChar *) "Account", NULL);
                assert( user_n != NULL );

                sqlite_xml_value(user_n, XML_ATTR, "uid", uinf, recid, 4);
                sqlite_xml_value(user_n, XML_NODE, "username", uinf, recid, 0);

                uid = atoi_nullsafe(sqlite_get_value(uinf, recid, 4));
                username = sqlite_get_value(uinf, recid, 0);

                if( infoType & USERINFO_user ) {
                        info_n = xmlNewChild(user_n, NULL, (xmlChar *) "flags", NULL);
                        assert( info_n != NULL );

                        // set DEACTIVATED flag, if deactivated field is not NULL
                        xml_set_flag(info_n, "DEACTIVATED", (sqlite_get_value(uinf, recid, 2) != NULL));

                        // set BLACKLISTED flag, if username is found in blacklist table
                        xml_set_flag(info_n, "BLACKLISTED", (atoi_nullsafe(sqlite_get_value(uinf, recid, 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, recid, 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, recid, 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, recid, 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(user_n, XML_NODE, "activated", uinf, recid, 1);
                        sqlite_xml_value(user_n, XML_NODE, "deactivated", uinf, recid, 2);
                        info_n = sqlite_xml_value(user_n, XML_NODE, "last_accessed", uinf, recid, 3);
                        sqlite_xml_value(user_n, XML_ATTR, "logincount", uinf, recid, 7);
                }

                if( infoType & USERINFO_certs ) {
                        // Extract certificate info
                        qres = sqlite_query(ctx,
                                            "SELECT depth, lower(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(user_n, NULL, (xmlChar *) "certificates", NULL);
                        assert( info_n != 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);
                                        assert( cert != NULL );

                                        sqlite_xml_value(cert, XML_ATTR, "certid",        qres, i, 6);
                                        sqlite_xml_value(cert, XML_ATTR, "depth",         qres, i, 0);
                                        sqlite_xml_value(cert, XML_ATTR, "registered",    qres, i, 5);
                                        sqlite_xml_value(cert, XML_NODE, "digest",        qres, i, 1);

                                        tmp = (xmlChar *)sqlite_get_value(qres, i, 2);
                                        xmlReplaceChars(tmp, '_', ' ');
                                        xmlNewChild(cert, NULL, (xmlChar *) "common_name", tmp);

                                        tmp = (xmlChar *)sqlite_get_value(qres, i, 3);
                                        xmlReplaceChars(tmp, '_', ' ');
                                        xmlNewChild(cert, NULL, (xmlChar *) "organisation", tmp);

                                        sqlite_xml_value(cert, XML_NODE, "email",         qres, i, 4);

                                        acpr = sqlite_xml_value(cert, XML_NODE, "access_profile", qres, i, 8);
                                        sqlite_xml_value(acpr, XML_ATTR, "accessprofile",         qres, i, 7);
                                        sqlite_xml_value(acpr, XML_ATTR, "fwdestination",         qres, i, 9);
                                }
                        }
                        sqlite_free_results(qres);
                }

                if( infoType & 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, lower(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, "Querying the lastlog failed");
                                xmlFreeDoc(doc);
                                return eurephiaXML_ResultMsg(ctx, exmlERROR, NULL,
                                                             "Failed to query the lastlog");
                        }

                        lastl = xmlNewChild(user_n, NULL, (xmlChar *) "lastlog", NULL);
                        for( i = 0; i < sqlite_get_numtuples(qres); i++ ) {

                                sess = xmlNewChild(lastl, NULL, (xmlChar*) "session", NULL);
                                assert( sess != 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);
                                assert( tmp1 != 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);
                                assert( tmp1 != 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 ) {
                        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, "Querying for login attempts failed");
                                sqlite_free_results(qres);
                                xmlFreeDoc(doc);
                                return eurephiaXML_ResultMsg(ctx, exmlERROR, NULL,
                                                             "Failed to query the login attempts log");
                        }

                        atmpt = xmlNewChild(user_n, NULL, (xmlChar *) "attempts", NULL);
                        assert( atmpt != 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 ) {
                        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, "Querying blacklist log failed");
                                sqlite_free_results(qres);
                                xmlFreeDoc(doc);
                                return eurephiaXML_ResultMsg(ctx, exmlERROR, NULL,
                                                             "Failed to query the blacklist log");
                        }

                        atmpt = xmlNewChild(user_n, NULL, (xmlChar *) "blacklist", NULL);
                        assert( atmpt != 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;
}


/**
 * Internal function.  Creates a new user account in the database.
 *
 * @param ctx         eurephiaCTX
 * @param usrinf_map  eDBfieldMap containing information needed for the new user account
 *
 * @return Returns an eurephia ResultMsg XML document with status of the operation.  On fatal errors,
 *         NULL is returned.
 */
static xmlDoc *useracc_add(eurephiaCTX *ctx, eDBfieldMap *usrinf_map) {
        xmlDoc *res_d = NULL;
        dbresult *res = NULL;

        DEBUG(ctx, 21, "Function call: useracc_add(ctx, eDBfieldMap)");
        assert( (ctx != NULL) && (usrinf_map != 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;
        }

        // Register the user
        res = sqlite_query_mapped(ctx, SQL_INSERT, "INSERT INTO openvpn_users", usrinf_map, NULL, NULL);
        if( (res == NULL) || (sqlite_get_affected_rows(res) == 0) ) {
                eurephia_log(ctx, LOG_FATAL, 0, "Could not register the new user account");
                res_d = eurephiaXML_ResultMsg(ctx, exmlERROR, NULL,
                                              "Failed to register the user account");
        } else {
                xmlChar *uid = malloc_nullsafe(ctx, 34);
                xmlNode *info_n = NULL;
                assert( uid != NULL );

                // Prepare an information tag/node with the new uid value
                xmlStrPrintf(uid, 32, (xmlChar *) "%ld", res->last_insert_id);
                info_n = xmlNewNode(NULL, (xmlChar *)"UserAccount");
                xmlNewProp(info_n, (xmlChar *) "mode", (xmlChar *) "add");
                xmlNewProp(info_n, (xmlChar *) "uid", uid);

                eurephia_log(ctx, LOG_INFO, 1, "New user account created (uid %i)", res->last_insert_id);
                res_d = eurephiaXML_ResultMsg(ctx, exmlRESULT, info_n,
                                              "New user account created with uid %i", res->last_insert_id);
                free_nullsafe(ctx, uid);
                xmlFreeNode(info_n);
        }
        sqlite_free_results(res);

        return res_d;
}


/**
 * Internal function.  Updates a user account
 *
 * @param ctx        eurephiaCTX
 * @param uid        Numeric user id to be updated
 * @param value_map  eDBfieldMap containing new values
 *
 * @return Returns an eurephia ResultMsg XML document with status of the operation.  On fatal errors,
 *         NULL is returned.
*/
static xmlDoc *useracc_update(eurephiaCTX *ctx, const int uid, eDBfieldMap *value_map) {
        dbresult *uinf = NULL;
        xmlDoc *res_d = NULL, *srch_xml = NULL;
        xmlNode *srch_n = NULL;
        xmlChar *xmluid = NULL;
        eDBfieldMap *srch_map = NULL;

        DEBUG(ctx, 21, "Function call: useracc_update(ctx, %i, eDBfieldMap)", uid);
        assert( (ctx != NULL) && (value_map != 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;
        }

        // Create an eDBfieldMap structure for the srch_map (to be used in the WHERE clause)
        xmluid = (xmlChar *) malloc_nullsafe(ctx, 34);
        xmlStrPrintf(xmluid, 32, (xmlChar *) "%ld", uid);
        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");
                eurephiaXML_ResultMsg(ctx, exmlERROR, NULL, "Failed to update user (uid %i)", uid);
        } else if( sqlite_get_affected_rows(uinf) == 0 ) {
                res_d = eurephiaXML_ResultMsg(ctx, exmlERROR, NULL,
                                              "Could not find any user account with uid %i", uid);
        } else {
                res_d = eurephiaXML_ResultMsg(ctx, exmlRESULT, NULL,
                                              "User account with uid %i is updated", uid);
        }
        sqlite_free_results(uinf);
        eDBfreeMapping(srch_map);
        xmlFreeDoc(srch_xml);
        free_nullsafe(ctx, xmluid);

        return res_d;
}


/**
 * Internal function.  Deletes a user account from the users table
 *
 * @param ctx eurephiaCTX
 * @param uid Numeric user id of user to be deleted.
 *
 * @return Returns an eurephia ResultMsg XML document with status of the operation.  On fatal errors,
 *         NULL is returned.
 */
static xmlDoc *useracc_delete(eurephiaCTX *ctx, const unsigned int uid) {
        xmlDoc *res_d = NULL;
        dbresult *res = NULL;

        DEBUG(ctx, 21, "Function call: useracc_delete(ctx, %i)", uid);
        assert( ctx != 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;
        }

        // 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 (uid %i)", uid);
                res_d = eurephiaXML_ResultMsg(ctx, exmlERROR, NULL,
                                              "Failed to delete the user account (uid %i)", uid);
        } else  if( sqlite_get_affected_rows(res) == 0 ) {
                res_d = eurephiaXML_ResultMsg(ctx, exmlERROR, NULL,
                                              "Could not find any user account with uid %i", uid);
        } else {
                res_d = eurephiaXML_ResultMsg(ctx, exmlRESULT, NULL,
                                              "User account with uid %i is deleted", uid);
        }
        sqlite_free_results(res);
        return res_d;
}


/**
 * @copydoc eDBadminUserAccount()
 */
xmlDoc *eDBadminUserAccount(eurephiaCTX *ctx, xmlDoc *qryxml) {
        xmlDoc *res_d = NULL;
        xmlNode *qry_n = NULL, *fmap_n = NULL;
        eDBfieldMap *fmap_m = NULL;
        char *mode = NULL;
        int uid;

        DEBUG(ctx, 20, "Function call: eDBadminUserAccount(ctx, xmlDoc)");
        assert( (ctx != NULL) && (qryxml != 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;
        }

        qry_n = eurephiaXML_getRoot(ctx, qryxml, "UserAccount", 1);
        if( qry_n == NULL ) {
                eurephia_log(ctx, LOG_ERROR, 0, "Could not find a valid XML for the user account request");
                return NULL;
        }
        mode = xmlGetAttrValue(qry_n->properties, "mode");
        if( mode == NULL ) {
                eurephia_log(ctx, LOG_ERROR, 0, "Invalid user account request (1).");
                return NULL;
        }

        fmap_n = xmlFindNode(qry_n, "fieldMapping");
        if( fmap_n == NULL ) {
                eurephia_log(ctx, LOG_ERROR, 0, "Invalid user account request (2).");
                return NULL;
        }
        fmap_m = eDBxmlMapping(ctx, tbl_sqlite_users, "users", fmap_n);
        assert(fmap_m != NULL);

        // Extract the value of the uid attribute in the UserAccount tag.  If not found, set value to -1.
        uid = atoi_nullsafe(defaultValue(xmlGetAttrValue(qry_n->properties, "uid"), "-1"));

        if( strcmp(mode, "view") == 0 ) {
                unsigned int flags = atoi_nullsafe(defaultValue(xmlGetNodeContent(qry_n,"extractFlags"),"0"));
                const char *sortkeys = xmlGetNodeContent(qry_n, "sortkeys");
                res_d = useracc_view(ctx, flags, fmap_m, eDBmkSortKeyString(fmap_m, sortkeys));
        } else if( strcmp(mode, "add") == 0 ) {
                res_d = useracc_add(ctx, fmap_m);
        } else if( strcmp(mode, "update") == 0 ) {
                if( uid == -1 ) {
                        res_d = eurephiaXML_ResultMsg(ctx, exmlERROR, NULL,
                                                      "Can not update user account without an uid value");
                } else {
                        res_d = useracc_update(ctx, uid, fmap_m);
                }
        } else if( strcmp(mode, "delete") == 0 ) {
                if( uid == -1 ) {
                        res_d = eurephiaXML_ResultMsg(ctx, exmlERROR, NULL,
                                                      "Can not delete user account without an uid value");
                } else {
                        res_d = useracc_delete(ctx, uid);
                }
        } else {
                eurephia_log(ctx, LOG_ERROR, 0, "UserAccount - Unknown mode: '%s'", mode);
                res_d = eurephiaXML_ResultMsg(ctx, exmlERROR, NULL, "Unknown mode '%s'", mode);
        }
        eDBfreeMapping(fmap_m);

        return res_d;
}


/**
 * Internal function.  Retrieves all administrator access levels granted.
 *
 * @param ctx   eurephiaCTX
 * @param fmap  eDBfieldMap with field values to narrow the SQL query
 *
 * @return Returns an eurephia XML document on success or with a failure message.  NULL is returned
 *         on fatal errors.
 */
xmlDoc *adminacclvl_Get(eurephiaCTX *ctx, eDBfieldMap *fmap) {
        dbresult *res = NULL;
        int last_uid = -1, i = 0;

        xmlDoc *doc = NULL;
        xmlNode *root_n = NULL, *rec_n = NULL, *acl_n = NULL, *tmp_n;

        DEBUG(ctx, 21, "Function call: adminacclvl_Get(ctx, {fieldMapping})");
        assert( (ctx != NULL) && (fmap != 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;
        }

        // Query the database, find the user defined in the user map
        res = sqlite_query_mapped(ctx, SQL_SELECT,
                                  "SELECT eac.uid, username, interface, access"
                                  "  FROM eurephia_adminaccess eac"
                                  "  LEFT JOIN openvpn_users USING(uid)",
                                  NULL, fmap, "uid, interface, access");
        if( res == NULL ) {
                eurephia_log(ctx, LOG_ERROR, 0, "Error querying the database for a access levels");
                return eurephiaXML_ResultMsg(ctx, exmlERROR, NULL,
                                             "Error querying the database for a access levels");
        }

        eurephiaXML_CreateDoc(ctx, 1, "admin_access_list", &doc, &root_n);
        for( i = 0; i < sqlite_get_numtuples(res); i++ ) {
                if( last_uid != atoi_nullsafe(sqlite_get_value(res, i, 0)) ) {
                        // Create a new block element when we get a new uid
                        rec_n = xmlNewChild(root_n, NULL, (xmlChar *) "user_access", NULL);
                        last_uid = atoi_nullsafe(sqlite_get_value(res, i, 0));

                        tmp_n = sqlite_xml_value(rec_n, XML_NODE, "username", res, i, 1);
                        sqlite_xml_value(tmp_n, XML_ATTR, "uid", res, i, 0);

                        acl_n = xmlNewChild(rec_n, NULL, (xmlChar *) "access_levels", NULL);
                }

                tmp_n = sqlite_xml_value(acl_n, XML_NODE, "access", res, i, 3);
                sqlite_xml_value(tmp_n, XML_ATTR, "interface", res, i, 2);
        }
        sqlite_free_results(res);
        return doc;
}


/**
 * @copydoc eDBadminAccessLevel()
 */
xmlDoc *eDBadminAccessLevel(eurephiaCTX *ctx, xmlDoc *qryxml) {
        dbresult *sqlres = NULL;
        xmlDoc *res_d = NULL;
        xmlNode *qry_n = NULL, *fmap_n = NULL;
        eDBfieldMap *fmap_m = NULL;
        char *mode = NULL;

        DEBUG(ctx, 20, "Function call: eDBadminAccessLevel(ctx, xmlDoc)");
        assert( (ctx != NULL) && (qryxml != 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;
        }

        qry_n = eurephiaXML_getRoot(ctx, qryxml, "admin_access", 1);
        if( qry_n == NULL ) {
                eurephia_log(ctx, LOG_ERROR, 0, "Could not find a valid XML for the user-certs link request");
                return 0;
        }
        mode = xmlGetAttrValue(qry_n->properties, "mode");
        if( mode == NULL ) {
                eurephia_log(ctx, LOG_ERROR, 0, "Invalid edit admin access request (1).");
                return 0;
        }

        fmap_n = xmlFindNode(qry_n, "fieldMapping");
        if( fmap_n == NULL ) {
                eurephia_log(ctx, LOG_ERROR, 0, "Invalid edit admin access request (2).");
                return 0;
        }

        fmap_m = eDBxmlMapping(ctx, tbl_sqlite_eurephiaadmacc, NULL, fmap_n);
        assert(fmap_m != NULL);

        if( strcmp(mode, "grant") == 0 ) {
                sqlres = sqlite_query_mapped(ctx, SQL_INSERT, "INSERT INTO eurephia_adminaccess",
                                          fmap_m, NULL, NULL);
                if( sqlres && (sqlite_get_affected_rows(sqlres) > 0) ) {
                        res_d = eurephiaXML_ResultMsg(ctx, exmlRESULT, NULL,
                                                      "Access level %s (%s) was granted to uid %s",
                                                      eDBmappingGetValue(fmap_m, FIELD_ACCESSLVL),
                                                      eDBmappingGetValue(fmap_m, FIELD_INTERFACE),
                                                      eDBmappingGetValue(fmap_m, FIELD_UID));
                }
        } else if( strcmp(mode, "revoke") == 0 ) {
                sqlres = sqlite_query_mapped(ctx, SQL_DELETE, "DELETE FROM eurephia_adminaccess",
                                          NULL, fmap_m, NULL);
                if( sqlres && (sqlite_get_affected_rows(sqlres) > 0) ) {
                        const char *uid = eDBmappingGetValue(fmap_m, FIELD_UID);
                        const char *acclvl = eDBmappingGetValue(fmap_m, FIELD_ACCESSLVL);

                        if( acclvl != NULL ) {
                                res_d = eurephiaXML_ResultMsg(ctx, exmlRESULT, NULL,
                                                              "Access level %s (%s) was revoked from uid %s",
                                                              acclvl, eDBmappingGetValue(fmap_m, FIELD_ACCESSLVL),
                                                              uid);
                        } else {
                                res_d = eurephiaXML_ResultMsg(ctx, exmlRESULT, NULL,
                                                              "%i access levels was removed from uid %s",
                                                              sqlite_get_affected_rows(sqlres), uid);
                        }
               }
        } else if( strcmp(mode, "list") == 0 ) {
                res_d = adminacclvl_Get(ctx, fmap_m);
        }

        if( res_d == NULL ) {
                eurephia_log(ctx, LOG_ERROR, 0, "Failed to update admin access");
                res_d = eurephiaXML_ResultMsg(ctx, exmlERROR, NULL, "Failed to complete %s operation", mode);
        }
        if( sqlres ) {
                sqlite_free_results(sqlres);
        }
        eDBfreeMapping(fmap_m);

        return res_d;
}