4

I have a table with both user_id and vote. They are stored together so each user can only give one vote. However it's also problematic because admins can simply read who voted what. Is there a way to make it so that only the system(minus all humans) and the user knows what was voted by h(im|er)self?

  • Does "admin" in this context mean "DBA"? Or "application administrator"? It's possible to prevent DBAs from reading certain bits of data but it will involve installing and configuring Database Vault (in addition to licensing it). Application administrators are much easier to deal with, you can just create an appropriate VPD policy. – Justin Cave Jul 4 '15 at 19:24
  • @JustinCave assumes Oracle, but what is the actual database you're using? Also, how do expect the database to distinguish "system" (whatever that is) from a "human"? – mustaccio Jul 4 '15 at 21:25
  • @mustaccio - Good catch. I mistakenly thought this was tagged for Oracle. – Justin Cave Jul 4 '15 at 21:30
  • Please, redo your question and add some context about what roles are. – oNare Jul 5 '15 at 0:07
0

I'm not sure how much scope you have to make changes but why not separate the 2 concepts.?

Have a field or flag on the user table has_voted. And put the vote in a separate table.

App logic checks to see if they have voted, and only allows them to vote if the field is blank. Vote gets inserted in separate table so you can't tell who has voted for what.

0

How about encrypting the vote using a key that only the user and/or the system knows?

Adding some more information:

  • Use a symmetric encryption so that you can decrypt it.
  • Encrypt outside the database. Do not use database encryption functions if you want to protect it from DB admin because admin has several ways to snoop into the queries and read the keys. Instead, encrypt the data in your programming language of choice, and just store the encrypted data in the database. Admin never gets access to the actual data.
  • You can either have one secret key per user, or one common secret key for the entire system. However, you have to keep the key secret from the DB admin.I prefer going for one key for the entire system because that requires me to maintain only one key.
  • If you use a single key, you should add some random data to the votes before encrypting those. This will ensure that encrypted values for votes would look different for each user. One way to do it is to prepend 5 (>0) random characters before the vote data every time before encryption, and discard first 5 characters after decryption. Other option is to add the userid before the vote data before encryption. In the absence of such randomization, the admin may be able to 'guess' and 'alter' votes.

Hope this helps.

  • Ranvijay, I agree with the direction you're suggesting. If you expand your answer to give more detail, perhaps an example and discuss how this gives the protection the original poster is asking and also what potential shortcomings your answer might have, then you will get more up-votes and soon you will build enough reputation to be able to participate in the site in more ways. – Joel Brown Jul 5 '15 at 12:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.