PHP message: WordPress database error CREATE command denied to user ... for table ...

[assarbad]

Hi,

tried installing this plugin in order to get around the issues with U2F and also in the hopes to get my FIDO2 keys to work. It broke my site until I disabled it again.

When I merely enabled it, everything seemed fine, except that under "Profile" I didn't see any 2FA-related settings anymore.

But the plugin list showed this plugin active and there was also a related setting tab that worked. So I logged off in order to test it and was met with a "critical error" message.

Digging into the server logs I saw the error message mentioned above.

I realize this is owed to the fact that I run a somewhat stricter regime than is perhaps usual for WordPress sites and I also know how to get around the issue with table creation.

However, a slightly more graceful failure would be nice (such as being able to show roughly what is wrong, i.e. table creation without perhaps mentioning too many DB details). And perhaps fail earlier. I.e. when I activate the plugin, not when I try to log back in.

Thanks!

[assarbad]

The recovery email I received, contained the following (just in case this helps):

Error Details
=============
An error of type E_ERROR was caused in line 56 of the file /srv/www/mysite/blog/wp-content/plugins/two-factor-provider-webauthn/inc/class-webauthn-user.php. Error message: Uncaught UnexpectedValueException: Unable to save the user handle to the database. in /srv/www/mysite/blog/wp-content/plugins/two-factor-provider-webauthn/inc/class-webauthn-user.php:56
Stack trace:
#0 /srv/www/mysite/blog/wp-content/plugins/two-factor-provider-webauthn/inc/class-webauthn-credential-store.php(102): WildWolf\WordPress\TwoFactorWebAuthn\WebAuthn_User->generate_and_save_handle()
#1 /srv/www/mysite/blog/wp-content/plugins/two-factor-provider-webauthn/inc/class-webauthn-provider.php(162): WildWolf\WordPress\TwoFactorWebAuthn\WebAuthn_Credential_Store::get_user_keys()
#2 /srv/www/mysite/blog/wp-content/plugins/two-factor/class-two-factor-core.php(444): WildWolf\WordPress\TwoFactorWebAuthn\WebAuthn_Provider->is_available_for_user()
#3 /srv/www/mysite/blog/wp-content/plugins/two-factor/class-two-factor-core.php(1699): Two_Factor_Core::get_available_providers_for_user()
#4 /srv/www/mysite/blog/wp-includes/class-wp-hook.php(324): Two_Factor_Core::user_two_factor_options()
#5 /srv/www/mysite/blog/wp-includes/class-wp-hook.php(348): WP_Hook->apply_filters()
#6 /srv/www/mysite/blog/wp-includes/plugin.php(517): WP_Hook->do_action()
#7 /srv/www/mysite/blog/wp-admin/user-edit.php(876): do_action()
#8 /srv/www/mysite/blog/wp-admin/profile.php(18): require_once('...')
#9 {main}
  thrown

[sjinks]

If you have access to phpMyAdmin or a similar tool:

CREATE TABLE wp_2fa_webauthn_users (
    user_id bigint(20) unsigned NOT NULL,
    user_handle varchar(128) NOT NULL,
    PRIMARY KEY  (user_id),
    UNIQUE KEY user_handle (user_handle)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;

CREATE TABLE wp_2fa_webauthn_credentials (
    id bigint(20) unsigned NOT NULL auto_increment,
    user_handle varchar(128) NOT NULL,
    credential_id varchar(767) CHARSET ascii COLLATE ascii_bin NOT NULL,
    public_key varchar(1024) NOT NULL,
    counter int(11) NOT NULL,
    name varchar(255) NOT NULL,
    added int(11) NOT NULL,
    last_used int(11) NOT NULL,
    u2f tinyint(2) NOT NULL,
    PRIMARY KEY  (id),
    UNIQUE KEY credential_id (credential_id),
    KEY user_handle (user_handle)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;

Please replace wp_ with your prefix ($table_prefix variable from wp-config.php), DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci with what global $wpdb; echo $wpdb->get_charset_collate(); returns.

[sjinks]

However, a slightly more graceful failure would be nice (such as being able to show roughly what is wrong, i.e. table creation without perhaps mentioning too many DB details).

Unfortunately, this is handled by WordPress; I don't think I have any control there :-(

And perhaps fail earlier. I.e. when I activate the plugin, not when I try to log back in.

This I hope I can do, thanks!

[assarbad]

However, a slightly more graceful failure would be nice (such as being able to show roughly what is wrong, i.e. table creation without perhaps mentioning too many DB details).

Unfortunately, this is handled by WordPress; I don't think I have any control there :-(

Oh, I didn't realize.

And perhaps fail earlier. I.e. when I activate the plugin, not when I try to log back in.

This I hope I can do, thanks!

Would be cool. But I guess whoever runs runs a configuration the way I do, knows how to deal with these issues either way. It's just that it seemed fine until I logged off and attempted to log back on.