Hi guys,

I’m using the DO database cluster with MySQL and want to power some simple databases for example WordPress.

I created a new user and database in the control panel and changed it to native authentication:

ALTER USER myusername IDENTIFIED WITH mysql_native_password BY 'USERPASSWORD';

So far it works very well. But the user has in this case access to almost every database in the cluster so I removed this and add it specific:

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, CREATE VIEW, EVENT, TRIGGER, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EXECUTE ON `mydatabasename`.* TO 'myusername'@'%';

Also if I login with another user in phpMyAdmin I also can see the access of this user but it doesn’t work and this users also doesn’t the the called database in the phpMyAdmin.

Which part I have missed?
Thanks in advance!

Best regards,

edited by AHA

These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.

×
1 answer

Hi there @goetzm,

What I could suggest in this case is to create a new separate database for your Wordpress website and then manually create a user with privileges only to that specific database.

That way the user would be restricted and would not have access to your whole Managed Database Cluster.

To do that you can run the following commands:

  • First connect to your Managed Database Cluster

  • Then create a database:

CREATE DATABASE wordpress DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
  • After that create a new user with mysql_native_password:
CREATE USER 'wordpressuser'@your_server_ip IDENTIFIED WITH mysql_native_password BY 'password';
  • Finally grant privileges for that user only to the specific database:
GRANT ALL ON wordpress.* TO 'wordpressuser'@your_server_ip;

For more information, I would also recommend going through this step by step tutorial on how to install WordPress with a Managed Database:

https://www.digitalocean.com/community/tutorials/how-to-install-wordpress-managed-database-ubuntu-18-04

Hope that this helps!
Regards,
Bobby

by Mark Drake
WordPress is the most popular CMS (content management system) on the internet. It allows you to easily set up flexible blogs and websites on top of a MySQL backend with PHP processing. In this guide, we'll focus on getting a WordPress instance set up on a managed MySQL database on an Ubuntu 18.04 server.
  • Hi Bobby,

    thanks a lot for your reply.
    I have no Idea why this doesn’t work in the past, but now it works.
    Maybe, because I change one singe topic:

    ‘mydbuser’@'mydbcluster.db.ondigitalocean.com’;
    to
    'mydbuser’@’%’;

    However, thanks a lot for your help!

    Regards,
    Michael

Submit an Answer