Voice of the DBA

Tag: security

  • SQL Server Truncate Table Permissions

    I saw a note recently where someone asked what permissions were needed for a user to execute TRUNCATE TABLE. In previous versions we needed ownership of the table or DBO level permissions. I had thought this was changed in SQL 2005 to require just the CONTROL permission.

    However when I checked the TRUNCATE Books Online page, I found this: The minimum permission required is ALTER on table_name. TRUNCATE TABLE permissions default to the table owner, members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles, and are not transferable. However, you can incorporate the TRUNCATE TABLE statement within a module, such as a stored procedure, and grant appropriate permissions to the module using the EXECUTE AS clause.

    Alter permissions is the minimum?!?!!?

    That sounded fishy, so I did this. First I created a new user, with no permissions other than public. My user was, appropriately, MyTestUser.

    Next I created a table and granted permissions:

    CREATE TABLE TRLC 
(
  est_no varchar(10) default ' '
, right_no int default 0
)
GO

INSERT TRLC SELECT 'Test', 1

GRANT CONTROL ON TRLC TO MyTestUser

    I then opened up another Query Window and changed the connection to use MyTestUser. This user only had CONTROL permissions and nothing else. A quick test showed that this user could indeed clear out the table. This:

    TRUNCATE TABLE dbo.TRLC

    executed without error.

    I think Books Online needs an update, and I’ll submit a note to that team to clarify this.

  • Bad IT People

    Know Your Enemy – A tenet of IT Security

    It’s going to happen. Someone, somewhere, is going to deliberately compromise security and abuse their position as an administrator. Hopefully it doesn’t happen at your company, but the longer you are in IT, the better chance you have of being around when a “security DR event” occurs.

    This article talks about IT people going bad, and it happens more often than we hear about. Many companies don’t want to disclose these types of events, and it’s possible one happened in your organization already.

    This is nothing new. In every industry in which I’ve worked there have been people that take advantage of loopholes in systems and processes to engage in criminal activity. I have had liquor stolen from bars and lumber from a warehouse. We can’t prevent all of these activities, and companies know this. Usually there is a line in your accounting system that marks these losses as some cost of doing business.

    As with many external hacking issues, companies are likely to try and prevent anyone from finding out about these activities outside of the company. That’s not a great solution for anyone, since often these employees are let go and hired by the next company who has no idea what the person they just hired has done.

    You shouldn’t participate in these activities, and you ought to turn in those that do. However I know many people find that harder to do when confronted by this situation. At the very least, if you turn a blind eye, don’t help prolong a bad IT person’s career in this business. Don’t give them any sort of recommendation, and don’t include them in your personal network in any way.

    Steve Jones

    The Voice of the DBA Podcasts

  • Slow Fixes

    Moving Slow

    Today we have an editorial reprinted from Jan 15, 2006 as Steve is on vacation.

    There’s an interesting piece at the Washington Post on Microsoft’s delays in releasing patches, with some analysis showing that when the flaw is disclosed to the public, a patch comes out much quicker. After some analysis over the last 3 years and researched the dates Microsoft knew about the issue and the dates that the patches were released.

    Surprise, when everyone knows, the patches come out quicker. It seems that the piece is intended to take a shot at Microsoft’s patching process, and maybe it is, but there are some interesting things in there to talk about. First of all is the time lag.

    Is this any different from any software vendor or even internal corporate software? If your boss knows, or the client knows, don’t you work a little harder and a little quicker? Isn’t it more critical and don’t you rush things in addition to working harder when it’s a “public” patch that is needed?

    I’m sure we all do. And it’s human nature to put more effort into something that’s widely perceived as an issue and less effort if you know that you may have more time. We all do that and our work schedules, effort, and productivity change depending on a variety of things, including the importance of the work.

    The piece also leaves open a number of questions and mentions this, noting that the analysis might be flawed. There’s no mention of if the rushed (or delayed) patches had to be repatched later. There’s concern over patches applying to one area, but other similar flaws found in other parts of the software remaining unpatched. That’s something for sure that should be examined in looking for re-patches or whether things are rushed. There’s also the lack of examination on what else was happening inside Microsoft, and whether people working on other projects had to be pulled off them.

    We all know that delays things as well. They take time to get their head back into code, they may be annoyed, a critical person could be out, etc. Not to mention that the statistical methods might not be the best ones, but I’ll leave that to the mathematicians to figure out.

    Patching is hard. As is finding bugs. I think Microsoft has done a much better job over the last 3-4 years and the quality of software, at least SQL Server, has improved. However there is still definitely room for more improvement.

    Steve Jones

    (published at http://www.sqlservercentral.com/articles/Editorial/72365/)

  • Cloud Hacking

    Hackers are training for the cloud challenges

    Not everyone is down on the cloud. Apparently someone found a great use for pay-as-you-go computing in the cloud by using it to hack wireless networks. What’s next, someone using Amazon’s Mechanical Turk to find security holes in police patrols of local businesses? It constantly amazes me just how someone will take a technology that is very handy and turn it into a tool used for criminal purposes. I suppose we just have to live with this use of technology and learn to protect ourselves as much as possible.

    I think that security will be one of the bigger impediments to cloud adoption than anything else. Sure there’s the cost factor of data transfers, and trying to determine how to manage the costs of large bursts. There’s the need to integrate local and cloud development codebases, and potential patching/version issues. However those are not large issues when compared with the potential security issues that come with cloud computing.

    Even as we move to private cloud structures, we may have security issues with more resources being grouped under one large services umbrella and the difficulties of managing security for large numbers of users. We want to use groups to make management easier, but the more people you manage and the more groups that become involved, the more likely it is that you will have some security hole in your system.

    I think we’ll start to find more hackers that will discover, and take advantage of, hole in cloud security that we haven’t thought of. As they do, we’ll see more resistance from companies in migrating their infrastructures. At least until it becomes more cost effective to do so. Then we’ll discover that many of those security issues don’t seem to matter quite as much.

    Steve Jones

    (published at http://www.sqlservercentral.com/articles/Editorial/72385/)

    The Voice of the DBA Podcasts