Information Vs Data Vs Security

A subject that has been close to my mind recently, is as to when data becomes information, and how the security of that asset is managed - and by whom.

We have probably all seen the triangle:

By Longlivetheux (Own work) [CC BY-SA 4.0 (http://creativecommons.org/licenses/by-sa/4.0)], via Wikimedia Commons

Which as you will gather, denotes that Information is comprised of (useful sets of) Data. There's also the concept, at least in UK law, of data, which comprises of Information:

Data means information which –(a) is being processed by means of equipment operating automatically in response to instructions given for that purpose,
(b) is recorded with the intention that it should be processed by means of such equipment,
(c) is recorded as part of a relevant filing system or with the intention that it should form part of a relevant filing system,
(d) does not fall within paragraph (a), (b) or (c) but forms part of an accessible record as defined by section 68, or
(e) is recorded information held by a public authority and does not fall within any of paragraphs (a) to (d).

https://ico.org.uk/for-organisations/guide-to-data-protection/key-definitions/

So it is clear that these terms are not defined exclusively, and that the terminology alone may present a problem.

The business often has a nominated Information Asset Manager, who looks after the types of information an organisation processes, and is responsible (perhaps to a Senior Information Risk Officer, or SIRO). The organisational policy may well give this person (delegated from the SIRO) the last word on policy - however this doesn't mean this person has the technical background to ensure that policy is implemented - or even practical.

There may also be someone in the IT infrastructure whose remit is security - either a dedicated IT Security Officer, or perhaps someone who has this responsibility as part of their work. Again, this is quite a wide ranging remit, and this person may feel it is better left to subject matter experts in specific systems, and lay guidance as to the principles to be observed.

This leaves the DBA with two potential places to go for security instructions, yet neither may understand the detailed technical processes necessary to actually undertake the remit, as their remit is either business focused or too wide for the focus on the specific technology of databases. That's where the DBA brings his or her expertise to bear, and as such brings value to the business.

Often the DBA role is also to mediate where the tension exists between data security and information use. This is because they are the guardian of the security on the data - and so the first place that gets approached when technical permission is denied. This brings to light the difference between a technical prevention measure, and a business policy allowing, forbidding, or laying restrictions on a course of action.

Whilst clearly data security will almost always win the balance between security and flexibility, the role of the data professional is increasingly to suggest a way to merge the data once permission is secured and in accordance with best practice. This is a difficult balancing act, which may not be fully understood by information users.

So how can we alleviate the issues this generates? Firstly - publicity. Explain what is necessary, and have a ready to roll out example of why you wouldn't like your data to be misused. Perhaps an example might be of sensitive personal data being unexpectedly available to the world for misuse, or the potential for processing of data in a way it wasn't gathered for - both breaches of the DPA, but still things that get requested disturbingly often.

A second avenue is to engage with the other data professionals in a proactive way, and ensure that clear paths are laid down to get issues circulated for discussion - and engage with the business to resolve them. Generally, there's a good way to do things, and a bad way - for example, would anonymised data do for a task? Could the risk be mitigated somehow? Perhaps we can't do something with the data we have, but changing how it is captured to ensure the correct permissions are granted gives us the ability to do this in the future. If the policies don't exist (or haven't been reviewed recently) then these can be bolstered to ensure they are fit for purpose - and in line with recent changes to regulation.

With these approaches, we lay down a safe way of working, that also keeps us within legal boundaries and hence providing value to the business with both of these points.

SQL Injection

At the November meeting of the SQL South West user group I gave a presentation on SQL Injection attacks. My main reason for giving the presentation was that I have been surprised by the number of SQL Developers (and DBAs) who don't know what this is - or have the ability to justify why they should care about it to thier management. As it's one of the most used attacks (http://cwe.mitre.org/top25/#CWE-89) it clearly isn't as well prevented as it should be, and it can be quite powerful. It's also been around a long time - hence the famous XKCD comic:




Explots of a Mom - http://xkcd.com/327/

I aimed this talk / post at those who haven't heard of, or don't know much about SQL Injection. I'm going to run through the highlights of the talk - and you can download the PDF file.

I'm going to run through the demonstration I gave below - please do read the PDF file to get more details on the presentation itself. I've ignored the danger of code which is in the application and does the same sort of thing - but clearly this is susceptible to a few more tricks, as well as those below.

Some prerequisites - I'm using the AdventureWorksLT2008 database which is available for download.

I'm using a couple of stored procedures, which I will call from a purpose built web page. The first one is :

CREATE proc [dbo].[Concatenated] (@CustomerLastName Varchar(500))
as

/* SP to demonstrate SQL Injection Attacks - http://d-a-green.blogspot.com/  (an example of what NOT to do!)*/

insert tbl (msg)
select 'select CustomerID,Title,FirstName,MiddleName,LastName,EmailAddress,''Concatenated''
as SP from SalesLT.Customer where LastName = '''+@CustomerLastName+''''
exec ('select CustomerID,Title,FirstName,MiddleName,LastName,EmailAddress,''Concatenated''
as SP from SalesLT.Customer where LastName = '''+@CustomerLastName+'''')

This is a stylised example of something that is often used - and shouldn't be.
The second SP I created was :

ALTER proc [dbo].[Parameterised] (@CustomerLastName Varchar(500)) as
/* SP to demonstrate SQL Injection Attacks - http://d-a-green.blogspot.com/  A better solution*/
select CustomerID,
Title,FirstName,MiddleName,LastName,EmailAddress,'Parameterised' as SP
from SalesLT.Customer where LastName = @CustomerLastName

Now, clearly these aren't doing anything tricky, and as you can see they accomplish the same thing. The difference is in how they work. The first SP, 'Concatenated', "trusts" the input from the client. Whoever that may be. In normal circumstances we would expect this to be the designed application, but this assumes that nothing else is passing in the data.

Anyway, let's see the application we're calling, with a typical output:

You can see that the screen is split into two return grids - these are to show the respective outputs of the two stored procedures. Notice also that seven columns of data are returned - and these look like a table.

So, one way of indicating that SQL Injection would work, is if you get an error, or unexpected data (or lack thereof) if you put a single appostrophie in the input. So, noting that we had seven columns before, let's see what we can do.

We can type the following into the text box to show us all customer records

Harris' or 1=1 --

This could be sold to a competitor, used to embarass the company concerned, or to send targeted "phishing" emails to the users (particularly to those with recent orders).  We can also combine this with use of the
UNIONcommand to get information from other tables or views.
The -- on the end stops any further clauses executing, and prevents the closing apostrophie in the stored procedure causing an error.

We can see what tables are in the database (this can be adapted to list SPs, too):

Harris' union select 1,'a',TABLE_SCHEMA,TABLE_NAME,'a','a','a' FROM INFORMATION_SCHEMA.TABLES -- 

We can also see who can log into the server (note, I've used a name at the beginning that won't return a result so I only get the answers I want returned to me. This is more convenient!):

fHarris' union all select 1,'a',name COLLATE DATABASE_DEFAULT ,'a','a','a','a' from master..syslogins where isntname = 1 --

Clearly this application isn't now doing what it should be - it has been subverted and can do as the attacker wishes.
Other possiblities include (depending on what user the system is running as)

• Command shell - with all it's possibilities.
• Linked Server - Am I running as the same user as another machine?
• Do I have email enabled? Can I enable it? This is a much more convenient way of obtaining data.
• Can I create a trigger to use email to send me updates in the future, potentially after this method of getting in is fixed?

 I've not demonstrated it in this post, but you can also use encoded statements to get round checking for key words (delete, update, drop, etc).

Note, I've used SQL Server here, but these points are valid on most RDBMS' - you just tailor them to the environment - the errors you get or characters/methods that work can even help you to determine what the server at the other end is running, and from that what you can do.

I hope I've demonstrated what a simple thing an SQL Injection attack is, and how easy it is to prevent - note that the parameterised proc prevented these. If you must use dynamic SQL in your SPs, please use sp_executesql with parameters. Please also restrict the permissions of the web application to the bare minimum needed.

There's some more details of walk-throughs on the slides, and what some of the potential ramifications are of the attack - do take a look.