Protecting Data from the Inside

It may seem unkind to suggest that your data is at risk from authorized users, either accidentally or maliciously, but insider threats are one of the major causes of data breaches.

Let me give an example. In 2011, Britain's News of the World newspaper folded after 168 years of publication when various immoral or illegal practices of its journalists were revealed. The Wikipedia entry 'News International phone hacking scandal' gives an excellent summary.

Journalists, it was alleged, were equipped with phone numbers of people, working in large companies and government departments. For a fee, employees of these organizations would then access their database systems to divulge useful information about individuals. The journalists were, in this case, most interested in celebrities, politicians and the aristocracy but everyone was at risk. Some journalists boasted that a day's work on the phone could produce details of the personal life about almost anyone, often before the police.

One journalist investigated by the police in 2002 reportedly bought information from former and serving police officers, customs officers, a VAT inspector and bank employees who had access to the data. They also employed 'blaggers' who would telephone the Inland Revenue, the Vehicle Licensing Agency, banks and phone companies, and deceive them into releasing confidential information from their records.

However impervious to external attack your application and database may be, the data that your organization holds may still not be safe. It is not an option to trust solely in the probity of your internal users, or to rely on their good sense with data.

How on earth do you guard against the theft of data within an organization? Firstly, determine what sensitive data you hold and why, and where it is stored or cached. If you hold sensitive data, then you must be able to identify individual authorized users of it. Next, you need a system that monitors usage that is entirely separate from the database system. It must log all accesses to the sensitive data. With all this in place, you can introduce a configurable alerting system that looks for any sort of unusual usage.

The database must use full access control, so as to prevent any SQL queries from accessing tables that have sensitive data in them. I always recommend an interface of stored procedures and functions to allow applications to access this type of data, and to make monitoring easier. Through this interface, you can restrict access to sensitive data to just the minimum the user needs to complete their task. There should be a fine-grained range of database roles that match the roles of members of the organization, so that no user is able to access either more data than is necessary for the business operation, or data that is irrelevant or inappropriate. Data exports must be restricted to aggregations done to the finest level of detail needed for reporting.

A seasoned curator of data will never be a popular figure in the organization, because insider data breaches are seldom publicized and the extent of the threat is greatly underestimated, but it is certainly there.

Phil Factor

Join the debate, and respond to the editorial on the forums

Phil Factor starts with the basics how to rebuild a set of development database from scratch, using SQL Change Automation, and then demonstrates how to check for any active sessions before rebuilding, import test data using BCP, and secure passwords if connecting to the target with SQL Server credentials.

Phil Factor demonstrates how using XML argfiles to pass parameters to SQL Compare cuts out a lot of the tedious scripting involved in modifying a database schema comparison and deployment process, as required for each target database.

SQL Compare is the industry standard tool for comparing and deploying database schema changes, and version 14.0 now provides a Command Line Interface (CLI) on Linux (available as a beta, at time of writing). Teams working with SQL Server on Linux, or across multiple platforms, benefit from seamless development processes, driven by SQL Compare, without the expense or ‘friction’ of needing to maintain a Windows VM on their Linux servers.

Andy Mallon explains what happens during database crash recovery, and the need for patience.

Erik Darling Proposes that, in a centralized schema, maybe called health, there should be stored all the details of Backups (schedule, failures), CHECKDB (last good, failures), Agent Job Failures/Operators Notified, and so on.

GigaOm recently published a performance benchmark of Azure SQL Database as compared to Amazon’s RDS service, but Joey D'Antoni wants to do a raw pricing benchmark - it turns out Azure SQL Database is cheaper.

Silvano Coriani explore the options for loading and retrieving JSON data, in Azure SQL Database, and benchmarks the results.

After working on a query for a long time, we want to make sure that we save the changes we have made. I have lost hours of work over the years because I didn’t save the changes. Azure Data Studio has a few features that can help prevent this from happening.

Wednesday October 23 16.00-17.00 BST/ 10.00-11.00 Central - Discover different tactics enabled by Redgate’s latest new product - SQL Data Catalog – to build a clear picture of your SQL Server estate, enabling you to ensure that the correct data is protected in the correct way.

Wednesday November 13 16.00-17.00 GMT/ 10.00-11.00 Central - PASS President Grant Fritchey is joined by fellow Microsoft MVPs Kendra Little, Steve Jones and Kathi Kellenberger to discuss their highlights and learnings from PASS Summit 2018, the largest Microsoft Data Platform conference.

Wednesday November 20 16.00-17.00 GMT/ 10.00-11.00 Central - Are you ready for growth in 2020? Join Tony Maddonna, Microsoft Platform Lead & SQL Server Enterprise Architect at BMW Manufacturing and Redgate's Arneh Eskandari to discover how to prepare for successful estate growth in 2020

