Proof of Work, Explained (Part 1 – POW for Non-Techies)

Overview

Personally, I’m fascinated by both the technical and financial implications of cryptocurrencies like Bitcoin, Bitcoin Cash, and Litecoin (to name a few). The way these currencies work is a complex topic, with lots of moving parts to discuss. One of the core components of cryptocurrencies like Bitcoin is the mechanism by which an entirely decentralized system of money can securely verify transactions as well as issue new currency, all while preventing fraud and issuing at a predictable rate.

Most of these currencies solve this problem using a concept called “proof of work” by which nodes solve a computationally difficult but easily verifiable mathematical problem. This concept goes beyond cryptocurrencies as well, and actually originated as an anti-spam measure.

Proof of Work – The 10,000 foot view

What is Proof of Work?

Proof of work, fundamentally, is the solving of a computationally intensive mathematical problem. This problem has two very important properties – the solution to the problem is both:

  • Difficult (computationally intensive) to find
  • Easy to verify once found

The idea is this: for an application like cryptocurrency or anti-spam, a “node” or computer is challenged to find a solution to this puzzle. The solution can only be found by brute-force guessing. However, once the solution is found, all the other nodes on a network or a server can verify the solution in one step. Since the answer can only be found by brute-force computation but can easily be verified as correct, the solution to the problem serves as proof that a certain amount of computing work was done – hence the term “proof of work”.

Why is it Useful?

First, let’s look at the original application of proof of work: anti-spam. The original idea was implemented in a system called HashCash, invented by Adam Back. Back’s system works like so: Before performing an action like posting to a forum or sending an email, the user of a site is made to do a small proof of work problem. This problem only takes half a second or so of computing to solve, and of course is almost instantaneous for the system to verify. For a legitimate user of a forum or email system, the half second of computing is no obstacle to completing his or her task. However, for a spammer trying to send hundreds of thousands of spam messages, the task suddenly becomes very uneconomical since it would tie up their computer for minutes or even hours at a time!

Now how does this system apply to cryptocurrencies like Bitcoin? In this system, transaction verification and currency issuance is totally decentralized – no third party is trusted to create new value tokens or verify that transactions are legitimate. This of course presents a massive fraud-prevention challenge – how can the network ensure that malicious parties don’t create “counterfeit” currency or send through transactions that aren’t valid?

Proof of work helps to solve this problem. On the Bitcoin network, new transactions are broadcast to computers running what is called “mining” software and accumulated into “blocks” of transactions that will be validated at one time. Every time a new block is waiting to be verified, all the nodes on the network running this software essentially “race” to solve a proof of work problem first. The Bitcoin network adjusts the difficulty of this problem so that about once every ten minutes, one miner wins the race and finds a solution to this problem. Once one node finds the answer, it tells all the other nodes on the network that it’s found an answer, and the other nodes can instantly verify that the answer is correct.

The node that finds proof of work for this block is rewarded with brand new Bitcoin (issued at a predictable rate) as well as all the transaction fees in that block. This computationally expensive proof of work problem creates an excellent system of economic incentives- the reward of new Bitcoin drives miners to to verify transactions are correct, and also make fraud more expensive than legitimate mining. If a miner were to try and cheat, all the other nodes running the legitimate software would instantly reject the new block since it doesn’t meet the rules of the network, and all of the time and computing power of the malicious node would thus be wasted.

Proof of Work – Powering Cryptocurrency and Thwarting Spammers

The idea of proof of work has incredible value for multiple applications. This system allows computing to be used as a precious resource in a purely digital economy; a way to both secure monetary transactions and prevent the waste of resources like time and storage space. In an anti-spam setting, proof of work allows the operators of a curated space to reduce the impact of spam on their systems, reducing wasted time, clutter, and storage space. In a cryptocurrency application, proof of work allows the secure verification of transactions and the issuance of new currency without the need for a trusted third party, the often fatal flaw in fiat systems.

The applications of this technology are incredibly interesting. In the case of cryptocurrencies, I would say its application is part of a system that is revolutionary. Now, as a software engineer, I find the actual technical workings of proof of work to be even more interesting than the surface description. In the next article, I’ll walk through how these algorithms work from a more technical perspective.

Bitcoin as “Digital Gold” is Bad for Crypto Adoption

“Digital Gold” vs. “Digital Cash”

The core Bitcoin network has a scaling problem, and has had this problem for a while now. As more and more transactions try to fit in Bitcoin’s 1MB blocks (once every 10 minutes), network fees have skyrocketed to $5-10 dollars, and that’s even a little low if you want your transaction confirmed within an hour or so.

One response to this problem, especially from supporters of the Bitcoin Core roadmap, is to ignore the problem to some extent. As Bitcoin has seen more attention over the last few years, the price has risen dramatically. As a result, many are now claiming that Bitcoin is not meant to be a “means of exchange” or a form of “digital cash” for day to day transactions. Rather, their viewpoint is that Bitcoin should be seen as a “store of value” or “digital gold”.

To be clear about my biases in this space – I think cryptocurrencies are at their most interesting and valuable as a means of exchange; a way to do truly global, peer-to-peer, decentralized cash. I do not care at all for the risky speculative investing that goes on in the crypto space; I believe these currencies should be used or held in small amounts, allowing one to learn more about this fascinating technology and spread adoption.

With that said, I don’t have a problem with a digital currency being used as a long term store of value like a “digital gold”. There is plenty of room in the crypto space for currencies that solve different problems in different ways. I do, however, think there is a big problem with Bitcoin being the currency of choice for that use case.

The Bitcoin Brand, and the problem with Bitcoin as “Digital Gold”

Let’s be honest, fellow crypto nerds. How many people in your daily life have actually heard of Bitcoin? And how many of them actually understand it, at least at a high level? How many people actually own some and use it? If you’ve got the same variety of people in your life as I do, the percentage isn’t that high.

Now how many of that small subset of Bitcoin-aware people in your life know about Bitcoin Cash? Ethereum? Litecoin, Vertcoin, Monero, Dash? It’s an even smaller percentage, surely. Even if they’ve heard of them, do they understand how these alternatives to Bitcoin solve different problems? We’re down to a sliver of people that understand and adopt these different currencies beyond Bitcoin.

Herein lies the crux of the problem:

Bitcoin is the defacto cryptocurrency. It is the face of digital money, the storefront, the brand, however you want to refer to it.

Whether anyone likes it or not, Bitcoin is what most people hear about first when they hear about cryptocurrencies. And with a now large chunk of the Bitcoin community marketing this as “digital gold”, we have the potential to miss out on opportunities for widespread adoption in the coming years.

More and more businesses are going to become interested in adopting cryptocurrencies as a form of payment, they’re going to want to start with Bitcoin. It is the biggest after all, and the original value proposition we still see on bitcoin.org is “fast peer-to-peer transactions” and “low processing fees”. The reality is far from that, however. When the coffee shop owner realizes his or her customer will have to pay $10 to buy a $3 coffee, they’re not going to find Bitcoin usable for their business. How many of the already small percentages of crypto-curious entrepeneurs are going to take the time to understand Bitcoin Cash, Litecoin, or Dash? Many will probably say: screw this and return to business as usual with fiat.

Satoshi’s “Peer-to-peer electronic cash” – How Does Bitcoin Continue That Vision?

The problem is not a cryptocurrency as “digital gold”, the problem is Bitcoin as “digital gold”. The original promise of Bitcoin was indeed a global, peer-to-peer, low fee alternative to centralized payment processors like Visa, Mastercard, or PayPal. But as the Bitcoin community shifts away from that vision, they take the adoption of that vision with them.

With a functional implementation of the lighting network at least two years away, and the lack of press for already-scaled alternatives like Bitcoin Cash, Litecoin, etc., it concerns me that adoption of cryptocurrencies will stall. I don’t at all fear that they will go away or stop the ceaseless flow of innovation that we’ve seen since the advent of Satoshi’s brilliant whitepaper. But I do worry that Bitcoin’s current scaling problems and the community’s attitude toward it will lead to several years of stalled adoption in the mainstream. I do hope, however, the problem gets fixed soon and that I am wrong.

Configuring SSL with Apache and Let’s Encrypt (Part 2 – Manual Apache Configuration)

Overview

In the first part of “Configuring SSL with Apache and Let’s Encrypt”, we discussed why you should be offering HTTPS connections to your site, and how to get a free certificate from Let’s Encrypt. That tutorial shows commands that use CertBot to automatically configure your Apache web server with the new certificate.

However, if you’re like me you may want some more fine-tuned control over how your web server is configured. We can use CertBot to just retrieve a certificate for us and set up Apache virtual hosts by hand. You can even force connections to your site to use HTTPS all the time.

Configuring HTTPS with Apache By Hand

Finding your Let’s Encrypt certificates

In the case you want to hand-configure your server, you can ask CertBot to just fetch the certificates and leave Apache alone with:

sudo certbot --apache certonly

This will create a folder containing the files you’ll need for that domain. You’ll need these two files:

/etc/letsencrypt/live/mydomain/fullchain.pem
/etc/letsencrypt/live/mydomain/privkey.pem

fullchain.pem is the certificate itself, and privkey.pem is the certificate’s private key. Make sure you keep these safe, especially the private key!

You can then copy these two files to their respective directories in /etc/ssl. If you want, you can change the file names. I like to name mine with the site and use .crt/.key for the file extensions:

sudo cp fullchain.pem /etc/ssl/certs/mydomain.crt
sudo cp privkey.pem /etc/ssl/private/mydomain.key

Setting up Apache Virtual Hosts with SSL

The next step is to set up Apache virtual hosts to use our certificate. Virtual hosts are a great tool that allow you to manage multiple websites (with multiple domains) on one web server. They also allow you to configure things like which SSL certificates to use and which folders to serve for your site, so it is valuable to learn how they work.

Your Apache SSL virtual hosts files will most likely be in the /etc/apache2/sites-available directory. For my version of Apache, the SSL vhosts file is called default-ssl.conf.

Let’s take a look at what a basic SSL-configured virtual host definition looks like:


<IfModule mod_ssl.c>

 <VirtualHost *:443>

  ServerAdmin you@yoursite.net

  DocumentRoot /var/www/html/yoursite

  ServerName yoursite.net
  ServerAlias www.yoursite.net

  SSLEngine on
  SSLCertificateFile /etc/ssl/certs/yoursite.crt
  SSLCertificateKeyFile /etc/ssl/private/yoursite.key

  <FilesMatch "\.(cgi|shtml|phtml|php)$">
    SSLOptions +StdEnvVars
  </FilesMatch>
  <Directory /usr/lib/cgi-bin>
    SSLOptions +StdEnvVars
  </Directory>

  BrowserMatch "MSIE [2-6]" \
    nokeepalive ssl-unclean-shutdown \
    downgrade-1.0 force-response-1.0
  BrowserMatch "MSIE [17-9]" ssl-unclean-shutdown

 </VirtualHost>

</IfModule>

Let’s break down what this all means, especially the SSL part.

  ServerAdmin you@yoursite.net

  DocumentRoot /var/www/html/yoursite

  ServerName yoursite.net
  ServerAlias www.yoursite.net

This first block of information is the same as a virtual host for plain HTTP. This information tells apache what to do if someone comes to your web server from the domain name yoursite.net. If a client makes a request, Apache will serve files starting at the document root /var/www/html/yoursite. All of the files your site will serve should be contained in that folder. The ServerAdmin bit tells someone who they can contact if something is wrong. According to this Stack Overflow question though, that feature is deprecated so you may want to omit it.


  SSLEngine on
  SSLCertificateFile /etc/ssl/certs/yoursite.crt
  SSLCertificateKeyFile /etc/ssl/private/yoursite.key

  <FilesMatch "\.(cgi|shtml|phtml|php)$">
    SSLOptions +StdEnvVars
  </FilesMatch>
  <Directory /usr/lib/cgi-bin>
    SSLOptions +StdEnvVars
  </Directory>

  BrowserMatch "MSIE [2-6]" \
    nokeepalive ssl-unclean-shutdown \
    downgrade-1.0 force-response-1.0
  BrowserMatch "MSIE [17-9]" ssl-unclean-shutdown

