(Warning: This gets a bit long and potentially tedious.)
Data privacy in pharmacies (and I’m speaking specifically of the area that dispenses prescription medication, not the rest of the store) is almost exclusively governed by HIPAA, including ID logs, and HIPAA is quite comprehensive.
Every pharmacy employee is required to undergo HIPAA training before they can start working behind the counter, and periodic refresher training is standard. HIPAA violations are taken very seriously in pharmacies. You are generally not going to be at risk of having any information leaked by pharmacy staff, and if any is, the person who does it will be penalized, often severely; for the most egregious offenses, the worker is blacklisted from employment in pharmacy (in addition to other penalties). Jail time is even sometimes involved for the worst cases.
I don’t know the technical details of the security involved, but I would be surprised if modern prescription data isn’t encrypted at every stage. Robust data encryption is mandated for receiving electronic prescriptions (the default these days). Pharmacies still have to maintain physical files for prescriptions received on paper (uncommon now, but still a measurable amount of traffic, especially in less urban areas), but access to those is restricted to HIPAA-trained pharmacy staff, and physical access to these files is rarely needed. And once the prescription information is entered into the computer, it is subject to the same protections as that received electronically.
When ID verification is required, it is logged by computer. There is no actual validation of the card info; it is simply logged. For prescription pickup, the card data is re-accessed only when there is reason to question who picked something up. The ID logs for pseudoephedrine are sent to a state-level database to ensure that people cannot violate quantity limits by going to multiple stores, but it is still subject to all applicable HIPAA provisions.
The only access to any of this data is by pharmacy staff, corporate staff (still requiring HIPAA training), and law enforcement (only when appropriate, not as a matter of course). Normally, when this data is provided to law enforcement, it is most often the pharmacy that determines law enforcement needs to be involved, not the other way around, so warrants are not commonly involved. If law enforcement approaches the pharmacy for this data, most pharmacies will be cooperative, because it’s in their best interests to do so—they can be liable for prescriptions dispensed to unauthorized persons. So while warrants are not unheard of, they’re not commonly used or needed for this.
Insurance companies do not have access to ID logs unless law enforcement determines it is necessary; to prove to insurance that an unauthorized person picked up a prescription normally requires only that the person who picked it up have been properly logged, not that the actual log data be provided to the insurer. Such verification of logging would normally be conducted by phone, not via electronic messaging, and the actual log data isn’t seen by the insurance company unless the law requires it (which usually happens only in the event of legal proceedings of some sort). The statement that an unauthorized person picked up a prescription is normally going to be taken at face value by insurance personnel, unless the patient has a history of early refills (actual or attempted), so it’s usually just a matter of the pharmacy staffer getting an override to a “refill too soon” rejection.
There are no “pharmacy databases” in the sense that you appear to mean. Pharmacy data belongs to the individual store (and to corporate, if it’s a chain), and is not accessible from outside the company except by going directly through a staff member of the store or chain in question. In many cases, only limited data is accessible to other stores in the same chain, and ID logs are not normally part of that. (This is all excepting pseudoephedrine logs, which are required by law to be accessible to law enforcement and other pharmacies.) The only time such data is shared outside the chain of care is if a prescription is transferred between pharmacies or if a pharmacy is permanently closed. In the former case, only the data relating to the transferred prescription(s) and the specific patient is shared, again HIPAA-protected; this is considered under HIPAA to be an extension of the chain of care. In the latter case, the data is purchased in toto by another pharmacy in the area so that the prescriptions are not lost, and is also protected by HIPAA. As for length of data retention, retention of ID logs varies by state. Other prescription data may be retained indefinitely, as a patient’s prior medical history is relevant at least until their death (and, usually, settlement of their estate).
The “chain of care” involves doctors, insurers, pharmacies, other medical providers, and patients, and all data transferred between these entities is subject to HIPAA. (In the case of patients, they are technically not subject to HIPAA restrictions, as the patient and their designee(s) are the only persons authorized by the law to grant access to their own medical data outside the chain of care. It is by legal definition impossible for a patient to violate HIPAA with regard to their own data.)
It is of course possible, and actually not that uncommon, for pharmacy staff to write down on paper any information they have access to on the computer, but that doesn’t absolve them of HIPAA requirements. Waste paper in retail pharmacies is typically collected in shred bins that are periodically taken by third-party shredding contractors (Iron Mountain is a commonly used one) and the contents made unreadable prior to final disposal. Accidental disclosures are possible when anything is written down, but in practice that rarely happens because of the strength of HIPAA.
It is important to not underestimate the power of the HIPAA law. No other personal data in the US is subject to as stringent privacy laws as that which is protected by HIPAA. While the law does not mandate specific technological practices in detail, the non-specific requirements are fairly comprehensive, and most of the details are codified in supplementary laws and regulations (often at the state level). These requirements are continually being updated as technology advances.
So no, you aren’t signing away any privacy rights when you sign for your prescriptions. You’re affirming them.