Voice of the DBA

Tag: software development

  • The Principle of Least Privilege

    One of the tenets of good security is that no person or process is granted more rights than it needs to accomplish a task. This is the Principle of Least Privilege, and is designed to ensure that your system is as secure, stable, and predictable as possible.

    For a SQL Server instance, this means that unless a person or process needs to perform instance level changes (configuration, patching, etc.), then they should not have instance level privileges. If a process accesses data in a database, there is no need to grant sysadmin privileges, even if they do need db_owner privileges.

    Many software packages require sysadmin access be granted, often without any justification. Developers build software as a sysadmin on their own workstation, or on a development server. They often don’t understand or realize that less privileges will still allow their software to work. As a result we find software running under elevated privileges, which can be a security risk if there are bugs or injection holes in the software.

    It’s hard to make changes later on, and it can be frustrating to argue with vendors that their software doesn’t need elevated privileges. The best way to combat this is to educate yourself and any developers you know about security.

    Adding login or user accounts does not require sa privileges. The securityadmin and db_securityadmin roles can be used. The rights to create objects in a database can be handled with granular permissions in a schema, or with the db_ddladmin role instead of db_owner.

    Work with limited rights when you can, and help other learn how to work with the minimum they need and we’ll all be more secure.

  • Deployment Failures

    Just Ship
    Shipping is good, but your deployment process needs to be solid.

    Years ago the company I worked for would patch the majority of our servers one Friday night each month. The Microsoft patches for the month, and other software patches, would be bundled up into SMS (Systems Management Server) packages and deployed to thousands of servers. We had an amazing administrator who built these packages, and it was quite an experience to walk into the data center and hear thousands of servers shut down and fans spin down for a moment before rebooting.

    That was the smoothest deployment system for vendor patches, but I worked in another place that deployed changes to a web application (the system that generated all our revenue and paid our salaries) every Wednesday night. We did this for over 18 months, over 70 deployments, pushing out changes on a consistent basis. We only rolled back three times, but we did roll back three times.

    Other jobs have had various levels of success at deploying changes. Many of the companies worked with the ad hoc, patch one machine at a time manually, process. Not very efficient, and probably not even possible at the numbers of systems many companies have today. I wanted to ask you this week how successful your company is.

    How often do you have problems during the deployment of some software change?

    Do you think that you have issues more often than not? Do you roll back when you have issues? I doubt that. In my experience, even broken deployments are often pushed forward, with the expectation that developers, vendors, or admins will fix things over the next few days. I’ve never thought that was a good plan, since we often fine broken features limping along for months or years, but organizational momentum can be hard to slow down.

    Let us know if you think you work inside of a smooth, strong deployment process, or one that’s more fragile and brittle.

    Steve Jones

    The Voice of the DBA Podcasts

    We publish three versions of the podcast each day for you to enjoy.

  • Planning

    I made it to the UK on a very smooth, uneventful, on-time trip. That’s just what I like when traveling. After arriving at Heathrow and traveling to Cambridge, I arrived at the Red Gate office just in time for lunch. A fortuitous event as I was hungry after flying all night.

    Most of my afternoon was meeting with various product groups talking about planned enhancements and changes and how these items might benefit DBAs in the field. Grant and I are usually called on for sanity checks on thoughts, ideas, and plans.

    It’s interesting to discuss with product groups how and why they want to make changes. I can’t talk about specifics, but hearing from developers and project managers is always interesting to me. We see the world differently and it’s good to give feedback on products that can make a DBA’s job easier. I haven’t always had the chance to do that, but when I do, it’s a growing experience to hear how a vendor sees the world.

    I wrote about this recently, and a few disagreed, thinking that Microsoft doesn’t listen to them. I disagreed then, and I still do. I get an opinion at Red Gate, and with Microsoft. I don’t get to make a decision, or even much of a vote, but I do get to voice my opinion. Just because it isn’t followed doesn’t mean that someone didn’t listen.

    I don’t know to what extent my thoughts will influence the products, but I did see and discuss things with a variety of people in different departments, and the discussions evolved, so I know my opinion is heard. Whether it changes things remains to be seen.

  • Don’t Explain Too Much

    sketch
    Leave out the details when communicating with management.

    I was reading a note recently from a DBA working at a software company. Their management wanted to ensure clients had a simple backup solution and were leaning towards Windows OS backup instead of SQL Server backups. They were planning on running databases in simple mode instead of taking transaction log backups, which were seen as too complex. While this can work, I’m not sure this is the type of discussion that should even come up.

    Management should be concerned with the higher level goals. Clients need a simple scripted backup. Period. The implementation of that isn’t something that management should be discussing with developers. This is the perfect example of where the software development goes off the track with micro management. Managers becoming deeply involved in technical decisions and implementations is a sure way to ensure that less than optimal decisions are being made.

    What should happen? Technical developers should get the goals of management (a simple backup process for clients, every day). They should then recommend a solution, but with a minimal of technical details. Managers should have no idea that transaction log backups are being made or a part of the process. Developers should write scripts, tools, or processes that allow an administrator to accomplish a goal in an easy to execute fashion, but shouldn’t need to explain how every detail works to the end user.

    Keep it simple and effective. That’s a mantra that’s worked well for me throughout my career.

    Steve Jones

    The Voice of the DBA Podcasts

    We publish three versions of the podcast each day for you to enjoy.