Redgate is hosting an upcoming series of exclusive Summits, in cities worldwide, to help senior data professionals deliver value quicker, while keeping their data safe. Find out more and register for your nearest event

On Nov 23, 2019, a free day of SQL Server training in Brazil

On Nov 23, 2019, a free day of SQL Server training in Parma, Italy.

On Nov 23, 2019, a free day of SQL Server training...

Understanding scoping rules is a basic skill for developers. In this article, Joe Celko gives a bit of the history of scoping in early programming languages and shows how scoping applies to SQL queries as well.

Before including the database in your DevOps pipeline, you’ll need to decide if you are going to use a state-based or migrations-based approach. In this article, Robert Sheldon explains the difference between them and discusses the benefits of each.

DevOps might sound intimidating if your organisation has not yet begun on its DevOps journey. In this article, Rahul Varshneya explains some of the benefits and reviews the stages all DevOps organisations go through to achieve it.

How many first/second/third cousins does this person have? If you have the genealogical data, SQL Graph can give the answers.

It is a commonly observed pattern for applications to utilize multiple datastores where each is used to serve a specific need. Now the challenge becomes how to keep these datastores in sync.

In a microservice architecture such as Netflix’s, propagating datasets from a single source to multiple downstream destinations can be challenging. These datasets can represent anything from service configuration to the results of a batch job, are often needed in-memory to optimize access and must be updated as they change over time.

Ed Elliott concludes his 4-part series by explaining what's involved in "testing in production”.

If you’re running availability groups it may seem like a great idea to offload your read only reporting workloads to a secondary. But, redo latency means that the data might not be instantaneously available on the synchronous secondary node.

A checklist, and some useful queries, for upgrading a pair of physical SQL Servers that hosted a single availability group of several terabytes of data, with minimal downtime.

Jonathan Kehayias on how and when Extended Events will discard an event that has been generated during data collection.

Thomas LaRock on the importance of the details that provide context to the PerfMon metrics, helping users understand what they are measuring, and where to find those details.

Erik Darling wants every database to have in it views to fully assemble data from the mess of DMVs that accompany query store, plan cache use, index use and so on.

Creating smart indexes, or occasionally rewriting the query, to fix performance e problems related to key lookups.

Of you find that Power Query is indeed querying your data source multiple times, the most common explanation is that it’s doing a “background refresh”.

How to add an additional “other” row to the selection obtained using the Top N filter in a Power BI report.

One of the big things that was lacking with the Data Profiling feature was the text length statistics, leading to incorrectly sized string columns in data warehouses. Well, the wait is over, text lengths are now available.

With some data sources, such as Analysis Services, you want to pass the username of the person running the report back to the server executing the query, but if your Power BI login does not match a UPN in our local Active Directory, then the lookup will fail…

Matthew Roche tries to clear up a few confusions surrounding use of dataflows and query folding.

How to you make sure that your SQL Server Agent jobs only do work on the instance that currently owns the Availability Group.

Create and share live PoweShell code!

Finding out when SQL Server was installed across all your instances.

Microsoft have published the features list by edition, for SQL Server 2019 (but are still editing it!)

One of the benefits of moving beyond 2014 (with SSRS) is that the appearance of the web portal (company logo & color scheme) can be customized (branded) using the same files that are used for branding PowerBI Report Server (PBIRS).

A database login or user (SQL Login, Oracle User, etc.) is actively being used and embedded in code, but the password is about to change. How can you find all of the places where it exists with SQL Server such as Jobs, Linked Servers and SSIS information stored on the server?

Lots of them weren't very good. Weakest of all was the password for Unix contributor Brian W. Kernighan: "/.,/.," representing a three-character string repeated twice using adjacent keys on a QWERTY keyboard.

In SQL Server 2019, there are new UTF-8 collations, that allow you to store your UTF-8 data natively. But what is the actual storage impact, and how does this affect memory grants and query performance?

Kathi Kellenberger takes on a logic puzzle in trying to understand how the windowing function, PERCENTILE_CONT, works.

A short but interesting side-road into deterministic values, and why it is important to get your data types correct.

Andy Malllon takes a high-level look at what it is, what it does, what it isn't

Does a query embedded in a stored procedure execute faster than that same query submitted to SQL Server as a stand alone statement?

Brent Ozar continues his series helping us to understand what indexes SQL Server needs to run queries efficiently, by thinking like it.

If you're troubleshooting SQL Server or Azure SQL Database performance issues that you think are related to concurrency, it's useful to know what isolation level is being used to execute a query. Bob Pusateri explains how to find out.

Suppose you are designing an SQL Server database application for a company’s CEO and you have to display the fifth most highly paid employee in the company. What would you do?

Ed Elliott updates SQL Cover, his open source SQL code coverage library.