Security Convenience

  • Comments posted to this topic are about the item Security Convenience

  • Security should always be cut and dried. Also, it should assume a permission denied position by default. If it doesn't know for certain then it means nothing is given up.

    Backwards compatibility is the root of many security issues.

    Gaz

    -- Stop your grinnin' and drop your linen...they're everywhere!!!

  • On the other hand, if setting up security requires more effort than necessary, some administrators might choose an easy way out by simply granting permission to (nearly) everyone. Even with security administration it is a balance between ease of use and, well, security, since you can't have both. I do aggree that backwards compatibility seems to be the cause of many security holes in current systems. However we have to deal with aging applications emitting code to grant new user access to their data that must continue to work, even if it depends on some questionable defaults like the equality of a user name and a login. It's not a perfect world we live in ...

  • One reason I miss a lot of these kinds of questions is that I don't do a lot of DBA oriented work such as backups and logins and server set up and configuration. I may do a bit of research on a question outside of my current and expected needs and then take a guess. Sometimes I get lucky. Saying everybody who participates should know these kinds of things is mistaken. I expect the population is quite varied in our needs and use.

  • As a sysadmin, I create a login:

    CREATE LOGIN BobSmith WITH PASSWORD = 'mypassword'

    I now am in the Sales database and execute this:

    CREATE USER BobSmith

    What happens?

    Considering that a user name can be different from the login name that it's mapped to; I agree that this default behaviour shouldn't work the way it does.

    However, I actually don't see the usefullness of naming a user account indepently from the login it's "mapped" to. That functionality itself potentially leads to confusion.

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • Steve, what is this curious attachment at the bottom of your Security Convenience article; a PostScript file?

    Resources:

    SSC BG M4.eps

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • The default behavior is to use the database user with the same name you use to login to the server with, if anything I would change allowing DB users with different logins than server....

    In what context would you even want to have a different db user than login?

  • Heh... the title of this article should be titled "Security vs Convenience" because the current title is actually a bit of an oxymoron.

    What I mean by that is that Active Directory Groups make it wicked easy on the DBA. When a new person joins the company, the DBA doesn't have to do a thing. The "Windows People" add people to the requested ADGs and "Security" is done. I love it and hate it because not everyone assigned to a particular ADG should actually have the privs that the ADG has but you don't even know because of the convenience of the feature.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Eric M Russell (1/15/2016)


    Steve, what is this curious attachment at the bottom of your Security Convenience article; a PostScript file?

    Resources:

    SSC BG M4.eps

    Logo for the site in EPS. Must have uploaded it in the same session as the article for some reason. I'll remove it.

  • ZZartin (1/15/2016)


    The default behavior is to use the database user with the same name you use to login to the server with, if anything I would change allowing DB users with different logins than server....

    In what context would you even want to have a different db user than login?

    In general, you don't. However you could create SQL Logins in a database with newer versions, so potentially you have collisions to resolve.

    Personally, I'd prefer it if there was no way to make his different.

  • Jeff Moden (1/15/2016)


    Heh... the title of this article should be titled "Security vs Convenience" because the current title is actually a bit of an oxymoron.

    What I mean by that is that Active Directory Groups make it wicked easy on the DBA. When a new person joins the company, the DBA doesn't have to do a thing. The "Windows People" add people to the requested ADGs and "Security" is done. I love it and hate it because not everyone assigned to a particular ADG should actually have the privs that the ADG has but you don't even know because of the convenience of the feature.

    We used to manage this by limiting groups to job functions, so that when we granted permissions in SQL, we were fairly sure that people in that group needed those rights. However, it is a security management issue then.

    For example, if we hired a new salesperson, they might have a set of 4-5 AD groups that allowed them rights into different systems. If we hired a sales assistant, they would have different groups, maybe a subset, maybe a superset. A Sales Manager would have still different set of groups.

    It wasn't too bad, as we kept a list of groups for different jobs, of which we had hundreds.

  • database mail show in queue (then "unsent") and also not show in Database logs using SQL Server 2008.

    Thanks & Regards,

  • Steve Jones - SSC Editor (1/19/2016)


    Jeff Moden (1/15/2016)


    Heh... the title of this article should be titled "Security vs Convenience" because the current title is actually a bit of an oxymoron.

    What I mean by that is that Active Directory Groups make it wicked easy on the DBA. When a new person joins the company, the DBA doesn't have to do a thing. The "Windows People" add people to the requested ADGs and "Security" is done. I love it and hate it because not everyone assigned to a particular ADG should actually have the privs that the ADG has but you don't even know because of the convenience of the feature.

    We used to manage this by limiting groups to job functions, so that when we granted permissions in SQL, we were fairly sure that people in that group needed those rights. However, it is a security management issue then.

    For example, if we hired a new salesperson, they might have a set of 4-5 AD groups that allowed them rights into different systems. If we hired a sales assistant, they would have different groups, maybe a subset, maybe a superset. A Sales Manager would have still different set of groups.

    It wasn't too bad, as we kept a list of groups for different jobs, of which we had hundreds.

    Make no mistake about it, we have similar. The failing is that the management doesn't actually know squat about any of their groups and thinks it's OK (for example) for a "heavy hitter" sales assistant to belong to the sales-person group. There's also the idea that, regardless of position, a brand new person to a group shouldn't automatically be given the same (possibly elevated) privs as the rest of the group.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Steve Jones - SSC Editor (1/19/2016)


    ZZartin (1/15/2016)


    The default behavior is to use the database user with the same name you use to login to the server with, if anything I would change allowing DB users with different logins than server....

    In what context would you even want to have a different db user than login?

    In general, you don't. However you could create SQL Logins in a database with newer versions, so potentially you have collisions to resolve.

    Personally, I'd prefer it if there was no way to make his different.

    I think I would prefer that too. 🙂

    Tom

  • So much I don't know about security. Thanks for keeping my interest high.

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply