Just a little idea for anyone who might want something of the sort in a mod. Instead of or in addition to the normal Great People, there could be Great Warriors. These Great Warriors could be combat units rather than city specialists. They would be produced from cities like great people instead of getting them from a tech or building them with hammers. Feel free to take this idea and warp it to your liking, it's just a possibility to think about when making your mod.

Something like this, maybe? :p Snaitf's Great General (http://forums.civfanatics.com/showthread.php?t=145525)

Yes i looked at that after i posted this. It's a cool idea there, but my suggestion is using the Great People to be able to generate units that are combat units rather than units to turn into specialists, research, create a building, etc. Actually, I'm guessing it's entirely possible to make them UU GP where each civ would recieve a different unit. It's also most likely possible for them to be Era based or some other way (tech/resource related) for them to upgrade thier strength as the game progresses. Making them UU as well as upgradeable would take much more effort though, but might be worth it for a complete game mod. Simply put, you could make Great People be Super Combat Units. As an example, lets say a star wars mod could use Great People to construct Star Destroyers while normal city production was used for different Tie's or proton torpedos. The options are limited only by your imagination. The Great Person system can be used for more than just one time use specialists.
Think outside the bun :D

Would it be possible to grant regular units the "hero" ability if they win a battle of epic proportions? Perhaps have them get the ability if they win despite having only a 15% or lower chance.
I just thought if this was possible then you might be more inclined to throw simpler units to the axe in hopes of creating a hero.