Late last year, my company (Intelligent Metering System) starts a new project on smart meters. I won’t go in the detail of smart meters because some of you might not be heard of how it works internally. So, the project is about to build an MDC (Meter Data Collector). The purpose of MDC is to fetch data from multiple smart meters periodically and stores the data inside the mysql database.
Recently, we have completed our project successfully and installed in one of our client’s system. But before installation, we have met this problem where we need to give mysql database access to our client. So, we don’t want to give the whole database access instead to give access only some specific tables of a database. Because the client only needs to know about smart meters data, not about the whole application data.
In this blog post, I’ll focus on how to grant some specific tables remote access when working with mysql database on Linux or Unix-like operating system. Before you begin, you must consider the following prerequisites:
- Install mysql server on the database server.
- Start the mysql service via
sudo systemctl start mysql.service.
- Open mysql and create a database instance which table access you want to grant.
- Allow the remote access to the mysql database server by editing the .cnf. You can see this link for enabling the remote connection in the mysql database server.
Below is the example of my complete mysql database. You can create your own database or use your existing database.
Create a new mysql user for remote database access
Now in order to grant some specific database tables access, we need to create a new user (with no grant options) from which our client can access only the exposed tables. First, let’s create a new mysql user with the following query at
Please note the
% sign in the above command refers to any host. It means our
testUser client can connect to mysql database server via any Ip address.
After the creation of
testUser let’s see all mysql user’s
username with the following query.
Grant some specific mysql database tables access to testUser
Before granting remote database tables access lets see the current grants for
testUser with the below query.
You see currently there are no database table grants for the
If you guys have seen the above
mdc_collector database then you know there are a total of ten tables and we only need to give remote access to some specific tables. Now let’s say we want to give access to
testUser only billing_data, monthly_billing_data, and load_profile_data tables instead of the complete database. To exposed database tables enter the following commands at
After giving remote access to mysql database tables let’s see the selected grants for
testUser with the following query.
You see the
testUser only has remote access to three tables instead of the complete mdc_collector database. And finally, you may also need to run flush privileges on
Verify remote database table access on a client system
Note: I already told you that in the prerequisites section that the remote connection of mysql DB only works if you’ve enabled the incoming connections in the mysql server machine. You can see this link for reference.
Now if you guys go to your personal machine or any client machine and try to connect the mysql server via any mysql client like the workbench you’ll see the mdc_collector database with only three tables in it. The following shows the connection with workbench.
The connection has been successfully made with the mysql database server. Now test the database tables.
You can also test the remote database access to mysql server via cmd. If and only if you’ve installed the mysql package inside the client machine.
The above picture is my client machine where I successfully login into mysql server database via remote access.
- -u testUser: The
testUseris our mysql user which we have created inside the server machine.
- -h 192.168.1.192: Ip-address or hostname where the mysql database server is hosted.
- -p: Asks for the
Now let’s verify that our client user has only three tables remote access instead of complete mysql database.
Alright, guys, this was all from this blog. Anything I miss, feel free to let me know using the comments section.
Thank you for being here and keep reading…
Disclosure of Material Connection: Some of the links in the post above are “affiliate links.” This means if you click on the link and purchase the item, we will receive an affiliate commission. Regardless, we only recommend products or services we use personally and believe will add value to our readers.