473,698 Members | 2,490 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

How do I reference a table in one database from a SQL script running in another database?

Ted
I am construvcting a number of databases, some of which contain
sensitive data and most of which do not. I am attempting to handle the
security issues involved in protecting sensitive data in part by
putting it in its own database. If the sensitive data is in a database
called d_SensitiveData , and in that database there is a table called
't_A' (I know, not very informative, but this is only a trivially
simple example :-), and I have a script running in a database
'NotSensitiveDa ta' (i.e. there is a statement at the beginning of the
script "USE NotSensitiveDat a"), how do I referenece the primary key in
table 'A'?

I tried a variety of things like:

ALTER TABLE t_nsX
ADD CONSTRAINT FK_PersonID FOREIGN KEY (p_idPerson)
REFERENCES SensitiveData.t _A (p_idPerson);

The above is, in fact, my latest attempt. Everything I tried has
failed precisely at the point where I specify a table in a different
database. So what is the trick to refering to a table in one database
when using another database?

This will be used in a ASP.NET v3 application where one of the things I
want to do is have the authentication provider be a different database
from the one used for the main application data.

Does anyone know of an example I can download from the web that does
the same sort of thing I want to try, with some discussion of security
issues involved (i.e. what I can do to harden the application and data
server)?

NB: I am an application developer, not a DBA nor a system
administrator.

Thanks

Ted

Jan 5 '07 #1
6 5889
MC
Well, first off, I really dont see the need to divide data in different
databases just for security issues. Permissions granularity shoudl give you
all you need.

As far as the referencing stuff goes, you cant add foreign keys across
databases. You can only programatically maintain data consistency. You can
select data from diff databases, but you cant declaratively maintain
consistency.
MC

"Ted" <r.*********@ro gers.comwrote in message
news:11******** **************@ 38g2000cwa.goog legroups.com...
>I am construvcting a number of databases, some of which contain
sensitive data and most of which do not. I am attempting to handle the
security issues involved in protecting sensitive data in part by
putting it in its own database. If the sensitive data is in a database
called d_SensitiveData , and in that database there is a table called
't_A' (I know, not very informative, but this is only a trivially
simple example :-), and I have a script running in a database
'NotSensitiveDa ta' (i.e. there is a statement at the beginning of the
script "USE NotSensitiveDat a"), how do I referenece the primary key in
table 'A'?

I tried a variety of things like:

ALTER TABLE t_nsX
ADD CONSTRAINT FK_PersonID FOREIGN KEY (p_idPerson)
REFERENCES SensitiveData.t _A (p_idPerson);

The above is, in fact, my latest attempt. Everything I tried has
failed precisely at the point where I specify a table in a different
database. So what is the trick to refering to a table in one database
when using another database?

This will be used in a ASP.NET v3 application where one of the things I
want to do is have the authentication provider be a different database
from the one used for the main application data.

Does anyone know of an example I can download from the web that does
the same sort of thing I want to try, with some discussion of security
issues involved (i.e. what I can do to harden the application and data
server)?

NB: I am an application developer, not a DBA nor a system
administrator.

Thanks

Ted

Jan 5 '07 #2
Ted
Hi Marko,

Thanks for your reply. It is appreciated.

MC wrote:
Well, first off, I really dont see the need to divide data in different
databases just for security issues. Permissions granularity should give you
all you need.
I tend to be a bit paranoid about security, at least in applications I
create for others. The benefit I am considering as a potentiality if I
can make it happen, involves the hypothetical circumstance in which
someone has somehow broken through a firewall and into a server. If
the sensitive data is on a different server from that holding the other
data, it will be that much harder for him or her to match up the data
in the two databases, even if he or she manages to find all the
relevant databases on all the relevant servers. It isn't about making
security foolproof, since that isn't possible. It is about making
unauthorized access to it as difficult and as expensive as possible, or
at least as practicable.

I figure that using a diversity of security related practices makes
violating my data harder than if I used just one. I tend not to
blindly trust the security technologies of any one vendour. Instead,
JScript for client side data validation, perl for server side
validation, Java's security library for JSP and servlets, &c.

And yes, I suspect some of what I want to do will necessarily involve
programmaticall y maintaining some relationships in one or two
middleware components.
As far as the referencing stuff goes, you cant add foreign keys across
databases. You can only programatically maintain data consistency. You can
select data from diff databases, but you cant declaratively maintain
consistency.
On some investigation, before I saw your reply, I found a little
information about distributed queries in SELECT statements. While I
found a bunch of pages in the MS SQL Server 2005 documentation (which
is the generation I'm using, and I have the developer's version), I
have yet to find an SQL script example showing how to do it. But,
given what I;'ve have found, I have the question, "Can we use a
distributed SELECT statement in a view?" and "Can we create a foreign
key constraint that references a column in a view?" If the answer to
both is yes, can we not get creative and sort of create a foreign key
constraint referencing data in another database through the services of
a view?

Thanks,

Ted
>
MC

"Ted" <r.*********@ro gers.comwrote in message
news:11******** **************@ 38g2000cwa.goog legroups.com...
I am construvcting a number of databases, some of which contain
sensitive data and most of which do not. I am attempting to handle the
security issues involved in protecting sensitive data in part by
putting it in its own database. If the sensitive data is in a database
called d_SensitiveData , and in that database there is a table called
't_A' (I know, not very informative, but this is only a trivially
simple example :-), and I have a script running in a database
'NotSensitiveDa ta' (i.e. there is a statement at the beginning of the
script "USE NotSensitiveDat a"), how do I referenece the primary key in
table 'A'?

