The Threat to Home Users
Many people underestimate the threat they face when they use the Internet. The prevalent mindset is "who would bother to attack me or my computer?", while this is true -- it may be unlikely that an attacker would individually target you, as to him, you are just one more system on the Internet.
Many script kiddies simply unleash an automated tool that will scan large ranges of IP addresses looking for vulnerable systems, when it finds one, this tool will automatically exploit the vulnerability and take control of this machine.
The script kiddie can later use this vast collection of 'owned' systems to launch a denial of service (DoS) attacks, or just cover his tracks by hopping from one system to another in order to hide his real IP address.
This technique of proxying attacks through many systems is quite common, as it makes it very difficult for law enforcement to back trace the route of the attack, especially if the attacker relays it through systems in different geographic locations.
It is very feasible -- in fact quite likely -- that your machine will be in the target range of such a scan, and if you haven't taken adequate precautions, it will be owned.
The other threat comes from computer worms that have recently been the subject of a lot of media attention. Essentially a worm is just an exploit with a propagation mechanism. It works in a manner similar to how the script kiddie's automated tool works -- it scans ranges of IP addresses, infects vulnerable machines, and then uses those to scan further.
Thus the rate of infection increases geometrically as each infected system starts looking for new victims. In theory a worm could be written with such a refined scanning algorithm, that it could infect 100% of all vulnerable machines within ten minutes. This leaves hardly any time for response.
Another threat comes in the form of viruses, most often these may be propagated by email and use some crude form of social engineering (such as using the subject line "I love you" or "Re: The documents you asked for") to trick people into opening them. No form of network level protection can guard against these attacks.
The effects of the virus may be mundane (simply spreading to people in your address book) to devastating (deleting critical system files). A couple of years ago there was an email virus that emailed confidential documents from the popular Windows "My Documents" folder to everyone in the victims address book.
So while you per se may not be high profile enough to warrant a systematic attack, you are what I like to call a bystander victim.. someone who got attacked simply because you could be attacked, and you were there to be attacked.
As broadband and always-on Internet connections become commonplace, even hackers are targetting the IP ranges where they know they will find cable modem customers. They do this because they know they will find unprotected always-on systems here that can be used as a base for launching other attacks.