Best practice for WordPress MySQL permissions says to limit the database user WordPress uses to connect to the database to have only SELECT, INSERT, UPDATE, and DELETE privileges. Some also argue that CREATE, ALTER, DROP, and INDEX are needed to allow for updates to WordPress and/or plugins, as well as for uninstalls of plugins to run. Unfortunately, there’s not much good documentation around this.
I ran across this little nugget tucked away in the WordPress Codex, in the Debugging a WordPress Network page, just below the “If new site creation suddenly stopped working” section, under the “Other lesser-known issues” subheader:
Check that the database user has ALL permissions on the database.
So that’s why it didn’t fully populate the new site in the database… ouch. My new best practice will be to switch to my full admin-rights database user (temporarily) in the wp-config.php file before trying to create my next site in the network.
Now on to figuring out the best way to repair the issue I have where the new site exists in the wp_blogs table, but it didn’t create all the wp_##_ tables for the new site.