GSquared (11/21/2012)

---

opc.three (11/20/2012)

---

GSquared (11/20/2012)

---

opc.three (11/19/2012)

---

GSquared (11/19/2012)

---

Keep in mind, with synonyms, that accessing a synonym is the same as accessing a table directly. If your security policy is to disallow accessing tables directly, then synonyms will NOT be compliant with that policy.

That is not completely accurate. While it is true that a synonym's textual reference is wholesale-replaced with the object it is created for before a query is executed, a synonym still acts as any other security container in the database would in that it can be in the initial object in an ownership chain, i.e. synonyms can be used in place of allowing direct table access just like a stored procedure or view might be used.

In the context of this post in particular there is an added element due to the need to abstract tables across a database boundary and therefore it implies that DB_CHAINING be ON for both the database where the synonym resides as well as the database where the table resides, and that the login have a user in both databases however synonyms could be used to abstract the underlying tables nonetheless.

I disagree. Yes, different permissions can be granted on a synonym than on the underlying object. But there's no real point to that. If you're using actual security, like minimum-needed-permissions on a login+user, then there's no point to using synonyms as a security layer. Views can actually allow for row-level security, simply by joining to a "login permissions" table. Synonyms can't do that. Views can allow for column-level security by being defined as something other than "Select *" in the Select clause. Synonyms can't do that.

Basically, synonyms allow for name-obfuscation. That can be a very useful thing, no doubt about that. But it's not a security measure. It's a coding tool, not a security one.

If the sole purpose of views is to obscure table names, that's its own form of useless waste-of-time engineering, and it also doesn't actually enhance security at all. I've seen databases designed that way, where every table had to have a "Select *" style view defined on top of it, none with a Where clause that did anything, and application code was only allowed to access the views. This is universally done out of ignorance of actual database security, and has no functional purpose at all. It's a "we don't know what we're doing, but this makes us feel safer" type thing.

Feeling safer has its own benefits, of course. It also has major drawbacks if the safety is purely an illusion.

I am well aware of the benefits and drawbacks of using a SYNONYM versus using a VIEW. I think you have blown right past the point GSquared. The intent of the OP is Alos we have a policy to to directly access the tables in the other databases and that is why we use views (sic). A SYNONYM can give us that abstraction, as will a VIEW, because through ownership chaining we can grant select on a synonym or view to allow for access to data, however not grant any permissions to local underlying structures they refer to. I suspect that the reason for having such a policy is less about security and more about allowing the database development team wiggle-room down the line if a schema change needs to be made. In my opinion it is a noble policy and one which I would like to see more shops adopt. A view that previously referred to a single table can later be rewritten to deliver data from three tables that were the result of normalizing the original one, and INSTEAD OF triggers can be added to maintain the DML interfaces. Similarly, a synonym can be dropped and a VIEW with triggers stood up in its place to accomplish the same level of refactoring. I am confident I am not telling you anything about this technique which you did not already know, so let's not beat the horse to death. Synonyms do offer the abstraction the OP is after and you stated in a previous post that it was not possible, that's all I am saying.

Then we disagree. I think it's a feel-good-through-ignorance policy.

If you want security, refactorability, RAD, schema-agnostic coding, etc., then what you really need is a proper DAL (Data Access Layer), even if that's as simple as stored procedure calls instead of direct access to tables/views or name-obscured tables/views (that's all synonyms are).

You're right that I'm blowing past the "we just need the name obscured" piece of the post. I pointed out already that I consider that an ignorant policy. I've run into it several times before, and EVERY SINGLE TIME, it's been created as a policy out of ignorance. I've never yet seen a convincing argument that it does anything useful at all, except possibly keep ignorant managers off the DBA's back. (Educating the managers is usually easier and definitely works better, but keeping them off your back has some tiny bit of value.) I'm blowing past it in order to try to point out a better way to actually accomplish something useful, instead of promoting blissful ignorance.

You disagree. That's your right.