Perhaps for someone who is undercover and needs to fit in, sure, but I don't think that predisposes a personality to take drugs in their own time. For both alcohol and drugs I doubt it has any relevance to most intelligence roles.
How.. mundane.
https://news.gallup.com/poll/353645/nearly-half-adults-tried...
Think: DEA employee, vs. DEA criminal informant. The job of the DEA employee is not to infiltrate drug gangs. It's to organize and monitor the infiltration of drug gangs. The criminal informant wears the wire. The DEA employee sits in the black van outside, listening to the wire.
The most common position in the CIA is "analyst." The job of a CIA analyst is to sit there in a cubicle and read/listen to all the evidence gathered by agents (or spy satellites, or wiretaps, or whatever), gathering any "interesting" stuff into a report (with a further fan-in to "senior analysts" who map-reduce all those reports into "intelligence".) Unlike an operative, an analyst doesn't even get to speak with any agents — let alone head out into the field to recruit them.
In other words, you need to have a slightly adventurous personality.