Spent nuclear fuel
A conventional nuclear reactor uses fuel in solid form, comprising pellets of uranium oxide (or sometimes MOX: mixed uranium and plutonium oxides) clad in zirconium in long tubes, and made into bundles. When they are fresh and unused they have very low radioactivity and can be handled easily, but once in a working reactor their fissile isotopes, such as uranium 235, get transmuted into many other isotopes, many of which have very short half lives and are thus very radioactive. Some of these "fission products" are good at absorbing the neutrons which are needed to keep the chain reaction going.
Over the years in a reactor the proportion of such neutron-absorbing fission products builds up, and the proportion of 235-U in the fuel, which started out enriched to 3 – 5%, decreases, until it gets to a point where the fuel is no longer productive. At this point it is removed. In light water reactors such as PWRs and BWRs the reactor is shut down for refuelling outages; this typically happens every 18 months or so, although only a proportion of the reactor's total fuel is replaced at each stop. In other designs such as CANDU reactors refuelling can be done while the reactor is still running.
When a reactor is shut down, the fuel still produces heat at around seven percent of the thermal power the reactor had while it was running. In an hour this heat production drops to 1.5 percent, and in a week it drops to 0.2 percent. For a reactor which produces 3 GW (3,000 MW) of thermal power (which would generate about 1 GW of electrical power) its heat output would have dropped to about 60 megawatts one week after shutdown. (That's still about as much as some Small Modular Reactors are designed to give at full power.)
When a fuel assembly is removed from a rector it is extremely hot, both thermally and radioactively, due to the heat produced by highly radioactive fission products (i.e. ones with short half lives). This fuel is immediately transferred to a pool (also known as a pond) which cools the fuel and shields its radioactivity from its surroundings (including people). It remains in the pool for a few years — normally between 2 and 10 years.
Dry cask storage
After cooling the spent fuel is moved to interim storage. This can be another pool, or "dry-cask" storage, where it can safely be stored for decades. At this point, air cooling is enough to keep the spent fuel from overheating, and it poses no danger to people near it.
See also Wikipedia article "Dry cask storage"
How does the danger from spent nuclear fuel change over time?
The activity of the spent fuel decreases rapidly when it is removed from the reactor. In the graphic above it is compared with natural uranium. After about a thousand years the spent fuel is dangerous to humans mainly because it is a somewhat toxic heavy metal, not because of its radioactivity. The main precaution then needed is not to eat it.