Voice of the DBA

Tag: security

Internal Controls

I was browsing the Internet and stumbled on a small part of a larger story that struck me. Many of you may have heard of the story of Jamal Khashoggi, the journalist for the Washington Post that was killed. I hadn’t spent much time reading about the story, and I don’t really want to discuss that topic here. The politics of the situation are not relevent here.

There’s a part of the NY Times background story that caught my eye when a quote was posted on Twitter. This is part of that quote: ” The intelligence officials told the Twitter executives that Mr. Alzabarah had grown closer to Saudi intelligence operatives, who eventually persuaded him to peer into several user accounts”. Essentially, an employee at Twitter was accused of accessing, and potentially disclosing, sensitive data about customers. This is what I want to discuss.

In my career, there are quite a few times that I’ve had to access data to solve some problem, debug an application, or produce a report. In many cases, I’ve had to maintain some confidentiality of the data, not even discussing specifics with other employees that were not supposed to view that information. To me, that’s just part of being a professional. We handle all sorts of data, some of which we should never use outside of solving an issue or producing a report.

As I thought about what was alleged here, I wonder how many social media companies have controls or auditing to determine who has accessed information. Would they be able to actually produce a report that validates some assertion that data was, or was not, accessed. I doubt many companies have these kinds of controls. Unless some Excel file or other export was on a file share, would there be evidence?

Then I thought does anyone really do a good job of producing audit records for information access? I know some government and law enforcement systems do this (and some legal software), actually tying queries and results to some individual and even a piece of work. That’s not the nature of information for most of us, though perhaps it ought to be.

Auditing data, especially for information access, could be a huge amount of data. Even keeping a record of all user access for a week in most SQL Server databases might be more data than many of us have in our database. I do think we ought to have the option, and I’d hope that we get more detailed, more capable, and more configurable methods of auditing SQL Server activity in the future (Hint, give us SQL Audit data in a csv).

Steve Jones

The Voice of the DBA Podcast

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

November 12, 2018
Republish: Data Breach Danger

In Seattle today, delivering two talks, so you get a republish of Data Breach Danger.

November 9, 2018
The Security Weak Link

I saw this cartoon, which I think is great: Data Security. It’s from John Klossner, and it perfectly shows that humans are, and probably always will be, a weak link.

I look at this in two ways. First, we need to account for and accept that our users will make mistakes, so we need to have security in place, but also monitoring that detects issues. The second item is that this could be a privileged user, so the less we need privileged users to actually access and do things, as in manually changes or deployments, and the more we require them to “submit” changes that are audited, the better off we are.

October 11, 2018
Deleting a User that Owns a Schema
This was an interesting question I saw posted recently. Through SQL Server 2000, trying to delete a user that owned objects was a pain. We had to actually rebuild all the objects, which was problematic. With SQL Server 2005 and later, we got schemas actually added as separate entities, so that solves some of the issue.

To allow a user to be removed from a database when they own a schema, we need to move the ownership, or authorization of the schema to a new user.

Here’s a short repro of what to do.

Let’s say that I have a user in my database called SallyDev. This user is a DDL_admin and can create objects. I’ll add Sally to my database with this script:
```
CREATE LOGIN SallyDev WITH PASSWORD ='SomethingStrong'

GO

CREATE USER SallyDev FOR LOGIN SallyDev

GO

ALTER ROLE db_ddladmin ADD MEMBER SallyDev

GO
```
As such. SallyDev has run this script:
```
CREATE SCHEMA SallyDev

GO

CREATE TABLE SallyDev.Class

( ClassKey INT IDENTITY(1,1)

, ClassName VARCHAR(100)

, Active BIT

)
```
Now, SallyDev has left the team and we need to remove her user and login. When we try to remove the user, we get this error:

In SQL 2000, Sally would have owned the table and we would have gotten a different message. Now, Sally owns the schema, but the schema now owns the table.

To fix this, we need a new owner for the schema. We can use any user with the ALTER AUTHRORIZATION command like this:
```
ALTER AUTHORIZATION ON SCHEMA::SallyDev TO JoeDev
```
I don’t recommend another user as we are just deferring the same problem. Instead, I’d move the schema to dbo.
```
ALTER AUTHORIZATION ON SCHEMA::SallyDev TO dbo
```
Once this is complete, we can drop the SallyDev user.

We can also verify the schema has moved to a new user.
September 17, 2018