Words: 349 Reading Time: ~1.5 minutes.
The lead up
Recently I was asked to create a temporary user with SELECT permissions on a database.
So far, not a problem. Taking advantage of the pre-defined roles in SQL Server, I just add this new user to the pre-defined role [db_datareader], which grants SELECT permissions to all tables and views in a database.
Why would I grant SELECT permissions this way and not manually choose the tables and views that this user could connect to?
Well,
- This is a test server so there is no sensitive information held that I’m worried about blocking access to,
- I didn’t get the exact requirements of the tables this user is to query so I don’t know which tables/views to grant access to and which to deny access to (I consider this a mistake on my part and something I have to act on next time),
- The test user is only required for 2 days, after which it is getting reviewed and deleted as quickly as I can, and
- Efficiency.
Efficiency, how?
Why grant SELECT on tables individually when I can grant on all tables in 1 fell swoop?
In the same vein, hypothetically speaking, if I was asked to grant SELECT permissions on 96 out of 100 tables, I would GRANT SELECT on all of them and then DENY SELECT on the 4 required as long as no column-level GRANTs have been given on those tables.
Summary
A recent notion that came to me was that one of the roles of a DBA is to gather knowledge, but to a level that promotes efficiency.
Sure, we know how to grant permissions, but we should also know the pitfalls, such as “deny beats grant unless the grant is on the column level” or “there are some combinations of permissions that allow more than intended“.
Knowing these caveats allows us to say when options can be automated or where rules need to be added to check for different statuses.
This will allow us to move on to the next aspect that needs a DBA’s eye and gentle guiding touch…or 2 cups of coffee and a full throttling !