This second block of code starts the configuration of SSL. It tells Apache that connections to this site via HTTPS can be accepted, and includes some information about what to do with certain browsers and CGI requests. You can safely leave the bottom portion alone; it is included by default and I’ve not found a reason in my experience to touch it.

The more important piece you’ll need to edit is the section containing SSLCertificateFile and SSLCertificateKeyFile. Make sure that those paths match up to the files you copied to /etc/ssl/certs and /etc/ssl/private. Those directives tell Apache what certificate file to use for incoming SSL connections, and the private key that will be used to decrypt data sent by the client.

Once you’ve got that file correctly edited, you can restart Apache using the command sudo service apache2 restart. If you get an error here, there is most likely a syntax problem in the default-ssl.conf file that you just edited.

Forcing HTTPS connections

Now that you’ve got a shiny, new, CA-signed SSL certificate configured for Apache, you may want to do away with HTTP connections altogether. Fortunately, Apache makes this fairly simple, again with the use of virtual hosts.

This time, you’ll be editing the HTTP vhosts file. In /etc/apache2/sites-available, my version of Apache has a file called 000-default.conf. Open this up in your favorite text editor, and add the following to each virtual host you want to force HTTPS with:

Redirect / https://mysite.net

This directive tells apache to redirect any plain text connection to the document root of that site to redirect to the document root of your site with HTTPS. One thing I’ve noticed is that this can cause issues with HTTP links to subdirectories like http://mysite.net/subdir, so you’ll want to update any links in your site to use HTTPS as well.

Other solutions include using the rewrite engine, but Apache discourages doing so for this use case.

Configuring Securely Encrypted Connections to Your Web Server

It’s now easy and free to get a CA-signed SSL certificate thanks to Let’s Encrypt and CertBot. Combined with the highly configurable Apache web server, it’s fairly straightforward to get your websites tuned up to use HTTPS with your desired site setup in mind.

Using secure connections is a great way to provide your users with increased security and privacy, preventing man-in-the-middle attackers from getting their sensitive information or interfering with the content you provide. Happy encrypting!

Configuring SSL with Apache and Let’s Encrypt (Part 1 – Let’s Encrypt and CertBot)

Overview

With the robustness of modern webpages, HTTPS is the way to go for almost any website. Most of the time an individual is online, they’re exchanging sensitive information like passwords and personal information. If you’re logging into a site with a password, a securely encrypted connection is a must.

However, even informational sites should use encryption! If you’re running a site for a small business, a topic that interests you, or even your own portfolio, your users can benefit from increased privacy and security when they connect using HTTPS rather than plain text HTTP.

Configuring SSL for a Website

Why SSL?

It’s important to roughly understand how HTTPS works, and why using it will improve the privacy and security of your users. From the 10,000 foot view, what SSL does is encrypt all the traffic between the client (the user’s browser) and the server (your web server). Everything beyond the domain name is encrypted, and therefore hidden from a man-in-the middle attacker.

From a security perspective, this prevents an adversary on the network from modifying content en-route to the user. Imagine you’re an application developer and you have software available for download. You even go so far as to supply a SHA-256 fingerprint of your application so that users can verify its contents. If you’re transmitting your application over HTTP, a malicious man-in-the-middle could modify the application and fingerprint in transit, giving your end user a nice dose of malware. But if you allows your users to download over a secure connection, the attacker could not see or change either component on its way to your (happy, malware free) consumer.

From a privacy perspective, HTTPS prevents a man-in-the-middle from snooping on what your user is viewing on your website. The only thing someone on the network could see is that they are connecting to your particular domain, no specific URLs or content. Imagine your user is doing research on a sensitive topic, or trying to download a piece of software that someone doesn’t want them to have. To some extent, HTTPS protects that person’s privacy by preventing a snooper from understanding what they see going over the wire.

Obtaining an SSL Certificate with Let’s Encrypt

