Link to home
Start Free TrialLog in
Avatar of JackieLee
JackieLee

asked on

Accessing Application User Name From Trigger

I have a trigger that generates a history when a record is inserted, updated or deleted.  I can get all of the information I need from the inserted table except for the username.

What is the best way to parse the user name to the trigger.  Some ideas I have are:

1) create a table to store current users and their Process IDs so I can determine which user is the user making the update from the id and pull the information from their
2) create a field in my table telling me the most recent user to update the record.  Update this field any time I update the record and retrieve it from the inserted table in the trigger.

Are there any better ways of doing this?  What would you recommend?
Avatar of JackieLee
JackieLee

ASKER

Note I am not talking about the SQL server or NT user names.  This is a username of the user who logged into the application.  We use an application role for the application to log in.
Why don't you pass your "internal" user name when they do the update...
Avatar of Scott Pletcher
Use:

SUSER_SNAME()

This  may require first storing the userId doing the update in a temp table, then retrieving that value from the temp in the trigger.

For example:

DECLARE @userId INT
SET @userId = (SELECT userId FROM #user)
INSERT INTO log (col1, col2, ..., updatedBy)
SELECT col1, col2, ..., SUSER_SNAME(@userId)
FROM inserted
Of course you could just store the SUSER_SNAME(userId) in the temp table also :-)
ASKER CERTIFIED SOLUTION
Avatar of namasi_navaretnam
namasi_navaretnam
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
arbert,
>Why don't you pass your "internal" user name when they do the update...
That's what I suggested in my second point.

Great idea namasi.  I never would have thought of that.

Regards,

Jackie Lee
MCSD
Because then it's very easy to "spoof" (fool) your security.  If someone ones to damage data on purpose, they could set the host name to "Mickey Mouse" or the equivalent and you would have no idea who did the damage.  My preference is to use a system-generated value w/in a trigger so that it cannot be spoofed.
TYPO: should be

"If someone wants to ..."

[not "If someone ones to ..."]
I tend to agree--use the variable for it's intended purpose.  You know the user name from within your application--pass it to your update routine.
Scott,

I disagree.  The developer sets this property internally in the application, how risky can that be?  Further, if this is a web application what use is knowing that HostName() = "somewebserver"  caused a problem. I already know (or should know) what server(s) my application is running on.  My point, is that without setting this property to something meaningful it is totally useless.  

As you can tell, we use that approach here at my instigation, so I am interested in knowing valid reasons why this is not a good idea.  I should also point out that the SQL Servers are behind a firewall and the only way of accessing them externally is through the web applications.

So how does that property get set when going thru, say, QA?  Yes, I know, QA should be blocked and all access done thru SPs, but sometimes that is just not the case, especially for limited periods of time for specific reasons, for example, errors must be corrected quickly so someone is granted access.  And of course there is always the possibility of a malicious/digruntled developer, which, according to security experts, is the most likely source of serious data damage for most companies.

In my view, if you want genuine identification, you have to use a system-controlled value within a tightly-controlled trigger in the db, not any code external to the db, which is easier to "fudge".
>>So how does that property get set when going thru, say, QA?<<
It does not.  That is just the point.  In that case you want to know what is the Host_Name().  In that case the fact that I just changed some row arbitrarily (or maliciously) form my workstation needs to be known.  But (and in our case) when the web application is running all we get is some web server name for Host_Name().  This is not very helpful.

>>Yes, I know, QA should be blocked and all access done thru SPs<<
This is the way it is done here.  But if we went the route of passing in the user name as a parameter that would be a way of bypassing security.  I mean there would be nothing to stop someone writing:
exec usp_Whatever @parameter1, @parameter2, @UserName = "Mickey Mouse"

One way that it could be bypassed would be to create an application that sets the "Workstation ID" property, but this would seem to me less likely than calling the stored procedure directly with the username as a parameter.

In a sense the reason I bring this up is because I agree with you from a purist point of view that it is a kludge, but it would seem to be the lesser of all evils.

In any case, as always I appreciate your input and I trust I do not sound too argumentative, at least not this time :)
We never alter the host_name property, but in our web applications, we do add the user name to the Application Name property so it shows up in Profiler or any of the other tools....
But any number of people could be using that workstation (ws)?  How does knowing the ws, but *not* the user name, help me?

Please note that I referenced SUSER_SNAME(), not HOST_NAME().  SUSER_SNAME() is not set by any user, but by SQL (in conjuction with Windows, if needed, I suppose).  I don't object to capturing HOST_NAME() *also* if you also want to know the ws, but over time people also move from place to place, get new hardware, etc., so ws seems of little help to me (?!).
No, I certainly *don't* feel it's an argument in any sense.  I enjoy the discussion.  There are a very limited number of people on the planet you can have an intelligent discussion about this with, so I actually enjoy being able to do it.  And I don't feel it's thread "pollution" in the traditional snese, since the requestor has already accepted an answer and thus hopefully had his/her issue resolved.
Arbert,

>>we do add the user name to the Application Name property so it shows up in Profiler or any of the other tools.... <<
I will check this out.

Scott,

>>But any number of people could be using that workstation (ws)? <<
We are all responsible for our own workstations.  If we walk away from our workstation and don't lock it, we are equally guilty.

>>but over time people also move from place to place, get new hardware<<
True.

>>How does knowing the ws, but *not* the user name, help me?<<
It depends.  From an ad-hoc query it is better than nothing.  From our web applications, I agree with you, that is why we assign it the User Name logged in.  (I am sure you know this, but just in case :) Setting the (optional) Workstations ID property is picked up by the Host_Name function)

Well you have certainly given me something to think about.  Thanks for the input.
Hi All,

I shouldn't have accepted this answer without testing it first.  I've only just now had a chance to use this and it doesn't work at all.  The host_name function only provides the windows username and has nothing to do with the workstation property.

Just thought I should say that in the interest of the forum.
Actually, I take it back.  I managed to make it work.  I hadn't set the workstation property properly.  What you have to do is set the workstation id property as so (just add this to the end of your connection string):

workstation id=MyUserName

then when you call the 'Host_Name()' function from the same connection it will return the suername that you input. This is great.  Appologies for saying it doesn't work.

Jackie
>>What you have to do is set the workstation id property as so (just add this to the end of your connection string)<<
Exactly.  That is precisely what namasi_navaretnam had stated: "Why not set workstation propery of the connection to the user name when you connect from the application."

Glad you got it working.
For some reason, I did not get mails from this thread previously. An interesting debate had occured... It is always good to learn some new stuff..

Regards,

Namasi Navaretnam