Voice of the DBA

Author: way0utwest

  • Your 2017 Goals

    Last week I wrote some predictions for 2017, and plenty of you added your own thoughts. Overall, security is a concern for most people, with little hope things will improve. I certainly hope they do, but like most of you, I think expediency and profit will overwhelm any chance that organizations will make patching their systems a priority, much less improving their data security.

    As the new year came and went, I ran across a great post from Buck Woody that’s a few years old. Inthe post, Buck notes that he likes to make goals in the new year with his family, not resolutions. That sounds like semantics, but I agree with Buck. There is power in making goals instead of open ended changes. Goals can help drive you, especially if they’re measurable and time boxed. Having someone else keep you accountable also helps.

    This week, as we start a new year of our careers, I wanted to ask you what goals you might have. List a few and think about asking a friend, spouse, parent, child, someone else to help hold you accountable and check on your progress each month. If you have just a few goals, chances are that you will achieve some of them. Don’t forget, you can always add a few more later in the year if you finish everything.

    Perhaps you want to learn something about a new technology. If that’s the case, make a specific goal. Don’t try to “learn Azure”, but instead think about “building a database to track my own movie reviews in Azure and use it for a year.” If you want to get better at a specific technology, like T-SQL, then think about challenging yourself with a specific set of problems, like the T-SQL challenge. Or complete something like the Advent of Code in T-SQL.

    Set aside some time to improve your career and take a step forward. Data professionals working with SQL Server have no shortage of new features, subsystems, and solutions to practice with as our platform grows wide and deeper each year.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 3.5MB) podcast or subscribe to the feed at iTunes and Libsyn.

  • Watch the Manual DBA v Automated Deployment Challenge

    At last year’s SQL in the City, I played the part of a manual DBA making changes and deploying those from development to test, staging, and production environments. Actually, I don’t think we went to production because I didn’t have time.

    My co-worker, Tom Austin, showcased the automated process using SQL Source Control and other Redgate tools. As much as I believe in using tools, and despite the fact I regularly use them,  even I was impressed by how much less work the automated process took.

    Watch our session below.

  • Quick Prompt Tips–Custom Procedure Templates

    One of the things that I often do is create stored procedures. The syntax for doing so is simple, but it has a number of items that need to be included. SQL Prompt makes this much quicker with the “cp” snippet. When I type “cp”, I get this:

    2016-09-13 13_01_43-SQLQuery1.sql - (local)_SQL2016.AlwaysEncryptedDemo (PLATO_Steve (64))_ - Micros

    I can hit Tab and I have a snippet, but it has a lot of things I don’t like in it. Plus, I want to save time coding, not have to remove some commented out items.

    2016-09-13 13_06_17-SQLQuery1.sql - (local)_SQL2016.AlwaysEncryptedDemo (PLATO_Steve (64))_ - Micros

    Let’s make this more efficient. I can go to the Snippet Manager under the SQL Prompt menu and select it. When it opens, the snippets are highlighted, so I type “cp” to get to the Create Procedure snippet.

    2016-09-13 13_36_11-SQLQuery1.sql - (local)_SQL2016.EncryptionDemo (PLATO_Steve (64))_ - Microsoft S

    I click edit and see the code, which I highlight before deleting this.

    2016-09-13 13_36_45-SQL Prompt - Edit Snippet

    Then I paste in the code that makes more sense to me. Notice that in my case, I have two placeholders, not one (as shown above).

    2016-09-13 13_37_03-SQL Prompt - Edit Snippet

    The code I use has a header in the procedure, and the procedure name is used both for the definition and a GRANT EXECUTE. I include the begin..end structure for the procedure with the cursor starting in the spot where I’d put code. I also have a placeholder for a role name. It looks like this.

    CREATE PROCEDURE $procedure_name$

    /*
    Description:

    Changes:
    Date       Who Notes
    ———- — —————————————————
    */
    AS
    BEGIN
    $CURSOR$
    END
    GO

    GRANT EXECUTE ON $procedure_name$ TO $role_name$

    In practice, when I type “cp” and hit Tab, I get the code with the procedure highlighted. I can enter a name here. Note what I typed is also placed in the GRANT statement at the bottom.

    2016-09-13 13_40_02-SQLQuery1.sql - (local)_SQL2016.EncryptionDemo (PLATO_Steve (64))_ - Microsoft S

    Once I am done and hit Tab, my cursor jumps to the next placeholder, in this case, the role name. Notice that SQL Prompt knows this is a role and gives me a list of roles and users to choose from.

    2016-09-13 13_40_44-SQLQuery1.sql - (local)_SQL2016.EncryptionDemo (PLATO_Steve (64))_ - Microsoft S

    When I finish and hit Tab again, the cursor jumps to the point between the BEGIN and End where I will enter my code. Now my job begins.

    2016-09-13 13_42_26-SQLQuery1.sql - (local)_SQL2016.EncryptionDemo (PLATO_Steve (64))_ - Microsoft S

    This little customization gives all my procedures some standard look as well as ensuring that I can quickly build procedures without a lot of mundane, tedious typing.

    Try out this quick SQL Prompt tip and see how much smoother your coding goes. And if you’re not a SQL Prompt user, download an evaluation today and see how much more efficient you can be when writing T-SQL code.

    You can see a complete list of SQL Prompt tips at Redgate.

  • Number Security

    There’s a scary item recently in a Database Weekly newsletter. Apparently your credit card number code can be guessed in seconds. Using techniques that spread guesses around to a number of sites, criminals can avoid triggering alarms, which makes some sense. I would hope that banks would find ways to patch this quickly as I’d expect that some central system should be providing authorization. However, since banks haven’t shown much inclination to adopt DevOps and move quickly when required, perhaps I’ll be changing my credit cards every quarter this year instead of just once.

    Perhaps a new card that can change numbers make sense. There is a technology that could change the actual digits on a card. That reminds me of the RSA SecurID VPN cards that I used to carry with a number that changed every minute. The current number was required to log into VPN and you only had a minute to enter it. I had expected this might be required more and more, but it seems relatively few companies implemented the system. I know some companies have moved to software based token systems, and certainly two factor authentication has become more widespread, but not as widely as I’d have hoped.

    However, no matter what is implemented, anything that is widespread, easy to use, and predictable, will likely be cracked. While I could see banks wanting to verify each charge somehow, that might be unwieldy in today’s highly cobbled together, distributed systems. The move to machine learning and more intelligent data mining can help, but when criminals try to make charges that don’t cause simple flags to be raised, what will banks do then? I think we will see more intelligent criminal attacks that avoid machine learning algorithms, especially when there criminals get organized and focus on digital attacks.

    I don’t mean to get caught up in the specifics of credit card numbers. I’d rather think more widely about the issues of data leakage and how information can be extracted from any mechanism that we use to try and secure information. Look at the Dynamic Data Masking (DDM) feature in SQL Server 2016. It’s a great feature in some ways, and as a convenience for developers, this makes sense. However, for determined attackers, there are easy ways around this. Query for a specific value, and SQL Server processes the query, even if the result returned to you is masked, you’ll know the underlying value. There are similar holes in Row Level Security, giving intelligent hackers the ability to determine what data is in your tables.

    I think ultimately some of the ways in which we can protect our individual data come from being able to analyze query patterns. Machine learning and an integration with the Query Store maybe helpful in performing this analysis. If we know what queries we expect from a system, then we can detect anomalous behavior, which may let us know there are attacks being performed. While we might not be able to prevent all information from being disclosed, being aware and limiting future access can be valuable in preventing the impact of a data breach from growing too large.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 4.6MB) podcast or subscribe to the feed at iTunes and Libsyn.