Voice of the DBA

Tag: security

  • Rights for one table

    I ran across a thread that was asking how to grant rights to a person for one table only, and not other tables.

    My response is simple:

    CREATE ROLE MySingleTableRole
    GO
    GRANT SELECT ON     dbo.MyCustomers TO MySingleTableRole
    GO
    EXEC     sp_addrolemember 'MySingleTableRole', 'Steve'

    That’s it. By default users do not have rights to any tables even if they have rights to the database. If you have a user that needs rights to one table, just grant them rights to that table.

    If the user is a member of a group that has rights to other tables, you should probably remove them from the other group/role. Then build another group for them, or for the other users. If this is the case, then your group is incorrectly being used as you have people in the group needing different permissions.

  • Social Engineering Dangers

    DefConLogoI heard about a social engineering contest at this year’s DefCon hacker conference. The write-up said that every company targeted would have failed in a security audit, and these were some large companies, like Google, BP, Proctor and Gamble, Microsoft and more. It truly highlights the “a chain is only as strong as its weakest link” analogy being applied to companies, and I’m sure that the larger the company, the more weak links there are.

    Security is a constant battle. It’s hard to get right, and it’s hard to get people to take is seriously. Most employees don’t necessarily think that the information in a company is all that important. In fact, if you look at your databases, how much data in there do you think is really critical?

    No matter how important most of the data is, I’m sure there are some things that you would view as definitely worth protecting. I would also bet that some of that data that is worth protecting is co-mingled with other, less important data. Whether it’s in a database, on the filesystem, or somewhere else, often we have critical data mixed in with other data.

    Which means that we ought to try to protect it all. We ought to be applying strong security, and educating users that disclosing anything about the company to someone they don’t know, no matter how innocent the request, could lead to a breach of security.

    We want to help others. We want to be seen as people that make the organization function more efficiently. We can do that within the constraints of good security practices. It just takes a little effort to build the habit to stick to security procedures.

    Steve Jones

  • Check Twice, Update Once

    I was reading through this blog from the SQL Server PSS team (a must-follow), and found this note about the SQL Server Best Practices Analyzer (BPA). This isa tool that can examine an installation of SQL Server and report on any deviations from best practices according to Microsoft, as well as warn you of potential issues with your configuration. I had used it with previous versions of SQL Server and it helped me quickly determine if I’d missed anything during setup.

    Now I haven’t much thought about running this regularly. I’d assume this is something I just run once after installation to be sure I’ve configured, but the blog gives me a good reason to run this regularly, and especially whenever I have an issue. In the entry, it talks about not only noting that an error being returned by SQL Server and noting it’s fixed in a CU, but that a trace flag is also needed. Running the BPA would inform the user that the trace flag needs to be enabled.

    I think it’s great that this type of feature is being designed into a tool like the BPA that can really help a DBA. Knowing that this trace flag needs to be enabled is the type of knowledge that someone might gain with experience, but it’s short lived. At least it should be as I would hope the issue is corrected in a Service Pack w/o the need for a trace flag.

    Tools like this don’t eliminate the need for DBAs. We still need people that can make the decision about whether to apply this, perform testing, and more. These tools just make it easier for us to do our jobs more efficiently.

    Steve Jones

    Update: as noted in the discussion, the MS Baseline Configuration Analyzer is required for the BPA tool. MS BCA v2.0

  • Security Panics

    I went to host the Red Gate SQL Source Control webinar recently and had a snag. I downloaded the GoToWebinar software (why must I download this every time?) and when I ran it, I got an error.

    First it was a FireFox, “This security certificate is invalid” and I had to click the “I understand the risks” button to move forward. But then I got a dialog that said

    “This security certificate has been revoked”

    Now, a smart guy would click “cancel” because perhaps there’s some phishing or malicious behavior going on. Being in a hurry, I clicked “OK, move forward anyway, I don’t need no stinking certificates.” Maybe not smart, but it failed, so no big deal.

    Fortunately I had been trying to connect 30 minutes early, so I had time. I called Citrix, and they attempted a remote desktop session, which failed for the same reasons. We chatted for a few minutes and the tech support guy said that the certificate didn’t expire for years, and could I try another machine.

    I went out and was about to do so when I noticed that my clock said “8/19/2039”. It hadn’t registered before, but that time it did. 2039 is likely past the end of the Citrix security certificate’s lifetime.

    I reset my clock, and things went well.

    I’m not sure what happened. I used my machine early in the morning, took kids to school, and then came back to a machine that wouldn’t come out of suspend. I rebooted, and when it responded (some Java error on the screen, figures), things seemed to work. Except that the webinar didn’t.

    I don’t know what causes a clock to slide, but this actually happened last year one time as well. This is a Win 7, x64 machine, and it surprised me that I got this much drift, if 29 years is “drift”, but it did.

    On the bright side, the Win 7 year-changing dialog is kind of cool. You might play with it.