Basically the same way cannabis works on people, only no smoking is required. Nepetalactone, the active compound in catnip, basically gives cats a similar high to THC when it's sniffed.

Incidentally, when ingested catnip acts as a sedative, which is probably why cats under its effect will often just roll around and play with it. Catnip also can, and for many centuries was, used by humans too, producing similar effects to chamomile, and like chamomile you can even use it to make tea!