Yes, as Lorna says, most sites accept them in lieu of passports. Unlike in the UK, on most French and many other continental sites, you pay when you leave, rather than when you arrive, so not unreasonably, they need some sort of deposit from you. We have also found that some sites which have a barrier key system require a cash deposit for this key, but will sometimes waive this if you have a Camping Card. Theoretically, some sites also offer a discount, but we have never stayed on one that does.
They are available from the Caravanning Club, the Camping and Caravanning Club and the RAC. I assume that you have to be a member of one of these to obtain one, but I am not certain of that - we are members of two out of the three, so it has never been an issue.