I don't think it's something that you need to worry about unless you're gonna be overtly shitty. I have my beefs with Valve, but not on this topic. Even if you get caught hacking in a multiplayer game, your account isn't banned (though you may be VAC banned from playing multiplayer in games that participate in VAC). Steam-wide bans only come from chargebacks that are against your EULA--and their refund policy is pretty reasonable, I think--and similar stuff pulled against Steam itself rather than anything you'll do during the course of using the service normally.

And if you're really paranoid about it, you can pretty easily back up games and play them with a cracked Steam DLL. It's not a big deal.