Easily resets a users TOTP status in a Guacamole My/MariaDB to false to enable them to scan a new QR code

create_totp_reset_procedure_guacamole.sql

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `reset_totp`(IN userName varchar(128))
BEGIN
UPDATE `guacamole_user_attribute` 
SET 
    `attribute_value` = 'false'
WHERE
    (user_id = (SELECT 
            user_id
        FROM
            (SELECT 
                `guacamole_entity`.`name` AS `name`,
                    `guacamole_user_attribute`.`user_id` AS `user_id`,
                    `guacamole_user_attribute`.`attribute_name` AS `attribute_name`,
                    `guacamole_user_attribute`.`attribute_value` AS `attribute_value`
            FROM
                (`guacamole_entity`
            LEFT JOIN `guacamole_user_attribute` ON ((`guacamole_entity`.`entity_id` = `guacamole_user_attribute`.`user_id`)))
            WHERE
                (`guacamole_user_attribute`.`attribute_name` = 'guac-totp-key-confirmed')) AS totp
        WHERE
            name = userName))
        AND (`attribute_name` = 'guac-totp-key-confirmed');
END$$
DELIMITER ;

Hi Kyle,

The above procedure does not work, you need to find the user attributes via the user table and not directly from entity.

Cheers, Edwin

Corrected procedure:
DELIMITER $$
CREATE DEFINER=root@localhost PROCEDURE reset_totp(IN userName varchar(128))
BEGIN
UPDATE guacamole_user_attribute
SET
attribute_value = 'false'
WHERE
(user_id = (SELECT
user_id
FROM
(SELECT
guacamole_entity.name AS name,
guacamole_user_attribute.user_id AS user_id,
guacamole_user_attribute.attribute_name AS attribute_name,
guacamole_user_attribute.attribute_value AS attribute_value
FROM
(guacamole_entity
INNER JOIN guacamole_user ON (guacamole_user.entity_id = guacamole_entity.entity_id)
INNER JOIN guacamole_user_attribute ON (guacamole_user_attribute.user_id = guacamole_user.user_id)
)
WHERE
(guacamole_user_attribute.attribute_name = 'guac-totp-key-confirmed')) AS totp
WHERE
name = userName))
AND (attribute_name = 'guac-totp-key-confirmed');
END$$
DELIMITER ;