Tag: security

How many of you grant access to schemas in your databases? I’m curious because I slowly am starting to see more and more people using schemas in their designs and not defaulting every object to “dbo”. That was the default action for many developers for years, and it’s still a habit of mine. Without SQL Prompt to add the schema to my queries, I’d still be producing code that followed that practice.

Schemas exist to allow some separation of different types of objects. In early versions of SQL Server, we didn’t have schemas, but considered the third of four part naming to be the owner. In other RDBMSes, the use of a schema was to allow grouping and separation of different objects. This was also, and still is, a security boundary that helps ensure that authorized users can be easily limited to data access for certain objects.

This is also a nice organizational boundary for related objects that have the same schema, or even name. I have seen auditing or etl schemas that maintain the same named table that exists in a dbo schema, but is separated by placing this in a separate schema. I have even seen some archiving schemas that move data to a related table that exists in a different schema, making it easy for new administrators and developers to find (or remove) older data.

This week, let us know if you manage objects and security with schemas or if you stick to granting access at a database/object level the two more common ways of working with databases. If you have never used schemas and stuck with the defaults, perhaps you want to learn a bit more about how schemas work and the ways in which they may make your work easier.

Steve Jones

The Voice of the DBA Podcast

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

We hear regularly about consumer backlash from data breaches. There are usually legal repercussions, and often hard dollars (euros, yen, etc.) being spent on things like identity protection or credit monitoring. In addition, in the last couple of years, there are plenty of people that try to limit, or cease, doing business with organizations that lose their data. That isn’t always possible, especially when the government is the one getting hacked, but more and more people are taking an active stance against poor internet security by organizations.

It’s not anything that I think will happen soon, but I bet it will happen. Airbus had a breach that lost employee data. I wonder when we’ll start to see employees initiating lawsuits or other actions against their employers. My suspicion is that this will happen with former employees first, but with the GDPR, current employees in the EU might feel emboldened, and with good reason. Employers have a lot of data about us, and that ought to be well protected.

There have been hacks to lower a stock price, or affect a company. Why not hacks to attack employees? I wouldn’t have thought of this until I talked with a forensic analyst about other possible second order attacks. Could there be individuals that might seek to attack IT staff through personal information and blackmail or otherwise extort them to copy data? What about partners and spouses of employees at big companies? I don’t worry about Redgate as a target, but what if you work for Google/Apple/Microsoft/Facebook? We already had a potential sleeper employee at Twitter.

Unfortunately, I see no depths to which criminals might sink. Across the last decade there have been stories of actions that I never would have contemplated. While I hope my fears are unfounded, I worry that sensitive data about employees might be on the radar for some nefarious individuals.

Steve Jones

The Voice of the DBA Podcast

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

If anyone sets up a new desktop machine, a new SQL Server instance, a new Elastisearch instance, or any other platform, you need to ensure there is default security. That was a problem recently with a financial company that had a default Elasticsearch instance up with no authentication required. When I look at the install directions, security isn’t mentioned. There is a secure settings page, but that’s not authentication. It’s not until you look way down the setup instruction list that you find configuring security. Even then this mentions “you can password-protect your data”, which isn’t a good option to have.

This should be built in with a “you must” protect your data. SQL Server used to allow a blank password, but there was plenty of outcry about this and setup changed to either require Windows Auth only, which inherently has some secure settings for accounts, or mixed security where a password must be entered for the only default account. That’s how many software programs work these days, and really how all should work.

I’m still amazed by people that don’t put a password (code, fingerprint, etc.) on a mobile phone. I’m surprised that we have any data store platform that doesn’t require some security. As much as I find Google Drive sometimes a pain, I am glad that I can’t put files in there and open them to the general public. At least, I haven’t figured out how to do this. I constantly need to add specific people to access files.

All data files/software/stores/platforms/etc. need to be secured. Containers, buckets, any file shares should be limited in some way. Any platform ought to require user accounts and not allow access to all data by default. Really, privileged accounts ought to separate configuration  and security from data access. I’d like to see “sa” allowed to work the instance and create databases and move files, but not allowed to access data.

We need better default security, and more importantly, we need to believe that we need better default security. That’s for all data, including backups, search services, and anything else that can access a database. Leaving data open to anyone that might stumble upon the URL is a mistake that has no excuse these days.

Steve Jones

The Voice of the DBA Podcast

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