CODEWITHSUNDEEP
mysqldatabase-designdatabase-schema
Kumar Roshan Mehta
Kumar Roshan Mehta

Reputation: 3316

Database Schema for group

I have a simple user and group database.

User: uid(pk), name, password ( don't worry about password in plain)

Group: gid(pk), name, owner_uid

Member: uid (fk), gid (fk) ( a user can belong to multiple group)

Now, I do not trust my database administrator and want to encrypt/secure specific coloumns so that db admin cannot be able to add any existing uid to any gid. How I could modify my scema and which coloumn I should encrypt.

I have a encryption key which is assumed to stay secure and I wanted to utilize sql queries also so cannot complete encrypt the tables and store as blob.

Upvotes: 2

Views: 791

Answers (1)

Gholamali Irani
Gholamali Irani

Reputation: 4350

I think you can not perform this requirement with database constraints. You can manage it by programming. However if you write some triggers to handle it, your (bad) administrator can access triggers too.

Add an extra column to Member like key_info.

  1. In Insert: make a hash code (like sha256Hex) with combination of uid and gid and specific salt. And insert with uid and gid into Member.

  2. In Selects from Member: you have an extra difficulty. You should select the records (from Member) that have correct hash code.

If you have a big and complex salt and your administrator can not access the salt (as you said in comments), he/she can not find the codec algorithm. So if he/she added some records into Member, your codes ignore the added record in selects, because the key_info can not be true.

Upvotes: 2

Related Questions