I developed a web app in which each company that registers on the app gets its own database with its own mysql-user credentials. Is this approach fine OR can I use one master mysql-user account to connect to and administer all the databases of various companies in my web app? The companies don't care how I have implemented the backend.
Mysql – Each thesql database with its own user
database-designMySQL
Related Solutions
Since you are connecting through a SSH tunnel, this means that the MySQL port 3306 from us-east-1.amazonaws.com is being opened locally on your computer. The ip address of your computer is 127.0.0.1 or localhost. When you connect to the mysql server on us-east-1.amazonaws.com, you're actually accessing it via 127.0.0.1, i.e. your computer. If you had another tunnel open, or MySQL running locally on your computer, then it may be that other MySQL server that's rejecting your authentication attempts
There are some tests you can try :
1. What ports is you Windows computer listening on
From a command prompt : netstat -a (lists all the ports that are open)
In linux it would be : netstat -tlpn
2. Basic connectivity test
From a DOS command prompt or linux console : telnet 127.0.0.1 3306
If you get a time out, or some other program responds then your tunnel isn't setup correctly.
3. Change the port number that MySQL Workbench is opening locally
We're assuming that MySQL workbench is creating the tunnel on your computer. If so, in MySQL workbench, try tunneling through another port number like 9000.
Make sure that 9000 wasn't listed as an open port from : netstat -a
If you have ssh access to us-east-1.amazonaws.com
4. Try connecting to MySQL from us-east-1.amazonaws.com
mysql -u myuser -h 127.0.0.1 -p
And as Rolando said, you'll want to verify that you are connecting with the right credentials. For example, if you are connecting as myuser@127.0.0.1 and you have a user myuser without a host, you probably won't be able to connect using user@127.0.0.1.
I think using a MySQL gui tool like SQLyog would solve your problem. It has User manager tool where you can set privilege to different users.
Best Answer
I would opt for different users accounts for each database.
If you implement the logon module seperately from the rest of the application, you have something that looks more like a portal to individual provisioned environments. That leaves you with more options in the future.
The only drawback I can think of is that this could lead to many database connections, not sure when that is a performance killer.
Having one connection to the database (and thus one session), means you have to switch between databases while handling requests from different companies. You should investigate how this affects performance as well.