Cyber Defense

Practical Risk Analysis and Threat Modeling Spreadsheet

risk analysis spreadsheet

[Download the spreadsheet for this article from the Downloads page of this blog. Get the SEC505 zip file ( and look in the Extras folder of the zip. The zip file contains many other folders and scripts in the public domain which I hope you will find useful.]

Learning to see your network through the eyes of your adversaries is an important part of the risk analysis and hardening process. Risk analysis involves identifying what you care about and the threats to these assets, hardening is about how to protect your assets.

A risk analysis and hardening process can range in character from the highly formalized (acronym soup, pseudo-mathematical formulas) to the very casual and informal (just a group of techs talking about "cool hacks" over beers and how they should "do something" about it). Practical risk analysis and hardening lies somewhere in between these two extremes.

Something near the middle of these two extremes, but tending toward the formal side, might be Microsoft's whitepaper "Improving Web Application Security: Threats and Countermeasures", and something near the middle, but more on the informal apply-some-commonsense side, might be Bruce Schneier's five-step recipe in his book Beyond Fear.

For most organizations, if a risk analysis can't be finished by a small group of network administrators in one afternoon (or at the most, a couple days), it just is not going to be done?that's just a fact of how most organizations operate, especially when IT staff is limited. Fortunately, half of the benefit of doing a risk analysis comes from simply trying to do one, no matter how ugly and sloppy the end result is.

So what follows is a recipe for a half-dozen IT engineers to do a practical risk analysis and get a hardening plan started, all in one day. It's not intended to be perfect or comprehensive, nothing ever is, it's intended to be practical. There's an associated spreadsheet for this article (get it here in the SEC505 zip file) which can help consolidate the notes from your brainstorming and planning, and it may help with generating a report or proposal to management afterwards.

Step 1: Make A List Of What You're Trying To Protect

What are the good things you want to preserve (for this sake of this project, not everything whatsoever)? What are the services you want to keep available? What are the benefits you want to keep enjoying? You don't have to use complete sentences, just single words or short phrases to describe the assets you are worried about and the benefits you want to continue getting from them; for example, "Assets: the four SQL and six IIS boxes in the DMZ, keep them up, running fast, no errors, no doing restores from backups, keep the databases in sync with the other SQL Servers inside the LAN, same for the other four SQL Servers inside the LAN too, the backup tapes for all of these boxes, the networking gear that connects them all to the LAN and the Internet, keep response times fast."

Step 2: Draw A Diagram And Add Notes

On a big piece of paper, draw a diagram showing all the important networks, servers, virtual machines, clients, firewalls, routers, switches and other components that either are your assets or are required by your assets to stay available. Use different colored pens or markers for different types of items. Leave lots of blank areas for notes. Write in the names of devices, operating system version and service pack level, network ID numbers, running applications and their versions, names of Active Directory domains, and other important facts. If you're good with Microsoft Visio or some other diagramming tool, feel free to use that instead, but don't let a tool slow your group down.

Next, using different colored lines, draw and label arrows representing important network communications between devices, including their protocol names and/or port numbers, and, in the case of a client request and the server's response, number them from beginning to end. In the margins, briefly indicate what's going on in each numbered step. Don't forget to add communications related to remote administration, monitoring, backups, and other maintenance tasks. Your diagram probably now includes more assets than originally conceived.

Next, if you have web, middleware and database servers, get a separate sheet of paper for each type of server and list or diagram the major components used inside each server. Just as there are flows of communication between devices at the network level, there are flows of communication within a server between its services, scripts, software, processes, databases, etc. Don't try to be exhaustive here, we just want a list of the important components so nothing is forgotten, and the list will certainly be edited as you go along.

For example, on a sheet of paper for your web servers you might start with "IIS 7.0, ASP.NET, WebDAV, custom application X using regular ASP, custom application Y using PHP, third-party ISAPI filter Z, NLB, ARCserve backup agent". For the database servers, you might start with the names of the databases, tables with high-risk data, third-party applications, custom stored procedures, etc. Again, don't get too bogged down in the details, you'll be coming back to these lists repeatedly.

Overall in this step, you don't want details for the sake of adding details, you want a summary of the important assets and how they relate to each other, i.e., you want to be able to stand back and see The Big Picture.

Step 3: Make A List Of Your Adversaries And What They Want

Who are your actual or likely adversaries? What do they want to achieve? What are their skills and resources? How determined are they? What would they be willing to risk or give up in order to achieve their goals? What about insiders? You obviously can't know all of these details for certain, but having rough profiles in mind of your actual/likely adversaries can be extremely useful, especially for the next step (and justifying your recommendations to others). For example, you might have "casual hackers looking for random sites to deface, skilled hackers looking for credit card numbers, botnets used for DoS attacks and extortion, XSS worms for installing spyware on random servers, paid hackers from other corporations or governments trying to secretly take control of our DMZ servers, our own disgruntled webmasters trying to steal a copy of the marketing database".

There are research reports available which help to characterize your adversaries, their goals and their methods. For example, a 2008 Verizon report on over 500 security incidents breaks down the percentages of attacks by source (internal, external or partner), percentages of breaches caused by zero-day exploits versus simple misconfigurations, how breaches were detected, etc. Similar reports from other organizations can be found most easily by searching or subscribing to free security-related news feeds, such as at and

Step 4: Brainstorm Threats From These Adversaries

Now, imagine you are one of your adversaries and try to see your network through their eyes. You know what you want, how would you try to get it? This assumes you have some familiarity with hacking techniques, but you don't have to be an expert or penetration tester yourself, you can get started with just a few hacking books (or a course at SANS). Vulnerabilities and threats also usually fall into rough categories that can guide your brainstorming, so keep your chosen adversary in mind and brainstorm answers to the following questions:

  • Denial of Service: How can I crash the server? Run the CPUs near 100%? Consume all the free hard drive space? Consume free memory? Prevent legitimate users from connecting, authenticating or using the applications successfully? Prevent administrators from connecting? Prevent backups from running? Confuse the protocol stack? Overwhelm the routers, firewalls, switches or bandwidth? Distract or waste the time of the network administrators? Perform "data diddling" against databases to render their information worthless? Capture, modify and retransmit packets to corrupt data or disrupt network sessions?
  • Authentication: How can I log on as a legitimate user? Sniff credentials off the wire? Highjack a user's existing session? Trick the server into using a less secure authentication protocol? Trick the server into not requiring credentials at all? Change my apparent source IP or MAC address? Use malware on users' computers to steal credentials?
  • Elevation of Privilege: If I can authenticate as a regular user, how do I execute commands with elevated privileges? Even if I cannot authenticate, how do I run commands with standard and/or elevated privileges? How do I trick the server into executing commands with no authentication or authorization checks at all, such as with a buffer overflow exploit?
  • Disclosure: How do I trick the server into revealing the information I want in plaintext form? How do I get the server to reveal the location of the data I want? How do I crack the encryption? Where are the keys stored or how are they generated? How do I make the server store data insecurely? How do I make the server transmit data insecurely, such as disabling SSL or killing the IPSec driver?
  • Tampering: How do I make and save changes to my target database, file, encryption key, registry value, session, or other data structure? How do I make the change so that it will be accepted as a normal transaction? How can I repeat or replay a transaction or transmission and have it accepted as legitimate?
  • Malware Installation: How do I get malware of my choice running on the server? How do I upload files or trick the server into downloading files of my choice? How do I construct a script or binary on the target server? How do I subvert a script or binary which is already on the target?
  • Stealth and Repudiation: How do I edit or delete log data after my attack? How can I hide my packets/commands/data from inspection by firewalls, IDS/IPS sensors, or security staff who are sniffing the wire? How can I thwart auditing or forensics of the server afterwards? How do I frame other users for the crime? What do I need to change in order to deny anyone the use of data which could get me prosecuted or fired if I am identified?
  • Social Engineering: And for all categories of attack, how do I use Social Engineering (SE) tricks to make the attacks work or be even more effective? SE is often both forgotten and the most effective attack method.

List your threats in a spreadsheet and expect to revise the list. Put all the threats in one column which has been labeled "Threats".

Step 5: Estimate Probability And Potential Damage (The Overall Risk)

In your threats spreadsheet, add the following column headers:

  • Threats: Description of the threat and/or vulnerability from Step 4.
  • Risk Score: Sum of the numbers in the following columns.
  • Legal Damage: How bad would the legal liability be if the attack succeeds?
  • Reputation Damage: How bad would the damage be to image and trust?
  • Productivity Damage: How bad would the damage be for user productivity?
  • Discoverability: How easy would it be to find the vulnerability or targets?
  • Exploitability: How easy is the attack in terms of skills and resources needed?
  • Stealthiness: How difficult would it be for IT to detect the attack?
  • Repeatability: How easy would it be to successfully repeat the attack after security personnel have learned to detect it?

Use your best guess to characterize each threat by assigning a roughly-estimated number under each column using the following guide:

  • None = 0
  • Low = 3
  • Medium = 5
  • High = 7
  • Worst = 10

Don't worry about the exact numbers and their precise meanings, it's not an exact science. Configure your spreadsheet so that the "Risk Score" column should be the sum of the other columns in that row, then sort on the "Risk Score" column from largest to smallest. You now have a rough prioritization of your threats in terms of potential damage and probability of occurrence. For the probability portion, the assumption is that a threat is more likely to be realized if 1) its corresponding vulnerability is easy to discover, 2) easy to exploit, 3) can be exploited without detection, and 4) can be successfully repeated even if prior such attacks have been detected and correctly characterized.

