Post reply

Secure a specific table

  • May 28, 2010 at 8:40 am

    #233149

    Hi,

    I have a database that has a number of different tables within it. But one specific table has very sensitive data in it, so I only want specific users to see the data within it.

    Can you tell me how I set permission to revoke all permissions (including sysadmins) from the table so that only a specific user can see or update the data.

    I am using mixed security mode.

    Thanks

    Pete

    Peter Gadsby
    Business Intelligence Consultant
    www.hgconsult.co.uk

  • May 28, 2010 at 8:49 am

    #1173966

    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

    #1173971

    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

    #1173975

    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

    #1173984

    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 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply