On Tue, Jan 18, 2005 at 12:35:01AM -0600, mstory@uchicago.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/
