Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Is it possible to whitelist access to a specific table (for example containing password hashes plus salts)?

My ideal situation would be zero permissions for anyone with access to insert, update, delete and select achieved through stored procedures.

How do I go about setting this up?

share|improve this question

migrated from stackoverflow.com Aug 6 '13 at 14:16

This question came from our site for professional and enthusiast programmers.

2  
using roles and permissions –  Mitch Wheat Aug 6 '13 at 8:21
    
Could you explain a bit more for someone who spends very little time working with the sql server? Perhaps sample code? –  ose Aug 6 '13 at 8:25

1 Answer 1

Access to databases in SQL Server is managed mainly through two principals:

  • Logins - Security principal that allows access to the instance.
  • Users - Security principal linked to a login that allows access to a specific database.

By default, SQL Server follows the practice of least privilege, meaing that when you create these principals (further documentation on that here), they have minimal access to a database and the objects within that database. Initially, these principals will only be able to connect to an instance or database but nothing else, such as selecting or modifying data.

This means that you, as an administrator, will need to GRANT permissions to principals that need access. For example, if you had a table and you wanted to give a user the ability to read data on that table:

GRANT SELECT ON table_foo TO user_bar;

Once this is in place, your user can select from table_foo, but do nothing else. You can further grant additional permissions to other users as necessary, or REVOKE/DENY permissions as well. See here for further explanation of this.

Finally, you can leverage roles to additionally manage your security. Roles are essentially containers that have permissions granted to them and you can assign users to these roles, much like Active Directory groups. SQL Server has several server and database roles that are available by default and are commonly used, but you can also create custom database level roles and assign specific permissions to those roles.

Overall, database security is a large topic that is outside the scope of a StackExchange answer, but the basic idea is you can manage a "whitelist" by assigning specific permissions on your database objects and tightly controlling the security around those objects.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.