I tried a variety of things like:

ALTER TABLE t_nsX
ADD CONSTRAINT FK_PersonID FOREIGN KEY (p_idPerson)
REFERENCES SensitiveData.t _A (p_idPerson);

The above is, in fact, my latest attempt. Everything I tried has
failed precisely at the point where I specify a table in a different
database. So what is the trick to refering to a table in one database
when using another database?

This will be used in a ASP.NET v3 application where one of the things I
want to do is have the authentication provider be a different database
from the one used for the main application data.

Does anyone know of an example I can download from the web that does
the same sort of thing I want to try, with some discussion of security
issues involved (i.e. what I can do to harden the application and data
server)?

NB: I am an application developer, not a DBA nor a system
administrator.

Thanks

Ted
Jan 5 '07 #3
Ted (r.*********@ro gers.com) writes:
"Can we use a distributed SELECT statement in a view?"
Yes.
and "Can we create a foreign key constraint that references a column in a
view?"
No. Foreign keys are inside the database. To keep cross-database of
cross-server referential integrity, you will have to use triggers.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jan 5 '07 #4
Ted

Erland Sommarskog wrote:
Ted (r.*********@ro gers.com) writes:
"Can we use a distributed SELECT statement in a view?"

Yes.
and "Can we create a foreign key constraint that references a column in a
view?"

No. Foreign keys are inside the database. To keep cross-database of
cross-server referential integrity, you will have to use triggers.

Thanks Erland.

Ted
>
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jan 6 '07 #5
MC
Let me just add something here.
You need to check for performanse before deciding to go that way. If you
need high performance, triggers really need to be tested before you commit
yourself in doing things that way.
MC
"Ted" <r.*********@ro gers.comwrote in message
news:11******** **************@ 51g2000cwl.goog legroups.com...
>
Erland Sommarskog wrote:
>Ted (r.*********@ro gers.com) writes:
"Can we use a distributed SELECT statement in a view?"

Yes.
and "Can we create a foreign key constraint that references a column
in a
view?"

No. Foreign keys are inside the database. To keep cross-database of
cross-server referential integrity, you will have to use triggers.


Thanks Erland.

Ted
>>
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Jan 6 '07 #6
MC (ma************ **@gmail.com) writes:
Let me just add something here.
You need to check for performanse before deciding to go that way. If you
need high performance, triggers really need to be tested before you commit
yourself in doing things that way.
Not to forget that triggers are more difficult to implement than foreign
keys. Not that it's rocket science, but just simply more boring coding
into which sloppy errors easily can creep in.

Also, if there are multiple databases, design for the situation that you
want to run two environments on the same server. Hardcoding database names
defeats that purpose. On SQL 2005 synonyms can be used to address the
problem.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jan 6 '07 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
3551
by: Tim Pascoe | last post by:
I've been trying to get the scripts posted earlier to the group by Clay Beatty to work properly. Using the three components he posted, I have managed to get a new SP generated. However, when I paste this scrip into a new copy of the original database (different name, same structure as a test), I get an Invalid Object Name for the temp table #PersistedVariables. Has anyone run into something like this before? Or used the scripts I...
3
7207
by: Dixie | last post by:
I know how to append records from one table to another in the same database, but I need to be able to append the records from all the tables in one database into new empty tables in another database. The tables in the second database would have the same names as those in the first database. Can this be done and if so how? dixie
2
1278
by: surya | last post by:
hi i have emp table in one database , how to retrieve same table another database through query analizer give code ,,,
3
16653
by: Darrin | last post by:
I've seen a couple threads on this that did not answer my question and I could not add a post to them. I've inherited a web application that is going to be replaced in the coming months. For the time being, I would like to add the Analytics (Urchin) script to the page, but there is no common components for the 100's of pages in the site. There is however a common javascript file that is referenced from all of the pages. I would like...
1
5353
by: colleen1980 | last post by:
I copy a form from another database into my database. I need help how to i link the table which is in another database with that form. Thank you.
12
5461
by: Sean Davis | last post by:
I am working on a simple script to read from one database (oracle) and write to another (postgresql). I retrieve the data from oracle in chunks and drop the data to postgresql continuously. The author of one of the python database clients mentioned that using one thread to retrieve the data from the oracle database and another to insert the data into postgresql with something like a pipe between the two threads might make sense, keeping...
3
30072
by: prashanth023 | last post by:
Hi, Can any body tell that suppose i have member table in database1 I want to insert data in to this table, getting data from another database table suppose database2 table And is it possible ? Thank you.
0
1115
by: vincent90152900 | last post by:
I need to export a database, x, of a server, X, to another database, y, of a server, Y and I need export the database schema only, not include the data. Does anyone know how to do that? Many thanks for replying.
6
2028
ahmedtharwat19
by: ahmedtharwat19 | last post by:
hi every one I hope to Find an Answer at This Qustion Question 1: How Can Create A New Module in CurrentDb or In Another DataBase? Question 2: How Can I Add A New Produce In A New Module i Created or Delete it From That Module? Question 3: I Think After The Answers in Question 1 , 2 We Can Change The Code In Mde File, Is That True?
0
8685
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
1
8905
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8880
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7743
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6532
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
4373
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4625
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3053
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2342
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.