If you’re sufficiently convinced that your website should use SSL, the good news is that you can set that up for free! The awesome folks over at Let’s Encrypt have developed a service that allows you to obtain your very own, CA signed SSL certificate from the command line.

To actually get the certificate for your system, you’ll want to use an ACME client called CertBot from the Electronic Frontier Foundation. CertBot uses the ACME protocol to automatically verify your ownership of the domain you want a certificate for and fetch that certificate from the Let’s Encrypt service.

The CertBot website has instructions for all kinds of web servers and server operating systems. You can visit that site if you need further help for a particular combination, but in this article I’ll show you how to get a certificate on Ubuntu Server for Apache (that’s what I use). To install the CertBot application, run:


sudo apt-get update
sudo apt-get install software-properties-common
sudo add-apt-repository ppa:certbot/certbot
sudo apt-get update
sudo apt-get install python-certbot-apache

Once certbot is installed, you have two options for fetching a certificate. If you only have one website with a basic configuration, you can have CertBot automatically configure Apache to use the SSL certificate it will generate. This is done using:

sudo certbot --apache

That’s all you need! You can now visit your website and you should see a little green lock in your browser indicating that you’re connected via HTTPS with a valid certificate.

Using HTTPS to Keep Your Users Secure

It’s important to encrypt traffic between your server and the clients browsing your site so that sensitive information cannot be snooped or manipulated by someone on the network. This article discusses why you should be using HTTPS for your websites, and how to get a free certificate with EFF’s CertBot and Let’s Encrypt. The Apache configuration is done automatically using the commands shown here.

However, if you’re like me, you may prefer a little more fine-tuned control over your system configuration. The next article will go more in depth on hand-tuning Apache for your needs – we’ll cover certificate-only CertBot usage, making SSL virtual hosts, and forcing secure connections.

Avoiding “Mixed Content” Issues In Your Client-Side Scripts

Overview

Web applications are particularly vulnerable to security issues, given that the internet is one giant, unsecured network that anyone can access. One of these major vulnerabilities is man-in-the middle attacks, where a malicious user accesses content sent between the client (the machine receiving web content) and the server (the machine providing web content).

Fortunately, the widespread adoption of SSL/TLS has improved the security of web pages by encrypting content between client and server and making it difficult for man-in-the-middle attacks to occur. It is important, however, that web pages and web applications are properly marked up and coded to avoid having content sent unsecured.

Avoiding mixed content for greater security

What is mixed content?

Mixed content refers to unsecured (HTTP) content loaded within a secure page (HTTPS). For example, this content could be an image, video, or audio file requested by the page with an unsecure URL. This could also be a script loaded by the page over HTTP. Scripts loaded unsecurely can potentially be more dangerous than media files like images.

Why is mixed content a problem?

Privacy concerns

Mixed content can create several problems for an end user of your website. The first issue is that of privacy. When a user loads a page over SSL/TLS (HTTPS), the only thing someone snooping on the network (a “man-in-the-middle”) can see is the domain of the page being requested. For example, a if user visiting https://somesite.com/sensitive-article, the only thing a snooper would see is that the user is requesting content from somesite.com. They can’t see the particular page the user is requesting. However, what if the page sensitive-article requests an image over HTTP? Then the snooper could see the unencrypted image, possibly giving them clues about what the end user is viewing!

When browsing with HTTPS, the user has some expectation of privacy between his or her self and the server. Mixed content breaks that by exposing some parts of the page in plaintext as they travel across the wire, potentially giving some clues as to what the user is viewing.

Code security concerns

A second concern with mixed content is that a script sent in plain text could potentially be modified en-route to the client. If a script is sent over HTTP, a malicious user could potentially intercept that script and inject some malicious code of their own. This code could be used to collect sensitive information or trick the user into visiting an illegitimate website (very common with phishing scams). The end user expects a legitimate script sent securely from the server (they requested a page over HTTPS, after all), but in a mixed content scenario this code could be intercepted and modified by an attacker.

How to avoid mixed content

Use relative links as much as possible

