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

[MC]

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

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.ted.byers@ro gers.comwrote in message
news:1168028487 .999025.302010@ 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
>

[Ted]

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

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.ted.byers@ro gers.comwrote in message
news:1168028487 .999025.302010@ 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

[Erland Sommarskog]

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

Ted (r.ted.byers@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, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Ted]

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


Erland Sommarskog wrote:

Ted (r.ted.byers@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, esquel@sommarsk og.se
>
Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

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

[MC]

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

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.ted.byers@ro gers.comwrote in message
news:1168054494 .981787.125540@ 51g2000cwl.goog legroups.com...

>
Erland Sommarskog wrote:

>Ted (r.ted.byers@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, esquel@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

>

[Erland Sommarskog]

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

MC (marko.NOSPAMcu lo@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, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx