It depends. For the 60s, I think the redshirts on TOS weren't that bad. They got in a couple scuffles. But you go to TNG era and it seems these guys only know how to use a phaser. There's barely any serious hand to hand. Sure, there's the big fight in OPS on DS9 with the Klingons, but those were our leads. All the security guys on the
promenade were chasing Klingons with phasers while getting eviscerated.
They all seem kind of soft if that's the right word. If I were an ambassador in hostile territory, I wouldn't feel safe with them. The MACOs on Enterprise though, I thought they were great --- and as an aside, I don't think you can go wrong with Steven Culp (Major Hayes)

.
Call 'em Marines, MACOs, SWAT, SEAL Team 47, or whatever. I thought it interesting on Enterprise to have the MACOs.
EDIT TO ADD:
It's kind of the same thing with the ship battles as well. The security guys in TNG fight the same way their ships fight in space. Slow, stilted, and awkward. Best we got was the OMFG Badass Martian Defense Perimeter. I would also note that every other culture was a bit more aggressive in their ground/hand to hand tactics than Starfleet security. It makes no sense that those Galaxy Class hall monitors were also involved in ground security.
FINAL EDIT, I swear:
With MACOs, the Kazon don't take Voyager either. Major Hayes would have made quick work of them and I'd even say Lt. Reed would have held his own.