Access controls in test environments

Good article from the Agile Web Operations crew about securing access to environments. Made me reflect on my brief career in the City. We were asked to be the "gatekeepers of quality" for all the changes that flowed through our test systems.

That was hard. One thing I did do was take a good look at the test environments and see who had administrative access. That was everyone. In response, I hacked some Ruby scripts together, and ran them from a CI system every 24 hours.

The first issue was simple. The administrators would make an Active Directory group for each host, and add privileged users to it. Unfortunately what would happen is that people would ask for access to the host and be put in that group. So the first thing to do was add all of the hosts that I could identify and then output all the folks who had access to them.

After the first couple of mass purgings of inappropriate user access, the helpdesk would call me up and ask if it were okay for someone to have admin access to a machine.

The second was harder for me - identifying every user that had admin access to the database. I needed to run a DB script against each instance to find out:

  • if a user had sysadmin privileges for the entire instance, and
  • if the user had sysadmin or db_owner for a particular database

After some frantic Googling, pleas to the DBAs and DB developers and blind experiments I worked out how to do this for SQL Server 2005, at least. SQL server 2000 kinda worked. That again flushed out a large number of people who had access, who shouldn't.

My workload went up some more as people who previously had far too much access to systems had to then come talk to us. We were able to ask people to script things like granting rights into their changes. This helped prevent a class of deployment failure where users would have no rights to execute stored procedures. Not sure I made many friends, either.

Anyway, here's Dan's post.

Limiting Access to Test and Production Systems — Agile Web Operations


DevOps New Zealand