I sell a lot of cat toys in my etsy shop. Every single one of them contains cat nip. Does this mean that I'm a kitty drug peddler? Apparently the answer is yes.
Not every cat reacts the same way to cat nip. Only about 3 out of every 4 will go wild for the stuff. The reaction seems to have a heavy genetic component; American felines often love it while their Australian counterparts tend to ignore it.
Humans usually find the odor pleasant but not overly interesting, probably because we don't use our noses the same way that cats do. I always assumed that the reactive chemical in the nip, (called nepetalactone) somehow stimulated the cat's vomeronasal organ, a chemoreceptor that we lack. Recent studies show that this isn't the case. Instead the nepetalactone binds to receptors in their olfactory epithelium where it probably mimics a feline pheromone. The result is temporary euphoria and possibly mild hallucinations.
This reaction is short-lived, however, and after a few minutes the kitty usually gets tired of rolling around and acting crazy. They will then be immune to the siren song of the nip for about two hours, after which they may return to the same toy for another hit.
There is conflicting data on the reaction of big cats (lions, tigers, pumas, and such) to nip. Some tigers seem to love it, but most seem to be fairly indifferent. Then again, these guys are a lot bigger than our charming feline housemates, so maybe they just need really big toys to fully appreciate cat nip's recreational potential.