locked
Error 262 SHOWPLAN permission denied in database when attempting to execute SP RRS feed

  • Question

  • I have this user call it odduser.  For some reason it was assigned execute rights to a stored procedure in the database which calls five other stored procedures for which it has not been assigned execute rights.  Each of these other stored procedures as well as the fist one makes selects against tables in the databases.  odduser has no other explicit rights and is not a member of any fixed database role.  Pursuant to DoD regs, the public role has had its access to everything revoked in all databases.  In addition, the guest user has been disable in all databases.

    When signed on as this user I try to execute the one stored procedure to which it has rights it throws Error 262 "SHOWPLAN permission denied in database 'DBName'"  at line 47.  Why is it not failing much earlier due to lack of Select permissions on the tables or lack of execute rights on the other stored procedures whihc are called well before line 47?

    I must admit I am troubled because given that it was set up this way it makes me wonder if it was actually working this way.  Has all of our database hardening been in vain?

    The only thing I can thing of is that when it attempts the run the stored procedure it is attempting to optimize it and failing at this point?  I would appreciate any thoughts any one may have about this.

     

    Update 12/15/2011: 

    Erland has been very helpful concerning ownership chaining and explaining why a user with only execute rights on a stored procedure can have the sp do all the selects and executes needed on objects in the schema to which he himself has no rights.

    I would like to hear from some more folks on this show plan permissions issue.   I need to find out though why this set up would cause a showPlan permissions error in SSMS when calling this sp from a query window connected as such a user, as this is how we've been testing that all permissions needed by the user have been granted.


    Edward R. Joell MCSD MCDBA

    • Edited by joeller Thursday, December 15, 2011 3:22 PM Update
    Monday, December 5, 2011 7:58 PM

