0

I have a multi-tenant application sharing a single Azure SQL database using row-level security. The security predicate keys off of a "TenantId" column in all tables. Simplified, I have the following setup:

  • ParentTable (Id, TenantId, Name)
  • ChildTable (Id, TenantId, Name, ParentId)
  • ChildTable.ParentId is a foreign key to ParentTable.Id

The behavior I am currently seeing is that I can insert records without any issues into ChildTable. However, once a record has been inserted, there is no way to update the value of the ChildTable.ParentId column. The error is: "The UPDATE permission was denied on the column 'ParentId' of the object 'ChildTable', database 'foo', schema 'dbo'." I can update the value of any other columns, and there are no other explicit permissions assigned to that column. The only thing that seems to make it "special" is the fact that it points to another table using row-level security.

My Google-fu might just be failing, but I have not been able to fund anything documenting limitations with row-level security and foreign key relationships, so I'm not sure if this is by-design or if maybe something isn't set up correctly in my security policies. (They are, however, pretty simple, so that seems unlikely.)

If anyone can point me to any documentation or best-practices around this, that would be super helpful.

1
  • Welcome to the DBA.SE community. We'd require the actual definition of the CONSTRAINTS to be able to provide a detailed answer. Consider clicking on edit and adding the details to your question. Thanks.
    – John K. N.
    Sep 18, 2023 at 12:59

2 Answers 2

1

The error is: "The UPDATE permission was denied on the column 'ParentId' of the object 'ChildTable', database 'foo', schema 'dbo'."

This is not the kind of error message Row-Level Security throws when a DML statement is blocked by a security policy. Usually an equivalent Row-Level Security blocking error message would state something along the lines of "The attempted operation failed because the target object 'foo.dbo.ChildTable' has a block predicate...". So I'm skeptical that the issue is your Row-Level Security policies.

This sounds like a regular security provisioning error message. Either the User doesn't have the UPDATE permission granted to ChildTable or an explicit DENY permission was provisioned against UPDATEs to the ParentId column.

The latter seems more likely to me, given the error message explicitly calls out that column. Are you able to update Name field in the ChildTable table?

1
  • Thanks for the ideas. Everything you state makes perfect sense, but that's what's so baffling in this case. The account in question just has db_datareader & db_datawriter roles and there are no column-level permissions anywhere. I did come up with a workaround (see response below) by dropping and recreating just the foreign key constraint, but I have no idea why that would do anything. Sep 18, 2023 at 12:54
0

Well, I have no way to explain why this is the case, but I can confirm that the foreign key constraint was definitely the problem. As soon as I dropped the constraint (ALTER TABLE ... DROP CONSTRAINT ...) I could update the column.

Even more strangely, after I recreated the constraint with the exact same definition, things continued to work. No permission changes at all in between those operations.

I have no idea why this is the case, but maybe it at least provides a workaround if anyone else is having this problem.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.