poor Question of the day 7_13_2003
-
A very poor Question of the day 7_13_2003! {One must guess the intent of the author!}
------
What permissions do you need to run the SELECT @@VERSION query?
You just need a login into SQL Server.
You must have sysadmin rights.
You need to have permissions to any database on the server.
You need db_datareader rights to the master database.
You don’t even need a login into SQL Server.
-----
For example, if Integrated groups are granted DB access e.g.(Domain\Users or Domain\Guests), no specific login is required; hence the answer "You don’t even need a login into SQL Server." is as valid as "You just need a login into SQL Server."
-
Hi sql_dba,
quote:
A very poor Question of the day 7_13_2003! {One must guess the intent of the author!}For example, if Integrated groups are granted DB access e.g.(Domain\Users or Domain\Guests), no specific login is required; hence the answer "You don’t even need a login into SQL Server." is as valid as "You just need a login into SQL Server."
I have to disagree on this.
The question didn't mentioned whether a specific login is required.
If you log in via some group account you HAVE a login, not for the single user but still a valid login. It would lead security stuff ad absurdum if someone who hasn't a valid login is able to get hands on SQL Server objects.
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Hi.
Just out of curiosity, I would like a clarification of 'You need to have permissions to any database on the server.' Does that mean you need to have access to ALL DBs or you need access to AT LEAST one DB? If it is the later, I would say it is hard to execute SELECT @@VERSION if you do not have access to 'master' and an ability to login. Anyone heard that it is possible to login if you can't access your LoginProfile? In this case it is quite theoretical since you are not allowed to remove 'guest' from 'master' and 'tempdb'... But then, so is also the QOD...
-
This is a poor question not because of the question itself, but because of the distractors. While it is correct that you JUST need a login to SQL server, you are added to master because of the default value of @defdb in sp_addlogin. At least that's the way I was thinking at the time... Am I off-base? Are you actually implicitly granted permission to master through the public role? It would seem so to me, and I did test this with positive results.
Oh, also, I did test-taking error # 1 on this, anyhow: I had A selected, and then thought about it too much... -
I didn't write the question, but here's my view:
Frank is right in that you do have a login, but you make a good point if that login is a guest login for the domain. As far as SQL Server is concerned, it received a valid login. However, that valid login may not mean a thing within the domain because it is a guest account.
The question isn't theoretical because of the reconnaisance a would-be attacker can gain. If I know the version, I know what vulnerabilities the SQL Server potentially has. For instance, if I see an RTM or SP1 build, I'm thinking, "Fresh meat!" If I don't see the build indicating it's SP2 with the hotfix or SP3, I'm thinking, "Target for SQL Slammer." Keep in mind that by running @@VERSION I also get the OS and service pack for the OS, meaning I may discover that the system is vulnerable to an exploit that attacks the OS. SELECT @@VERSION available to public access is very bad indeed!
The question is a basic one and it's one that should be asked. I've talked with DBAs and developers who had no idea you could run SELECT @@VERSION if you had a login to the SQL Server. For that matter, you can typically run SELECT * FROM master.dbo.sysdatabases as well with nary an issue (public has access to it by default and should you remove public access you'll break things like ODBC configuration).
BTW, I read "You need to have permissions to any database on the server," as having permissions to at least one. Otherwise I would have thought it worded as, "You need to have permissions to all databases on the server." As far as why this is a valid choice, there may be people who don't realize guest is enabled for master and tempdb and this is a configuration you can't change.
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:
While it is correct that you JUST need a login to SQL server, you are added to master because of the default value of @defdb in sp_addlogin. At least that's the way I was thinking at the time... Am I off-base? Are you actually implicitly granted permission to master through the public role?No, the login is not added as a user to the master database upon account creation, even if the default database is set to master. The guest account is enabled for master and this is a what a login defaults to if it doesn't have an explicit user mapped to it within a database. Guest is required to be on for master, so it does mean anyone who has login rights does have access to whatever permissions the public role has access to in master. BTW, this is a lot.
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:
BTW, I read "You need to have permissions to any database on the server," as having permissions to at least one. ... As far as why this is a valid choice, there may be people who don't realize guest is enabled for master and tempdb and this is a configuration you can't change.I feel very strongly that this choice ("you need to have permissions to any (meaning at least one) database on the server") should be the correct one. It's not true to say that you can run SELECT @@VERSION without access to at least one database. Yes, the guest access in master and tempdb cannot be revoked, but this is where the theoretical nature of the question comes in. In *this* version of SQL Server, you cannot remove guest permissions from all databases. Therefore, every login has access to at least one database, and can therefore establish the shared database lock (or database context, if you prefer) required to execute Transact-SQL batches. Now, your "correct" answer says that a login is all that's required, and furthermore it indisputedly implies that database permissions are *not* required (since you say that's *not* the right answer). But it's ludicrous to say that the guest user does not have permissions to the master or tempdb database, since guest can certainly establish database context and execute Transact-SQL batches with a context of master or tempdb.
All this to say: throw this question out.
If the distractor would've said "You need to have specifically-granted permissions in at least one database on the server," then it would have been false, and your question would be OK. But as it is worded ("you need to have permissions to any (meaning at least one) database on the server") it is a valid answer. Although guest is a special user, it is a *real* user and should be treated as such. It conveys *real* database permissions to SQL logins, which is exactly why the question should be thrown out.
Just my 0.02. Feel free to disagree, but I think you need to come up with a pretty compelling reason to tell me that guest is not a mechanism by which database rights are conveyed to a login.
Edited by - chrisleonard on 07/14/2003 2:45:59 PM
-
RE: "I have to disagree on this.
The question didn't mentioned whether a specific login is required.
If you log in via some group account you HAVE a login, not for the single user but still a valid login. It would lead security stuff ad absurdum if someone who hasn't a valid login is able to get hands on SQL Server objects.
Cheers,
Frank"
Hi Frank,
As you correctly noted, "The question didn't mentioned whether a specific login is required."; actually, that is part of the problem with the question.
Surely you also noticed that neither the question (or any of the answer choices) mentioned that a VALID login is required, either?
{Again, one must guess at the intent of the author; that is what makes it a very poor question.}
Consider the following:
Exec sp_Denylogin @loginame = 'Domain\You'
"You" thereby certainly HAS "a" login, (one that happens not to have certain sysxlogins.xstatus bits set); however, 'Domain\You' cannot run the SELECT @@VERSION query.
-
quote:
Now, your "correct" answer says that a login is all that's required, and furthermore it indisputedly implies that database permissions are *not* required (since you say that's *not* the right answer).I think this is a bit of semantics and here's why:
- I can assign anyone a login within SQL Server.
- I don't have to assign any permissions specifically to that login.
- In normal conversation, we don't talk about what rights the guest user has in master and msdb and tempdb when we're talking about what rights a particular login has.
Therefore, this login that I'm using has no explicit rights assigned to it. Again, it's really all wrapped up in the semantics. But consider it from this perspective: if you have a junior DBA and you need to get across to him who has access to @@VERSION, how are you going to explain it? If he/she doesn't have a lot of experience, you're probably just going to say, "If someone has a login to SQL Server, that someone can run SELECT @@VERSION." If you have the time, you'll go into an explanation of the guest user account in master, but if you don't, you'll leave it as the first.
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:
- In normal conversation, we don't talk about what rights the guest user has in master and msdb and tempdb when we're talking about what rights a particular login has ... Therefore, this login that I'm using has no explicit rights assigned to it. Again, it's really all wrapped up in the semantics. But consider it from this perspective: if you have a junior DBA and you need to get across to him who has access to @@VERSION, how are you going to explain it? If he/she doesn't have a lot of experience, you're probably just going to say, "If someone has a login to SQL Server, that someone can run SELECT @@VERSION."But the point is that semantics work both ways. I have three questions:
1. What difference does it make if the rights are explicit? The user still requires them.
2. How do you know how I would explain this to my junior DBAs? The fact is that I'm a verbose trainer and author <g> and I am a big believer in teaching somebody "how to fish." I think there's actually a very good chance that, if my junior staff asked about this, I would give a brief but fairly complete answer. I do *not* like to give people blow-off answers like "oh, it just magically works for anybody who can login." Why act like it's entirely outside the normal security model when it's really not? I mean, the fact that you can't REVOKE or DENY is different than the "normal" model, but the fact that GUEST access enables this is totally standard security stuff, and it is something I want all my DBAs to know.
3. This one is most important: why do I either have to act like a "junior DBA" or be telepathic enough to guess the outcome of all these semantic points just to answer a simple question like this? The point is that the question does *not* reliably differentiate between a knowledgable DBA and a rookie, not because of the semantics, but because of wording that is not clear enough.
Not to sound cocky, but I have a substantial background both in psychometrics (which involves, among other things, making sure tests are measuring what they say they are measuring) and exam writing (I've written or helped write about a dozen certification exams). Your points about normal conversation might carry more weight with me if we were talking about normal conversation, but I think most people regard this like a certification question - and on certification exams, you have to read every word carefully. Problem is, the words on this question were not put together carefully enough. You may disagree, but I don't see why. This one's no good. Throw it out.
Edited by - chrisleonard on 07/14/2003 5:38:58 PM
-
quote:
What difference does it make if the rights are explicit? The user still requires them.Yes and no. We can go into the semantics about whether or not a user requires them or not. Really it's a point that there's no reason to argue about. Every login has access to master. Even if I take the appropriate steps and revoke public's access to everything but the two objects required in master, the login still has access to master.
quote:
The fact is that I'm a verbose trainer and author <g> and I am a big believer in teaching somebody "how to fish."I'm a big believer in how to fish as well, but I'm also very keenly aware that there are a lot of junior DBAs who need more foundational knowledge before an explanation beyond "SELECT @@VERSION works for every login" is going to be understandable. Having dealt with this issue recently in a few cases with a couple of different organizations, this unfortunately is reality. They first have to get past the difference between logins and users before they can understand the details of what the guest account means in master and tempdb.
quote:
3. This one is most important: why do I either have to act like a "junior DBA" or be telepathic enough to guess the outcome of all these semantic points just to answer a simple question like this? The point is that the question does *not* reliably differentiate between a knowledgable DBA and a rookie, not because of the semantics, but because of wording that is not clear enough.I think a lot is being read into what these questions are supposed to represent. We'd need to get a reading from Brian, Andy, or Steve, but from my perspective they weren't intended to be "certification hardened" questions so much as ones to spur on conversations like this one... thereby leading to a better understanding for anyone who jumps in and participates.
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 -
Hi sql_dba,
quote:
...Surely you also noticed that neither the question (or any of the answer choices) mentioned that a VALID login is required, either?
{Again, one must guess at the intent of the author; that is what makes it a very poor question.}
there are many cases in such tests where one must guess.
In fact, such questions are a test by themself, not for knowledge, but for how one reacts under stress. In these test questions I've made the experience the in most cases the first thought is also the best. I like keeping it simple and stupid. Especially for me, when I think too long about the wording. Anyway, is just a question, not the end of the world
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
I agree exactly what ChrisLeonard said and if I could, I would have said the same thing. Thanks for expressing my feelings in a discussion based way...
Regards, Hans!
Edited by - hanslindgren on 07/15/2003 05:54:30 AM
-
quote:
I think a lot is being read into what these questions are supposed to represent. We'd need to get a reading from Brian, Andy, or Steve, but from my perspective they weren't intended to be "certification hardened" questions so much as ones to spur on conversations like this one... thereby leading to a better understanding for anyone who jumps in and participates.Exactly, I'm actually quite enjoying the debate here. They probably would *help* with certification and actually some of them are taken from certification-type questions I've seen from years-past memory. The main purpose though is education and debate.
Brian Knight
http://www.sqlservercentral.com/columnists/bknight
Brian Knight
Free SQL Server Training Webinars -
CONSIDERING:
* "I think a lot is being read into what these questions are supposed to represent. "
[This is occurring because the question lacks sufficient details to be answered logically.]
* "I think this is a bit of semantics ..."
[As are many questions! The meaning of a question is often important, as it is to answer the 7_13_2003 question.]
* "3. This one is most important: why do I either have to act like a "junior DBA" or be telepathic enough to guess the outcome of all these semantic points just to answer a simple question like this? The point is that the question does *not* reliably differentiate between a knowledgable DBA and a rookie, not because of the semantics, but because of wording that is not clear enough. "
[A true enough sentiment. The logical presentation of the question is flawed, and insufficient context is provided to reasonably infer the author's intended meaning.]
* there are many cases in such tests where one must guess.
[A true enough statement. When the logical presentation of a question is flawed, and insufficient context is provided to reasonably infer the author's intended meaning, one may be left to guess the intent.]
* In fact, such questions are a test by themself, not for knowledge, but for how one reacts under stress. In these test questions I've made the experience the in most cases the first thought is also the best. I like keeping it simple and stupid. Especially for me, when I think too long about the wording. Anyway, is just a question, not the end of the world
[To a point, A true enough sentiment, likely a useful approach, and a good attitude. 😉 However, would you prefer tests in which all questions are ambigous in a similar manner? - their presence rather muddles and serves to invalidate what is being measured.]
* If a login is invalid for connections (but has not been revoked), it is still a login, however establishing a connection to run the SELECT @@VERSION query would not be possible; in other words in practice, the login would also need to be valid.
As these and several other points amply demonstrate, it IS necessary to guess the intent of the author of the 7_13_2003 question. This is so precisely because the meaning of the question is not logically apparent from its composition, and insufficient context is provided to reasonably infer a meaning from:
> What permissions do you need to run the SELECT @@VERSION query?
> 1 You just need a login into SQL Server. <--{too obscure to decipher}
> 2 You must have sysadmin rights.
> 3 You need to have permissions to any database on the server. <--{too obscure to decipher}
> 4 You need db_datareader rights to the master database.
> 5 You don’t even need a login into SQL Server. <--{too obscure to decipher}
For an unrelated illustrative example: What does the expression "Today be bad" mean?
1 One is being commanded to misbehave today.
2 Today is Monday.
3 Today is a very negative day because someone dear has died.
4 Today is a very good day.
5 Today is not yesterday, or tomorrow.
Note it IS similarly necessary to guess the intent of the author of the question. This is so precisely because the meaning of the question is not logically apparent from its composition, and insufficient context is provided to reasonably infer a meaning.
To better see this compare it to an (imperfect) interpetation of the (Question of the day 7_13_2003):
Which of the following is true in regard to running: SELECT @@VERSION ?
1 SELECT @@VERSION may be run from any successfully initiated connection. <-- {Assumed intent - original too obscure to decipher}
2 SELECT @@VERSION may only be run from a login connection initiated by a login with sysadmin (System Administrator) server role membership.
3 <--{too obscure to decipher}
4 SELECT @@VERSION may only be run from a connection with a database security account assigned db_datareader rights in the master database.
5 SELECT @@VERSION may be run without a current connection into SQL Server. <-- {Assumed intent - original is too obscure to decipher}
Of course this alternative version of the Question of the day 7_13_2003 may not reflect the intent of the original question's author either, (but that question's inscrutability is the point, after all - QED). {Please note that NO offence is intended towards the question's author, or anyone else for that matter.} As noted, semantics, and general semantics, in particular, is in some ways germane to problems with the question. see http://www.general-semantics.org/
Oh, also, I did test-taking error # 1 on this, anyhow: I had A selected, and then thought about it too much...
Viewing 15 posts - 1 through 15 (of 44 total)
You must be logged in to reply to this topic. Login to reply