0

I need to give a remote user access to a MySQL database. I also need to limit the permissions they have to the MySQL database tables. The current database contains approx. 50+ tables so rather than revoke permissions table by table, I'm trying to give the user access with no privileges and then add those that are needed. What I've done appears to work when:

  1. I'm logged in as root (via SSH)
  2. I login using "mysql -u username -p" - enter my password for the newly created user.

This users account honors all the privileges I've set up. However, when this user tried to connect via their desktop client - they can connect but can not view the database. When using "use databasename" they get this error:

Error Code: 1044. Access denied for user 'username'@'%' to database 'database_name'

Not sure if this helps but I'm using Media Temple DV w/ Plesk. I've first added the user. Then I SSH in and revoked ALL privileges. Then I added the privileges for the user and did command "flush privileges". Nothing is working. As I said, if I'm accessing via the command line (after SSHing in as root) the permissions are honored and I can access the database - just not remotely.

Any help would be greatly appreciated.

ScoobaSteve
  • 543
  • 1
  • 5
  • 19
  • If you have access check: select User, Host from mysql.user; There you will see from what hosts this user can log into mysql – xto Jan 11 '16 at 22:27

2 Answers2

0

May be network that is allowed to connect is localhost, but not users network IP. And check max connections amount.

Lev Bystritskiy
  • 159
  • 1
  • 7
0

If you're logged in as root via SSH and MySQL database is on the same machine, then i guess that if you run:

mysql -u username -p

you try to connect to MySQL as 'username'@'localhost' user. If you want to connect to MySQL database from outside of this server, you need to:

  • create user like 'username'@'%' which allow to connect to MySQL using 'username' from anywhere ('%')
  • allow mysql server to connect from outside by setting bind-address more details here
xto
  • 406
  • 2
  • 8
  • I tried this (connecting as the user using mysql -u username -p) and that works - when I'm SSHed in. It's when I connect using a remote client - like MySQL Workbench) when it doesn't. Because I'm using Plesk I first create the user in Plesk, then revoke all privileges then manually add them back (i.e. USAGE, SELECT, INSERT, UPDATE, etc.). There's dozens and dozens of tables so want to revoke all at first and allow specific access to a very select few tables. – ScoobaSteve Jan 11 '16 at 23:38
  • But it is not about privileges. It's about user that you want to connect with. When you're SSHed and try to connect to local MySQL then you connect as 'user'@'localhost'. This user is allowed to connect only from localhost - from server you are SSHed. If you want to connect from outside, via Workbench for example, you need to create user 'user'@'%' and grant all privileges to that user. – xto Jan 12 '16 at 06:43
  • select current_user(); - you can check what user you are after login – xto Jan 12 '16 at 06:44
  • select User, Host from mysql.user; - you can check what users are created in your database, and from what host they can log in – xto Jan 12 '16 at 06:44