MS SQL Permissions
-
My point was that you have to know what you're doing and really be trying to get things into that kind of state. So maybe I am arguing against @blakeyrat (business as usual).
-
To get into the state they've managed to get it
The point, which you've missed about 17 fucking times, is that it shouldn't be possible to get into this state. That it is is a bug.
-
It's not a bug. It's not understanding how features are supposed to work together. This is 100% a your company issue.
-
Goddamned I hope you don't write software for a living.
-
I do, but my market isn't for bad DBAs.
-
As much as I hate to say it, I think @blakeyrat is right here. I'm a DBA, and if you told me I had to set things up so developers could alter or replace views without being able to see them or the definitions, I'd have to google it.
I agree with @antiquarian and @blakeyrat on this one. I just can't see how having the rights to overwrite something you cannot see makes sense.
I can accept permissions that allow a user to see and modify the view definition but not execute it. But I can't think of a good reason to allow the behaviour Blakey described. I also didn't see anything in this thread to make me think of it as a valid feature rather than a bug.
-
I also can't think of anything that would justify allowing the configuration as Blakey's described. This does however leave open the remote possibility that there is something I can't think of. I know, rite?
But it's worse than that. Apparently you can see the view if you look in the right place. "Oh that view? Yeah, never heard of it OR I'm not allowed to tell you about it. But if you look over there in the schema you might find it anyways."
Reminds me of EVERY WEBSITE EVER where I go to recover my password and it says "Oh that account? Yeah maybe it exists, and if it does, we'll send you an email. But if you go to the registration page we'll just fucking tell you as soon as you enter something in the name field."
-
No worse than discourse, it lets you type in all the information, it tells you it's all good, and now it flags duplicate email addresses after you submit the form.
-
TRWTF is whoever is administering that server.
Pretty much. This doesn't have any real advantages over just granting
db_owner, unless stuff likeCREATE TABLEandALTER TABLEare denied. Either you trust your developers and give themdb_ownerorsysadminon their development databases/servers, or you lock it down and have the DBAs do all the DDL. This middle ground is silly.There is legitimate use cases related to security and keeping people out of which they know nothing about.
My personal favorite is
DENY VIEW ANY DATABASEon a multi-tenant server.MS SQL Management Studio shows Create Date in the Object Explorer Details, but not last modify.
If it's really needed, the last modify is in
sys.objects. If permissions are tightly controlled, it makes sense to forbiddrop + createbecause all the permissions on the object disappear withdrop.Today I learned that not only do I have access to prod as a non-developer (I didn't know the right server name to connect to, when I was told the correct one I could access it directly), but also I can query the PII tables in prod.
But having the same credentials everywhere makes rollouts so easy!
I've heard horror stories of secretaries using
sato pull Excel spreadsheets.
-
The... wha... how... the FUCK!? I don't have permissions to run the view, or even to see the view in Object Explorer. But I can overwrite it!? That doesn't make sense on about 87 different levels.
On one core processing system I used to administer for a bank, you could set a user up to maintain (write to) account information but not view them. Thought it was a WTF until they explained to me a use for that: tellers depositing money into a fellow employee's account - can't see the employee info, but can write to it to process transactions.
Still, kind of disconcerting...what if they put the decimal point in the wrong spot? (receipts...ok...)
-
what @blakeyrat was saying
is what blakeyrat always says, which is that the only use cases that could possibly exist are the ones he can think of.
-
But I can't think of a good reason to allow the behaviour Blakey described. I also didn't see anything in this thread to make me think of it as a valid feature rather than a bug.
One reason could simply be MS executing a drop all tables command due to license expiration?
It's a stretch, I know. I don't like it regardless.
-
This is like blaming car companies for being able to fly in reverse at WOT for more than a minute. You'll never need to do it, but to criticize it is stupid.
-
@locallunatic said:
what blakeyrat was saying
is what blakeyrat always says, which is that the only use cases that could possibly exist are the ones he can think of.
Well, no-one else has managed to come up with a good use case. @redwizard had a go and even he admits a stretch.
I'll concede that this behaviour is less bug and more feature if someone can demonstrate it as such.
-
The... wha... how... the FUCK!? I don't have permissions to run the view, or even to see the view in Object Explorer. But I can overwrite it!? That doesn't make sense on about 87 different levels.
The list and create permissions are separate, so I'm pretty sure it's possible in pretty much any database to be able to overwrite an item you couldn't see in the first place.
What it usually means when this happens is that your DBA is a moron.
-
I see pretty much everyone else already said exactly what I did.
But I'll add, it's partly for dbas that are ridiculously anal and create a different user for every single possible promotion task... so then you have a user with create/delete object permissions but can't list, select, update, or insert. In that case, it's not about whether you can lose the data, it's about whether that user can see it.And, in your case, maybe that table is supposed to be hidden from you to prevent you from seeing sensitive data, just like those fancy-shit oracle columns you so vehemently defended. That, or your DBA is an idiot.
-
Here's the $64 million question:
Can this happen in MySQL, Oracle, et al?
-
Here's the $64 million question:
Can this happen in MySQL, Oracle, et al?
*shudder* MSSQL could have a bug that killed my entire family and I'd still probably use it over Oracle or MySQL. Well, MySQL is okay if you don't care so much about your data.
-
I can see the convoluted logic of write permissions for an object you can't see: it prevents discovery through error ("Oh, an object with this name already exists, because I can't create it; I just can't see it").
Whether this approach to security is sensible is a different story altogether...
-
All of the suggestions people have made make a good case for not being able to SELECT from tables or views while also INSERTing to them, but not for not being able to see that they exist at all. That's just silly.
In conclusion, I agree with @blakeyrat
-
MS SQL uses the revolutionary WORN technology (Write Once, Read Never). It's one of the most secure paradigms in modern computing, since data is never read there is never any security issue.
-
configurations that make no fucking sense should be impossible
But this configuration does make sense. I have a friend who worked at a bank with this very set up.
It allows a person to have permission to update view definition without having access to the data. The WTF is that you were allowed to then give yourself permission to the data. You shouldn't have been allowed to do that.
Admittedly this is a bit of a niche situation, but it was valid for their company.
-
I gotta agree with Blakey somewhat here. A lot of the edge cases mentioned make at least some level of sense, but while having DROP or ALTER without SELECT seems to have some perfectly reasonable uses, having DROP or ALTER without VIEW DEFINITION seems pretty darn irrational. And that was what I thought he was saying wasn't sane.
-
It allows a person to have permission to update view definition without having access to the data. The WTF is that you were allowed to then give yourself permission to the data. You shouldn't have been allowed to do that.
And how did they stop the developer from creating a "new" view that wasn't in fact new but overlapped with an older one by the same name and breaking everything?
People are ignoring the extremely dangerous accidental data loss problem here.
-
People are ignoring the extremely dangerous accidental data loss problem here.
Bullshit. Who is ignoring that?
-
And how did they stop the developer from creating a "new" view that wasn't in fact new but overlapped with an older one by the same name and breaking everything?
They wouldn't be allowed to create a new view with the same name because that view already exists. It's simply that they don't have
select,insert,update, etc permissions for it, they only havealterpermissions.The fact that they can't see the view in object explorer doesn't mean that the view doesn't exist, and it doesn't mean it will behave as if the view doesn't exist. It only means that they don't have permissions to see it.
-
I'm with @blakeyrat here. Not being able to see an object exists, yet still being able to clobber it by accident, is a WTF that should never be allowed to exist. It's like blaming a switchman because he let a cut of cars go in the yard and it struck a guy wearing some sort of magic invisibility cloak.
/me hugs the blakeyrat, commiserates with him re: DBAs.
-
having DROP or ALTER without VIEW DEFINITION seems pretty darn irrational
I won't say anything to the rationality of such an approach, but in some cases it may be mandated that implementation details (such as definitions) are not disclosed to those who don't, for want of a better term, have a need to know. In such a case, a junior employee may be entrusted with making minor changes, without the opportunity to learn anything more than absolutely necessary about what they're working on.
I work in a sector subject to all sorts of confidentiality requirements mandated by law, so I can see where such ideas may arise. That said, I believe this is most likely the result of a flexible permission framework being ill-used.
Filed under: with great power comes great responsibility
-
They wouldn't be allowed to create a new view with the same name because that view already exists. It's simply that they don't have select, insert, update, etc permissions for it, they only have alter permissions.
We've already been over this you fuck.
Scroll up, I'm not retyping it.
-
Not being able to see an object exists, yet still being able to clobber it by accident, is a WTF that should never be allowed to exist.
Yes, seems like Object Explorer is probably doing is wrong.
-
We've already been over this you fuck.
Scroll up, I'm not retyping it.
You don't need access to the view data to be able to do this. You just need to check the
sys.objectstable. That's a pretty standard way of doing things.
-
You don't need access to the view data to be able to do this. You just need to check the sys.objects table. That's a pretty standard way of doing things.
He wants the MSSQL developers to figure out blakeyrat's list of pathological permission settings so that people can then complain about why the system is fucked up and won't let them do what they want to do.
-
And how did they stop the developer from creating a "new" view that wasn't in fact new but overlapped with an older one by the same name and breaking everything?
The fact that CREATE and ALTER are 2 different commands and you'd have to be completely retarded to "accidentally" ALTER a view when trying to CREATE a new one?
-
BTW, this "bug" could have come about in a much more innocent way. If the owner of the view is different from the owner of the functions it calls, then there are two levels of permission checks when SELECTing; it checks if you have SELECT on the view and checks if you have EXEC on the functions. In this condition, you would be able to drop and re-create the view because you have full access to it.
This behavior is necessary, otherwise you could create a view and bypass permissions.
-
The fact that CREATE and ALTER are 2 different commands and you'd have to be completely retarded to "accidentally" ALTER a view when trying to CREATE a new one?
Already discussed. Scroll up. Not gonna repeat it. Fuck off and die.
-
Except the bug seems to be that Object Explorer (whatever the fuck that shit is) isn't showing that the view exists at all.
-
Is that the first time FOAD has come out on this Discourse install? I'd search but that wouldn't tell me anything useful.
-
Already discussed. Scroll up. Not gonna repeat it. Fuck off and die.
Not going to repeat how you aren't capable of realizing a view existed when CREATE tells you it can't because the view already exists?
-
The standard way of writing a new View or Sproc in SQL is to preface it with a "hey if this already exists, then delete it buddy, 'kay?"
This has never been the standard way here, because, you know... that would cause data loss if it existed already and you weren't trying to replace but add.
-
So Boomzilla is ok now, and Darkmatter is the new Boomzilla? WTF is going on. It's all topsy turvy.
-
I just don't get it. There's an ALTER VIEW command. Why would you do DELETE+CREATE instead of ALTER for ALTERS, and just CREATE for CREATE. You deserve to have your data dropped for that.
-
This has never been the standard way here, because, you know... that would cause data loss if it existed already and you weren't trying to replace but add.
That's why he said "View or Sproc". Those things don't contain data. Notice the lack of "Table" in his list.
-
ALTER isn't safe without a check that it exists.
A common workflow is:
- Developer creates an SP.
- Developer modifies said SP due to bug discovered during testing.
- Developer sends version of script from step #2 off for QA and deployment.
If step #2 has an ALTER instead of a DROP/CREATE, it wouldn't work. I know not all cases are like this, but there is almost no downside to doing it, so many shops enforce it as a standard.
-
So what's your stance on global temp tables? When was the last time you checked before auto dopping ##temp?
-
Those things don't contain data. Notice the lack of "Table" in his list.
Okay, DDL loss? The view itself is "data" in a sense. I was not referring to the data inside the actual table in that case.
-
If step #2 has an ALTER instead of a DROP/CREATE, it wouldn't work. I know not all cases are like this, but there is almost no downside to doing it, so many shops enforce it as a standard.
Why would you send the ALTER command to QA instead of FIXING THE ORIGINAL SCRIPT AND SENDING THAT? Seriously.
-
Yay, someone else is repeating my arguments so I don't have to. THAT IS NOW YOUR JOB JAIME. Who I hope is Jaime Farr.
-
Also, in this case, you clearly know the SP/View exists, because YOU CREATED IT.
We're talking about the initial CREATE command, using DELETE + CREATE to create something when you don''t know whether it exists already or not is just retarded.
-
So Boomzilla is ok now, and Darkmatter is the new Boomzilla?
No, I still think you're wrong. I understood what you meant from the beginning.
-
Right but the reason I used to get so pissed at you is you would ask 28,000 clarifying questions about stuff we'd already fucking talked about, and turn the thread into a swirling tornado of going nowhere at all. I don't give a shit whether you agree or disagree with me, it was the pointless, constant, boring back-and-forth I hated you for.
Now you're not doing that, but Darkmatter is.
