0

Create Security Policy without schemabinding?

Is this a bad idea? I have an indexed view in another database that I need to use in my security predicate for row level security. I can either turn off schemabinding and use a synonym, or I have to start using transactional replication to copy to a table in the main database. I control both dbs and they are on the same server.

2

The security policy documentation incorrectly states that the inline table value function must have been created using the SCHEMABINDING option. The actual behavior, however, is that the function needs to be schema bound only when the policy is schema bound (default). Since the function with cross-database references cannot be schema bound, the implication is the policy cannot be schema bound either.

Without schema binding, the onus is on you to ensure no breaking changes are made to the function or dependent objects and that the other database is always available. Also, users will need SELECT and EXECUTE permissions on the function as well as SELECT on objects accessed by the function (not necessary with SCHEMABINDING on the function). This may be a security concern depending on the sensitivity of the predicate data.

I think the general best practice is SCHEMABINDING for the above reasons but you'll need to weigh those benefits with the costs of managing replicated data.
Besides transactional replication, you could use an ETL process or Service Broker to replicate the security policy data. Not sure why the data is in a different database to begin with but another option would be to move the objects to the same database.

| improve this answer | |
  • Hi Dan, Scheme Binding is not strictly required as it is possible to turn off schemabinding in the security policy. So my question is not whether it is possible to do this (it is). It is whether it would be bad practice to do it. It would obviously save having to set up transactional replication. There is no reason for the user not to have access to the other database, indeed they already do. – James Birch Sep 22 '17 at 11:11
  • @JamesBirch, you are right about policy schemabinding as I was thinking of the function schemabinding and trusting the documentation. I updated my answer with that in mind. – Dan Guzman Sep 22 '17 at 13:05
  • Many thanks Dan. The 2 databases is a legacy issue. Effectively they are both part of the same application and security scope. They should really be different schemas in the same db. I will look at whether I can find a way around tranactional replication without turning on cross database ownership chaining. I still find it very annoying that there is no way to set up a trust relationship between 2 databases, without opening up the whole instance. – James Birch Sep 25 '17 at 8:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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