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.*********@rogers.comwrote in message
news:11**********************@38g2000cwa.googlegro ups.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
