November 15, 2012 at 8:12 pm
Databases in our servers communicate with each other using views. For instance, dbA has views that gets data from db2 through its views. Is there another way for a database to use data from another database other than views.
November 15, 2012 at 8:53 pm
Using linked servers (http://msdn.microsoft.com/en-us/library/ms188279.aspx) you should be able to access tables directly given appropriate authorizations.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
November 15, 2012 at 10:10 pm
Linked Servers are useful for server-to-server communication but if you're just needing to communicate from one database to another on the same instance then Linked Servers would not be a great option.
With the proper permissions any SQL statement executed within a database can use 3-part naming to access data in any other database on the instance, e.g. This
SELECT column_a
FROM dbA.dbo.table_a;
can be run in dbB to retrieve data.
SQL statements that use 3-part naming can belong to views, triggers, stored procedures, user-defined functions...pretty much any SQL object can contain a query with a 3-part name.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
November 15, 2012 at 10:16 pm
opc.three (11/15/2012)
Linked Servers are useful for server-to-server communication but if you're just needing to communicate from one database to another on the same instance then Linked Servers would not be a great option.With the proper permissions any SQL statement executed within a database can use 3-part naming to access data in any other database on the instance, e.g. This
SELECT column_aFROM dbA.dbo.table_a;
can be run in dbB to retrieve data.
SQL statements that use 3-part naming can belong to views, triggers, stored procedures, user-defined functions...pretty much any SQL object can contain a query with a 3-part name.
dbA can be a DB2 database? I didn't know that.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
November 16, 2012 at 7:42 am
dwain.c (11/15/2012)
opc.three (11/15/2012)
Linked Servers are useful for server-to-server communication but if you're just needing to communicate from one database to another on the same instance then Linked Servers would not be a great option.With the proper permissions any SQL statement executed within a database can use 3-part naming to access data in any other database on the instance, e.g. This
SELECT column_aFROM dbA.dbo.table_a;
can be run in dbB to retrieve data.
SQL statements that use 3-part naming can belong to views, triggers, stored procedures, user-defined functions...pretty much any SQL object can contain a query with a 3-part name.
dbA can be a DB2 database? I didn't know that.
Not straightaway but we could always define a SYNONYM named dbA.dbo.table that resolved to a remote table referenced by its 4-part name, however that's a different bunny trail.
There is an ambiguity in the original post, and I read "db2" as the name of a SQL Server database on the same instance as dbA. If "db2" actually means an instance of an IBM DB2 database to the original poster then Linked Servers are pretty much the only option if the requirement was to do everything in T-SQL. if that is the case then I would edit to replace "3-part naming" in my initial post with "4-part naming."
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
November 17, 2012 at 6:31 am
We are trying to stay away from linked servers. Alos we have a policy to to directly access the tables in the other databases and that is why we use views. Synonyms might not be a bad idea. I'd look into that, otherwise does anyone have an other tricks for databases to communicate.
November 17, 2012 at 2:43 pm
wanox (11/15/2012)
Databases in our servers communicate with each other using views. For instance, dbA has views that gets data from db2 through its views. Is there another way for a database to use data from another database other than views.
As a couple of folks have pointed out, Synonyms are probably the best way to go here and can be pointed at either a local (same instance) database or a linked server database. There are two great things about synonyms... you don't need to remember to regen (SELECT *... a very bad thing) or rebuild (discreet named columns) views if columns are added to or deleted from the underlying tables and, as with views, you can avoid making your code dependent on another database by sticking to a 2 part naming convention.
I said "probably" because the bad part about Synonyms is that they can't hide columns or rows (if you need to) using just the Synonym like you can with a View.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 18, 2012 at 3:25 pm
Thanks Jeff. I ahve a presentation ready to sell Syninyms to the rest fo my team.
Thank you all for your responses!!
November 19, 2012 at 6:03 am
wanox (11/18/2012)
Thanks Jeff. I ahve a presentation ready to sell Syninyms to the rest fo my team.Thank you all for your responses!!
Be prepared. Like I said, the [font="Arial Black"]ONLY [/font]advantage that synonyms have over "pass through" views is that they never need to be regenerated if the underlying object (table, in this case) changes. Views have a lot more advantages (which I also covered) over Synonyms and if you need the type of functionality they offer, a Synonym just isn't going to hack it.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 19, 2012 at 6:37 am
Why do you not want to use a view?
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
November 19, 2012 at 1:04 pm
In my organization we stay away from accessing tables directly, so instead we use views. For instance, if we want to create a database based on data from a table/tables in another database, we are not supposed to access the table directly. Typically we will create a view in the database that accesses data from another database through its views. So we will end up with a new database that does not have any tables but only views. The policy we have to access data from a table through views will stay but creating a database based on data from another database through views will need to be changed.
November 19, 2012 at 2:02 pm
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.
On the other hand, using views as a security method instead of tables is usually not actually effective in accomplishing what the security policy is intended to accomplish.
- 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
November 19, 2012 at 2:25 pm
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.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
November 20, 2012 at 7:26 am
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
November 20, 2012 at 9:10 am
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.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply