Voice of the DBA

Author: way0utwest

  • Small Data SF 2024

    I have often made an effort to attend conferences in the past to grow my career. Even today, when I speak at a conference, I’ll try to go to a few sessions and learn something, but I can be distracted. It’s rare I focus on just learning stuff without other responsibilities.

    I’m at Small Data today and tomorrow doing precisely that. I’m just an attendee, and today is a couple of workshops for me with a variety of talks tomorrow. It’s a real career growth opportunity for me and I’m excited. I’m doing the Data Warehousing and Design workshops today and sitting in sessions all day tomorrow.

    I can’t remember where I heard of this conference, but when I saw the manifesto and sessions, I was intrigued. There were tentative plans this week for me to do a customer visit, but when that got delayed, I jumped on the opportunity to visit San Fran and learn something.

    As I’ve worked with a lot of customers, I see the value of small data sets providing lots of agility for teams, while also allowing them to get work done, as long as the data sets are relevant and representative. That’s a big part of me pushing the Subsetter at Redgate.

    In this case, the conference focuses more on analytics and AI, and likely more developers than DBAs, but I think it’s a chance to get different perspectives, maybe learn a few things, and perhaps get others to see my data viewpoint in the hallways discussions.

  • No Backdoors

    Every once in awhile I hear about someone in law enforcement sure that tech people can build in a safe, secure way for data to be unencrypted by the company or vendor. The latest appears to be from Australia, where the Security Intelligence Organization wants tech companies to build this into products.

    Backdoors never work. Anytime an encryption key is stored, it could be stolen. We see this all the time. Keys are just data, and companies lose data all the time. At scale. Governments are certainly not immune from this. One of the reasons that Azure allows a BYOK (bring your own key) for encryption mechanisms is that many organizations don’t want to trust Microsoft to store their keys. I’m guessing Microsoft doesn’t want the liability, either.

    Many of the data protections an organization might implement are outside the scope of data professionals, but we certainly have responsibilities in this area. We should certainly manage our keys appropriately, and my recommendation for many people is that they have a separate repo and pipeline for the deployment of privileged objects, such as encryption keys. Your organization also ought to have a process (and test it) for key rotation. Keys expire relatively infrequently and the person who last rotated the key might not be available when you realize this process needs to be completed.

    It’s especially important everyone knows how this works for those emergency situations where something expires and none of you realize there’s a problem until a client calls.

    There are plenty of other security mechanisms we ought to be using. Secure your backup files and limit access to shares or even processes that move these files around. In general, limit access to those who need access to everything. Using organizational groups is the best way to do this, along with regular audits to ensure that those who change jobs are removed from groups that aren’t needed. I haven’t seen any organization that has a good process for knowing what positions need what access. I often only see people given new access for a role change, giving them the new roles that match some other employee. This is usually done without any previous access removed when it is no longer needed. The most senior people often have the most access, not because they need it, but because they keep getting new roles.

    Managing security with roles too granularly can become a nightmare, though ensure you use roles everywhere you can while trying to limit the numbers of roles. For most databases, we are giving access to nothing or everything, but there are reasons to limit access for certain data. You might consider two roles by default in most places: one for privileged users and another for everyone.  Easy to ensure new objects get grants to one or both roles and let access be managed by role membership.

    Keep it simple, but keep it secure.

    Steve Jones

    Listen to the podcast at Libsyn, Spotify, or iTunes.

    Note, podcasts are only available for a limited time online.

  • A New Word: Mottleheaded

    mottleheaded – adj. feeling uneasy when socializing with odd combinations of friend and family, or friends and colleagues, or colleagues and family – mixing a medley of ingredients that don’t typically go together, which risks either watering down your identity into gray much or accidentally triggering some sort of explosion.

    Mixing groups of people from different situations has sometimes been a struggle for me. I’ve felt quite mottleheaded at times when I have people I work with in coaching, at Redgate, and from friends/neighbors/family. It seems that they can be very different people.

    I’m sure I’m overthinking this, but I’ve rarely invited different groups of people to the same place, precisely because I do worry that someone might not enjoy the time with those others. I know I’ve felt awkward at times at horse-related things for my wife. I don’t mind and am happy to support her, even if it’s strange for me.

    From the Dictionary of Obscure Sorrows

  • Bad Stored Procedures

    I don’t see a lot of SQL at The Daily WTF, but this one was great. It’s a stored procedure that was likely just converted from embedded code, as noted by the poster. It’s a strange set of code, that doesn’t quite make sense to me, and I can’t imagine why someone wrote it. Arguably, this is no better than having this code in a C# or ASP.NET application.

    Or is it?

    I think it is better. If I saw this code in a review or even in a production database, I could work on cleaning it up, adding protection against SQL Injection, and even tuning how it works to reduce the load on the database. I could likely wrap testing around this and get it deployed way quicker than if I were trying to update the source code for an app. More importantly, this is centralized code. If this is called from multiple places in the app code, I’ve fixed it once, not requiring an app developer, who has other work being piled on them, to spend time updating repeated sections of the code.

    Even better, I could refactor some of the schema behind this stored procedure and easily find that my changes might affect this code. I could add a feature flag to this procedure and slowly migrate my schema in the background, without disturbing the user, again because the code is centralized. That’s a technique that most developers use in C#/Java/Python/etc., so why not in SQL?

    I find it very interesting that a lot of developers refactor their classes and methods to better adhere to SOLID or some other practice, and they are happy to remove repeated code in their language. Yet, they don’t want to implement a stored procedure or function into their calls, essentially creating a database method for the things they need.

    The more I work with legacy systems, the more value I see in using stored procedures. Every developer ought to know how to build them, and every developer ought to be able to create them in dev systems so they can easily deploy database and code changes together. More importantly, they can also share the load of tuning queries with operations staff, who may notice things in a production environment that are not apparent in development ones.

    The big challenge in all of this is that database tooling is immature. Capturing your database code in source control is hard, and often it is a separate process from the one you follow for application code. I see some companies (including my employer) trying to make this easier, but there is a long way to go, and a lot of habits to change for developers.

    Steve Jones

    Listen to the podcast at Libsyn, Spotify, or iTunes.

    Note, podcasts are only available for a limited time online.