The system administrator will need to be able to see it, and will need remote
access to it.
The security requirements are extremely high, the standard in this case is set
by the state, so encryption will be a must.
I hope you remark that these two are incompatible.
* For instance 'extremely high security', for me, in the case of credit card numbers would be either one of these two solutions :
1- not having them at all (the user does its transaction directly with the bank)
2- storing them encrypted with the bank's public key, so noone can read them, even you, and only the bank can decipher them with the assorted private key. I dunno if banks are evolved enough to do that. Some I checked, gave online payment confirmations to my website, which were not even cryptographically signed...
In these two cases, the existing information is not compromised even if the machine is.
1* Now we have 'high security' :
Information stored, encrypted with a key that is different for each user. That can be derived from his password. So if the machine is compromised, the hacker can still dump the session info for users logged at that time, and recover their keys, but that's it.
2* Medium, made high security with a trick
If you, the administrator, can view the info, then surely a hacker can. For instance if you store all the information encrypted with a single key, well, this key has to be on the server somewhere, so you have to define how hard it is to rip it, and in reality, it is little more secure than no encryption at all. Should it be in the webserver scripts ? In a database function ? The best place for it is in your head, so :
You can sidestep this thing by storing the information twice :
- encrypted with a key unique to each user (like some password hash)
- encrypted with a global key that only you know and that is not on the server
Thus, when you're not logged in, your key is not available on the server, so a hacker would only get the active sessions. When you log in, your key musi be stored somewhere, but at least it's not in a variable named 'key' in a function named 'decrypt'.
Public/private keys should be used so that users can be inserted without you having to insert your secret key !
3* Medium security Encrypting the stuff in the database with a global key. In that case, the key can either be in the database, or in the client.
Key in database :
SELECT get_decrypted_user_name( user_id );
The key will be in that function, thus the db admin (and anyone rooting the machine) has it.
Also here, the DB trusts the client :
You write your app in PHP so it has some SQL injection holes. Someone injects "SELECT user_id, get_decrypted_user_name( user_id ) FROM users". Boom.
Key in clien1 :
SELECT get_decrypted_user_name( user_id, key );
- This one will nicely dump the key in the logs or even on-screen when some error occurs (or someone turns on statement logging)...
Of course in cases 1) and 2) above, the same remark applies, never put your key in a request, it will end up in a log.
Key in client v2 : SELECT crypted_username ... decrypt in client
Some lowly script xploit can make it public.
You see that this is not really better than no encryption. Of course anyone xploiting the webserver and getting a shell will have full access to the full secret information, which was not the case in 1) and 2) above.
From Michael Fuhr :
SELECT * FROM users; id | name ----+---------- 1 | ******** 2 | ******** (2 rows)
SELECT * FROM users_private; ERROR: permission denied for relation users_private
Hum, did you notice that in this case, the users can't view their own usernames ?
Well, does it really matter... after all you can store it in their session, they used it for logging in !
What I think you should try :
Store the usernames in the database, encrypted with a public key. For an added sense of security, make the crypted_username column hidden (with a view) and only offer a function verify_user( ) which is used to log in users, but cant be used to dump the table.
When someone logs in, encrypt their username and password, and check it in the database.
When the admin logs in, his password is used to decrypt the private key which can decrypt the usernames.
Now, how to avoid that things end up in the log which shouldn't have ? This could happen at login :
SELECT verify_user( crypted_username, crypted_password ) -> returns user_id or NULL
-> BAD !
Unless you use a prepared statement and verify there is no way it can end up in a trace somewhere.
To protect yourself from log leak, you can encrypt these two parameters again in the client and decrypt them in the server ; again with a pair of public/private keys, adding a salt generated by a sequence, and used only once. This way the two crypted strings will be used only once, and more important, be usable only once in this transaction :
- function get_key( bool increment )
increments (or not) a sequence and returns a throw-away secret key generated from it.
in the client :
SELECT get_key( true )
encrypt crypted_username, crypted_password with this pubkey -> crypted_username2, crypted_password2
SELECT verify_user( crypted_username2, crypted_password2 )
which in turn does get_key(false) which returns the same key as the client had, and decrypts the data.
I hope this is useful to you, and don't think you have 'really high' security when anyone who roots your server can read all the secret info in it.
---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
