Related

Product Now Available: Managed MySQL Databases Product
Bulk insert to MySql Managed Database via LOAD DATA LOCAL INFILE Question Question
Tableau is unable to connect to Digital Ocean Managed Database Question Question

Question

Native authentication user - grant access to a single database

Posted February 26, 2020 955 views
MySQLDatabasesDigitalOcean Managed MySQL 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,

Add a comment
By:
goetzm
edited by AHA

Related

Product Now Available: Managed MySQL Databases Product
Bulk insert to MySql Managed Database via LOAD DATA LOCAL INFILE Question Question
Tableau is unable to connect to Digital Ocean Managed Database Question Question

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
bobbyiliev March 31, 2020

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

How To Install WordPress with a Managed Database on Ubuntu 18.04
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.
Reply Report
  • goetzm March 31, 2020

    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

    Reply Report
Submit an Answer

Related

Looking for something else?

Ask a new question Search for more help