Improved Data Security with MySQL Privileges and DreamFactory

All MySQL installations naturally include a root account and offer the ability to create restricted user accounts. However, otherwise sane developers will often use these root accounts for application-level communication, dramatically raising the likelihood of data theft, data exfiltration, and other security issues. For that reason the DreamFactory team always recommends users take care to create restricted MySQL users before using the platform to generate APIs.

In this tutorial, you'll learn how to create a non-root MySQL user and then further restrict this user's privileges to a specific database and even table subset. You'll also learn how to subsequently revoke a user's privileges to reflect changing requirements.

In any case, it can be easy for developers and database administrators alike to continue relying on this root user account for doing more than system administration-related tasks. This is a terrible practice and opens up enterprises to a myriad of potentially devastating outcomes, including unmonitored mass *data exfiltration*, malicious third-party theft and destruction, and more severe effects associated with other security issues such as SQL injection.

Fortunately, MySQL offers a robust user creation and privilege management solution that can greatly reduce the likelihood of unauthorized data access and manipulation. In fact, this feature is so important it is the first thing we recommend DreamFactory developers do to employ a multi-layered security strategy. Used in conjunction with DreamFactory's unique API key generation capability, role manager, and API limiting and logging facilities, API integrators can rest assured their applications are backed by an incredibly powerful security-first API management platform.

Creating a MySQL User

To create a new MySQL user, you'll login to your MySQL client as the root user and run the CREATE USER command:

mysql> CREATE USER 'sales'@'123.456.789.999'

       IDENTIFIED BY 'password';

Let's break this statement down into the key components:

• The 'sales' string represents the account user name.
• The '123.456.789.999' string represents the *originating* network address. This means this particular user can only connect from a client identified by this IP. Domain names are also supported, as is localhost which is useful when the MySQL server happens to reside on the same server as the client.
• The 'password' string defines the account password. Of course be sure to choose a lengthy random string!

After creating the account, login to your MySQL server using the account via the designated IP address (you'll need to install a MySQL client to do so). For reasons of convenience you might specify localhost as the originating network address when creating the user, so you can test the login on the database server:

$ mysql -u sales -p

Enter password:

Welcome to the MySQL monitor.

...

mysql>

Congratulations, you've just logged in as this new user. However, because the user has no privileges, the user is unable to do anything but login (known as the *usage* privilege). You can see this for yourself using MySQL's SHOW GRANTS statement:

mysql> show grants for 'sales'@'localhost';

+-------------------------------------------+

| Grants for sales@localhost |

+-------------------------------------------+

| GRANT USAGE ON *.* TO 'sales'@'localhost' |

+-------------------------------------------+

1 row in set (0.00 sec)

If there's a database on the server, try entering it using the use statement and you'll be greeted with an access denied message:

mysql> use corporate;

ERROR 1044 (42000): Access denied for user

'sales'@'localhost' to database 'corporate'

Granting Privileges

The new user account can login to the MySQL database, and nothing more. You might however want to grant the new user what effectively amounts to administrator-level access to a database. To do so you'll use the GRANT ALL PRIVILEGES statement:

$ mysql -u root -p

Enter password:

Welcome to the MySQL monitor.

...

mysql> GRANT ALL PRIVILEGES ON corporate.*

       TO 'sales'@'123.456.789.999';

Once executed, the 'sales'@'123.456.789.999' user will immediately be assigned total control over the corporate database, including the ability to create and delete tables, view all data, and insert, modify, and delete records. But is this really want you want to do? Is it really necessary that the user possesses the ability to create and delete tables? For that matter, is record deletion really a requirement? If not, you can instead selectively grant privileges like so:

mysql> GRANT SELECT, INSERT, UPDATE

       ON corporate.*

       TO 'sales'@'123.456.789.999';

The user will only possess the ability to select, insert, and update records. The corporate.* string means these privileges are granted for *all* tables found within the corporate database. This again raises an important question; does the user really require these privileges for all tables in this database? If not, you can instead grant table-specific privileges:

mysql> GRANT SELECT, INSERT, UPDATE

       ON corporate.customers

       TO 'sales'@'123.456.789.999';

If you want to grant privileges for other specific tables, you'll just execute the GRANT statement multiple times:

mysql> GRANT SELECT, INSERT, UPDATE

       ON corporate.customers

       TO 'sales'@'123.456.789.999';
mysql> GRANT SELECT

       ON corporate.marketing

       TO 'sales'@'123.456.789.999';

Revoking Privileges

Over time your business requirements may change, and with them so might the level of privileges assigned to a specific user. You can remove previously assigned permissions using the REVOKE statement. Suppose you wanted to remove the UPDATE privilege from the sales user:

mysql> REVOKE UPDATE ON corporate.customers

       TO 'sales'@'123.456.789.999';

Deleting a User

Once a project ends or a particular user or department no longer requires access, you should take care to ensure database access is immediately revoked! To remove a user you'll use the DROP USER statement:

mysql> DROP USER 'sales'@'123.456.789.999';

Layering DreamFactory Security Atop Your MySQL Users

Securing the users responsible for interacting with the MySQL database on behalf of your APIs is an incredibly important part of the API development and integration process. However, this is but one of several crucial steps you can take to ensure your data is protected. By taking advantage of the DreamFactory platform's other security-first features you can:

• Create and distribute unique API keys for one, several, or all API users.
• Associate restricted roles to each API keys, applying platform-level access controls with no coding required.
• Limit API request volume according to user, API key, and API on a minute-by-minute, hourly, daily, weekly, and monthly basis.
• Perform real-time auditing of API access, allowing your team to know who accessed what data and when via your APIs.

Head on over to DreamFactory.com to learn more about the hundreds of APIs you can generate in minutes using the platform, or even better, download our open source version!