Voice of the DBA

Tag: security

Protecting Our Stream of Data

Protecting the data our companies collect is important. Many of us go to great lengths to secure our databases, firewall connections, limit access, and more. However, we can’t secure the data before it gets to us, and that can be a problem. I ran across a link on Bruce Schneier’s blog that shows a criminal placing a skimmer on a credit card scanner in a convenience store. The original video is gone, but there are plenty more.

In general, the loss of data from the application (and physical hardware in this case), isn’t really a database issue. After all, the data is essentially split into two streams, with some going to the legitimate database and other processes while another stream goes to the skimmer. If this happens, and the skimmer isn’t discovered, however, it’s entirely possible that any data loss might be blamed on the database or IT infrastructure.

If someone suspected you were hacked because of data being lost, could you prove you weren’t hacked? Or that the data didn’t come from your database? This is impossible, since you can’t prove a negative, but would you have any evidence that could be used to bolster your claims? Is there auditing or other tracking of activity? Does your organization keep any logs that would show a lack of activity and are protected against tampering? Ideally you would find the source that lost the data, but if you can’t, it can be difficult to prove that the losses didn’t come from your systems.

For most of us, we might not have much in the way that would show our systems have only had legitimate access. Instead we’d depend on the limited SQL Server logging, as well as other infrastructure tracking, such as firewall activity logs. The strength of our presentation would likely determine whether security staff or management accepted our claims as valid.

Point of Sale systems are different than the applications that most of us use, but certainly we have database security concerns that we should address. I would hope that SQL Server would bolster its capabilities in this area, providing a way to set up a tamper proof log easily that accepts writes of activity from a database in some structured text file that uses minimal resources. I’m not even sure what I’d want here that I can’t get from XE, but I do think having something more robust and standardized would be nice. If nothing else, a local SQL Server could stream XE events out to a file target in a remote location that only supports writing. This wouldn’t necessarily prove anything if the connection were disrupted, but it would ensure that a business was aware of the breakdown between the instance and audit log.

I know the ways in which people attempt to access and steal data will continue to evolve and become more complex and creative. We can’t protect against many of them from the database side, but I’d like to think that we could protect the data we have. We should ensure it is safe from theft, loss, or inappropriate access and prove we have done so.

Steve Jones

The Voice of the DBA Podcast

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

August 28, 2018
The Weakest Link

I noticed another data breach recently. This breach was from PageUp, a firm that helps companies find employees. This means that they have lots of information, potentially PII data, and some of it might be out there. Since they provide job sites for many other companies, you might have used them to apply for a job and not realized it. Certainly if you have used them, you might keep an eye out.

The company notes that no personal information was lost, though encrypted usernames and passwords were disclosed. These were salted values encrypted wtih bcrypt, which is secure, but all encryption can be broken given time and effort. Some people see bcrypt as secure, but others disagree. However, the strength of bcrypt depends on how the hashing was set up, and I wouldn’t depend on this to be foolproof. If you used a password to apply for a job that you use on other sites, change it.

The bigger issue for me is near the end of the BBC piece. A bank notes that a third party supplier had a security issue, so that means they need to check their systems. To me this means one thing.

Their security depends on the security of their business partners.

Depending on the level of access and integration, this might mean that your security is compromised by a link much weaker than the weakest link in your internal environment. Or that your security depends on the weakest human link not only inside your organization, but also within your partners. Despite all the work you’ve done to increase the security of your systems, you might have other holes out there.

It doesn’t appear this breach is as bad as originally thought, but the point is still valid. The more interconnected you are with partners, especially with shared access, the larger your attack surface area. I take away the need from this that I need to ensure a limited API and protected access with minimal privileges for internal systems that are connected to any other networks. Production level security is important not only to public facing systems, but also those that are semi-private with business partner access.

Steve Jones

The Voice of the DBA Podcast

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

August 23, 2018
Getting the Role Permissions–#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

If you are wondering what permissions a role has, and don’t want to spend time searching and digging through Books Online, there are a few system procedures that can help. These will give you the permissions for a role, or all roles.

Let’s see how to use these.

Server Role Permissions

Let’s say that you are wondering what a Processadmin can do. There is a procedure, sp_srvrrolepermission, that you can use to get the list of permissions. This can be run with no parameters, in which case you’ll get all roles and all permissions.

However, often you’ll want a more manageable set of data that a person can understand. Let’s see what processadmin can do.

Only two permissions here, so I can see that granting this isn’t going to affect security or databases.

Database Roles

The database has a related procedure, sp_dbfixedrolepermission, that looks for database roles. This can again be run without any parameters, as shown here.

If we include a role, we see just those permissions.

SQLNewBlogger

This is a short post, really about 5 minutes, based on my need to solve another issue. However, this is something a DBA should know, and it’s something I’ll likely use again.

August 16, 2018
Granting CONTROL on a database–#SQLNewBlogger
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I wanted to grant a login the CONTROL permission on a database. This wasn’t a simple as I expected. I had a login (JoeDev) with no user mappings or server roles, and a database (EmptyFileTest) that I wanted to grant them permissions on.

My first attempt was this:
```
GRANT CONTROL on EmptyFileTest to JoeDev
```
This didn’t work.

I’m a sysadmin, so it’s not permissions. Maybe it’s qualification. The database isn’t found as an object, so let’s fix that.
```
GRANT CONTROL ON DATABASE::EmptyFileTest TO JoeDev
```
Aha, I’m making progress.

The database is found, but the user isn’t. That implies this is an internal permission in the database that can’t be granted to the login. One more try:
```
GRANT CONTROL ON DATABASE::EmptyFileTest TO LOGIN::JoeDev
```
Nope.

Let’s add the user.
```
CREATE USER JoeDev FOR LOGIN JoeDev
```
Now we’ll try this again.
```
GRANT CONTROL ON DATABASE::EmptyFileTest TO JoeDev
```
As you can see, the context is the master database. This won’t work.

Let’s change the context and execute this again. Once I do that, things work.

We can see this worked in the database properties for our database, under the Permissions tab.

SQLNewBlogger

Security and permissions are important. A series of short pieces on different aspects of managing, assigning, or using security will teach you a lot and show that you’re cognizant of the need for security for your databases.

This took about 10 minutes to write. Note that I’m showing my process of learning and progress, not just writing about what is required. Think about writing your story, not just the information.
August 9, 2018