Question

Native authentication user - grant access to a single database

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,

Subscribe
Share

Submit an answer
You can type!ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!

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.

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