• 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.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon