Posted by Ted on 01/05/07 21:50

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
> >

[Back to original message]