Having set up the MySQL server and created an application database, I wanted to try scaling it over a small network – basically a couple of laptops and a router. Hopefully I can build a much larger service on this, and there are some ideas I have for turning my sofware development and malware analysis hobbies into a career.
So far, I’ve been experimenting with MySQL using the root account as default. This account needed a stronger password, plus it’s bad practice to log into the server’s admin account from a client machine for a number of reasons. Remember this is not the host system’s root account:
$mysqladmin -u root -p password
It will prompt for the current password and ask the user to confirm the new one. By examining the main mysql database where the user account details are stored, you’ll notice the account passwords are hashed and salted. As the account details are stored in its own database table, the system could potentially be scaled to accommodate many thousands of users.
Next, login to the MySQL server and use the ‘FLUSH PRIVILEGES;‘ command.
Since we don’t want anyone except the administrator to alter the server or the other databases, another shared account will be created for the application’s users. This could be done in the command line, but I’ve used MySQL Workbench, to ensure all the permissions were properly set.
The New Schema Privilege Definition restricts the account to just one database (nexus).
The settings under the Schema Privileges tab define whether the account can perform certain admin operations or modify the database in certain ways.
Even if there was an SQLi attack, proper configuration should minimise the impact. For example, it shouldn’t be possible to use SQLi within the application to modify the users table in the mysql database, as the application itself wouldn’t have access to it.
The new account setup can be tested from the LibreOffice front-end (see my last post). Only the application database and its tables should appear:
While on the subject of security, it’s important to check that port 3306 is closed on the perimeter firewall. The server should never be directly accessible from outside the network.
I had to make do with the LibreOffice .odb file to simulate the deployed front-end application on the client machine for now, as I’m still working on the Java UI. The .odb file can be ported to other hosts on the network and configured for a remote connection to the MySQL server.
Initially I had problems getting a connction from wihin LibreOffice, so I checked that libreoffice-mysql-connector, JDBC and ODBC were installed. These are available in the package manager. When the connection attempts were still unsuccessful, I decided to troubleshoot this methodically. The problem was something outside LibreOffice.
Running ping showed the server is reachable, so it wasn’t a routing problem. The next obvious thing is the firewall, so I also checked that iptables was disabled temporarily.
The next layer to check is MySQL itself, on both the client and server, using mysql-navigator. I eventually nailed the problem down to an entry in the configuration file /etc/mysql/my.cnf, which tells the MySQL clients to bind only to localhost by default. Comment out the line in the config files on both machines, then restart them.
Checking again with mysql-navigator, the client should now be accessing the database on the server. Permissions and privileges are good here also.
Now, returning to the LibreOffice front-end, this time with the underlying connectivity issue sorted, it should work. The database can now be accessed remotely from the .odb file.
A finishing touch here, but important later if I build a malware tracking system with multiple data sources and clients. In the MySQL Workbench on the server, there are options for configuring the server performance. Again, this is all managed from the Server Administration window.
Most the options under the Performance tab are for memory usage, cache and disk writes. This only needs looking at if there are thousands of users and stuff’s really getting maxed out. Also, the networking equipment between the server and client machines is a design consideration, as that could become a major cause of packet drops.
We also have more general security options. Some of them make the server more secure, but might cause compatibility issues.