How do I get Safari to prompt me before allowing cookies
Safari Cookies (10.5-10.6?)
...is a cookie manager that I've used in the past. It's the only cookie manager I know of for Safari. I couldn't install it in Lion just now. I assume this is because they released a non-free version for lion.
I don't recall whether this extension prompts the user when cookies are stored. Features which you need are:

Automatic mode: Only keep cookies/Flash cookies/Databases from sites which are in your Favorites, BookMarks, Top Sites, or History.
Save favorite cookies to a whitelist.

Using the latter feature, you will accept cookies on a per website basis - not per cookie basis. This allows you to block vast amounts of cookies without spending too much time managing them.
The developers have released a version for Lion which is not free:
Cookie (Mac App Store)
You can download a trial on their website.
Other stuff
Also consider the following extensions to block cookies and protect privacy.

Ghostery: Full detection of tracker scripts, img tags, and iframes from over 600 data collection services.     Blocking capability for each detected element.
Adblock: Hides ads and also prevents some related cookies from being download. You can include lists which will allow you to block more than just ads. E.g. EasyPrivacy List, Antisocial List, Malware Domains List.
Incognito: Incognito protects your privacy by blocking Google Adsense and Google Analytics on non-Google pages. In addition, it allows you to optionally block Facebook and Twitter content on third-party websites as well as embedded YouTube movies outside of the YouTube website. Google also provides other extensions to opt-out of their tracking. However they are not yet available for Safari. (IBA Opt-Out, Keep my Opt-Outs)
Google Analytics Opt-out (beta): Official Google extension. The add-on communicates with the Google Analytics JavaScript (ga.js) to indicate that information about the website visit should not be sent to Google Analytics.