Bascially, all types of radioactive material can create mutations in living organisms, as long as the organism's DNA is exposed to the radiation long enough.
All light (including heat) is radiation, but only some types of radiation carry enough energy to harm organisms. Radioactivity is one such type of radiation (UV light is another). Radioactivity is harmful to living cells because it is ionizing radiation, which means that it can rip away electrons from an atom. This can destroy or harm important biological molecules, not just DNA.
Very strong radiation can kill cells outright. This is the type of radiation that is used to kill cancer cells, for example. In other instances, radiation will not kill the cell but instead may cause a change in the structure of the DNA (a "mutation") within the cell's nucleus. DNA is a complex molecule involving bonds between many atoms. If some of those bonds are disrupted because of a loss of electrons from the atoms involved in the bonds, then the DNA molecule now contains errors, and the cell may be unable to produce proteins or enzymes. There are repair processes within cells to fix errors that accumulate in DNA molecules, but these processes may not catch all errors, or may not be able to keep up with rapid mutations. Some mutations may be so harmful or there may be so many of them, that the organism dies within a few months. On the other hand, the mutation may take years to affect the organism's health, or may not be harmful at all. The average person would only be exposed to low doses of continuous radiation (smoking or lots of X-rays, for example), and so would see the effects of a mutation only after a long time lag.
There are two things you need to worry about when considering exposure to radioactivity:
1. how strong the radiation is (how much energy you're exposed to)
2. what type of energy it is: alpha particles, beta particles, gamma rays or X-rays
Alpha particles are the weakest form of radioactivity and cannot penetrate the skin. However, if you were to ingest or inject alpha particles into your body, they would do more damage to your cells and DNA than other types of radiation (for a given amount). Some beta particles also cannot penetrate the skin, but some can. Beta particles can also interact with some materials (lead) to produce gamma rays. Obviously, X-rays can penetrate the skin but are stopped by bone, which is why they are used to look at the skeleton. Gamma rays are the strongest form of radioactivity and, if not shielded, can penetrate the skin from across a room. If you don't go around drinking or injecting radioactive solutions, gamma rays are the worst of the bunch.

Radioactive materials in general emit three basic types of radiation: alpha particles (energetic helium nuclei), beta particles (electrons or anti-electrons), or high energy (x-ray or gamma ray) photons.
Each of these three types of radiation can penetrate into the human body to a different extent. The particular type of radiation that is emitted by a given radioactive substance usually depends on the number of protons and neutrons in the nucleus. Each type of radiation might have slightly different effects on organisms.
The basic idea, as I understand it, is that the radiation can damage the DNA in a cell when it hits it, which would cause a mutation of some sort. Most of the time, this would cause the cell to die.
Sometimes, though, the cell may be able to survive with the mutation and even replicate itself. Cancer is caused by a mutated cell gone awry. The cells don't work correctly, don't die when they are supposed to, and replicate themselves like mad, usually preventing other normal cells from functioning properly.