May 28, 2010 at 8:49 am
The best way would be to put it in another schema and manage the security on that schema, but I will operating under the assumption that you can't move it to a new schema.
Create a database role and add users to it. Grant that role access to the table, and no other roles or users should have access.
This will not prevent administrators from accessing this table. The nature of the roles means that it has to have access to everything. If you really need to protect the data from administrators, encryption is probably the only way (presumably where the responsible parties have the encryption key, and the administrators do not). If the data is truly that sensitive, encryption might be a good idea any way.
--J
May 28, 2010 at 8:55 am
Ok thanks. I'll give encryption a crack... The problem is this is HR data and therefore even administrators should not see the information.
Peter Gadsby
Business Intelligence Consultant
www.hgconsult.co.uk
May 28, 2010 at 8:59 am
If I added a schema, could I restrict sysadmins to that?
Peter Gadsby
Business Intelligence Consultant
www.hgconsult.co.uk
May 28, 2010 at 9:08 am
No, you can't restrict sysadmin access from anything. They are the sysadmin, and have to be able to maintain the entire database. Using a schema is just best practice, and allows you to group multiple HR tables together without having to maintain their security independantly. It's a little more scalable than securing a single table.
--J
Viewing 4 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply