I think that the (only) relevant factor is whether everyone else knows and wants it. Suppose your mod affected only yourself (so other players wouldn't know about it) and you played in a way that was mostly reasonable, but providing a little bit more covering fire than should be possible. Your teammates might still have fun while they're playing, though only if you don't overdo it; but even then, they probably wouldn't be too happy to discover your cheating once they'd already finished the map. If you give everyone infinite ammo, and tell them about it then that's fine, but because they know about it, not because they benefit from it.

For that matter, cheating in a competitive server set up for that purpose can be fun (though it can also be wildly unbalanced, especially if the game is asymmetrical)