I will be showing you on how to grant MySQL privileges to client machines to access databases.
So, let’s get started.
- You must have a MySQL Server installed in your computer. If you don’t have, go to MySQL website and download the MySQL Server.
- We also need the PhpMyAdmin for MySQL user interface or you can choose your own like SQLyog, etc. (This is included in the WAMPServer)
- To save your time installing and configuring the MySQL Server, simply download and install the WAMP Server. It is free of charge and can be downloaded at their website WampServer. Windows Apache MySQL Server Php
In this stage, I assume you already installed the WAMPServer or MySQL Server. In my case, I installed the MySQL Server 5.5 and PhpMyAdmin and the Apache Server(separate install). But if you installed WAMP, it’s ok because there’s no big difference.
1. Open your MySQL Server. If you installed this manually, hit start orb->type in ‘services‘ in the searchbox and select the Services in the search results. Now, scroll down until you find the MySQL and right-click ‘Start‘. If you’re using WAMPServer, just fire it up and it will run the services.
2. Now, let’s open the MySQL Command Line/Console.
3. A command prompt will appear and type in your password. If your root(default) has no password, just hit enter.
NOTE: In case you have another user account created and has password, you can type in its password or use the root password. MySQL will detect the user and privilege so nothing to worry.
In my case, I set a password to the root so I’m going to type in the root password.
4. Now, type in this code:
GRANT ALL PRIVILEGES ON databasename.* TO username @ ‘%oripaddress_of_computerorlocalhost‘ IDENTIFIED BY ‘password’;
Example: GRANT ALL PRVILEGES ON mydatabase.* TO yanyan @ ‘%’ IDENTIFIED BY ‘yanyanpassword’;
Simple explanation on code :
GRANT ALL PRIVILEGES – add,update,delete etc.
mydatabase.* – the database that will be using.
* (ALL) – means all access to the tables in the database. In this part of the code, only the database specified will be accessed. If you want all the database be accessed by other machines, then change it to *.*
username – you can create a new user, just type in a username (ex.yanyan) or you can use root (which makes another account with root username) . You can set your username to root but I will not recommend you to use the root because root is the default user (it will be confusing in your part because you’ll be having a new user with the same username).
% – makes the database, username, password available to all host/computer in the network.
192.168.0.110 (using IP Address) – using this line/code, you are limiting which computer can connect to the specified mysql database. In this part, I put 192.168.0.110 so only the computer with this ip address can connect to specified mysql database. (I’m expecting that computers in your network have a fixed ip address). I do not recommend you to do this but I just have to add in this tutorial for you to know that IP Addresses work.
localhost – as we all know, localhost is your local machine.
Now, you can choose either of the three.
password – of course, your account password (ex.yanyanpassword).
Now, let’s try.
Here’s my code : GRANT ALL PRIVILEGES ON itrmcdb.* TO yanyan @’%’ IDENTIFIED BY ‘yanyanpassword’;
After that, we need to refresh the privileges. Type these line : FLUSH PRIVILEGES;
And finally, type exit to end.
Now we’ve done on granting privileges. What we’re going to do is to check.
Open your browser and type in http://localhost/phpmyadmin
and login as root to check if the user has been created. Go to Privileges
tab and you’ll see the user account.
If you find this post helpful, please do share and like this. Also, feel free to post comments and suggestions.