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
'NotSensitiveData' (i.e. there is a statement at the beginning of the
script "USE NotSensitiveData"), 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

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@rogers.com> wrote in message 
news:1168028487.999025.302010@38g2000cwa.googlegroups.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
> 'NotSensitiveData' (i.e. there is a statement at the beginning of the
> script "USE NotSensitiveData"), 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
> 

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
programmatically 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@rogers.com> wrote in message
> news:1168028487.999025.302010@38g2000cwa.googlegroups.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
> > 'NotSensitiveData' (i.e. there is a statement at the beginning of the
> > script "USE NotSensitiveData"), 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 (r.ted.byers@rogers.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@sommarskog.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

Erland Sommarskog wrote:
> Ted (r.ted.byers@rogers.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@sommarskog.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

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@rogers.com> wrote in message 
news:1168054494.981787.125540@51g2000cwl.googlegroups.com...
>
> Erland Sommarskog wrote:
>> Ted (r.ted.byers@rogers.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@sommarskog.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
> 

MC (marko.NOSPAMculo@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@sommarskog.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