Step 6: Brainstorm Countermeasures And Their Issues

Now add two more columns to your spreadsheet:

  • Countermeasures: What will block or mitigate the threat?
  • Issues: What are the negative side effects of the countermeasure(s)?

Very briefly describe what countermeasures you intend to implement to block or mitigate the potential damage and/or probability of occurrence of each threat. Don't use full sentences, just indicate the ideas, e.g., "apply latest updates, use IPSec for RADIUS and SQL traffic, use host-based firewall, hourly live backups, add Snort sensor, require long passphrases", etc. If you need more space, add a new tab to the spreadsheet for that threat and make a note of this in the "Countermeasures" column.

Not sure where to start when choosing countermeasures? Well, consider starting with the 20 Critical Security Controls of the Consensus Audit Guidelines (CAG).

Most countermeasures involve a monetary price, time/effort expense, opportunity cost, negative externality, compatibility issue, or some other unwanted side effect which is not intended. Or perhaps the countermeasure is only effective when very special requirements are met. Or perhaps a countermeasure is required for compliance (PCI or SOX) even if it doesn't do much good. The "Issues" column is where you don't forget to consider all these things, it's a reminder. In some cases, the negatives that comes with an effective countermeasure simply make that countermeasure not worth it, even though it's effective, hence, it will have to be replaced with something else. In other cases, a countermeasure might be only partially effective, but because its "price" is small, you should still deploy it as long as other countermeasures are implemented too. Unfortunately, there is no formula for filling in the "Issues" column, it contents come from experience.

Step 7: Plan, Test, Pilot, Monitor, Troubleshoot and Repeat

You now have a rough guide of what threats are most important and what their countermeasures will be, so it's time to start creating more detailed project plans, performing lab tests, pilot tests, monitoring affected systems, and getting prepared to troubleshoot issues as they arise. Along the way, don't forget to document what you're doing and what you've discovered so that others in the organization can benefit as well. Start with the threats that scored the highest and work your way down. By the time you're done, the network will have changed or you will have learned about new vulnerabilities and attack methods, so the whole process will repeat again, but at least the next time through you won't be starting from scratch.


Even a crude risk analysis and hardening plan is vastly better than just winging it, and in many ways a crude plan is better than an overly formal one if the formal one will never be completed...or even started (another case of "the perfect is the enemy of the good"). I hope this seven-step recipe will help you get your own security projects underway!

Post a Comment


* Indicates a required field.