Tips, Expertise, Articles and Advice from the Pro's for Your Website or Blog to Succeed
In this article, we'll talk about fine-grained access control in DB2 - hiding data that is, in fact, present in the database, but should not be accessible to certain users.
Fine-grained access control is usually done in the database itself as the data is being accessed, but it can also be done between the database server and the database client using a programmable proxy.
Sometimes you need to control access to the data in your databases in a very granular way - much more granular than most databases allow.
For instance, you might want some database users to be able to read only the last few digits of some credit card number, or you may need certain columns of certain rows to be readable by certain users only. Or maybe you need to hide some rows from some users under specific circumstances.
The problem of data masking comes up surprisingly often in the world of IT. Any time you need to share some potentially sensitive data, you may need to hide, obfuscate, randomize, or otherwise dissimulate some of that data -- we'll call that the secret data.
In this article, we'll focus on the mechanics of data masking and gloss over a massive issue: data classification -- knowing who can access what data. Data classification is a whole different problem, especially in organizations with huge amounts of sensitive data. I'll refer you to a different article that touches on this topic. For the rest of this article, we'll assume that this problem has been solved and that we know who can access what data. The question is -- how do we hide the secret data?
Sometimes you just need to know what a database client is doing in the database. You might be:
Since version 10, PostgreSQL supports the concept of rewrite rules, which allow you to change how queries are executed.
In fact, rewrite rules are how views are implemented in PostgreSQL. When you access a view, your query is actually rewritten according to the rule that was created when you created the view. A view is basically a rewrite rule, as we're about to see.
As we all know, data security is a never-ending battle. Every day, we hear of new data breaches. It's a hard problem, and there is no single solution, other than defense in depth.
Let's look at one of those defenses for databases: query control.
In SQL Server 2016, Microsoft introduced the concept of row-level security, which gives you fine-grained control over who gets access to what data, potentially down to the level of individual rows. Normally, SQL security grants coarse access to a whole table or view (SQL Server can also do it for columns), and anything more granular than that requires the use of views or stored procedures.
There is another way to do row-level security without changing the database clients, and that's with Gallium Data - a free database proxy that can change the network traffic between your database clients and your database servers.
In SQL Server 2016, Microsoft introduced a new feature called dynamic data masking, which allows you to mask the values of certain columns and keep that data hidden from certain users, without having to modify your applications.
Let's take a look at how SQL Server does data masking, and compare it to the way Gallium Data goes about it.
Smart database proxies may not be familiar to many people, and it's a shame because they can solve many difficult problems elegantly. This article explains what they are, what they do, and when they are useful.
Let me start with a seemingly unrelated topic: web pages. Bear with me, it will all come together in a minute.