It’s T-SQL Tuesday time again and this month we look at security. Kenneth Fisher has chosen this as his topic for February and you can read his invite here. There are lots of choices on what you write about, and I’m looking forward to reading what people choose.
You can join in, by writing a post today and publishing it with a link in Kenneth’s invite. Or you can write later and just put your own thoughts down on the subject.
T-SQL Tuesday is the idea of Adam Machanic (b/t) , and it’s a monthly party where everyone writes on a specific topic. The first Tuesday of the month usually has a new invitation issues, and you have to watch for it. I’d recommend putting a reminder in your calendar. The second Tuesday of the month is when we publish posts.
If you’d like to host, contact Adam.
Security Across Environments
At one point in my career, I worked with a startup company. We had a number of experienced people working in development, and we wanted to set up a series of environments early on to perform agile development. When I arrived, the application we built had been running for about 4 months, and we were looking to improve our data handling and development processes.
At the time, we had a production server and a development server. There were accounts for the web application and the initial security had been to grant security on tables as appropriate for the web application. Any tables that existed for administrative use were limited to sysadmin access.
This wasn’t a bad plan, but as we implemented a test environment, I knew this would be an issue. We didn’t want to give testers (or developers), access to the production AD account that was used by the web application. We also didn’t want any problems during deployment.
Moving to Roles
It can be hard to change security around on an existing application. Fortunately we had limited numbers of objects and applications accessing our SQL Server database, so I could easily determine if refactoring was going to break anything.
My first refactor was to create two groups in each environment. I used code similar to this in Development, QA, and Production
CREATE ROLE WebAppUser;
CREATE ROLE WebAppAdmin;
By creating these roles in each environment, we had a consistent place to set security for objects. We proceeded to assign generic read/write/execute security to objects to these roles as needed. The WebAppAdmin role accesses all objects (essentially as datareader/datawriter). to grant rights to the WebAppUser role for specific objects, we scripted out the rights assigned to the current WebUser user and then granted those rights to the role.
The last step was adding the WebUser to WebAppUser. Once this was done, we essentially had duplicated permissions for the user, WebUser, through the user account and the role.
Our test procedure for the change was to begin removing the rights granted to the user in the QA environment. Once we verified the web application still functioned, we made one final change.
In the Development environment, we created a new user, WebAppUserDev and put this user in the WebAppUser role. We then changed IIS to use this user account. From this point forward, development was separated from production. The Windows admin changed the WebUser password and development was locked out of production. We did the same thing in QA and created a new account there.
Once everything was done, we removed all direct object rights from the WebUser account in production. This was a scary day, as we were counting on our role having all the correct rights. Fortunately our process had worked, and the application continued to function.
That was a lot of work, but a refactoring that doesn’t break anything can take some time. There will be multiple steps and it can take days or weeks to implement.
Moving Forward
From this point forward, development proceeded without problems. All of our object code now included one, or both, of these lines at the bottom of the script.
GRANT EXECUTE on MySP to WebAppUser;
GRANT EXECUTE on MySP to WebAppAdmin;
We migrated object code between environments, but not security. Security for each environment was handled separately, with separate accounts added to these roles. Our deployments became much easier.
When we needed a new role (for client auditors that could access specific tables), we added the role as a part of our deployment and assigned security to the role. The role deployment was handled separately from application deployment with a new role being deployed in a script, but a different user added in production by the sysadmin to AD and the role. Later deployments had permissions grants for the role that were the same in development, QA, and production.
When we added a Staging environment, it was as simple as restoring the production database, deleting the orphaned WebAppUser user from SQL Server and creating a new user for that environment.
Consistency
One of the big issues with deployments not proceeding smoothly comes when one environment is not set up the same as others, and the scripts run in one environment do not execute on another. When users are included in all environments, you have a security hole, but when different users get different security, you create scripts that must be edited, and potentially mid-edited.
The more you can abstract away portions of your application, whether this is through roles, synonyms, linked servers, or other items that can be named consistently, but configured differently in separate environments, the smoother your deployments will be.
There are certainly challenges with deploying new items across environments, but that’s a discussion for another day. For security purposes, I think that roles are an important way to ensure that security is maintained, but deployments are not impacted.
Voice of the DBA 