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


4 thoughts on “Access controls in test environments

  1. Dan Ackerson says:

    Nice one, Julian! It’s never easy doing the right thing but I’m sure your persistence paid off in the long run. Too many cooks in the kitchen will ruin the soup!

  2. Neil says:

    Interesting because at the place I work (a well known media company) we’re getting quoted SOX everytime we request access to any test and development environments. Dev environments here are the old fashioned kind that people share, can’t be rebuilt, are not baselined and approximate production (just). And they’ve chucked a new spanner into the works called credit card industry compliance. What this all means is that we can’t have password to generic userids such as a build user, or oracle user. Every interaction with these accounts has to be via sudo and sudo su. The solution is to have local development environments of course but this company kick off projects and discover they don’t have the right development environments 6 months down the line!

  3. simpsonjulian says:

    As a sysadmin I don’t mind that the user has to elevate privilege to get root. It means that you can actually see who is doing what to your environments. This isn’t as good as being able to rebuild at will, of course.

    I’d love to go down there and play the ‘5 whys’ game … !

  4. […] operations, Point2, systems, testing A couple of posts relating to development/ops gaps, and access control reminded me how very far we have come with our environments over the past few […]

Comments are closed.

%d bloggers like this: