poor Question of the day 7_13_2003
-
Hi!
Frank, how do you mean?
Brian: No, I did not add my Test user to master. I let it rely on the Guest priveleges. Adding Guest to the db_denydatareader role, makes all attempts to login fail for all the Users that not explicitly exist in 'master'.
Trial: Adding the Testuser to master in the Public role and putting Guest in db_denydatareader => Login works.
Removing Testuser from 'master' => Login fails.
Removing Guest from db_denydatareader => Login works.
Ciao, Hans!
-
quote:
This is not completely correct. I'll have to dig in my notes, but really you only need access to two objects.
You have said a couple of time that you need access to TWO objects. I only know of ONE - the table master..spt_server_info. As far as I know, this is a "normal" table (not a trumped-up one like sysprocesses), but I could be wrong on that. But the queries "select @@version as version" and "select attribute_value as version from master..spt_server_info where attribute_name = 'DBMS_VER'" return identical results.
So I thought there is only ONE object you need access to. If somebody wrote up a test question <big grin> that said there were TWO, and asked me to name them, I would name the TWO objects that get locked: spt_server_info ... and the master database. But that brings us back to the obvious fact that the person accessing the information in spt_server_info must first have access to the master database, a fact which part of this community seems emotionally committed to denying. <second big grin>
But I think I'm wrong about this, because if I was right, you could just run DENY SELECT ON SPT_SERVER_INFO TO GUEST in the master database if you wanted to lock this down. But I tested that and it doesn't work. So what *is* the second object? Hmm, I may have to sleuth that out just for fun...
Picking up on some other comments from the past couple days:
Someone said that this is supposed to be FUN. Yes, I agree, and the debates are fun too. But it's more fun when the questions are reliable (I never said they should be certification-hardened, I just said I had that kind of experience). A question should give a reasonably accurate measure of who knows its answer and who does not. Its language should also be defensible in that there should be no "reasonable interpretation" that leads to an answer that would be scored incorrectly. These are minimalist standards for writing questions, not certification-hardened standards, and it has been demonstrated in several posts by several people that this question doesn't (didn't) live up to these minimalist standards. If this is supposed to be FUN, then it would be more fun if there was a chance that you could post a simple correction and have it acted on. But it's like the Olympics - sometimes the judges have more of an emotional purchase on the issues than an analytic one, and then these debates get going nicely. <yet another grin, as he jumps into a flame-retardant suit that he happily finds nearby>
Someone else said that the final word on this issue could only be delivered by the question's author. I actually believe that this is not true, even though it sounds self-evident. If this question *were* going to be used in a more formal context (like a certification exam), it would go through a technical review process, and I am pretty certain that this question would be salvaged by rewording the choices to make the options meet the minimalist standards above. In other words, the question would certainly be easy to fix, but it would certainly need to be fixed. In this environment, I suppose we are like the tech edit team, so you could argue that the final word rests with us. Now the original poster might have said that only the question author could enlighten us as to questions of intent, which seems obviously true to me (so sorry if I misread that - I didn't go back to check just now). But the question of "who owns the decision that the question is good enough" is an interesting one at any rate.
By the way, I just used my sandbox server to conduct an interesting test. I turned on 'allow updates' and ripped guest out of sysusers. That was interesting. I think maybe I should've just changed it's status, but that would've required research. Am I the only one who tries stuff like this? Fortunately, I was barely clever enough to back out the cataclysmic problems that I was stupid enough to cause ... and like I said, it was just a sandbox anyway (and I had fresh backups of master!)...
After patching things back up (getting the guest row back in master..sysusers), I went the other route and set status=0 for guest in master. That was much less interesting ... no access violations or anything. <biggest grin>
-
From the OpenHack 4 config and with confirmation of Richard Waymire at Microsoft.
quote:
I only know of ONE - the table master..spt_server_info
This isn't one. It's revoked from public.
quote:
But I think I'm wrong about this, because if I was right, you could just run DENY SELECT ON SPT_SERVER_INFO TO GUEST in the master database if you wanted to lock this down.
In keeping with what I said:
REVOKE ALL ON [dbo].[spt_server_info] to public
The permissions given back to public:
GRANT SELECT on spt_values to public
GRANT EXEC on sp_MSHasDBAccess to publicquote:
By the way, I just used my sandbox server to conduct an interesting test. I turned on 'allow updates' and ripped guest out of sysusers. That was interesting. I think maybe I should've just changed it's status, but that would've required research. Am I the only one who tries stuff like this? Fortunately, I was barely clever enough to back out the cataclysmic problems that I was stupid enough to cause ... and like I said, it was just a sandbox anyway (and I had fresh backups of master!)...
Yup. I spent over a year doing this stuff every waking hour when I was working on a SQL Server Security book with APress (now cancelled, unfortunately, due to market and time constraints). Guest is in every database and the 0 or 1 determines whether it's active.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
Edited by - bkelley on 07/16/2003 12:07:32 PM
K. Brian Kelley
@kbriankelley -
This isn't directed at Chris or anyone in particular. Need to state that up front. My intent isn't to attack anyone else here, but to point out something that I think is being forgotten.
<rant>
Keep in mind that Andy, Brian, and Steve have full time jobs of their own. SQLServerCentral.com represents a labor of love for them. While I agree with in principle that some of the questions could be better, I really think the fact that they've put effort into having a QotD should be applauded. I've seen several threads which say, "This is a poor question and it doesn't measure up," or "I could care less about how to turn on C2 auditing in SQL Server," which makes me come back and say:
QotD is free. It's intended for fun. Enjoy it for what it is there for. You didn't pay money to get the question. SQLServerCentral.com isn't a certification site, it's a SQL Server support site. Would you rather Andy, Brian, and Steve spend the bulk of the time they are devoting to SQLServerCentral.com improving the site or working on making questions absolutely perfect?
</rant>
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley -
quote:
The permissions given back to public:GRANT SELECT on spt_values to publicGRANT EXEC on sp_MSHasDBAccess to public
I saw that this was the OpenHack configuration. What I meant was what basic permissions (if any) give somebody the permission to run @@version? I don't know the answer to this. I had assumed that it was either not controllable through normal permissions, or that it was tied to permissions to select from spt_server_info. But if you deny select on that object, @@version still works. So I wasn't wondering what the OpenHack spec was (I had that already from somebody else's post) but what, if any, object permissions enabled @@version and thus could be specifically revoked or denied to shut it off. This is way off the track of the original QOTD - rather, I'm following up on the thought Brian offered way back on "page 1" of this thread that mentioned how this is a good reconnaissance tool for a hacker. I'm just wondering, "how do I shut this off, if I want to?"
So I think I might have mis-interpreted your original statement: I was thinking "what permissions in master enable the use of @@version" but if you were referring to OpenHack, then you were probably talking about permissions in master that must be guest (public) enabled.
As a sidebar - when you say
quote:
Guest is in every database and the 0 or 1 determines whether it's active
you mean the hasdbaccess flag, right? I was talking about the status flag, which I think is 0 or *2*. I think hasdbaccess is computed and can't be updated, but is controlled by the status column. At least that's what I hacked from sp_grantdbaccess.
-
quote:
<rant>Keep in mind that Andy, Brian, and Steve have full time jobs of their own. SQLServerCentral.com represents a labor of love for them. ... QotD is free. It's intended for fun. Enjoy it for what it is there for. You didn't pay money to get the question. SQLServerCentral.com isn't a certification site, it's a SQL Server support site. Would you rather Andy, Brian, and Steve spend the bulk of the time they are devoting to SQLServerCentral.com improving the site or working on making questions absolutely perfect?
</rant>
I've got to echo what you're saying here. I hope that none of the debate seems like anybody's unthankful for the effort put into the site. Brian says that the debate is part of what they want, and debate (the presidential ones excepted, of course) involves trying to persuade. That's all this is about, at least from my point of view. I don't know if the debating is fun in the volleyball-on-the-beach sense, but it definitely spurs thinking in the gotta-finish-the-crossword-puzzle sense. In this case, it's got me thinking about how I might be able to shut off some of the reconnaissance information without breaking apps. I might try replacing information in spt_server_info in my sandbox and see what that does. Anyway, from both Brian's comments and yours, it sounded like this is what you guys wanted. Sorry if I obliged too much.
FWIW, I think the QOTD is the most fun part of the site, in a way, because it's something you can take a stab at even on a day when you're really busy and you can't read anything else. But the whole site's great, as I've discussed with at least Steve in the past, and I apologize if we're sending any bad vibes about the QOTD. I've been thinking about volunteering to write questions, in fact, so that people could complain about *my* writing for a change. :o) But unfortunately, I'm pretty busy through the end of the year, or the fall at least, so we'll see.
As for "real jobs," I only have to work a half-day on Wednesdays. Can you tell? :o)
-
quote:
Yup. I spent over a year doing this stuff every waking hour when I was working on a SQL Server Security book with APress ...
Finishing off one rabbit trail:
I also started to work with APress a few times, by the way, but had to back out - once because a project coworker decided to pull out, once because my team got a better offer from ... well, a test vendor ... and once because I was just too busy to get started. Too bad, because they seemed really good to work with.
-
Hello Hans,
quote:
Frank, how do you mean?
what?
Thinking about my stock example afterwards, I think it was not the best. Sorry, if it has lead to confusion!
I guess all I want to say is, that for this question don't spent too much time thinking about what might has been meant! I just read the words, and guessed right!
Using the KISS aproach:
What do you need to do anything in SQL Server? Obviously a login!
Whether this login is valid or not, was not the qusetion, but WITHOUT having a login you're not even potentially in the situation to run a query
That was what I meant, and that was what my stomach told me.
Compare it to this site. Without registering you cannot post. OK? But if you post, answer, read or anything else is a totally different question.
BTW, without a harm to anyone. This discussion is certainly worthwhile, but the original question is now several days old. Shouldn't we concentrate on more constructive discussions?
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Hi Chris,
quote:
I've been thinking about volunteering to write questions, in fact, so that people could complain about *my* writing for a change. :o) But unfortunately, I'm pretty busy through the end of the year, or the fall at least, so we'll see.
as to speak with our sales team: We must communicate this!
I think it is a great idea to add the author uf the question to QOD. So we know right from start who's responsible and can bomb this poor one with offline mails
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
quote:
So we know right from start who's responsible and can bomb this poor one with offline mails
Frank, you are evil!
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley -
quote:
quote:
So we know right from start who's responsible and can bomb this poor one with offline mails
Frank, you are evil!
are you this poor one?
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Hi Frank!
Sorry if I'm disagreeing abit. This thing about not beeing able to do anything without a login is true. But if you have a login and no permissions (implicit or explicit) you can't do anything either...
So we are not talking about if it is needed or not, we are talking if it is sufficient or not. I.e Having a login is needed but is it enough? The answer have to be able to be deduced from the question, right? And also I would like to be able to explain (completly) my answer to someone with any kind of doubts.
Taking another strange approach with for example fruit salad. One kind of fruit is needed for a fruit salad, but it is not enough (according to most people). In this case I would assume that an answer to how to make a fruit salad would not be 'Take ONE kind of fruit'.
As for me, now it is time to go home 🙂 Have a nice evening!
-
Hi Hans,
quote:
Sorry if I'm disagreeing abit. This thing about not beeing able to do anything without a login is true. But if you have a login and no permissions (implicit or explicit) you can't do anything either...
yes, you are right. but in how many cases does a login have no permissions? Or is the first thing you do, cutting down the login in this way?
quote:
Taking another strange approach with for example fruit salad. One kind of fruit is needed for a fruit salad, but it is not enough (according to most people). In this case I would assume that an answer to how to make a fruit salad would not be 'Take ONE kind of fruit'.
don't let my wife hear that, but I stay away from fruit if I can
quote:
As for me, now it is time to go home 🙂 Have a nice evening!
I can't believe this!
Why is everybody going home at 5 pm ??? Have a drink on me!
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
quote:
are you this poor one?
No, I haven't written any of the questions. I just am putting myself in their shoes.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley -
Hello Frank!
Some extra time had to be spent in office 🙁
quote:
but in how many cases does a login have no permissions
Yepps. Good question. Sounds abit more theoretical then something that happends alot in real life. But then again, going back to one of Brians posts about a would-be attacker, it's important to know exactly where you might be hit and for beeing able to derive a good way of protecting yourself.
quote:
don't let my wife hear that
😉
quote:
Have a drink on me!
Yepp! Sure thing 🙂
Regards, Hans!
Viewing 15 posts - 31 through 44 (of 44 total)
You must be logged in to reply to this topic. Login to reply