I like this idea. You are right in that a percentage chance of detection would have to be worked out for subs operating in the waters of another civ, based not only on the average technology of that civ, but also the type of submarine. Earlier subs had to operate on the surface far more often than current nuclear powered subs, so early boats would be far more vulnerable to spotting and enemy action the longer they operated within the coastal waters of the other side. Additionally, while subs could be used in an espionage capacity, I think their primary role in this respect would be to aid the escape of spies that are operating in that civilization. Perhaps make the escape through an enemy harbor the safer option at a reduced time for return of spy to action if the player has a sub parked off of a coastal city of the target civilization.
The more obvious role of submarines operating in enemy coastal waters is the targeting and destruction of the merchant shipping capability of a civ with which you are at war. Assigning the sub to an "Interdict shipping" mission would only be available to subs operating in enemy coastal waters, and would allow the sub to reduce the gold, food, production, or a combination of the three, that is produced by trade routes every turn. This doesn't eliminate an enemy trader unit, but rather reduces its effectiveness until the submarine discontinues the mission or is sunk. Obviously, a single sub will have minimal impact, whereas a sub fleet or armada (or two) can have a devastating effect on an economy.
