Setting Database Permissions

A good way to reduce the potential attack surface of your application is to explicitly deny it the permissions to perform actions that it’s not supposed to be able to do. If it’s not meant to write files to the file system, deny it that privilege. If it’s not meant to access ports other than 80 and 443, deny it that privilege. And if it’s not meant to read from or write to certain tables in the database, deny it those privileges too.

In many cases, reducing application attack surface is a tradeoff between security and functionality; for example, no one likes having to solve a CAPTCHA to post an update to a wiki, but we put up with these inconveniences so that our wikis don’t get filled up with spam. But in this case, we can get the attack surface reduction without any cost to the user since we’re only removing the rights to perform actions we weren’t using anyway. We’ll cover methods for denying file system and network access in later chapters, but in this section we will cover ways to reduce database privileges, and specifically we’ll cover the use of stored procedures to reduce privileges.

Single Account Security

Most web applications use a single, highly privileged account to access a database. If the actual person using the application is John Smith, the application usually doesn’t access the database as “John Smith”; it accesses it as “Application User” or something like that. There are a couple of reasons for this. Remember that web applications are inherently stateless: If the application were constantly switching the database user, it would have to open and close new database connections on every call. This can be bad for performance. As long as the database user remains the same, the application can keep the same connection open or cache it for better performance. And second, it can be difficult for a database administrator to maintain a database with a separate database user provisioned for every person using the application. You could automate the process with role-based policies, but if everyone is going to have the same permissions anyway, there’s no point in bothering to set up different accounts.

Before we continue, step back for a minute and think about all of the different things that a fully privileged administrative user can do with a database. He can read all of the data in all of the tables, change any of the data, add new data or database objects (columns and tables), and delete data or database objects. (If the target was a SQL Server database, the attacker could execute the xp_cmdshell stored procedure or re-enable it if it was removed.)

But it’s very rare that the account you use to access the database from your web application would actually need full administrative rights. If so, this would mean that your application needs the ability to create, read, update, and delete (commonly referred to as CRUD actions) every row, table, and view in the database. That would be a fully featured web application indeed! Usually you’ll only need a small subset of these: reading from the Users table, reading from and writing to the Orders table, and so on.

If you restrict the application database user to have only the rights that you need for your application, you can dramatically reduce the potential damage that any SQL injection attack could have. Here’s an example of how this might work.

Let’s say our example order management database has only two tables: Orders and Products. Users can always view their existing orders, place new orders, and cancel open orders, so the application will need SELECT, INSERT, and DELETE permissions for Orders. However, once an order has been placed, the user can’t change it any more, so the application won’t need UPDATE rights for Orders. The application will need SELECT permissions for Products, since we want users to be able to see everything there is to buy in our store, but it won’t need the ability to add, change, or remove products since that’s handled through a completely different offline application.

You can add or remove the necessary privileges either through the GUI administration tool for your database if there is one, or through the SQL commands GRANT and REVOKE. For example, to revoke update permissions from the user “web_app_user” for the Orders table, you would execute the command:

Now that we have the object-level CRUD permissions set, let’s set the appropriate system-level permissions. These include the rights to drop tables completely, or alter their schemas, or create new tables. Our application doesn’t need any of these, so we’ll revoke these permissions too. Here’s an example of how to revoke the ability to create new tables:

By revoking these unneeded privileges, we’ve limited the impact of any successful SQL injection attack. Even if someone slips up and adds an injectable ad-hoc query to the application, an attacker wouldn’t be able to exploit it as fully as he could have before. He’d still be able to extract the orders data from the database, but he wouldn’t be able to change products’ prices or to run a denial-of-service (DoS) attack on the application by deleting the tables.

Separate Accounts for Separate Roles

It’s likely that your application will have features that shouldn’t be used by everyone. Anonymous users might only be able to search for products, but authenticated users can place orders. Both anonymous and authenticated users might be able to search for salespeople, but only administrators can add and remove salespeople from the system. You can see an example Venn diagram of some example role functionality in Figure 7-7.

Figure 7-7 A Venn diagram of example anonymous, authenticated, and administrative user functionality

If you set up your application with only a single database user, you’re going to have to grant this one database user a lot of privileges that most application users shouldn’t have, and a lot of the benefits of reducing privileges in the first place would be lost. If an attacker managed to find a SQL injection vulnerability in this scenario, he could still do a lot of serious damage.

A better alternative approach is to create a separate database user for each “role” that your application uses. There could be one database user for anonymous users, and another for authenticated users; or there could be one user for employees, one role for managers, and one role for executives. Just as you did in the single-user case, give each database user the permissions that its role requires, and only those permissions. (You can use GRANT and REVOKE on a role-wide basis too, as in: REVOKE CREATE ON Orders FROM anonymous_users). You’ll still have the benefits of database connection caching as long as you keep the number of roles to a reasonable level (for example, 30 different database users is probably too many), and you’ll have the benefit of reduced attack surface.