Voice of the DBA

Tag: security

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
The Combinations of Software

Security issues seem to be appearing more frequently, not less. I’d expect that we would be getting better at writing software, and I think many of us are. The problem is that more and more people are writing software and we still haven’t found a way to better train developers early in their careers. Perhaps the one good thing is that more and more developers are using frameworks, which create more consistent software. If issues are discovered, a patch can ensure a large swath of systems can be patched.

The bad news is that far too many development groups build systems quickly, but don’t patch them in an expedient manner. They may be afraid or just not bother.

A short while ago there was a loss of data from Ticketmaster ticket sales. Apparently a chatbot was used to steal information. As soon as Ticketmaster discovered the issue, they disabled the software. There is some disagreement as to who is at fault here. The chatbot vendor says their JavaScript chatbot should not have been running on a secure payment page.

The specifics here aren’t important, but it is a concern that more and more often we are assembling applications from pieces of software. We often use plugins on websites and other building blocks when we put together a system. In more and more cases, we will be connecting this software to our data stores. That wasn’t the case here, but often there is some data access, and since we may keep both secure and non secure data in the same database, any vulnerabilities in one building block can cause security issues in others. The weakest link in the chain saying applies here.

I wonder how many of you worry about issues with the assembly of whole pieces of software. The pieces should be more secure, or at least, more easily patched. There should be more incentive and resources to patch software used by many people, though many times vendors become hesitant to do any more than absolutely necessary.

I’m not sure if it’s better to build out of pre-written pieces of code, but I do know that security is a shared responsibility and I wish it was more of a priority for all developers. The security of our application can depend on that weakest link.

Steve Jones

The Voice of the DBA Podcast

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

July 19, 2018
Storage that Expires

Whether you like the idea of the GDPR (and the new California law), I’m sure you agree that these laws will likely change our data handling in business. Even if they are weakened through legal challenges, many companies have already started to comply and change some of their practices.

I’ve written about the GDPR plenty of times this year, and I like the law. I hope the law stands strong and resists most challenges. While I’m sure there will be plenty of spurious or silly requests and complaints, I do think these laws are asking for the good data handling practices that most data professionals have advocated for years. These include not only security but also integrity. How often have many of us advocated for corrections to problematic data and been told no? How many times have we complained about security practices?

One area that I think has been neglected too long in most industries is the area of retention. Most companies I’ve worked for have retained data indefinitely, without any thought or policy. In my mind, we ought to explicitly think about how long we hold data, and remove older data that isn’t needed for our organization’s operation. I feel more strongly about this over time as we find that data beaches become more and more prevalent.

Azure has started a preview of immutable storage, essentially WORM (Write Once, Read Many) drives as an Azure container. I’ve used WORM storage, but it’s often been viewed as a way of keeping information forever. that can change with this new Azure storage, as you can set a lifecycle management period. The blobs will be removed after this time, which removes one management headache from administrators.

I could see quite a few uses for this type of storage. If it’s inexpensive enough, what about storing backups here? We could have policies set to remove files after some limited period. I’m sure there are plenty of other uses for storage the is immutable, but also contains lifecycle management options. What creative use would you have for this type of expiring WORM storage?

Steve Jones

The Voice of the DBA Podcast

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

July 18, 2018