Using a 2nd SQL server instance with linked views to a 1st sql server is no more secure than having a data source directly to the 1st server.
In terms of the 1st (the secure) SQL instance - its data is being queried under a given user account that the 2nd instance has been given say (non_secure_user)
so when im configuring my secure server im giving select only permissions to views X, Y, Z to non_secure_user
So as far as the 1st server is concerned if its another linked server or Coldfusion conneting via JDBC its just another client.
I know *some* sysadmins are a bit thick (someone does a 6 week mcse course and they think they know everything) , but hopefully they can understand this logic and just let you query the DB directly.
Pat
On 7/6/06, grant <[EMAIL PROTECTED]> wrote:
ok thanks guys - i resent the impilication it's a dailywtf though scott!
anyway, you've basically clarified the issue for me. I am now armed to
go back to my boss and say, we can do it this way...
as for passing in parameters, yes you can query against a view with
parameters (as per dale's example above), but you can't actually call
a view like you do with a stored proc where you actually *pass* the
parameters to the proc. that's all i meant.
it may seem a little stupid (why not just use stored procs) but it all
comes down to linked views - you can't link stored procs from one
server to another, but you can with views. we have a view on the
secure server that gets all data relevant to my site, and that view is
linked to a view on a non secure server that cf has access to. the cf
query pares back the initial dataset with parameters, but what i am
after is an abstracted way to query the secure db with those
parameters initally. did that make sense?
it looks a little like this:
secure sql server view (which is linked to the non-secure server):
SELECT *
FROM db1.tblclients
...which returns a huge resultset.
non-secure sql server view:
SELECT *
FROM db1.the_secure_view_above
and the coldfusion query:
SELECT *
FROM db2.the_non_secure_view_above
WHERE clientid = '666'
and ultimately what i'd like to be doing in CF is:
SELECT *
FROM db1.the_secure_view_above
WHERE clientid = '666'
but unfortunately the ppl in control here don't want me accessing
db1(secure) directly. which let me to the initial question. and this
following clarifaction.
anyway, whatever, we'll get it sorted. i'm off to get my dose of
thedailywtf..........
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "cfaussie" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at http://groups.google.com/group/cfaussie
-~----------~----~----~----~------~----~------~--~---
- [cfaussie] Re: ot: db architecture question Patrick Branley
