locked
Execute AS Owner - SECURITY LOOPHOLE? RRS feed

  • Question

  • Quick Steps to produce security loophole. (i'm using SQL Server 2005) Using SSMS,

    1. create a new database.

    2. set the new database to trustworthy...

    ALTER DATABASE [TestDB] SET TRUSTWORTHY ON
    

    3. create a new login/user that is mapped to ONLY that database, and make him db_owner.  

    4. login via the new login created in step 2, and while using that login...

    5. execute the following stmt...

    select * from msdb.dbo.sysjobs
    

    you should get the following error, and rightfully so, because the login does NOT have permission to access the msdb database...

    Msg 229, Level 14, State 5, Line 1
    SELECT permission denied on object 'sysjobs', database 'msdb', schema 'dbo'.
    

    6. now, using the same login context, execute the stmt below...

    create proc dbo.loophole
    with execute as owner
    as
    select * from msdb.dbo.sysjobs
    go
    
    exec dbo.loophole

    As you can see, the new-login can now access/modify/execute ANYTHING on the entire SQL Server simply by being db_owner for just one database and specifying EXECUTE AS OWNER when creating sprocs!!! 

    I have to be missing something here because this does not make any sense. If a db_owner creates a proc using EXECUTE AS OWNER under the schema [dbo] which is owned by user, [dbo], who is mapped to the [sa] login, it does not make sense for the user to be able to do anything beyond his original permissions -- going from just db-owner to becoming sa for the entire SQL Server.

    Is this behavior a bug?  How can you assign DB_OWNER to a user for one DB WHILE making sure that's the ONLY database he can access?

    Thanks for listening. I look forward to hearing from you!

    Friday, January 21, 2011 12:02 PM

Answers

  • Ok, as you can see from Erland Sommarskog's article, there are reasons to avoid EXECUTE AS OWNER.  Since you are looking into these issues, you probably should read the whole article.  But for your issue, see:

    http://www.sommarskog.se/grantperm.html#execasowner

    It sounds as if you have cross-database ownership chaining turned on?  (And, out of curiousity, are all databases owned by the 'sa'?)  If you are using cross-database ownership chaining, please read the following two documents:

    http://msdn.microsoft.com/en-us/library/ms188694.aspx
    http://msdn.microsoft.com/en-us/library/ms188676.aspx

    Cross-database ownership chains should be as few as possible, just so as to avoid the exposures you have found.

    In SQL Server 2000, when we realized how easy it was to break into another database, we changed database owners so that each database was owned by a different login.  That broke the 2000 cross-database ownership chaining. 

    RLF

    • Marked as answer by Idev Friday, January 21, 2011 8:18 PM
    • Unmarked as answer by Idev Friday, January 21, 2011 8:18 PM
    • Marked as answer by Idev Friday, January 21, 2011 8:20 PM
    Friday, January 21, 2011 4:19 PM
  • It does indeed look like this is known behaviour as the following (short) article indicates.

    http://msdn.microsoft.com/en-us/library/bb402845.aspx

    Thanks Russell for the links - i'll take a read - and you're right that the databases are owned by a sysadmin account.


    every day is a school day
    My Blog
    • Marked as answer by Idev Friday, January 21, 2011 8:17 PM
    Friday, January 21, 2011 4:24 PM

All replies

  • As per my understandings:

    On which database u have mapped that login... ie., is either in TestDB or MSDB??

    If u mapped the user to TestDB, then how can you execute the select state on MSDB database??

    your select st : select * from msdb.dbo.sysjobs

     

    Thanks,

    Satish Kumar.

     


    Thanks, Satish Kumar.
    Friday, January 21, 2011 12:27 PM
  • Hi Satish,

    Thanks for replying.  The new login/user should be mapped to [TestDB] ONLY.

    "If u mapped the user to TestDB, then how can you execute the select state on MSDB database?" -- EXACTLY! that's my point. You will get an error, and rightfully so, as i stated in the op.  BUT, the new login/user can then wrap that statement into a stored proc using EXECUTE AS OWNER, and get access [aka loophole].

    thanks again.

     

    Friday, January 21, 2011 12:48 PM
  •  

    If u r running any queries by using execute as, then it will become impersonate logins..

    which allows you to impersonate another user in order to validate the necessary permissions that are required to execute the code without having to grant all of the necessary rights to all the underlying objects and commands..

     

    please go through the following:

     

    http://www.mssqltips.com/tip.asp?tip=1227

     

    Thanks,

    Satish Kumar.


    Thanks, Satish Kumar.
    Friday, January 21, 2011 1:58 PM
  • Hi Satish,

    I appreciate your taking the time to reply. I've read the link you provided; however, the loophole still remains.  Specifying EXECUTE AS OWNER will execute in the context of the owner of the module, which in this case would be dbo. i.e. impersonation - there's nothing wrong with this concept.

    The problem arises when a person with just DB_OWNER rights on one database can create a proc using "EXECUTE AS OWNER" HIMSELF, and will then be able to do ANYTHING beyond his original permissions -- going from just db-owner to becoming sa for the entire SQL Server!

    This is a very serious security loophole:

    for instance you could do this...

    create proc dbo.loophole
    with execute as owner
    as
    drop table [AnyDatabaseIShouldNotHaveAccessTo].dbo.[AnyTableIShouldNotHaveAccessTo]
    go

    It's one thing if the SYSADMIN wrote this proc to EXECUTE AS OWNER; but an entirely different thing if the DB_OWNER wrote this proc to gain access to another database that he DOES NOT have permissions for in the first place.

    Is this behavior a bug?  How can you assign DB_OWNER to a user for one DB WHILE making sure that's the ONLY database he can access?

    Thanks everyone for listening. I look forward to hearing from you!

    Friday, January 21, 2011 3:16 PM
  • I agree that this seems a bit strange / dangerous.

    I can repro your example and, to take it a step further have added a further proc which elevates access of the login.

    EXECUTE AS LOGIN = 'RestrictedRightsLogin'
    GO
    create proc dbo.loophole_sa
    with execute as owner
    as
    EXEC sp_addsrvrolemember 'RestrictedRightsLogin', 'sysadmin'
    GO
    EXEC dbo.loophole_sa
    GO
    
    

    This does only seem to work if the database is set to TRUSTWORTHY but even then, i'd expect it to be more secure than that.

    BTW, i'm run SQL2008 SP1 (10.0.2734.0)

    I'll do a bit more reading and see if there is anything that might explain this behaviour.


    every day is a school day
    My Blog
    • Edited by richbrownesq Friday, January 21, 2011 4:15 PM add version
    Friday, January 21, 2011 4:12 PM
  • Ok, as you can see from Erland Sommarskog's article, there are reasons to avoid EXECUTE AS OWNER.  Since you are looking into these issues, you probably should read the whole article.  But for your issue, see:

    http://www.sommarskog.se/grantperm.html#execasowner

    It sounds as if you have cross-database ownership chaining turned on?  (And, out of curiousity, are all databases owned by the 'sa'?)  If you are using cross-database ownership chaining, please read the following two documents:

    http://msdn.microsoft.com/en-us/library/ms188694.aspx
    http://msdn.microsoft.com/en-us/library/ms188676.aspx

    Cross-database ownership chains should be as few as possible, just so as to avoid the exposures you have found.

    In SQL Server 2000, when we realized how easy it was to break into another database, we changed database owners so that each database was owned by a different login.  That broke the 2000 cross-database ownership chaining. 

    RLF

    • Marked as answer by Idev Friday, January 21, 2011 8:18 PM
    • Unmarked as answer by Idev Friday, January 21, 2011 8:18 PM
    • Marked as answer by Idev Friday, January 21, 2011 8:20 PM
    Friday, January 21, 2011 4:19 PM
  • It does indeed look like this is known behaviour as the following (short) article indicates.

    http://msdn.microsoft.com/en-us/library/bb402845.aspx

    Thanks Russell for the links - i'll take a read - and you're right that the databases are owned by a sysadmin account.


    every day is a school day
    My Blog
    • Marked as answer by Idev Friday, January 21, 2011 8:17 PM
    Friday, January 21, 2011 4:24 PM
  • Hey richbrownesq & russell,

    Hats off to you guys for taking the time to dig in and explain this behavior. I really do appreciate it.

    richbrownesq - that link you gave me is SPOT ON.

    For anyone reading this (in case the link ever dies), the link says that If TRUSTWORTHY is ON and DBO is mapped to sa, then the DB_OWNER can elevate his privileges all by himself. NOT GOOD!  Therefore, the solution is either to set TRUSTWORTHY to OFF or revoke sysadmin from the dbo role.

    In my case, the most reasonable thing to do would be to revoke sysadmin privileges from the dbo role because my shop relies on TRUSTWORTHY being ON, e.g. - u need that in order to execute sp_send_dbmail for instance.

    Regardless, I strongly feel that this behavior (the ability to elevate your own privileges in this case) should be classified as a bug instead of by-design. Having somewhat seemingly trivial settings, TRUSTWORTHY and dbo-mapped-to-sa-by-default, serve as the basis of a security loophole really seems like a real disaster waiting to happen for a lot of shops.

    What do you guys think?

    Thanks again.

    Friday, January 21, 2011 8:17 PM
  • Having somewhat seemingly trivial settings, TRUSTWORTHY and dbo-mapped-to-sa-by-default, serve as the basis of a security loophole really seems like a real disaster waiting to happen for a lot of shops.

    What do you guys think?

    Thanks again.

    Trustworthy is far from trivial and why it is off by default.  The same applies to cross-database chaining.  Both are appropriate options in environments where only DBAs can create objects and choose to extend module security context among databases. 

    The downside of using these features is that DBAs must be especially mindful of the security implications when they grant DDL permissions to non-sysadmins.  I wish the Books online were more explicit about the risks of TRUSTWORTHY combined with non-sysadmin DDL permissions.

    In my opinion, certificates are more secure and provide more control than EXECUTE AS.  Erland does a good job of describing this in his article.

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    Saturday, January 22, 2011 4:00 AM
  • Hi Idev,

    Not only for db_owner it also works the same way for any other ordinary user who has grant execute permission on the execute as owner SP , I am sure this is intended behaviour if you have trustworthy on for your database. As Dan has pointed out , it should be used where only DBAs are sysadmins.


    Thanks, Leks
    Saturday, January 22, 2011 8:26 PM