0

Please forgive me if the title is not correct. I am still trying to understand how this works.

I have installed MySQL workbench and the MySQL server on my laptop. I have successfully created a database that store driver details, as per the picture.

I can access this info only one my laptop. However, I would also like to access this info from other PC's. I was thinking of having the main database in a secured room and then connecting about 3 additional PCs to the main database.

I have also written a simple c# script on Visual Studio that runs on my main laptop where the database is stored. This script just allows a user to enter his username and password and then displays the contains of the database (the table in the database) on a datagrid view. I have included the SQL injection code to prevent unwanted characters. This application also allows the user to insert, update, and delete info from the database provided the user that logs in has these privileges granted.

Now I would also like to run this application on the other 3 PC's, but obviously I have a problem. This problem is these PC's cannot access the main database. The main database user has privileges that allow him to alter the database but the other uses that are located in the access points to do not have this privilege. All they can do it enter their username and password and see the data from the database been displayed on the datagrid view.

So my question is how do I grant access to these other three PC's to access the main database? MySQL Workbench and MySQL server is so far only installed on my laptop and not on the other 3 PC's.

Picturial view of application

Database users

Error message

Shadow
  • 33,525
  • 10
  • 51
  • 64
David
  • 39
  • 5
  • The PC does not need granting access. You need to define the User Accounts to be allowed to connect from different domains than the PC running the Database. – RiggsFolly Sep 07 '20 at 12:13
  • @RiggsFolly "You need to define the User Accounts to be allowed to connect from different domains than the PC running the Database." I have no idea how to do this. All I know is how to create a database, tables and then enter info into those tables. – David Sep 07 '20 at 12:15
  • Then that is where your research should start, maybe a look [at this tutoria](https://www.mysqltutorial.org/mysql-create-user.aspx) will get you started – RiggsFolly Sep 07 '20 at 12:17
  • @RiggsFolly Oh I did that. Did not know that was what it was called. See picture. I have included the % sign because I have read if you want to connect from other PC that what you should do. But it does not allow me to connect to the main laptop. – David Sep 07 '20 at 12:22
  • So you get an error message I assume? Can we see it?? – RiggsFolly Sep 07 '20 at 12:23
  • @RiggsFolly Yes! The IP address of my laptop was entered into the second PC as the hostname. My laptop and PC1 is connected to the same network via WiFi – David Sep 07 '20 at 12:39

1 Answers1

0

The entire point of database technology is to allow multiple clients to access the same server and share the same data. (Actually there's another point: the ability to handle vast amounts of data. But that's not your problem right now.)

Here's what you need to do.

  1. Get MySQL server software set up on a server in your server room. Find out the hostname of that server.
  2. Log in to that server using MySQL Workbench. It asks you for the hostname.
  3. Use Workbench to migrate your data from your localhost MySQL server to the one in the server room. Workbench has decent features to help you do that.
  4. Create a mysql account on the shared server that just has access to the database you just created. Keep in mind that MySQL users look like this: 'david'@'localhost' or 'david'@'*' or 'mickey'@'*.animation.disney.com'. That is, they specify both the username and the machine the user runs on.
  5. Change your C# connection string to mention the shared server's hostname and the account you just created.
myConnectionString = "server=shared.example.com;uid=root;pwd=12345;database=test";

Somehow tell the other users of your app to use this connection string.

There's another, simpler but less robust, way to do all this. Make sure your laptop has its own hostname. I dunno, david.example.com maybe? Ask your local LAN or VPN administrator. Or just use your laptop's IP address in place of a hostname. Then put that hostname into your connection string. Then other users of your application can hit the MySQL server on your laptop. But, if you switch off your laptop and take it home they'll lose access.

O. Jones
  • 103,626
  • 17
  • 118
  • 172
  • Just for clarification. I am a not a database expert just starting learning this now. "Use Workbench to migrate your data from your localhost MySQL server to the one in the server room. Workbench has decent features to help you do that." By server room you mean the server located in the Accesspoints (1 to 3)? – David Sep 07 '20 at 12:33
  • You wrote "I was thinking of having my main server located in a secured room." I'm talking about that server. The things you called "access points" in your diagram don't contain their own servers. They are connections to your main server. I suggest you stop calling them "access points" and start calling them "connections" to make your diagram comprehensible. (Unless you want replicas of your main database on each user's machine. But you need a lot of database skills to do that, and it doesn't gain you much.) – O. Jones Sep 07 '20 at 12:49
  • Database migration with Workbench. https://www.mysql.com/products/workbench/migrate/ – O. Jones Sep 07 '20 at 12:52
  • Thank you noted. I am not in any company just a kid learning new stuff. So I got access to everything. No need to go ask any admin guy. So I typed in hostname in my laptop and the cmd returned the hostname of the laptop (Hostname was DESKTOP78-987P). I then entered this in the connection string as you did. I then ran that application on my PC1 and entered the username and password. When I click connect a error comes up saying "Unable to connect to any of the specified MySQL hosts." PC1 user in my laptop was granted all privileges. – David Sep 07 '20 at 12:56
  • I am also using my laptop as a server. Cannot afford to buy a server so I am using my laptop as the server. – David Sep 07 '20 at 12:59
  • Gotcha. Here's the thing: A MySQL server is a kind of TCP/IP (Internet) server. As you know every machine connected to a router has its own IP address. All machines happen to share the hostname `localhost` and the IP address `127.0.0.1`; those mean "this machine" or more formally, "loopback." So when you develop with your local machine you use one of those addresses. – O. Jones Sep 07 '20 at 13:15
  • But if you want software on SOME OTHER MACHINE ON YOUR NETWORK to hit that same MySQL server, you have to tell that software YOUR machine's tcp address or DNS hostname. You can find out your own Windows machine's IP address by saying `ipconfig` to a `cmd` prompt then looking for the IPv4 address in the output. Something like `192.168.0.999`. Windows' `hostname` command is worthless unless you're in an enterprise. And, most home routers don't assign DNS hostnames to the machines they serve. So use the IP address. Welcome to the wonderful world of networking. – O. Jones Sep 07 '20 at 13:15
  • I changed the script to server=192.168.XX.XXX. This IP address is the IP address of the laptop where the server is installed. I then opened the application on PC1 and got the same error. Why is this so hard? If both and laptop and PC1 are on the same network why is it given my this error..... – David Sep 07 '20 at 13:28
  • Forgive my language, but my stupid firewall was blocking. The wonderful world of networking. This sarcasm is an understatement. Lol. – David Sep 07 '20 at 13:54
  • You don't have many friends in the wonderful world of networking. `ping` is a steady friend. – O. Jones Sep 08 '20 at 10:45