I love mods, probably way more than I should, and it is always amazing the things that creative people can come up with. The Heroes of StarCraft mod for Civilization V, along with a partner mod by the same modder, adds hero units to Civilization V.
These hero units include Jim Raynor, Kerrigan, and Zeratul. Along with these hero units there are also the Protoss, Zerg, and Terran Civilizations and all of the units that you would expect. To install this mod just subscribe to it on the Steam Workshop.
In the comments section, the mod's author, epic, says his eventual plan is for the mod to have heroes from other Blizzard games like Diablo II and Warcraft III. Sounds good to me.