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

asked 08 Feb '15, 14:40

J%20Diaz's gravatar image

J Diaz
830243044
accept rate: 14%


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

permanent link

answered 08 Feb '15, 14:55

Breck%20Carter's gravatar image

Breck Carter
27.1k424582831
accept rate: 21%

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

(08 Feb '15, 15:21) J Diaz
Replies hidden

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.

(09 Feb '15, 03:35) Volker Barth

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

Jim

(09 Feb '15, 18:33) J Diaz
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×13

question asked: 08 Feb '15, 14:40

question was seen: 363 times

last updated: 09 Feb '15, 18:33