|
|
Posted by Nick Stansbury on 11/01/05 12:58
(snip)
Nick wrote:
> > For security reasons every user of our database system logs into our
> > custom security system all under the *same* sql-server user name (who
> > only has access to a discrete set of stored procedures).
Erland wrote:
There is a net_address column in sysprocesses, but really what you can
> make with that one, I don't know.
Thanks - I'll look at this possibility. I'm not sure if I'm allowed
access to sysprocess with my security rights though.
Erland wrote:
>If you had been using the middle-layer scenario that I mention, the
> middle-layer could have used SET CONTEXT_INFO to set information that you
then could pick up from sysprocesses.context_info.
> But I think the root problem is that you are using general accounts,
> instead of individual accounts. (I don't understand what you mean with
> "we are limited to 3 database users", could you explain that?)
Ok - I wasn't clear enough - I apologise. Let me be very clear. Our hosting
provider has allocated us
3 database logins - and *only* three database logins. So I'm stuck with
this. Here is what I'm trying to achieve.
We have a web-client which is open to the world - anyone can use it to
register themself as a new user and browse our event calendar. We, or a
member of our admin team, allocates these guys various "rights" and this
determines what they can and can't see / edit / delete etc.
We also have a windows client utility for our heavy users - this plugs into
the same framework of SP's etc.
I'm proceeding under the assumption that anyone will have our general
database login name (i.e. anyone could listen in on the traffic between
client and server and get that login) - so what I was hoping to do is this:
1) Client tool (either web system or windows system) logs into DB and opens
connection
2) User logins in (using our LOGIN sp) to database - passing a small part of
his password (like a bank login syste)
3) The Stored Procedure records the IP address of the request, and then
allocates this IP address and user with a unique KEY (a GUID) and returns it
to the client
4) In every additional request made by this user the "Key" is passed back to
the database - and the IP address of the requesting machine is then checked
against the IP address stored in the table - if there is a discrepancy the
request fails and the key is "deactivated" permanently.
But obviously for this to work i need to reliably get the IP address of each
request - *not* just the host_name (because it seems like you can basically
make that up however you want!)
Any further thoughts?
Nick
Navigation:
[Reply to this message]
|