locked
Theory question on Extending the Scope of Impersonation RRS feed

  • Question

  • Hello!

    This article - Extending Database Impersonation by Using EXECUTE AS - says for extending the scope of impersonation the two conditions must be met:

    • The authenticator must be trusted in the target scope.

    • The source database has to be marked as trustworthy.

    ...but the first example uses only the first condition:

    In this example the scope has been extended WITHOUT setting up the trustwothy option for the source database - why?

    Thank you in advance,

    Michael



    • Edited by MF47 Monday, July 24, 2017 10:30 AM typo
    Monday, July 24, 2017 10:29 AM

Answers

  • Try the below. As posted, the SELECT from sometable fails. Uncomment the statement that makes the database TRUSTWORTHY and it succeeds.

    When you impersonate a database user, you are sandboxed into your own database, and two doors must be opened. One door out of the database - that is TRUSTWORTHY - and one door into where you want to go - that is the AUTHENTICATE permission.

    What the article does not say is that TRUSTWORTHY can incur a security risk as it can permit for permission elevation.

    CREATE LOGIN MarketingDBO WITH PASSWORD = 'MarketingMumboJumbo'
    CREATE LOGIN SalesDBO WITH PASSWORD = 'SellSellSell!'
    CREATE LOGIN SnMPerson WITH PASSWORD = '50 shades of some colour'
    go
    CREATE DATABASE Marketing
    ALTER AUTHORIZATION ON DATABASE::Marketing TO MarketingDBO
    CREATE DATABASE Sales
    ALTER AUTHORIZATION ON DATABASE::Sales TO SalesDBO
    -- ALTER DATABASE Marketing SET TRUSTWORTHY ON
    go
    USE Sales
    go
    CREATE TABLE sometable(a int NOT NULL)
    CREATE USER SnMPerson
    GRANT SELECT ON sometable TO SnMPerson
    CREATE USER MarketingDBO
    GRANT AUTHENTICATE TO MarketingDBO
    go
    USE Marketing
    go
    CREATE USER SnMPerson
    EXECUTE AS USER = 'SnMPerson'
    SELECT * FROM Sales.dbo.sometable
    go
    REVERT
    go
    USE tempdb
    go
    DROP DATABASE Marketing
    DROP DATABASE Sales
    DROP LOGIN SalesDBO
    DROP LOGIN MarketingDBO
    DROP LOGIN SnMPerson
    • Marked as answer by MF47 Thursday, July 27, 2017 1:32 PM
    Tuesday, July 25, 2017 7:27 PM

All replies

  • The first part is correct - the database must be marked trustworthy and the database owner must have been given AUTHENTICATE permission in the other database. It seems that the TRUSTWORTHY part is implicit in the section you have highlighted.

    • Proposed as answer by Teige Gao Tuesday, July 25, 2017 2:03 AM
    Monday, July 24, 2017 9:30 PM
  • "It seems that the TRUSTWORTHY part is implicit in the section you have highlighted" - I don't think so:

    Trusting the Database

    In SQL Server, the trust model goes one more step in order to provide additional security and granularity to the act of extending the scope of database-level impersonation. You can use the AUTHENTICATE permission as a way for the target scope to trust the authenticator of a context, but you can also determine whether the instance of SQL Server trusts the source database and the contents within it.

    - the article says that just when you need to "provide additional security and granularity ... you CAN ALSO determine whether the instance ... trusts the source database" so I see no grounds for assuming that the TRUSTWORTHY part is implicit in the first section.

    As far as I understand the main purpose of the TRUSTWORTHY option is to further restrict the number of source databases that can be allowed to access external data in case all source databases have the same authenticator (MarketingDBO in this article) - if there's no need in this extra-restriction then TRUSTWORTHY option is not required, as seen in the first example:

    "However, in order to access a table in the Sales database, the Sales database must trust the authenticator MarketingDBO." - only trusting the authenticator has been enough for allowing external access.

    Regards,

    Michael





    • Edited by MF47 Tuesday, July 25, 2017 8:03 AM
    Tuesday, July 25, 2017 7:48 AM
  • Try the below. As posted, the SELECT from sometable fails. Uncomment the statement that makes the database TRUSTWORTHY and it succeeds.

    When you impersonate a database user, you are sandboxed into your own database, and two doors must be opened. One door out of the database - that is TRUSTWORTHY - and one door into where you want to go - that is the AUTHENTICATE permission.

    What the article does not say is that TRUSTWORTHY can incur a security risk as it can permit for permission elevation.

    CREATE LOGIN MarketingDBO WITH PASSWORD = 'MarketingMumboJumbo'
    CREATE LOGIN SalesDBO WITH PASSWORD = 'SellSellSell!'
    CREATE LOGIN SnMPerson WITH PASSWORD = '50 shades of some colour'
    go
    CREATE DATABASE Marketing
    ALTER AUTHORIZATION ON DATABASE::Marketing TO MarketingDBO
    CREATE DATABASE Sales
    ALTER AUTHORIZATION ON DATABASE::Sales TO SalesDBO
    -- ALTER DATABASE Marketing SET TRUSTWORTHY ON
    go
    USE Sales
    go
    CREATE TABLE sometable(a int NOT NULL)
    CREATE USER SnMPerson
    GRANT SELECT ON sometable TO SnMPerson
    CREATE USER MarketingDBO
    GRANT AUTHENTICATE TO MarketingDBO
    go
    USE Marketing
    go
    CREATE USER SnMPerson
    EXECUTE AS USER = 'SnMPerson'
    SELECT * FROM Sales.dbo.sometable
    go
    REVERT
    go
    USE tempdb
    go
    DROP DATABASE Marketing
    DROP DATABASE Sales
    DROP LOGIN SalesDBO
    DROP LOGIN MarketingDBO
    DROP LOGIN SnMPerson
    • Marked as answer by MF47 Thursday, July 27, 2017 1:32 PM
    Tuesday, July 25, 2017 7:27 PM
  • I see...  Thank you so much for the great code example!

    But it means the article is wrong as it doesn't mention TRUSTWORTHY option in the first section.

    Regards,

    Michael

    Thursday, July 27, 2017 1:32 PM