One of the easiest ways to avoid mixed content in web pages is to use relative links. With a relative link, the browser will use the protocol the page was requested with by default, no hard-coding of a protocol required. For example, loading your images from a subfolder called photos can be done with a relative path like so: src="photos/myimage.jpg". If the user requests your page over HTTPS, the image will be loaded over HTTPS as well.

Use // to auto-detect the protocol for outside links, or always use HTTPS

Sometimes you may want to load content from outside your own domain and your own web server. Many developers like to load scripts like jQuery from Content Delivery Networks (CDN’s) rather than store those scripts on their own server. In this case, you can use // in front of the URL instead of a hard coded protocol like http://. This will match the protocol the page was requested from. For example, src="//cdn.jquery.com/some-jquery-version.js". Better yet, if you know the site provides content over HTTPS, just use that by default!

Avoiding mixed content is fairly straightforward!

While mixed content can pose several issues for user privacy and application security, it is thankfully trivial to mitigate. Avoiding mixed content problems is as easy as fixing the links in any pages your site provides. If you’re not sure your pages avoid this problem, the developer console in a modern browser like Firefox will tell you if you’re trying to load insecure content. Many browsers (including Firefox) even block that content from loading. Be sure to be mindful of how media and scripts are requested in your web applications, and your users will be more secure for it!

Starting to Secure MySQL (Part 2 – Principle of Least Privilege)

Overview

In the last article, we discussed a solid starting point for securing MySQL installations using mysql_secure_installation. Running this script is great way to eliminate some default security flaws with MySQL, but it is just a starting point.

In MySQL, multiple database users can be created for different hosts and privileges can be set for different databases. In order to further secure each database created for a particular application, it is important to look at how users are created and what privileges are granted on that database.

Creating users for the database

Why isolated users improve security

Say you’re creating a web application and need to supply a database user and password in your backend code so you can connect to the database and select some records to display. It might be tempting to just plug in the root user. After all, root has all the privileges you need and it’s already built in, so it should just work.

But what happens if say, PHP isn’t properly configured and a user requests your webpage from the server? Now they have the root username and password since the source code was returned to them instead of executed! A malicious attacker could gain access to your server through another vulnerability and wreak havoc on your databases.

This is the first part of using the “Principle of Least Privilege” to secure your database. It’s a really good idea to have a user account (or multiple user accounts) with access to only one database. Don’t configure this user to have privileges on any other database. In the event that user’s credentials are compromised, the damage can at least be isolated to only one database.

What about hosts?

Another important consideration for creating users is where they have privileges to access the database from. With a database engine like MySQL, it’s possible to connect directly to the database from a remote location. You could configure a user for your web application’s database to connect from any host, so you could always work on the database from a coffee shop or a friend’s house.

However, this is another important place to limit privileges to the least amount needed to be operational. In the above scenario, if an attacker gets the credentials for your database user, they could log in from their bedroom in their PJ’s and start messing with your database!

Instead of allowing a user to connect from any host, consider the only place a user should be able to connect from to make the database functional. In a web application scenario localhost is often a good choice because it limits the user to connecting from the web server the application is running on. Now if an attacker gets access to your user’s credentials, they would have to also gain shell access to the server. This presents an additional obstacle between them and your database that can be difficult to overcome with a properly configured server.

How to create a user for a specific host

In MySQL, creating a user for a specific host only requires one command that is fairly straightforward. For example:

CREATE USER "MyUser"@"localhost" IDENTIFIED BY "MyLongAndStrongPassphrase!";

The above example creates a user called MyUser that can only log in from localhost. The IDENTIFIED BY part tells the database engine what password the user will log in with. Be sure to create a unique, strong password for this user.

Configuring user privileges

What privileges should a user have?

Much like the question of “what hosts should I allow my user to connect to?”, the question of what privileges a user should be granted comes down to the least amount of privileges needed for the user to do its job.

Just like it’s easy to use the root user for everything, it’s easy to grant all privileges to a user for your database so everything just works. But again consider the scenario where an attacker gains access to that user’s credentials. Even if that user can only log into one database, they could drop tables or modify records maliciously. Imagine an attacker being able to modify the price of an expensive item in your web store to any amount they want!

Now in that scenario, the only access your web store needs to the database is to SELECTthe prices of available items. Why give the user access to update or delete records at all? This is the core of the “Principle of Least Privilege”. Only give users the minimum amount of privileges they need to do the job they do. In our web store example, that means they only need the ability to do a SELECT on the database and nothing more.

How to grant user privileges in MySQL

In MySQL, specifying user privileges is done using the GRANT statement. For example:

GRANT SELECT ON MyWebStore.PRICES to "MyUser"@"localhost";

The first part of this command (the GRANT SELECT) tells the engine that this user will get the SELECT privilege only. There are a bunch more, but some of the common ones include INSERT, UPDATE, DELETE, SELECT, DROP, CREATE.

The second part of this command (MyWebStore.PRICES)specifies which database and which table(s) the privileges will apply to. If we’re following the “Principle of Least Privilege”, this user should only be granted these privileges on one database. As for tables, you may want to specify one table or all tables in the database depending on your application. You can apply the privileges to all tables using the * wildcard operator, like MyWebStore.*.

The final part of this command ("MyUser"@"localhost") specifies which user (on which host) the privileges will apply to.

There is one final step, and that’s to immediately apply all these privileges. In MySQL the command FLUSH PRIVILEGES immediately commits the changes to the database.

Being mindful

Applying the “Principle of Least Privilege” is another important methodology for securing MySQL database installations against attackers. It’s important to be mindful of the needs of your application when creating users and granting privileges, so that each user has the minimal amount of access needed to do their job. By isolating users to one database, one or few hosts, with a minimal amount of privileges, we can isolate and minimize the potential done by an attacker that gains access to database user credentials.

Starting to Secure MySQL (Part 1 – mysql_secure_installation)

Overview

Databases should always secured, regardless of the environment. However, database security is particularly important for web-facing applications. MySQL is a very popular database especially for web development, as a part of the LAMP (Linux-Apache-MySQL-PHP) stack.

Database security can be a complex topic, but there are a few basic security measures everyone can and should use when building applications that use MySQL.

The mysql_secure_installation script

Fortunately, the developers of MySQL wanted to make security accessible. They’ve added a great security script to the package called mysql_secure_installation. This script allows the user to quickly attack some basic security flaws in the default MySQL installation.

What it does

This script addresses several issues with the default installation:

  • Sets a password for the root user
  • Disallows root login from remote hosts
  • Removes anonymous users
  • Removes the test database

Why these measures improve security

    • Sets a password for the root user

For any system with an “administrative” user, it is paramount that not just anyone can access that account and the privileges associated with it. Giving the root user a secure password prevents an unauthorized person from modifying databases, users, or user privileges

    • Disallows root login from remote hosts

Similar to setting a secure password for root, preventing root login from remote hosts is an extra measure that prevents an unauthorized user from having access to do anything they want to your database installation.

  • Removes anonymous users

By default, MySQL allows anyone without a user account to connect to the database engine for testing purposes. This is undesirable because even though anonymous users may not have the same privileges as root, it could allow an unauthorized user to snoop around your MySQL installation and see how databases are defined, what users are available, etc. Removing anonymous users means that only an authorized user with a password can connect to MySQL.

  • Removes the test database

Another default feature of MySQL is a test database that anyone can connect to. Since anyone can connect to the database, this gives an attacker a starting point for access to your database if there are any vulnerabilities. We don’t want any databases that allow unauthorized users to connect.

How to use this feature

Since this application comes bundled with MySQL, it is fairly straightforward to run. At a terminal window, execute mysql_secure_installtion. You’ll see several prompts for each security enhancement:

Change the root password? [Y/n]
Remove anonymous users? [Y/n]
Disallow root login remotely? [Y/n]
Remove test database and access to it? [Y/n]
Reload privilege tables now? [Y/n]

The last item ensures that the changes made by the script take effect immediately. It is best practice to answer Y (yes) to all of the prompts!

Next Steps

All of the features provided by mysql_secure_installation are a great starting point for securing your MySQL installations. In another blog, I’ll address how to set user privileges for your database following the “Principle of Least Privilege”.