DB Security Practices



  • A few years ago, I had an interview with Reasonably Large Corporation X (NASDAQ: RLCX) for a DBA position. Now, while I don't really consider myself a DBA (and have little desire to forfeit my developer hat), they were impressed with my experience and wanted to bring me in. After some questions about data modeling and integration with third-party systems, they asked me how I would set up the security for a database. Much of my experience in this area was with systems that did things the Wrong Way™ (i.e. one login with server-wide sysadmin), but I had read up on the security features of SQL Server, and gave them this answer:

    I would have users connect via Windows authentication and each login would be granted access only to the securables needed to perform their work. If per-table per-user granularity was too much, I would create a database role containing all the necessary securables for each "group" of users so that new users can be assigned permissions based on their role in the organization.

    One of the interviewers objected.

    "That's too much maintenance work. Is there a way that it could be simpler?"

    So I said:

    "Well, if maintaining database roles is too much effort, one could create a single user for the application with access only to the stored procedures and views it needs to run, and provide access controls within the application itself."

    And the response:

    "There are still some headaches with that - a list of all objects used by the application will need to be maintained and permissions GRANTed and DENYed. Even simpler?"

    I sighed internally.

    "Well, if worst comes to worst, you create a single user with db_owner or sysadmin and enforce access controls through the application, at the cost of defense in depth against privilege escalation."

    They stopped their line of questioning after this answer, which I would assume meant that they were satisfied.

    That got me thinking, though. Are the big dogs really no better than the 5-man cowboy teams at this sort of thing?


  • Grade A Premium Asshole

    "Are the big dogs really no better than the 5-man cowboy teams at this sort of thing?"

    In many cases, they are much worse. Read up on any major data breach for ways that the big boys are Doing It Wrong.



  • What you were really looking for is essentially business roles.

    You create a group role, and assign individual users to it (IE: Windows NT auth users default to X group, unless explicitly stated otherwise - or as part of your provisioning process add / remove them from roles - with an optional application level group for each role. Role chaining is your friend)

    Your business roles manage access and permissions to individual tables/databases

    Unless you introduce a linked server, then all concept of authentication and ownership go right out the fucking window and leave you open to cross server query attacks, but I digress.

    Small shops usually have better potential to manage security at the fine grain level, but most don't know how. Most large enterprises should manage at a fine level, but don't know how or don't want to invest in using their 'enterprise' edition of sql server.

    It's depressing really.


  • :belt_onion:

    @Groaner said:

    Are the big dogs really no better than the 5-man cowboy teams at this sort of thing?

    No they are not better at all.
    The big dogs want the easiest, cheapest method and don't give a shit about security unless it falls under PCI or other required external audits. And even then, they would generally do anything they can to get around paying for it unless you can luck out and get a boss that cares (and manages to suck up enough to not get fired by the non-IS C-levels who are angered by a slightly slower turnaround).


  • Discourse touched me in a no-no place

    @darkmatter said:

    The big dogs want the easiest, cheapest method and don't give a shit about security unless it falls under PCI or other required external audits.

    If the big dogs could, they'd just handle the required external audits by bribing the auditors. Sometimes, responsible disclosure has to involve an anonymous deposition with the press and a suitable politician. They're both scum of the earth, but have their uses in setting a fire under relevant asses. Alas.


  • ♿ (Parody)

    @Groaner said:

    Are the big dogs really no better than the 5-man cowboy teams at this sort of thing?

    The big dogs are likely an accumulation of 5-man cowboy teams.



  • @boomzilla said:

    The big dogs are likely an accumulation of 5-man cowboy teams.

    Multiple 5-man cowboy teams, fused together with waterfragile development and a conglomeration of PHBs.



  • I always found it disturbing there are "enterprise" apps that use the Oracle user system as the application user system. They also default to giving users full access over every table in the schema when you create an user through their GUIs.



  • @delfinom said:

    I always found it disturbing there are "enterprise" apps that use the Oracle user system as the application user system. They also default to giving users full access over every table in the schema when you create an user through their GUIs.

    And this is why the I-Hate-Oracle Club exists.



  • That's not why, but it's a contributing factor.



  • Did I mention one application licenses based on number of database users because their shit user system? Want to use a user/schema for something completely unrelated to said shit app? Nope.




Log in to reply