Security and the real world

When talking about Security, most people think about something where "they" attack and "we" defend. If they succeed only once, we have lost. If we succeed in defending, the next wave of attackers will be ready, meaner and faster than the first wave. This is not "Security", this is "Space Invaders".

The rules of "Space Invaders" can be simplified even more. The most important rule is "Eventually, you will lose". And you can't refuse to play, either, because you want to stay in the business - somebody out there will gladly become the attacker and try to harm you. If you think like this, you will be very depressed, very quickly.

Before you quit, you might think about the game a bit more and search for strategies. You'd try to delay losing, to limit your losses, and to come up with recovery strategies. If you are out for style, you'd even try to turn around the game.

Now that you have started to think about it, you might ask yourself: What for am I in the game? What is it that I actually want to achieve by playing, what do I risk and what assets do I have that I can leverage to delay losing? This thinking fundamentally changes the outlook on security: From playing "Space Invaders" we turn it into something that one can actually win.

This is also changing the topic from a traditional narrow definition of security "Integrity and Confidentiality, sometimes with Availability or Privacy thrown in as well" into a much wider field, the field of "Security Management". Security Management is essentially Corporate Risk Management applied to IT. There do exist codes of practices, as for example written in ISO 17799 and ISO 27001, which define security processes and fields of coverage, and one can even get ISO-certified on them.

The ISO-Standards define only an organizational framework covering 12 large categories with several subcategories. They do not actually contain requirements or proscribe best practices - this is something that must be done locally.

ISO 17799/27001 is compatible with I TIL and with ISO 9000.

Most likely you are not playing one single game: Most users have different services with different requirements, costs and practices attached to them: DWH vs. OLTP, "Production" type systems vs. "Decision Support" vs. "Production Support" systems. It is important to identify the different classes of systems clearly, and assign sensible "damage" and "probability" values to anything that may harm them.

So come up with possible scenarios such as a natural disaster, a hardware failure, a burglar or anything else that may go wrong, and assign for each possible scenarios a probability. Qualitatively, the probability should be "close to never", "very rare" to "happens seldom". For each risk, assign a damage that would be done to your system if the risk materializes. Qualitatively, the damages would be "deadly" (no company any more), "shows up in earnings report" or "substantial" (somebody will get no bonus this quarter). Bigger probabilities should not be handles as risk managements, they must be dealt with as part of regular operations, and smaller damages are not to be dealt with at the Risk Management level as well: These damages must be seen as business expenses.

To sum it up:

Security Management is Risk Management, and a bottom-up approach to this is likely to be useless.

Before you have a look at your security toolbox at all, you should have a good idea about what your business objectives and requirements are, what possible attacker models there are that are relevant to this, and what the probabilities and damages are that are assigned to these.

What you can do to alleviate these risks is limited by your operational requirements, and there will be a residual risk that can not be dealt with at a technical level.

A good example for an operational requirement is the "I am running an online business" scenario which has the operational requirement that the system must be online. So cutting the network cable is not an appropriate way of dealing with the risk of hacking, because the operational requirements prohibits this.

IT security requirements and business requirements often clash: For example, internal security guidelines are often applied to inapplicable entities such as customers, or the requirements are priced in a way that they are not covered by the earnings of the business process in question, of the requirements do not scale at all with an increasing number of users.

Also, the security requirements must match at all levels: So if you have a highly available database, the network equipment used to transport the data must have the same requirement, as must the monitoring. Monitoring in turn generates alerts, which must be handled by people. One of these people is most likely to be the product owner for the product using this particular database.

This is often a good way to scale requirements, by the way. Often product owners have ridiculously high expectations on the availability of their databases. It is important to make them involved personally to have them actually relate to the scenarios talked about:

If that database fails at 4am Sunday morning, an alert will be generated and operating will need guidance to handle the crisis. Are you, the product owner, available at this time to make decisions? If not, the crisis is probably not as important as you think.

This approach works wonders to scale a product owners requirements to realistic levels.

Another common example for dealing with risks is the risk of running over people when driving a car and hurting or even killing them. If you have the operational requirement of driving a car for whatever reasons, there will be a lot of security tools that you are deploying: You will have a drivers education and passed a test to show that actually have it, there are traffic rules and people out there enforcing them to prove that drivers adhere to them, and there will be plenty of technical security measures built into your car and a regular inspection of their fitness to prove that they work. Even then it may be that you are involved into an accident and cause damage - this is the residual risk of driving, and you have to deal with it by insuring yourself (and in some countries you have to prove that you have insurance).

Only at this stage it is useful to actually look into your security toolbox for a concrete example of applied security.


In this talk, we are looking at the web application scenario, because it is so common, but the ideas are applicable to all other scenarios as well. In this example, we are likely to see web servers talking to middleware layers, which in turn talk to databases, and there is a good measure of firewalls thrown in between.

In this world we are likely to encounter outward facing security, such as policy enforcement with firewalls, input filtering in the holes the firewall leaves, data management using queuing and pruning, and transport encryption. Dealing with local security we have to deal with operating system security, storage encryption, backup, user accounts and permissions and logs and audit trails.

The firewall defines the trust boundary of your application. The rules inside your firewall determine which machines and protocols are visible from the outside, from the customer side. The trust boundary coincides with the realms of single administrative control in most sane setups.

Data is crossing the trust boundary and is coming in to our application via the channels the firewall leaves open. This data must be filtered, checked and cleaned. Data that is no longer useful in production must be deleted, or if it cannot be deleted it must be pushed to some other, non-production system for DWH or PS. It is important to keep production lean and small to have a small MTTR in case disaster strikes.

Also, data is often crossing from a zone of high security trough insecure areas into another trustworthy zone. Most commonly, data is coming in from an end users system trough the internet into our application. This data must be encapsulated and transported through encrypted and secured tunnels.

We can deal with the problem systematically: The firewall defines our outward facing surface of IP numbers and ports reachable from the outside. At these locations, there are URLs or other accessible objects. We have to look at them: Is this really the outward facing surface we want to present to a customer? Most often it is not, it is more - almost all installations have data lying around in DocumentRoot or other reachable locations that should not be there. Try looking for "Googledorks", and try any of the search terms explained there to get an idea of the size of the problem.

For each URL we can list the names of the allowed CGI parameter names, and for each of these parameters there is a list of allowed values. If we see that set as static, we can enforce this set of parameter rules with something like mod_security or another input filter.

These are stateless filters, though, and for better security we need dynamic filters that know about the state machine of the application and its current state. These filters must be part of the application - a framework such as Ruby on Rails, or a Ruby-like framework like almost any PHP MVC framework go a long way to help you to insure that no unchecked data can be touched by application developers, ever.

There are two basic design strategies: Decontamination on import - the session, the database and any persistent storage all only see valid and harmless data. The other is decontamination on export - accept potentially dangerous data, and escape it in dangerous contexts - in shell commands, in eval()s, in SQL commands, and in generated HTML at least, but there may be plenty of more situations that need to be covered.

One of these strategies works. The other is designed to turn you into a Bugtraq news item.

Production is basically what keeps you alive. You have no production, you have no income. Design production for recovery: Small data sets can be recovered quickly, so keep as little data as possible in your production data set. If you cannot delete data, queue it into an export table and move it into a DS/DWH system.

A fairly canonical example is any Nagios setup: The main OLTP purpose of the system is to turn incoming system failures into text messages on administrators cellphones. The OLTP system should only contain data such as open, unhandled incidents, and lists of actively monitored systems, administrators and so on.

Often these systems also contain historic performance data, and a lot of this. This does not belong into a monitoring instance at all, but into a special DWH instance of the application, which has different availability and other requirements than the monitoring instance. Not keeping more than say a week or a month worth of data in the monitoring instance will also magically make than instance much faster.

Similar rules can be applied to a web shop system: People should still be able to make purchases in your production web shop even if the customer management component is down and people momentarily cannot view their historic orders or change their credit card number.

Production is what the customer can touch. It should be using secure communication. This automatically protects against exposure of sensitive data and session hijacking, and also validates the systems identity to the customer. This is much less expensive than you might think. For example, a freemail provider I have been working for was pushing ~80 MB/sec, and for this the combined power of ca. 28 Dual-Xeon blades was being used (this compares to ~1200 systems in their data center in totality). A 14 blade center consumes 3500 Watt, so this is 7 kW out of a data center consuming 900 kW. One could say that you need ~10 horsepower to push 80 MB/sec through SSL, that's an additional lawnmower for your SSL needs.

The connection between MySQL and the application can be SSL'ed as well, but should it be? If it is inside the same trust boundary, it need to be - if you have that topology and think SSL is helping you any, then you have other problems as well. To actually use SSL in MySQL, "SHOW VARIABLES" should show "HAVE_SSL" as "YES".

To use this on the server side, use the "REQUIRE" clauses as shown in a "GRANT" statement for the users that should be using SSL. On the client side, you must use the 4.1 API (for example, using mysqli on PHP), and have to push a "ssl_set()" call inbetween the init and connect phases of the API initialization.

For storage encryption, there are solution that can work at the driver level (for example, using something like the BeSequre ODBC driver), at the column level or at the file or filesystem levels. They address different problems and handle different attacks.

Storage encryption is required for credit card data, and is recommended for all information with privacy issues, that is, everything that is relating to persons and is not public knowledge.

If you are encrypting data on a column level, equivalencies can be handled mostly unchanged and can even make use of indexes, at least in principle. But encryption changes the sort order of data by necessity - it has to, otherwise you could bracket likely cleartext values of encrypted data with controlled plaintext insertion. In consequence, anything that uses range queries cannot work on encrypted data.

Use AES_ENCRYPT() to encrypt data, do not use DES_ENCRYPT() - it requires SSL support in your server in the first place, and DES is no longer secure. Do not use unspecified algorithms, and never touch the MySQL password() functions for application usage. They are hashes, not encryption in the first place, and they may change without notice between versions.

Also, make sure you have a concept for plaintext recovery whenever you are dealing with encrypted storage.

MySQL is easy on the operation system when it comes to privileges: It can run as a regular user with regular privileges and uses no privileged ports, either. Everything sensitive is in or below datadir, so make sure you protect that appropriately. Also, if you do not require networking, use bind-address. skip-networking is deprecated, and it is incompatible with everything but the Connector/C based APIs, anyway.

Backups are often leaving your secured physical parameter and are being shipped and handled by persons you may have no control over. Always encrypt them, at least by piping the backup stream through "openssl enc -e".

Disk and file encryption can be used for the same level of protection at the disk level, but are basically ineffective against hackers - the hacker will see the filesystem as a mounted disk, and consequently will have access to the data in clear.

Backup is useless, it only costs money. The value is in recovery. So talk about backup in terms of recovery to make your bosses understand how you can meet their requirements. Replication is essentially a rolling recovery: You recover a new slave from a full backup, and then automatically apply the binlog as it is being generated. Use slaves for backup whenever possible - in a failure situation the slave is a readily recovered instance, saving you a lot of hassle and time.

Accounts: In Unix, you have to secure your installation manually, in Windows "setup" takes care about this by default. Use a simple and easily audited permission schema for databases. If you need to dive down to table or column level privileges, you are most likely using a too complicated architecture (or have a weird problem). Sometimes, you can make do with 3 users per schema - r/o, r/w and schema admin.

You do get away with this, because even if you have many principals (usernames) at application level, these are usually not propagated down to the database layer. This enables connection pooling, and creates good performance and many problems in auditing.

DNS has no security. If you make your security dependent on DNS security, you won't have any security at all.

There are a lot of queries that you can use to audit your user table.

4.1 improves password security. Use it.

Audit is less useful than you'd think at first glance - most often you'd like to know what was done in terms of the application, but at database level, you'll see only database users (to little information) and database operations (too much information).

It is currently not possible to fake audit using triggers on mysql.* tables (Bug #18361).