opc.three (11/21/2012)
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.
Feel-good-through-ignorance, what on earth are you talking about? Why are you harping on this as a security issue? I have to say, we have been on a lot of the same threads and I have read a lot of your posts so naturally I have a lot of respect for your opinion because you're a really smart guy, but on this one you are are way off the mark. If implementing cross-database ownership chaining, yes, there are concerns, but that is a discrete issue separate from what is being discussed by implementing abstraction so that data consumers cannot refer directly to tables. Please, enlighten everyone as to where you see a security issue with abstracting local table names using synonyms, views and stored procedures?
I feel bad that you have not worked with a group senior enough (managers, technicians or otherwise) where they could effectively demonstrate to you the benefits of designing and implementing a database such that it would maintain its own interface on the network, allowing us to think of a database instance as a network service considered on equal footing with a web service or a network device, independent of any data-access layer with no dependencies flowing in an outward direction towards the data consumer. For the effort of treating a database in this way we gain the ability to change a database used by many data consumers without affecting those customers, again, independently. Notice that security is not a concern when thinking about the database in these terms. Security must permeate everything we do, but it is a different aspect of the conversation from this one which I would think of more as architectural and design-driven.
I haven't the faintest darn clue what you're on about here.
He said they have a policy that they can't access tables directly, but use views instead. Someone else suggested using synonyms instead of views. I'm challenging "what the heck does either of those things do that has any actual value?"
Thus far, nobody has answered that question. The only answer given thus far is, "it allows moving the target object and redefining the synonym, which allows for a higher level of code to continue to access the synonym, agnostic of the target object's actual location". I pointed out that a real DAL allows for that, and a LOT more, like redefining the target object in a way that is completely invisible to application code. In short, synonyms used that way are a solution, but they're an inferior solution. They're also a completely unnecessary solution.
In many cases, they'll actually break things if you use them that way. Move a table to another server, change the synonym to point at a four-part-name instead of a three-part on the local server, and suddenly code has to deal with all the limitations of the DTC and no way around that. Have a stored procedure pull the data, and higher-level applications call the proc instead. There are ways, inside procs, to mitigate DTC impact, even to effectively eliminate it on small datasets. No way to do that with a synonym or a view. Any properly built DAL (procs or otherwise) allows that kind of functionality, but synonyms and views don't. Hence, synonyms and views are a seriously inferior solution.
I pointed out that, in my experience, every time I've run into policies like this one (no table access, only views/synonyms), it's been out of a misguided idea that it somehow adds to database security. Does that mean that's universal? Of course not! It means EXACTLY what I wrote, nothing more, nothing less.
If object-mobility is the real goal, then why not allow direct table access? If you move a table, drop a synonym in place with the same name the table had in that location, and the application won't know the difference. No need to provide a synonym for every table, just for ones that have been moved. Less work, easier to document the database (less objects), less object-name collision, minutely better performance (less schema locks), easier troubleshooting and performance tuning, and so on. Accomplishes the same thing you are advocating, but does it the easy way. This is based on the idea that tables are moved far less often than not.
Really, how many tables do you move to different databases/servers on a daily basis? Weekly? Or is it more like once or twice in the lifetime of an application, if that often?
In my experience (again, limited dataset, but it's the one I have to actually deal with), it's much more common to change table structures than to change table locations. Business needs change, and suddenly what was in 3NF for the prior needs isn't in 3NF, isn't even a complete tuple, any more. Synonyms won't help with that at all, and views frequently won't. A DAL (proc or otherwise) can allow for legacy code to continue to access the same objects/methods with no changes, while new code can take advantage of the new data definitions, without data loss, and with much less work than otherwise. Hence, again, synonyms to obscure table names are an inferior solution, if they even help at all.
So, what I'm suggesting is a serious, honest appraisal of what that policy is intended to accomplish. Most likely, a move towards n-tier/MVC, would be a much better solution, would actually do what the policy intends (instead of creating the illusion of accomplishing it). Hence, I suggest looking into that. Was that advice asked for? No. Do I still give it? Yes. I have found, much of the time, that unsolicited but well-informed advice, helps overcome Dunning-Kruger issues, as well as just plain "newbie" issues of not knowing what to ask for.
I'm not sure why you feel the need to be insulting about this. I'm offering well-intended advice, based on long experience, that I have found is almost always helpful to people who actually need the help. If that offends you, so be it.
- 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