SET OPTION PUBLIC.disconnect_procedure

[JimDiaz]

Version 12.0.1

Does something like this exist in which I can determine the User disconnecting. A before of after would be fine.

Thanks,

Jim

[Breck_Carter]

CREATE EVENT ... TYPE "Disconnect" with code that calls EVENT_PARAMETER ( "ConnectionID' ) might do the trick.

[JimDiaz]

Thanks I'll give this a try. Maybe you've been down this road and can save me some time.

I've created a login_procedure which will raiseerror 28000 when anyone with dba access attempts to connect to a remote database. But then for our technical support personnel I've created a procedure which will grant dba access to the current user that is a member of a specific group as well as turn certain auditing on both sybase as well as our own. This actually is working very well.

Now I want to automatically revoke DBA and turn off this auditing when the user disconnects.

Thanks again

Jim

[VolkerBarth]

FWIW, here's a simple event body - we have not tried to revoke privileges but simply to "log" orphaned connections for web users who had not explicitly logged off (therefore we check for the disconnect reason, too)...

create event EV_Disconnect
type "Disconnect"
handler
begin
   declare nConnId int;
   declare strUser varchar(128);
   declare strDisconnectReason varchar(128);
   set nConnId = event_parameter('ConnectionID');
   set strUser = event_parameter('User');
   set strDisconnectReason = event_parameter('DisconnectReason');

   message '   EV_Disconnect for Connection ' || nConnId || ' and user  ' || strUser
      || ' with reason ' || strDisconnectReason || '.';

   -- notify orphaned connections as disconnected
   if strUser in ('...', '...') and strDisconnectReason in ('abnormal', 'inactive') then
      ... -- log user as disconnected in our own "connect log"
   end if;
end;

In your case, I guess you would have to check whether there are still other connections for the according database user/group (say, by calling sa_conn_info and check whether the UserId column does contain the desired "strUser") - and if not, revoke the privilege and reset the options. And you might usually ignore the disconnect reasons as all would matter.

Note: As events have no real owner it's important to create them under a DBA account itself.

[JimDiaz]

I've completed these modifications and our testing this is working very well. Thanks for all your help.

Jim