I've seen this question asked multiple times, and the answer is never detailed. I initially assumed that either hydrogen or oxygen had relatively large neutron absorption cross sections, however that is not the case, so what actually makes water a good absorber?
Water is useful for neutron shielding, even though water is not an especially good neutron absorber. Oxygen nuclei are basically invisible to neutrons, since oxygen-16 is a spinless doubly-magic nucleus. However, hydrogen has a both large scattering cross section and a low mass. Basically, in every hydrogen-neutron scattering event, the outgoing neutron momentum is spherically symmetric in a reference frame with half of the neutron’s initial speed. Since the neutron momentum is roughly halved with every scatter, neutrons with basically any energy reach thermal equilibrium with the water quickly.
High-scattering barriers are opaque for the same reasons that the undersides of clouds (which are made of transparent water droplets or ice crystals) are dark. There is some thickness of scatterer where the incident radiation is equally likely to be transmitted or backscattered. Many of these thicknesses, and the incident radiation is exponentially attenuated, even with negligible absorption. Thermal neutrons in water are about a thousand times more likely to scatter than to capture — which sounds like a small capture cross section, but really isn’t.
Water is also extremely cheap. Liquid water has the property that a water barrier (unlike, say, a brick barrier) has no chinks or gaps where the radiation can shine through unimpeded. The mass of the water makes it pretty okay at absorbing the gamma photons emitted when the neutrons capture on the hydrogen. And to top it off, the “activation products” produced by neutron radiation in water are deuterium and oxygen-17. These are both non-radioactive; they aren’t even poisonous.
Water is not a good neutron absorber compared to other materials. Water is a good neutron moderator; water slows neutrons down due to scattering collisions. Once slowed, the cross section for absorption of the lower energy neutrons by other materials such as boron (or uranium for fission) increases.
Heavy water is even less of a neutron absorber than light water. For some applications, absorption of neutrons by light water is an issue. A reactor fueled with natural uranium (CANDU design) requires heavy water as the moderator instead of light water for criticality due to the lower absorption cross section of heavy water.
Water is a good moderator (good at slowing neutron) because of the two hydrogen atoms. Recall that, in the collision of two billiard balls where the target is initially at rest, the incident particle can stop after collision only if its mass is equal to the mass of the target. In this way, the protons of the hydrogen nucleus can more or less stop the neutrons (of course most collisions are not head on). The recoil protons are themselves slowed by transferring energy to vibrational or rotational modes of the water molecule.
Paraffin, which contains a lot of hydrogen, is good at neutron shielding precisely for the same reason. On the other hand, heavy water contains only deuterons (aka heavy hydrogen) is in fact not as good a moderator as regular water.
The abundance of water, the facility to transport it and store it, are some of the reasons why was historically used as a moderator.
Just to put some numbers on scattering vs absorption of neutrons in water, I've pulled data from ENDF.
First, neutrons on hydrogen:
The total cross section is in green, the elastic scattering (neutron bounces off, has some energy loss between 0 and 50% roughly), and the absorption of neutron to make deuterium. Well, you really can't see the total curve since it falls under the elastic cross section line - basically all scattering is elastic. At fission energies (~2 MeV), the absorption cross section is 4 or 5 orders of magnitude lower than the elastic cross section. Only once the neutron is thermalized does that cross section get within several orders of magnitude.
It looks even worse - even at thermal energies the absorption cross section is 4 orders of magnitude smaller than elastic scattering, and that only gets worse at higher energies towards the original fission neutron energy.