All replies

  • Are all procedures and tables owned by the same user? In that case ownership chaining sets in. Meaning that for everything the procedure ownes, permissions checks are suppressed, so the user can execute all the other five tables, and also selects the table.

    Why the SHOWPLAN error is flagged on line 47, I cannot say on the top of my head - unless there really is a SET SHOWPLAN ON command on that line!

    Did you enable Show Actual Execution Plan in SSMS before running the query? Did you get to see any execution plans at all?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, December 5, 2011 9:40 PM
  • I gave a user execute rights on a stored procedure which did a explicit select from a table in that database.  The user was added to the database but was not put into any role, and not given any explicit rights so should not have had select rights to that table.  Yet using that user I was able to open a datareader in a web app and populate a grid.

    That does not seem right.  Am I missing something here? 


    Edward R. Joell MCSD MCDBA
    Monday, December 5, 2011 9:47 PM
  • Neither user owns anything.  Everything is owned by dbo.  The sp does not carry out an explicit ShowPlan command.  I did not enable Show Actual Execution Plan .  I was not looking for an execution plan.

    We ran "Revoke Select on tblssoAndSo from Public" for each table in the database.  Is it possible that this does not disable select for "Public"?

     


    Edward R. Joell MCSD MCDBA

    • Edited by joeller Monday, December 5, 2011 9:53 PM
    Monday, December 5, 2011 9:51 PM
  • So if everthing is owned by dbo, the user should be able to execute the procedure, the subprocedures it calls and perform all SELECT, INSERT, UPDATE and DELETE statements in the procedure. That is how ownership chaining works. (Note that it does not extend to dynamic SQL.)

    The SHOWPLAN error on the other hand is mysterious. The error would only occur if there really is a request do display execution plans somewhere.

    Without seeing the code, I cannot really give any further suggestions. (Not sure if I can even if I see the code!)

    The user was added to the database but was not put into any role, and not given any explicit rights so should not have had select rights to that table.  Yet using that user I was able to open a datareader in a web app and populate a grid.

    What the the DataReader execute? The stored procedure? Or a plain SELECT against the table?

    Also, keep in mind, that by default a newly created user in a database, does not have access to any table or stored procedure.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, December 5, 2011 10:38 PM
  • But the user is it its own schema not dbo.  Are you saying all dbo stored procedures to which a user in its own schema has execute rights can  perform all crud actions on tables, and execute all other dbo stored procedures?   Then what is the point of any kind of security for the user?
    Edward R. Joell MCSD MCDBA
    Tuesday, December 6, 2011 1:03 AM
  • Users are not in a schema. A user can have a default schema which is different than dbo, but that has nothing to with security at all. A schema is a namespace/container, nothing more. (OK, this is not 100% true, but that is not relevant here.)

    What matters here is the ownership of the objects. As long as dbo owns all procedures and all tables, permission to a stored procedures means that you have permission to all references objects. If you would change the ownership to this procedure some other user, you would break the ownership chain, and the user's own permission (or lack of) would apply to those tables.

    I have an article on my web site that covers ownership chaining as introduction to other means to grant permissions through stored procedures:
    http://www.sommarskog.se/grantperm.html
    You can find more information there that I have not covered in these posts.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, December 6, 2011 10:04 AM
  • Users are not in a schema. A user can have a default schema which is different than dbo, but that has nothing to with security at all. A schema is a namespace/container, nothing more. (OK, this is not 100% true, but that is not relevant here.)

    What matters here is the ownership of the objects. As long as dbo owns all procedures and all tables, permission to a stored procedures means that you have permission to all references objects. If you would change the ownership to this procedure some other user, you would break the ownership chain, and the user's own permission (or lack of) would apply to those tables.

    I have an article on my web site that covers ownership chaining as introduction to other means to grant permissions through stored procedures:
    http://www.sommarskog.se/grantperm.html
    You can find more information there that I have not covered in these posts.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, December 6, 2011 10:04 AM
  • I am not sure I understand.  A user has a default schema but they are not members of that schema?  Yet a user has ownership rights over everything in that default schema, but has to be granted specific rights to anything in any other schema.  I am not clear on how that makes them not members of the default schema.

    Meanwhile it sound as if not only all of our security structures need to be re-evaluated but the Navy's as well.


    Edward R. Joell MCSD MCDBA
    Tuesday, December 6, 2011 2:07 PM
  • Attempts to open your link return "Internet Explorer can not display page" error


    Edward R. Joell MCSD MCDBA
    Tuesday, December 6, 2011 4:11 PM
  • I am not sure I understand.  A user has a default schema but they are not members of that schema?  Yet a user has ownership rights over everything in that default schema, but has to be granted specific rights to anything in any other schema.  I am not clear on how that makes them not members of the default schema.

    To be perfectly honest, you seem to have a whole number of misconceptions schemas and users work that are wrong. That's why you have so difficult to grasp what I'm saying.

    SQL Server includes a whole bunch of objects: databases, credentials, certificates, tables, database principals, constraints, database triggers and whole bunch more.

    Some of these objects are server-level objects others are database-level objects. Some of the database objects are schema-bound, for instances tables, views, stored procedures to name a few. Other classes of objects are not schema-bound. Examples of the latter are assemblies, DDL triggers and database principals a k a user.

    That is, a user is not member of a schema. But a user has a default schema. The default schema serves to set up a search path if the user says:

    SELECT * FROM tbl

    Say that the user fred has the default schema sch1. SQL Server will first look for sch1.tbl and if this table does not exist, SQL Server will try dbo.tbl.

    A user may own a schema, but that does not make the schema his default schema. Likewise, the user may not own his default schema. (Many users has dbo as the default schema.)

    Furthermore, just because a user owns a schema, does not mean that he owns all objects in that schema. The default however, is that if you create an object in a schema, the schema owner will be the object owner.

    Meanwhile it sound as if not only all of our security structures need to be re-evaluated but the Navy's as well.

    If you have set up security structures from gross misconceptions, it is not unlikely that you need to make a thorough review, yes.

    It does still not explain the SHOWPLAN error, though.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, December 6, 2011 11:08 PM
  • Attempts to open your link return "Internet Explorer can not display page" error

    I tested it from my web browser, and it works. (Some times the web UI messes up the URLs.) But it sounds like you are in some military business. Maybe there is some evil firewall blocking good content?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by amber zhang Monday, December 12, 2011 7:58 AM
    • Unproposed as answer by joeller Monday, December 12, 2011 1:48 PM
    Tuesday, December 6, 2011 11:13 PM
  • Possible.  Though unlikely as the DoD has no reason to block any site unless it has been shown to be from a subnet that has been suspected of being the source of attacks.

    I plan to try it from home but this is the Xmas seasons and I have far too many responsibilities at home to spend any time on the computer.  After the holidays I will have time.  Meanwhile everything is on hold. 

    I would like to hear other responses as to why the failure for lack of Show Plan rights in the SSMS query window but  no failure when called from an ASP.Net web app.


    Edward R. Joell MCSD MCDBA
    Monday, December 12, 2011 1:54 PM
  • I would like to hear other responses as to why the failure for lack of Show Plan rights in the SSMS query window but  no failure when called from an ASP.Net web app.

    Well, you typically do not enable any SHOWPLAN option from ASP .Net, but it is not uncommon to do from SSMS.

    Sorry, I don't have any information about your case, to say anything more intelligent.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, December 12, 2011 10:46 PM
  • But I don't enable Show Plan period.  Neither from SSMS or ASP.Net.  So why the error one place and not the other?  Is it perhaps one of those SQL Server errors messages which have nothing to do with the actual error ,which results in sending you on a Wild Goose Chase through BOL and on-line Forums to find out what is wrong only to find out the problem was something simple that had nothing to do with the error message?

     

    I still want to read you page and try to get a clearer understanding of the relationship between users and schemas.   I have still not been able to get to your link from work.  With the holidays upon us I have not been able to try from Home.

    Because I first ran across schemas using Oracle, I assumed that SS2005 + schemas were like them.  From the explanation you provided as well as the stuff I've been able to find out that is not exactly the case.  But the links to which I've been able to get to from here have not provided any clarity.  

    If you have any other links which you think may provide a clear explanation of how schemas work in SS, and to which you think I may be able to get from here can you recommend them?  Otherwise I will try to get time to check out your blog at home.  I hope to do so after Xmas.

     


    Edward R. Joell MCSD MCDBA
    • Edited by joeller Wednesday, December 14, 2011 2:39 PM spelling
    Wednesday, December 14, 2011 2:36 PM
  • But I don't enable Show Plan period.  Neither from SSMS or ASP.Net.  So why the error one place and not the other? 

    I have no idea. I don't see the code or your environment.

    I still want to read you page and try to get a clearer understanding of the relationship between users and schemas.   I have still not been able to get to your link from work.  With the holidays upon us I have not been able to try from Home.

    You might try:
    http://gnudb.blogspot.com/2008/04/excerpt-giving-permissions-through.html
    It appears that the article is replicated there. (An older version, but for the schema/user thing that should not matter.)

    Because I first ran across schemas using Oracle, I assumed that SS2005 + schemas were like them. 

    I don't know about users and schemas on Oracle. But there is an important difference between SQL 2000 where owner and schema was always the same. Not so on SQL 2005.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, December 14, 2011 11:21 PM
  • The Set-up of SQL Server 2005 is standard setup out of the box.  It is possible that SSMS attempts to carry out a show-plan when run from SSSMS whether you specifically request it or not? 

    The only unusual thing about the stored procedure is that it calls several other stored procedures to populate a table variable, and the user calling it has no execute rights on those stored procedures or any other rights in the database beyond those given implicitly by being added as a user.  The ASP.Net code is basic SQLclient connection, command of stored Proceduretype with parameters being instanciated and set and SqlClient.DataReader dr = cmd.ExecuteReader(); 

    I was able to get to the new site.  So I plan to read over your article.  I will continue to research the issue of schemas and users and ownership chaining to discover the implications it has for our security set up.  We already knew that assigning a user the default schema of dbo would cause issues due to ownership chaining.  But it never occured to me that the stored procedure would acquire those rights.  It had been my experience that the stored procedure acquired the permission of the user calling it.  I wonder if that was just a memory from my 7.0 experience which had never had an opportunity to be refuted because I always made sure the user had the rights needed to do what was needed?  So I presume that it means that the stored procedures in the dbo schema can carry out all CRUD operations on the tables in the same schema. 

    Regarding schemas in SS2K, it has been 5 years since I used SS2K and then only for a few months.  (The agency I support was very slow to move to 2K.  They did not install it until July 2006, by which time I had moved to other taskings.)  I really don't recall ever hearing any mention schemas in it.  However, I read an article after all this started, when I couldn't get to your link, that spoke of how schemas in SS2K were implemented behind the scenes and were utilized like the equivalent of user ownership of the object like in 7.0.  

    I first ran across schemas in Oracle in 2007 before I had any experience with SS2005.  That was how I learned that they were containers which owned objects.  The documentation seemed to be stating that the user who schema was named after, owned the objects in that schema.  (But you know how obtuse and convoluted Oracle documentation is.  It may in fact be constructed the same as it SQL Server.)  When I saw this added to SS2K5 I jumped to the conclusion that schemas were the same as I understood them to be in Oracle, and never researched any further.  I fell into the trap of Oracle arrogance.  ;-)

    I would like others to comment on the Show Plan permissions thing, so I am reluctant to show this thread as answered and give you the points.  (They say that no one ever looks at threads marked as answered.)  If I don't hear from anyone else in a couple of months I will show it as  answered.  

    So all you moderators do not mark this thread as answered.  (Bolded and highlighted to catch attention, not to signify yelling.)


    Edward R. Joell MCSD MCDBA
    Thursday, December 15, 2011 3:15 PM
  • However, I read an article after all this started, when I couldn't get to your link, that spoke of how schemas in SS2K were implemented behind the scenes and were utilized like the equivalent of user ownership of the object like in 7.0.  

    There is no difference between how schema/owners work in SQL 2000 and SQL 7. The break came with SQL 2005.

    I would like others to comment on the Show Plan permissions thing, so I am reluctant to show this thread as answered and give you the points.  (They say that no one ever looks at threads marked as answered.)  If I don't hear from anyone else in a couple of months I will show it as  answered.  

    Well, there is no argument on that question is unanswered.

    When happens if you run the procedure if you connect from SQLCMD on the command line?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, December 15, 2011 10:21 PM
  • When happens if you run the procedure if you connect from SQLCMD on the command line?

    Good question.  I never use the command line, (having had my fill of it in Oracle), so it never occured to me to try.  I will have to do that.
    Edward R. Joell MCSD MCDBA
    • Edited by joeller Friday, December 16, 2011 10:00 PM addendum
    Friday, December 16, 2011 9:59 PM