I would like this too. I have thought along similar lines, and would like to expand a bit on the concept. I don't think it should be a "random" thing like, say, "there's a 1% chance each turn that a mine will run out", but each resource having a preset value of shields that can be exploited, something like a mine or tree in Age of empires. Working a mine would not necessarily add to the current production, but could also be stockpiled in the city. For example, overworking an iron mine would quickly exhaust it, but also add lots of iron to the city for future use.
Specific number of "iron" shields (or anything - grain, animals!) could then be used as a Railroad tycoon-type commodity, transferred between which city of yours need it the most, or even traded to other civs! It would add a completely different dimension to the game, and of course a micromanager's heaven.
As for units needing certain resources, you could say that each type requires a certain percentage of their total cost to be of the specific material. A swordsman, for instance, would need at least 10 iron shields. It can't be too many, because with this system, you couldn't actually build a swordsman without iron in the city's range - it would have to be transferred from a city that works it (which could be an automated and painless process, but still requiring that the other city actually works or having worked the iron mine, not merely having it connected). If a city is captured, so are the resources it contains.
A middle way would be to give each resource a set number of turns it can be worked, after which it expires. To counter this, there would need to be more resources to start with.
Also, stuff like plants and animals could possibly be re-planted in different tiles. Imagine if a worker had the ability to "lift" crops from one tile, carry it to another, and there plant it. This could potentially be overpowered, so some measures would have to be taken to counter the effect. The worker could only move one square at a time while carrying crops or herding animals, and during the time (several turns) that the original square regrows and the new one grows, neither can be used.