none
Grant execute on SP that accesses Change Tracking tables

    Question

  • Hi,

    According to this article, granting 'Execute' on a stored procedure to a user/role will allow them to access any tables or views that the procedure can access (provided that the objects in the chain of execution have the same owner), without explicitly granting the user 'Select' or other permission on the various objects accessed by the stored procedure.

    That being the case, do any of you happen to know, is VIEW CHANGE TRACKING permission an exception to the above rule??

    I have a SP that accesses data from Change Tracking and inserts it into a table. The procedure works fine when run with db_owner permissions. But when I try to run it as a specific role to whom I've granted 'Execute' permission on the SP, it fails and returns an error stating that the role lacks 'View Change Tracking' permission.

    When run under this role, the procedure will only work when I grant the role 'Execute' on the procedure AND 'View Change Tracking' on each tracked table.

    I just want to know, is this the expected behavior? Is there no way to get my procedure to run under this role with 'Execute' permission only?

    Thanks!

    Dylan

    Monday, August 05, 2013 9:09 PM

Answers

  • What's not clear is why View Change Tracking permissions don't participate in ownership chaining. The whole point of granting 'execute' on a stored procedure in an ownership chain seems to be to give roles/users the right to get, update, insert, etc. when (and only when) they use that procedure. At the same time, they are denied those privileges outside the context of that stored procedure.

    I'm frustrated as to why the same wouldn't be true with View Change Tracking. If I explicitly grant this permission to a user/role, then they will be able to view change tracking data outside the context of this stored procedure. Additionally, right now I have only one role that I want to grant execute to, and only three tracked tables. But we plan to extend this functionality. Explicitly granting View Change Tracking permission on multiple tables to multiple roles will become increasingly complex over time, compared to just granting 'execute' to each new role.

    I'm still suspicious that I might be missing some piece of information that would allow different approach.

    I see your point and understand your frustration. Microsoft apparently realizes that Change Tracking is different than normal data access to which ownership chaining applies because it exposes not only current data in tables but historical data that may have changed over time before permissions were granted.  The choice to explicitly require VIEW CHANGE TRACKING was likely made because the security implications are not obvious and security is a high priority.

    There are a couple of ways you can provide access to Change Tracking data through a stored procedure without granting users direct permissions. One is to use EXECUTE AS with a user that has the needed permissions.  Another approach, which I suggest, is to create a certificate user with the needed permissions and sign the proc with the certificate. This will provide normal ownership chaining behavior plus extend the proc permissions to include the ability to view CT data.  Below is an example gleaned from Erland Sommarskog's article (http://www.sommarskog.se/grantperm.html).

    -- create a certificate for user credentials
    CREATE CERTIFICATE CT_User_Cert
        ENCRYPTION BY PASSWORD = 'dfaD%$#)MMepb943'
        WITH SUBJECT = 'User certificate example',
        START_DATE = '20020101', EXPIRY_DATE = '21000101';
    GO
    -- create a user from the certificate
    CREATE USER CT_Cert_User FROM CERTIFICATE CT_User_Cert;
    GO
    -- grant needed permissions to certificate user
    GRANT VIEW CHANGE TRACKING ON dbo.TrackedTable TO CT_Cert_User;
    GO
    -- when you create or change the procedure:
    ADD SIGNATURE TO dbo.usp_CT_Proc BY CERTIFICATE CT_User_Cert
        WITH PASSWORD = 'dfaD%$#)MMepb943';
    GO
    

    By the way, what SQL Server version are you running and what CT functions are you using in the proc?


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Tuesday, August 06, 2013 11:23 AM

All replies

  • Who is the owner of th procedure?

    What permissions does he have?

    Quote from BOL:

    "DDL Operations to Change Data Capture Enabled Source Tables

    When a table is enabled for change data capture, DDL operations can only be applied to the table by a member of the fixed server role sysadmin, a member of the database role db_owner, or a member of the database role db_ddladmin. Users who have explicit grants to perform DDL operations on the table will receive error 22914 if they try these operation."


    so if you are talking about this error 22914, "Only members of the sysadmin or db_owner or db_ddladmin roles can perform this operation when Change Data Capture is enabled for a database.", then this is the reason :)





    Andreas Wolter | Microsoft Certified Master SQL Server
    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com

    Monday, August 05, 2013 10:36 PM
  • Thanks for the feedback.

    No, that's not the error I'm getting. Plus, I'm not doing any DDL operations. The error I'm getting is "View change tracking permission was denied on the object <schemaName>, <tableName>."

    The owner of my procedure is the schema that it's in, and the owner of the schema is dbo. dbo is in the db_owner role. The procedure inserts into a table, also in the same schema. It gets from the change tracking cache data on tables in other schema, but those objects are also owned by their schemas, which are also owned by dbo.

    So granting 'Execute' on the procedure to the role should allow that role to run the procedure regardless of whether the procedure inserts, selects, updates, deletes, etc. And indeed, it is able to do all those things. What it is unable to do when called by this role, is get data from the change tracking cache without explicitly granting "view change tracking" permission (on each table tracked) to the role.

    Thanks,


    • Edited by dylbud Monday, August 05, 2013 10:50 PM
    Monday, August 05, 2013 10:49 PM
  • According to this article, granting 'Execute' on a stored procedure to a user/role will allow them to access any tables or views that the procedure can access (provided that the objects in the chain of execution have the same owner), without explicitly granting the user 'Select' or other permission on the various objects accessed by the stored procedure.

    That being the case, do any of you happen to know, is VIEW CHANGE TRACKING permission an exception to the above rule??

    According to http://msdn.microsoft.com/en-us/library/hh710064.aspx, VIEW CHANGE TRACKING permission is needed to use the change tracking functions because:

    <Excerpt>

    ◦ Change tracking records include information about rows that have been deleted, specifically the primary key values of the rows that have been deleted. A principal could have been granted SELECT permission for a change tracked table after some sensitive data had been deleted. In this case, you would not want that principal to be able to access that deleted information by using change tracking.

    ◦ Change tracking information can store information about which columns have been changed by update operations. A principal could be denied permission to a column that contains sensitive information. However, because change tracking information is available, a principal can determine that a column value has been updated, but the principal cannot determine the value of the column.

    </Excerpt> 

    This is in addition to permissions obtained through ownership chaining.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Monday, August 05, 2013 11:00 PM
  • Thanks for the response.

    That makes sense...sort of.  It's clear why you would want to grant/deny someone explicit permissions to view change tracking data (just like you might want to grant/deny select, delete or anything else).

    What's not clear is why View Change Tracking permissions don't participate in ownership chaining. The whole point of granting 'execute' on a stored procedure in an ownership chain seems to be to give roles/users the right to get, update, insert, etc. when (and only when) they use that procedure. At the same time, they are denied those privileges outside the context of that stored procedure.

    I'm frustrated as to why the same wouldn't be true with View Change Tracking. If I explicitly grant this permission to a user/role, then they will be able to view change tracking data outside the context of this stored procedure. Additionally, right now I have only one role that I want to grant execute to, and only three tracked tables. But we plan to extend this functionality. Explicitly granting View Change Tracking permission on multiple tables to multiple roles will become increasingly complex over time, compared to just granting 'execute' to each new role.

    I'm still suspicious that I might be missing some piece of information that would allow different approach.

    Thanks,

    Dylan

    Monday, August 05, 2013 11:41 PM
  • What's not clear is why View Change Tracking permissions don't participate in ownership chaining. The whole point of granting 'execute' on a stored procedure in an ownership chain seems to be to give roles/users the right to get, update, insert, etc. when (and only when) they use that procedure. At the same time, they are denied those privileges outside the context of that stored procedure.

    I'm frustrated as to why the same wouldn't be true with View Change Tracking. If I explicitly grant this permission to a user/role, then they will be able to view change tracking data outside the context of this stored procedure. Additionally, right now I have only one role that I want to grant execute to, and only three tracked tables. But we plan to extend this functionality. Explicitly granting View Change Tracking permission on multiple tables to multiple roles will become increasingly complex over time, compared to just granting 'execute' to each new role.

    I'm still suspicious that I might be missing some piece of information that would allow different approach.

    I see your point and understand your frustration. Microsoft apparently realizes that Change Tracking is different than normal data access to which ownership chaining applies because it exposes not only current data in tables but historical data that may have changed over time before permissions were granted.  The choice to explicitly require VIEW CHANGE TRACKING was likely made because the security implications are not obvious and security is a high priority.

    There are a couple of ways you can provide access to Change Tracking data through a stored procedure without granting users direct permissions. One is to use EXECUTE AS with a user that has the needed permissions.  Another approach, which I suggest, is to create a certificate user with the needed permissions and sign the proc with the certificate. This will provide normal ownership chaining behavior plus extend the proc permissions to include the ability to view CT data.  Below is an example gleaned from Erland Sommarskog's article (http://www.sommarskog.se/grantperm.html).

    -- create a certificate for user credentials
    CREATE CERTIFICATE CT_User_Cert
        ENCRYPTION BY PASSWORD = 'dfaD%$#)MMepb943'
        WITH SUBJECT = 'User certificate example',
        START_DATE = '20020101', EXPIRY_DATE = '21000101';
    GO
    -- create a user from the certificate
    CREATE USER CT_Cert_User FROM CERTIFICATE CT_User_Cert;
    GO
    -- grant needed permissions to certificate user
    GRANT VIEW CHANGE TRACKING ON dbo.TrackedTable TO CT_Cert_User;
    GO
    -- when you create or change the procedure:
    ADD SIGNATURE TO dbo.usp_CT_Proc BY CERTIFICATE CT_User_Cert
        WITH PASSWORD = 'dfaD%$#)MMepb943';
    GO
    

    By the way, what SQL Server version are you running and what CT functions are you using in the proc?


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Tuesday, August 06, 2013 11:23 AM
  • Thanks for those suggestions. They both sound like reasonable approaches. 

    The version we are on is 2008 R2, and the only change tracking function being used in the procedure is CHANGETABLE(CHANGES).

    Dylan

    Tuesday, August 06, 2013 4:36 PM
  • Here's another interesting piece of information that I found related to this discussion. It does not specifically talk about View Change Tracking Permission, but it makes it clear that not all permissions participate in ownership chaining.

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


    Dylan

    Thursday, August 08, 2013 6:23 PM
  • OK, I've done a bit more researching, sleuthing and testing on this issue. It turns out I may have taken us down the wrong path to begin with.

    I've discovered that View Change Tracking permission DOES participate in ownership chaining. However, permissions required to execute dynamic SQL do not. This is probably well-know to many, but was not something I was aware of.

    The procedure I was referencing throughout this thread contains dynamic SQL, and the call of the Changetable() function is in the dynamic SQL.

    I did a few simple tests to verify that a role or user that has been granted EXECUTE on a stored procedure owned by user with the necessary permissions will retain View Change Tracking permissions. Only when you execute the change tracking functions as dynamic SQL does the ownership chaining break.

    So I apologize for the wild goose chase here, but at least I can rest easy knowing that View Change Tracking permissions are not as anomalous as I thought.

    I am still marking Dan Guzman's post as the answer, because it provides an alternate solution that will work in our case. In this particular situation we don't think we can get rid of the dynamic SQL and retain the functionality we need.

    Thanks!

    Dylan

    Thursday, August 08, 2013 8:24 PM
  • I am still marking Dan Guzman's post as the answer, because it provides an alternate solution that will work in our case. In this particular situation we don't think we can get rid of the dynamic SQL and retain the functionality we need.

    Thanks for following up.  Note that if you must use dynamic SQL, the same user certificate approach can be used to avoid direct permissions on the underlying objects.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Friday, August 09, 2013 12:15 AM