Voice of the DBA

Author: way0utwest

  • Acing an Audit

    Good procedures built into your processes should make passing an audit very easy.
    Good procedures built into your processes should make passing an audit very easy.

    I’ve been through relatively few audits in my database career. I’ve worked in a few industries that didn’t require them, and avoided the stringent requirements of PCI and HIPAA. ISO 9000 was the first audit I encountered and I had been preparing for Sarbanes-Oxley (recently passed) when I left that company to come work for SQLServerCentral.

    The preparation for an audit required a lot of work, meetings, and organization. The first time I suffered through an ISO audit, I was amazed at how much of our daily work was interrupted and the time spent ensuring we would pass the audit. The second time wasn’t much better, though I’d instituted some processes and controls for the DBA group that did reduce the amount of preparation needed for our portion of the audit.

    I wish that more companies I’d worked for had actually built the controls, security, and documentation into their processes. Maybe then they’d only need a 30 minute window to prepare for the audit. That’s what an insurance company needed to do recently according to this piece. I found many of the rules and regulations required in the ISO and SOX documents to be ones I’d want to implement for my database systems. The hard part was getting management to agree and implement the rules as part of our daily work.

    I did find it interesting that the company had built their own software to match their processes and allow employees to work efficiently. Lots of companies have struggled with the idea of becoming their own software company, but if software is truly going to be an important part of most businesses, perhaps it’s a good investment for most of them.

    Steve Jones

    The Voice of the DBA Podcasts

    We publish three versions of the podcast each day for you to enjoy.

  • Granting Permission to Grant Permissions

    I’ve never felt the need to allow this, but I saw someone ask the questions recently. Suppose you had a view and you wanted to allow a user to grant permissions to this view to other users.

    It’s not a weird edge case, but it’s just not one I’ve normally bothered with. I manage by groups, always, and never want a user to be granting permissions to a specific view. I inherently see users as fragile in the lifetime of an application (administrators as well)  and don’t like the idea of allowing them permissions.

    If you need to do this, however, there is a way. Let’s examine a basic scenario. I want to allow Bill to grant permissions on the view “MonthlySales”, which I’ve created in AdventureWorks. If I want to allow Bill to use this view, I need to do this:

    GRANT SELECT ON MonthlySales TO Bill

    If I log into the server as Bill and execute 

    SELECT * FROM MonthlySales

    I get this:

    grant1

    Simple stuff.

    However if I want Bill to be able to allow other people in his department to see this view, what can I do?

    If I examine the BOL page for GRANT, I see there is an option that can help here. The WITH GRANT option allows the person specified in the statement to grant the existing permissions to others.

    Let’s assume I have another user Sue. If I log in as Sue, and I execute the same SELECT that Bill ran above, I get this:

    Msg 229, Level 14, State 5, Line 1

    The SELECT permission was denied on the object ‘MONTHLYSALES’, database ‘AdventureWorks’, schema ‘dbo’.

    I could easily manage permissions as the DBA, and I prefer this, but for when you have some data that a user can manage, and you are in an environment where things change quickly, perhaps you want to delegate some permissions to users.

    Now, let’s change the initial permission I set up for Bill to this:

    GRANT SELECT ON MonthlySales TO Bill
 WITH GRANT OPTION

    Bill can still see the view and the data. However Bill can now run this:

    GRANT SELECT ON MonthlySales TO Sue

    Once he does this, Sue can see the view.

  • Still 32

    I was listening to a presentation recently and the presenter talked about some of the restrictions in working with 32 bit SQL Server on 64 bit Windows hosts. Another person joked that any companies still running 32 bit software were way behind the times. I heard a comment that surely everyone runs 64 bit hardware these days, don’t they?

    Do they? How many servers in large companies are still running on the x86 architecture? I know I have old laptops still running those processors, though I’d like to think that most companies have a hardware refresh rate that’s no longer than 5 years, and most laptops and servers would be x86.

    The platform architecture, however, doesn’t necessarily imply our software has been upgraded to 64 bit. There are problems with various drivers that won’t run under 64-bit architectures. Excel is often a problem as SQL Server moves to 64-bit versions. There are 64-bit drivers, but the change, testing, etc. effort for many companies may not be worth pursuing. It can be annoying for us data professionals, but it’s a valid business decision. The same argument often applies to those companies running SQL Server 2000 (or older versions).

    Five or six years ago I heard a presenter that worked for a large Fortune 100 company say that their policy was for applications to last ten years. That included the hardware and software, since the cost of change, whether through new software or major upgrades, was significant. With many companies still running SQL Server 2000, I suspect that many companies have a similar, unofficial, policy.

    I don’t know whether the majority of software we use in our careers is 32 bit or 64 bit, but I am sure that we will have to deal with the former for years to come. You can complain about it, but you better learn to work with it.

    Steve Jones

    The Voice of the DBA Podcasts

    We publish three versions of the podcast each day for you to enjoy.

  • LinkedIn and Connections

    I don’t use LinkedIn a lot,  but I do try to go over there once every week or two and update my status, add a link, and accept connections.

    I have a simple philosophy on connections: I accept them all. I don’t worry if I know the person or not, if they’re a recruiter or fellow technical pro, I just accept them.

    My thoughts are that the more people I can touch if I need help, the better off I am. It doesn’t matter where they are, or how I know them. A broadcast for network help is a broadcast that I want to reach everywhere.

    If the person is someone I know well, I do take a moment and move them into my A or B networks.

    I feel differently on Facebook, which is for real friends and family and I rarely accept friends there.