There was confusion recently with a poster that was moving permissions around and asked why I said they should revoke permissions and not deny them. I decided this was worth a post to explain.
If I GRANT SELECT (or UPDATE/INSERT/DELETE) permissions to a user, then they can use those permissions to view data in a table. If I REVOKE the permissions, it’s the same as if the user never had them. They would need to be GRANTed permissions again to see the data.
However, if I DENY them the ability to see data, then that’s different. They can’t see the data, but a subsequent GRANT will not allow them to see the data because the DENY will still be in effect.
It’s a more permanent change, and should be used when you need to be sure that someone cannot see data, not when you are looking to remove permissions. To undo a GRANT, use REVOKE.
Does the certificate matter for TDE? Apparently not as I found a number of people discussing the fact that the expiration date for certificates is not checked for TDE restores. So if your certificate expires, you can still restore the TDE backup.
However the documentation doesn’t mention this, and I think it ought to be clarified, so I submitted this Connect item. Feel free to vote if you agree:
I was reading over a digital supplement that I received from Dark Reading recently, which details some of the issues in the Epsilon, Gawker Media, and a few other data breaches. It was light on details, but there were some nuggets of knowledge in there about how these attack occurred. Some were sophisticated, and some were insider attacks, but the advice given to help protect your data was all similar: limit access, watch for injection, audit, and monitor.
I know that over the last decade as I’ve run SQLServerCentral, the topic of security and auditing has grown in importance. More and more people are implementing auditing functions in their applications and slowly tightening security where they can. There is a lot of work to do, and a lot more education that needs to be spread to a wider audience, but the trend is positive.
However one thing in the article caught me eye, and it had me wondering how many people are going beyond the basics. For those of you that have auditing built into your application or database, I have a question this week:
Do you regularly analyze the audit data to look for abnormal trends or access?
All the data in the world doesn’t have any value if it’s not used. In a security context audit data isn’t all that useful if it’s only examined when an incident is discovered. The real value in auditing data is the ability to uncover problems before they occur. Looking for inappropriate access, unusual access for a particular individual or application, or even repeated attempts to gain access can help prevent a data breach.
After all, catching the criminal later doesn’t necessarily mean you’ve “recovered” the data. Unlike physical objects, data can easily be copied and spread in way that prevents it’s complete recovery.
Does the age of software matter? It’s an interesting question and one which recently came to my attention after a blog post by Dan Jones. Dan works on the SQL Server team and posted a note that the SQL 11 (Denali) tools would only be supported on certain operating systems, of which Windows XP was not one. In the comments on his post it seemed that there was a split in how this decision was viewed by readers, with a number of people saying that a ten year old OS didn’t need to be supported and should be upgraded.
However, in my mind, the age of the OS isn’t an issue. If the OS still works for you, there’s no problem running it. My wife’s company has the majority of their users on XP, and she just received a new laptop with Windows 7 just a few weeks ago. There wasn’t any fundamental reason for the change, as all their business applications work fine with Windows XP, but as new hardware is purchased, there often are not XP drivers, so employees are slowly moving. That seems to be the case with many companies, who find XP to be a very solid OS for most users.
However for SQL Server, I agree with the decision. The test matrix for SQL Server tools is likely huge, and they are supporting two desktop operating systems (Vista and Windows 7) as well Windows Server 2008 and Server 2008 R2. I would rather that the resources working on adding features, and fixing bugs in SQL Server, not be diverted to working on XP or Windows Server 2003 as those are too old. Not old in age, but old in versioning, being more than a version behind the current operating systems.
I know there are a lot of people that are not happy with this, and I can understand their reasoning, but I just don’t think that the number of companies that want to go to SQL 11, and are unwilling to allow developers and DBAs to upgrade to Windows 7 is large. Personally I’d rather have more resources devoted to more exhaustive testing on the current set of operating systems and fixing any bugs that are found.
Voice of the DBA 