July 30, 2003 at 2:33 pm
Hi,
For some reasons, our users can delete record(s) from web site and we have triggers for tracking any deletion. My issue is the web site is connected with DB through System Admin account. So the triggers write down System Admin as USERID and I don’t know real users who deleted record(s). Is there any way to catch up that information?
By the way, ASP has been used to develop our web site.
Thanks a lot.
July 30, 2003 at 4:04 pm
First - you are giving anyone who signs into the database through the web site system admin? That's right up there in the poor practices list.
You have to use IIS to find out who is signing in. SQL Server is a service. It only knows the connection.
Dr. Peter Venkman: Generally you don't see that kind of behavior in a major appliance.
Patrick
Quand on parle du loup, on en voit la queue
July 31, 2003 at 12:04 am
Check the IIS anonymous login setting. Maybe someone did put an overauthorized user-account overthere.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
July 31, 2003 at 2:21 am
If the users are all logging in as 'sa' then you will not by default be able to tell them apart. Also using the 'sa' account in this way is VERY BAD. Even if the users require sysadmin rights (which they shouldn't) they should be loggin in using a different account name to reduce the likely hood of a hack attack. Remeber sysadmins have permission to SELECT,INSERT,DELETE,CREATE and DROP even if you DENY them access.
Try converting the app to use a different login. If the site is on an intranet you can use you existing NT Logins or even better NT Groups.
Alternatively you will need to issue each user there own SQL Login, or use a common login/app. role, and require the users to identify themselves to the site using a custom login page. You can then use this id in your triggers.
Also as a quick not try not to sent passwords to your website in plane text as the can be intercepted using a packet sniffer. Use an encryption API, or Secure Sockets to send autentication details.
July 31, 2003 at 7:09 am
If your web pages are directly accessing the database, as most coding samples in books do, then you should set the connection strings up and trusted connections, and manage your security through SQL.
If your web pages are accessing through an MTS middle tier and you are using impersonation (the recommended approach in this case), you will have to have MTS determine the original caller(there is a property for this) and pass that information back in the delete request.
If you are using MTS but not using impersonation, you can still get the same results as the first instance I outlined, but you lose out on such features as connection pooling, which greatly help scalability.
July 31, 2003 at 12:27 pm
Hi,
I have read your suggestions seriously. But I still have some concerns and like to discuss with you.
1. We use ODBC connection rather than MTS middle tier. It makes the thing a little bit difficult.
2. When users login the web site, our web app checks their identification. And then, they are able to access the DB as ‘sa’. Don’t you think this approach is secure enough?
3. Our users are already members of an existing group. Can I use this group ID to make ODBC connection? How are triggers able to catch individual user ID?
4. It isn’t difficult for me to create individual SQL logins, but in this case, how can I make ODBC connection?
Any ideas?
Thanks for your reply.
July 31, 2003 at 12:37 pm
Re your points:
2. I don't recommend, and think almost everyone who uses this site would agree, NOT to use the sa. If your connection string has to have the password in it, there is no way (that I know) to keep people who don't need to know what that password is from knowing.
If you're app simply looks up user names in tables, it's better than nothing, but hardly tight security. You will have to weigh the risks versus the importance of keeping the information secure.
3. Can't use a group to make a connection(i.e. it can't be in the connection string). Triggers can catch the user id by ensuring the tables have a user id field. The user id field is inserted and available in a trigger via the inserted table.
4. Not exactly sure what you mean, but if you mean how do you know whose logged in, then I don't have first hand advice, because we don't do it this way. I have thought that if I had to do it that way, I'd created a login screen into which the user entered their SQL login and password, and which hid itself for the duration of the app. If a web app, you could store the information in a cookie or something called a data island. I don't know too much about web pages; just pick up a few things from the web developers.
July 31, 2003 at 1:45 pm
I agree with you about ‘sa’ account. My question is if I don’t use ‘sa’ to connect ODBC, which account should I use?
I am thinking your suggestion that tables have a user id field, because there are already sort of field and it doesn’t cost me too much.
Thank you very much.
July 31, 2003 at 1:47 pm
quote:
I agree with you about ‘sa’ account. My question is if I don’t use ‘sa’ to connect ODBC, which account should I use?
Can't you add your own user SQL User login?
July 31, 2003 at 1:58 pm
Yes, of course. But what do you mean? I didn't get it this time. Sorry.
July 31, 2003 at 2:01 pm
Create the SQL Account with a password. Alter the connection string, substituting sa with the new account name, and substituting the sa password with the password for the new account.
July 31, 2003 at 2:07 pm
Unbelievable. Is it so easy?
Well, appreciate your advice.
August 1, 2003 at 11:35 am
And also only give the new account specific rights. Do not give it the same as sa.
On the production servers we only give the web users rights to execute stored procedures. That's it. They cannot create new ones, scan tables, etc.
Try to give the new account the least amount of privileges so that they can do their work, but that's all.
Dr. Peter Venkman: Generally you don't see that kind of behavior in a major appliance.
Patrick
Quand on parle du loup, on en voit la queue
August 5, 2003 at 2:59 am
You say that the users are a member of an existing group. Do you mean that they are a member of an NT Security Group? If so add an NT Login for that group and change your ODBC connection to use Windows NT Authentication. You will then need to set the permissions for this Group in SQL Server, or add it to an existing Role. Following this you will be able to get the users' NT Login name using the SUSER_SNAME() function (SQL 7/2000) in the trigger.
-Ed
August 5, 2003 at 2:51 pm
Good idea, I'll try this way.
Thank you very much!
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply