Security is #1
Security is of utmost importance for and data repository and any Database Administrator with their weight in antacids and aspirin will tell you of many a restless night as they mull over whether they're doing all they can to secure the environments in their dominion. At the same time I've contended that one of the biggest things Database Administrators get wrong is that they make themselves known to their organization in a negative light. They do so by getting in the way of development, of impeding performance, of destabilizing the instances they manage though risky or heavy-handed monitoring, and more. It's this first point I want to dive into a bit more in this article: getting in the way of development. To the point: balancing that with the need to provide a secure space for users to store data and source valuable and intelligent decisions from the data.
The Principle of Least Privilege
The Principle of Least Privilege in effect states that any process/user/program should only have access to the information and resources that are necessary for the purpose of fulfilling the legitimate purpose for the access to that information. In other words: only grant access to the object(s) the end user needs and only those methods (INSERT, UPDATE, DELETE, SELECT) necessary. In Microsoft SQL Server there are multiple ways to accomplish this. One can grant individual rights to individual objects as rights are required or access methods are developed. This is the most surgically-precise approach for accomplishing Principle of Least Privilege. It's also a large block to your development teams or business processes. Every time a new stored procedure is created (you're only using stored procedures to control access patterns into your databases right?) you as the DBA would need to collaborate with your development teams to grant execute permissions to one or more users that need rights to execute the stored procedure to accomplish whatever task(s) are controlled through the stored procedure. This adds one more step to the process of development and impedes Agile development.
Multiple Ways to Accomplish Least Privilege
#1: Caution Over Efficiency
What does this default approach look like in the "real world"?
Cash Money is a Developer at CruiseCorp and pushes through development initiatives like a rockstar. He's no problem creating stored procedures in the company's sandbox environment - he's been granted the proper access to do just that. However the DBA team led by Paige Split is doing their job by ensuring that only the DBAs can grant rights in not just production - but all the data environments at CruiseCorp. This means that every time Cash creates a new stored procedure he needs to coordinate with one of Paige's DBAs to run a command like what you see below:
GRANT EXECUTE ON <this_awesome_stored_procedure> TO <some_user>;
The Pros
The Principle of Least Privilege is definitely accomplished through this method. Only the permissions required are the permissions granted and security is controlled by the DBA Team and not a wider range of staff.
The Cons
Whenever you have to communicate across teams to accomplish what should be an agile (small "A") process you impede your turnaround time. Imagine a highly-proficient and busy development environment like CruiseCorp. New features get delayed because of human interaction and multiple layers of paperwork and processes that go into communicating the need for granting rights to a new stored procedure. Additionally the DBA team is inconvenienced by the constant stream of requests to grant execution permissions and we all know how much DBAs enjoy being interrupted. There is a better option:
#2: Grant Execute at the Schema Level
By granting execution permissions on a schema to a specific user you're giving that user the ability to execute any stored procedure that exists or will exist on that particular schema. This removes the DBA from being any sort of blocker in the development process because rights are granted implicitly moving forward. The syntax is simple - the ramifications of doing this is why I'd advocate only doing using this practice in your development environments. First the syntax:
GRANT EXECUTE ON SCHEMA::<some_schema> TO <some_user>;
After execution the user will now be able to execute any stored procedure owned by that particular schema.
The Pros
Consider the improvements on this. We as DBAs are now removed from the development process because our work is done here. Not only are DBAs removed from the process but Developers don't need to worry about granting rights either. It's been taken care of. Move along.
The Cons
Consider for a second what this means in an environment where your schemas are broad in their scale and where anyone can download and install Microsoft SQL Server Management Studio in 10 minutes with moderate Internet bandwidth: any application safeguards can be bypassed with use of Microsoft SQL Server Management Studio directly executing stored procedures against a database. Anyone who knows the login and password combination for getting into the database or can gain access to a connection string and look that information up now has access to executing all the stored procedures the associated user has rights to through that schema grant by bypassing any roadblocks created through the application layer by hitting the objects directly in Microsoft SQL Server Management Studio. Never accept a response by third-party vendors or internal development teams that security is provided through application segregation because it's not when you consider how easy it is to install Microsoft SQL Server Management Studio, identify a server and database pairing and login & password combination to match.
Cash may have only been interested in assuring the user ID customers employ to submit orders can view their previous orders though this great new stored procedure. Unbeknownst to him they now have access to alter billing records because the billing stored procedures are part of the dbo schema just like the orders stored procedures. This practice may be sufficient for sandboxes but I'd not advocate for production.
#3: Grant Control on the Schema to the Developers
I recently had one of the Developers I collaborate with ask for db_owner permissions in the development sandbox so they could provision rights to new stored procedures as they were developed. Saying I was reticent is an understatement. In addition to allowing the Developers to grant access being a db_owner would open access up to a wide range of possibly negative scenarios from changing data types in tables, to dropping tables, to dropping the database. Another item I also like to bring up in interviews is the question of what additional impact granting db_owner for one database can have on another database in a consolidated environment. A member of the db_owner role has full control over the sizing of database files. This means that a db_owner role member could re-size a transaction log file to fill the entire amount of free space on a volume reserved for your transaction logs. Hilarity does not ensue when that happens. So no, db_owner was completely off the table (no pun intended.)
Granting control on the schema on the other hand does allow the grantee the ability to administer security on the schema without all the nasty side effects. It's one level down from the database. Yes, schema changes are still allowed but no one is dropping a database or filling a log volume.
The syntax is clear:
GRANT CONTROL ON SCHEMA::<some_schema> TO <some_user>;
The Pros
We once again are back to the fact that the DBAs are remove from the roadblock of security provisioning and now end users only have rights to do what they need to do against database objects they need to do it to (so long as the Developers can be held to the same standards as the DBAs are held to for security.) Developers don't have rights they could be given through db_owner but they still have greater rights than ever before.
The Cons
We have effectively doubled the number of teams that can impact security.
Conclusion
#4: A Balanced Approach
I prefer to use a hybrid of #3 where we employ that model (GRANT CONTROL ON SCHEMA) to the Developers in our sandbox/development environment in conjunction to granting them db_ddladmin role membership. When migrating objects to environments closer-to-and-including-production we use comparative tools like Redgate SQLCompare to build deployment scripts that include the underlying grants required for any new stored procedures. This maintains the ability to deploy fast in test/sandbox but reign-in control as we proceed through the lifecycle towards production. In the end we maintain The Principle of Least Privilege in all environments other that the initial development environment while getting out of the way of rapid development.
