Allow Remote Access Of Specific MySQL Database Tables | Views

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 mysql> prompt.

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 hosts and 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 testUser.

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 mysql> prompt.

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 mysql> prompt.

flush privileges;

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 testUser is 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 testUser password.

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…