Re: Users and unique identifyers

I need a way to link every user name to a unique ID held in a table in the
database. I've tried several ways of doing this but all of which are not secure
enough. It's essential that no one be able to view the user names, though the
unique identifyers will be viewable to all users on the public schema. My best
bet right now is to store the user names in an encrypted table with a column for
the user name and a column for the id. This method seems to be the most secure.
Am i on the right track here?

many thanks,

matt

On Tue, Jan 18, 2005 at 12:35:01AM -0600, mstory(at)uchicago(dot)edu wrote:
>
> I need a way to link every user name to a unique ID held in a table in the
> database. I've tried several ways of doing this but all of which are not secure
> enough. It's essential that no one be able to view the user names, though the
> unique identifyers will be viewable to all users on the public schema.

How do you define "secure enough"? Does "no one" include even the
database administrator? The system administrator? Whoever's making
and storing the backups?

> My best bet right now is to store the user names in an encrypted table
> with a column for the user name and a column for the id. This method
> seems to be the most secure.

Encryption might be the way to go if security requirements are high.
Make sure the keys are safe. Do some research on how to handle
credit card numbers and treat your user names with the same care.

Even with encryption you might want to prevent other users from
viewing the ciphertext by using a view. Something like this:

CREATE TABLE users_private (
id serial PRIMARY KEY,
name text NOT NULL
);

INSERT INTO users_private (name) VALUES ('John Doe');
INSERT INTO users_private (name) VALUES ('Mary Smith');

CREATE VIEW users AS
SELECT id, '********'::text AS name FROM users_private;

GRANT SELECT ON users TO PUBLIC;

Another user does this:

SELECT * FROM users;
id | name
----+----------
1 | ********
2 | ********
(2 rows)

SELECT * FROM users_private;
ERROR: permission denied for relation users_private

Even without encryption this would be a way to hide the names,
although it might not be secure enough for your needs.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

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.

the table itself will need to be accessed by triggers, so the encryption plus
the view seems to be a good way to handle it.

Thanks for the help,
matt

Quoting Michael Fuhr <mike(at)fuhr(dot)org>:

> On Tue, Jan 18, 2005 at 12:35:01AM -0600, mstory(at)uchicago(dot)edu wrote:
> >
> > I need a way to link every user name to a unique ID held in a table in
> the
> > database. I've tried several ways of doing this but all of which are not
> secure
> > enough. It's essential that no one be able to view the user names, though
> the
> > unique identifyers will be viewable to all users on the public schema.
>
> How do you define "secure enough"? Does "no one" include even the
> database administrator? The system administrator? Whoever's making
> and storing the backups?
>
> > My best bet right now is to store the user names in an encrypted table
> > with a column for the user name and a column for the id. This method
> > seems to be the most secure.
>
> Encryption might be the way to go if security requirements are high.
> Make sure the keys are safe. Do some research on how to handle
> credit card numbers and treat your user names with the same care.
>
> Even with encryption you might want to prevent other users from
> viewing the ciphertext by using a view. Something like this:
>
> CREATE TABLE users_private (
> id serial PRIMARY KEY,
> name text NOT NULL
> );
>
> INSERT INTO users_private (name) VALUES ('John Doe');
> INSERT INTO users_private (name) VALUES ('Mary Smith');
>
> CREATE VIEW users AS
> SELECT id, '********'::text AS name FROM users_private;
>
> GRANT SELECT ON users TO PUBLIC;
>
> Another user does this:
>
> SELECT * FROM users;
> id | name
> ----+----------
> 1 | ********
> 2 | ********
> (2 rows)
>
> SELECT * FROM users_private;
> ERROR: permission denied for relation users_private
>
> Even without encryption this would be a way to hide the names,
> although it might not be secure enough for your needs.
>
> --
> Michael Fuhr
> http://www.fuhr.org/~mfuhr/
>

> 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.