Security Convenience

Question

Security Convenience

Steve Jones - SSC Editor

SSC Guru

Points: 736162
More actions
January 14, 2016 at 8:01 pm

#306928

Comments posted to this topic are about the item Security Convenience

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

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

Gary Varga SSC Guru Points: 82166 More actions · Answer 1

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!!!

vliet SSCommitted Points: 1998 More actions · Answer 2

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 ...

tom.w.brannon Hall of Fame Points: 3976 More actions · Answer 3

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.

Eric M Russell SSC Guru Points: 125560 More actions · Answer 4

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

Eric M Russell SSC Guru Points: 125560 More actions · Answer 5

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

ZZartin SSC-Dedicated Points: 30920 More actions · Answer 6

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?

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 7

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)

Steve Jones - SSC Editor SSC Guru Points: 736162 More actions · Answer 8

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.

Steve Jones - SSC Editor SSC Guru Points: 736162 More actions · Answer 9

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.

Steve Jones - SSC Editor SSC Guru Points: 736162 More actions · Answer 10

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.

kailashIndia SSC Eights! Points: 935 More actions · Answer 11

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

Thanks & Regards,

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 12

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)

TomThomson SSC Guru Points: 104773 More actions · Answer 13

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

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 14

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