My opinion based on going through the clearance process for the NSA is that the federal gov't is fairly pragmatic and the anti-drug use policy is primarily a way of ensuring state secrets are not leaked, via extortion, bribery, etc. For instance, buying drugs from people who might intend to blackmail with that information at a later point. See also someone with e.g. a gambling addiction, skeletons in one's closet. In general, owing people money, wanting certain personal things hidden, and so on tends to compromise one's integrity. Which is to say, if you can demonstrate that integrity it is my belief that no one would deny clearance on moralistic grounds.
Went through a security clearance years ago (in the UK). Explained to another candidate also about to go through the process that we would be asked questions about our sex lives, etc. and had to be honest. They were horrified.
I suspect it was the horrified nature of having to discuss it that ruled them out (they failed), not the fact that they - I inferred through gentle questioning - had experimented a little with a same-sex relationship whilst at University.
If you're OK with your family finding out or it going in the local paper, you can't be blackmailed over it. You're probably fine.
If you are terrified by the thought of whatever it is - even if it's legal - becoming knowledge of those you care about, and even more so those you don't care about, that's likely a problem.
Within security services and law enforcement, hanging around with criminals - which is what drug dealers in most places are - will rule you out for obvious reasons.
Even in places where drugs (weed increasingly, booze almost everywhere in Europe and North America), being a heavy and habitual user of substances is going to raise an eyebrow and prompt a discussion, at least.
