SQL Server, and, I believe, Sybase, have the SETUSER command. But it can only be issued by sysadmin or dbo, cannot be used with SQL Server/NT integrated security, and resets if the current database is changed with the USE command. It also has a WITH NORESET option that prohibits reseting to the sysadmin or dbo. It's usually used by sysadmins in scripts (but is not required) to create objects as impersonated users. Oracle admins use the CONNECT command in scripts for the same reason. Their overhead and limitations make them less than useful in connection pooling scenarios, you might as well not pool connections.
Bob Beauchemin [EMAIL PROTECTED] -----Original Message----- From: ChongQing Xiao [mailto:[EMAIL PROTECTED]] Sent: Friday, May 10, 2002 1:26 PM To: [EMAIL PROTECTED] Subject: Re: [DOTNET] question about connection pool I agree the application level audit can have much richer and finer control than DB level audit but 1. DB level audit will be faster since it doesn't need another round trip of db call. 2. For general audit purpose (say update record, delete record), using trigger in db level will be easiler. I will be very interseted in either sql server or oracle to support changing the user in the fly for the same connection as Bob mentioned in previous post. Chong -----Original Message----- From: Marsh, Drew [mailto:[EMAIL PROTECTED]] Sent: Friday, May 10, 2002 3:13 PM To: [EMAIL PROTECTED] Subject: Re: [DOTNET] question about connection pool Beauchemin, Bob [mailto:[EMAIL PROTECTED]] wrote: > Correct, but that behavior, (not being able to reset > credentials) is a "database implementation detail". ;-) True. > Imagine if SQL Server could reset credentials, in a manner > analogous to how sp_reset_connection works today. Then it > would be possible to write a connection pooling > implementation that works the way ChongQing wants it to. It > doesn't, of course, work that way today. I was actually curious if MSSQL2k supported changing user on the fly, but a quick search before I replied originally didn't turn up anything. > OTOH, reset of credentials (and flushing user specific > database objects, etc) might require enough database > "bandwidth" that it would invalidate the cost-saving (which > considers session setup overhead in addition to other items) > of pooling. Yep, would all depend on the implementation. FWIW, I agree that the auditing should be handled at the application level, not by the DB. All access should be done through a single DB user with restricted rights that meet the needs of all end users of the application and finer grained control should be applied using some other form of authentication. Later, Drew You can read messages from the DOTNET archive, unsubscribe from DOTNET, or subscribe to other DevelopMentor lists at http://discuss.develop.com. You can read messages from the DOTNET archive, unsubscribe from DOTNET, or subscribe to other DevelopMentor lists at http://discuss.develop.com. You can read messages from the DOTNET archive, unsubscribe from DOTNET, or subscribe to other DevelopMentor lists at http://discuss.develop.com.
