Re: Users and unique identifyers

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