permission error when connecting to mysql  from remote location

adbyits
adbyits used Ask the Experts™
on
hi all i have almost got my deployment server ready work but anytime i try to so anything on mysql wordbench from remote i am getting a error
Executing:
CREATE SCHEMA `flightdata` ;

Operation failed: There was an error while applying the SQL script to the database.
ERROR 1044: Access denied for user 'root'@'%' to database 'flightdata'
SQL Statement:
CREATE SCHEMA `flightdata`




can someone please help
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Distinguished Expert 2017

Commented:
root is commonly restricted to localhost only.

it seems you may have created another user root@% but it does not seem this user has the requisite rights

You should reconnect on the localhost and make sure the rights you grant root@% includes creation of DB/schemas ..
https://dev.mysql.com/doc/refman/8.0/en/grant.html

grant ALL  on *.* TO 'root'@'remote_system'
flush privileges
to make the change effective. never expose an administrative user to anyhost ('%') always limit/restrict it to access from a specific host.
You could setup phpmyadmin on the mysql server if needed.
David FavorFractional CTO
Distinguished Expert 2018

Commented:
If you use a sensible Distro with sensible database packages, all this is handled for you.

For example, if you install Ubuntu Bionic (LTS - Long Term Service version), then use standard MariaDB Installer the packaging system will arrange for the root Linux user to also be the MariaDB root user.

In other words, your initial root database user is generally handled, either easily/seamlessly or poorly, depending on the Distro + package installer used.

As arnold mentioned, you must actually login a your Linux root user to make changes to system level processes like running database root user commands.

Depending on your hosting, this may or may not be possible.

Likely good starting point will be to open a ticket with your hosting company or if you do your own machine admin, you'll ssh into your machine as root or su/sudo to root after login, before interacting with your database subsystem.

Author

Commented:
thanks mate when i ru nthat i get a error ERROR 1133 (42000): Can't find any matching row in the user table
Distinguished Expert 2017
Commented:
Presumably you reference the grant, you first need to create the user 'username'@'remote_system' with password=...
Then you grant the users the rights needed.

If you gave workbench or phpmyadmin on the mysql server, you can use it to setup the user for the remote_system and grant it the rights you wish or that user needs.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial