Voice of the DBA

Category: Editorial

  • 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.

  • 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.

  • When Do We Worry About Scalability?

    “We need to get the code written for feature X. Can you finish this query today?”

    We’ve all heard some variation of that request. We have a request or demand, and we need to get it done. We need to get code out so our business can advance, sell more things, get more customers, etc. There’s always some reason to get new code pushed to production quickly.

    However, many technical people want to ensure their code works well. At least, I believe most do. While most people can write code that works and meets the requirement, some don’t know how to write code that performs well or don’t know how to test their code to check. Often there isn’t a large workload in dev or test environments to verify things.

    There may not be a large workload in production either, at least not at first.

    So, what do you worry about first: your code being used or performing well? That’s a similar question to this one: Worry about Scalability or Popularity First? While most of us don’t work for a startup and our organizations have some sort of financial stability, does popularity matter?

    I’d say that for any feature you build, whether a startup mobile app or a legacy ERP system, you’re still looking at this type of question. You want to know if it’s used, and how often. That might determine if you spend more time on this feature or area. Maybe you have some idea of popularity, or just plan old use of the feature. In that case, certainly make sure it will scale to not only meet your data size now, but plan for some level of growth across the next 6-12 months.

    If it’s a new area of functionality for your application, then maybe you have no idea. In that case, the DevOps approach is get something working, a minimally viable version of your code or query, and then tune it later if it becomes a problem. Many technical people approach the endless number of tickets and requests they get like this.

    The problem is management often doesn’t budget in time to clean up the technical debt (to care about scalability).

    My view for database code is that we should always be leveling up our database code knowledge. If we deploy bad code in production, and can’t fix it, then at least we can avoid adding to the problem by writing the same poorly performing code again. Learn a better way to write that type of query. Whether you’re splitting strings, finding islands and gaps, calculating running totals, or anything else. Learn what works well and write that code next time.

    That helps your team balance the scalability and popularity-chase by producing good code the first time. Or at least, the next time.

    Steve Jones

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

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

  • Technology and Privacy

    There can be a big divide among tech professionals on how they view data privacy. Some don’t worry too much, and some are very upset about the lack of data privacy and poor data handling practices from many organizations. Most people are probably in the middLe. I do know that every time I post about a Tesla, there are many people who mention the incredible amount of data Tesla collects, and how that makes them nervous.

    Tesla discloses that they collect, and I get copies of my data with my own logger. I find the data interesting and I glance at it over time to look for trends. I’m still happy that my top destination in the last year (after home) is the gym 😉

    Other car companies collect data on you and GM is in trouble in Texas. I suspect other companies do this, likely to learn more about how to build better cars, but also to get data they may be able to sell. The same thing likely happens with most of your data. Certainly, mobile phone companies collect lots of data, and I wouldn’t be surprised if a lot of your rentals/purchases/visits are being captured and sold to others from all sorts of vendors. Some of you might even be capturing usage data in your software that your customers might not like.

    I don’t know how I feel about this. I do think disclosure is important but not in the form of some EULA or a contract that people have to click through. I think there ought to be choice of what data is being collected and how it’s used. Or if it can be used. While I often just accept or dismiss cookie banners, I do appreciate that I can refuse cookies and still use more websites. I ought to be able to easily opt out from most data collection from most companies. It really should be optional.

    The world isn’t going back to analog devices and actions. Digital technology, with software invading most parts of your life, is here. I wouldn’t be surprised to find out most new appliances have a phone home feature that captures data and sends it back through unsecured wi-fi. A good reason to put a password on all your networks.

    In portions of the world, there are restrictions on data and privacy. They could be stronger, and the laws could limit more of what companies can do. Many companies still make money or run fine.

    They will be fine. Organizations always find ways to work within whatever system exists.

    I think that would be a good thing for most of us if more data privacy restrictions were in place. However, I don’t know how most of us would even go about helping enact them.

    Steve Jones

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

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