All Forums Viewpoint
Jeanne-Anne 5 posts Joined 09/05
19 Dec 2011
How can users abort their own queries without being able to abort anyone else's?

I have power users to whom I have given Viewpoint access. I want them to be able to abort their own queries, but I do not want to grant them a general ability to abort queries. I have activated for them the "abort query" priv in the My Queries portlet, but it won't allow them to abort when it connects to the database because I have not granted them abortsession.

Does anyone have a general solution for this?

stever 149 posts Joined 08/09
19 Dec 2011

This would be the recommended way to configure individual users to abort queries via Viewpoint.  By granting the abort query permission in the My Queries portlet, you've obviously restricted the queries that the user will have the ability to abort.  As you mentioned, you do need to grant the user the ability to abort sessions on Teradata.  Viewpoint forces a user to log in to Teradata to abort queries so that there's an audit trail on the database of the abort occurring.  If the abort were to be accomplished via a generic user account, then this audit trail would be far less valuable.

Maybe some other Viewpoint users can comment on what permissions they have granted on Teradata to allow this level of aborting individual user queries.

dngrlfriday 9 posts Joined 03/11
09 Feb 2012

I want to be able to grant abort session rights on a few IDs to a set of users.  These users want to be able to kill sessions that load IDs are running, so they would not be using the My Queries protlet, but the Query Monitor portlet.  Is there any way to grant access like that?

 

stever 149 posts Joined 08/09
10 Feb 2012

Check out the new Query Groups feature that's part of the Viewpoint 13.12 release.  This should meet your needs.

http://developer.teradata.com/viewpoint/articles/teradata-viewpoint-13-12-released#qgroups

garyadmin3 3 posts Joined 11/11
28 Mar 2012

we didn't want to grant ABORTSESSION to many users, so we have a macro using the APIs that could be modified to what you need, this just targets and aborts sessions with a certain string in the username, and are blocking other users-  but could be modified to look for something that identifes your group of  users 

Users don't have exec access to syslib,but they can execute macros we put in our PUBLIC_AREA database  -so we have control over access to what can be run (NB public_area has to be given the EXEC macro privilege to syslib with grant option) as DBAs control what goes in this database

 

 

create macro PUBLIC_AREA.Abort_blocking_XXX as

(

SELECT AbortSessions (HostId, UserName, SessionNo, 'Y', 'Y')

from   TABLE (MonitorSession(-1, '*', 0)) AS t1

where  sessionno in 

(SEL  

blk1sessno  

FROM TABLE (MonitorSession(-1,'*',0)) AS t1

WHERE Blk1UserId > 0)

and Blk1UserId = 0

and  username like  '%xxx%';

 

)

ke186006 3 posts Joined 07/13
17 Jul 2013

How can I know the user who has killed my session?
Is there any query or mechanism to find this?
 
Thanks

dnoeth 4628 posts Joined 11/04
19 Jul 2013

There recently was a thread on this topic on the TeradataForum:
http://www.teradataforum.com/teradata/20130606_124329.htm
 
Dieter

Dieter

StevenSchmid 33 posts Joined 07/11
30 Sep 2013

Hi
Back to the original post, is there a way in Viewpoint/RDBMS a user can be given abort session access to their own sessions.  From what I can see, the user needs to be assigned the portlet permissions in Viewpoint which is easy enough to setup, but they also need to have monitor privileges with abort session.  If a user is restricted to the My Queries or My Query Groups portlets (ie. not Query Monitor) and has the abort session privilege set in the RDBMS, then this will be ok for viewpoint, however if the user then logs onto SQL Assistant, then can issue an abort session for any other session.
I created a macro like garyadmin3 mentioned above which would only abort a user's own queries, which would allow them to abort their query but not with the nice viewpoint drop down menu option.  I have used a macro below that just shows the sessions the user has active, so they can run this before they execute the abort macro.  The problem however is that the user can execute this macro in SQL Assistant, but not in the SQL scratch pad on Viewpoint, just returns an empty result screen (same user logged on to SQLA and Viewpoint).  The macro makes use of the user variable, which works in the SQL scratch pad if I execute "select user;".  I can also run the contents of the macro, however I need to grant the MonitorSeesion privilege, which is not an option as mentioned earlier.
Replace macro Sysdba.Show_Sessions
as
(
SEL
Username
,SessionNo
,AMPState
,LogonSource
FROM TABLE (MonitorSession(-1,'*',0)) AS t1
WHERE UserName like user || '%'
;
);
 
The following abort session macro can be executed from SQLA and Viewpoint, not sure why the Show_Sessions can't ?
 
Replace macro Sysdba.Abort_Session (SessNo integer)
as
(
SELECT AbortSessions (HostId, UserName, SessionNo, 'Y', 'Y')
from TABLE (MonitorSession(-1, '*', 0)) AS t1
where sessionno in
(SEL
SessionNo
FROM TABLE (MonitorSession(-1,'*',0)) AS t1
WHERE UserName like user || '%'
and SessionNo = :SessNo
);
);
 
Cheers
Steven
 

Steven Schmid
Teradata DBA
Canberra, Australia

Glass 225 posts Joined 04/10
01 Oct 2013

Steven,
Please elaborate
"however if the user then logs onto SQL Assistant, then can issue an abort session for any other session."
I only use console or viewpoint to abort sessions.
What Sql command would you use?

StevenSchmid 33 posts Joined 07/11
01 Oct 2013

Hi Glass
Yes you are right, the user also needs access to the syslib.abortsessions function to run from SQLA.
Therefore is it safe to "grant abortsession" to users, knowing that they can only abort sessions using viewpoint if they have the abort query option enabled ?
Unfortunately we can't use the My Queries portlet as the LDAP user account is abc123 for example, but this maps to a Teradata account of abc123_ldap.  We can however use the Query Groups portlet with abort session enabled, however I will need to set this up for user groups.
Any reason why my Show_sessions macro does not work in SQL scratch pad, but does work in SQLA ?
Cheers
Steven

Steven Schmid
Teradata DBA
Canberra, Australia

Glass 225 posts Joined 04/10
02 Oct 2013

Steven,
 
I tried you show_sessions in SQL scratch Pad and it worked.
I'm on VP V14.01.00.00-b29
DB Release 13.10.06
 
Robert Glass

StevenSchmid 33 posts Joined 07/11
03 Oct 2013

Hi Robert
That's weard.
We are on 13.10.06.03, but viewpoint 14.00.00.00-b9
I have tried our DEV an PRD systems with the same result.  If I try with the Teradata admin account (not LDAP), still get null output.
I ran with the LDAP user on SQLA (2 rows returned) and Viewpoint (no rows).  When I check the query logs, both queries were successful, the SQLA query shows NumResultRows as 2 rows which is correct, and the viewpoint query shows as 1 row, however result was null.   I even tried changing the macro to just return the username and session number, but same result.
Steve

Steven Schmid
Teradata DBA
Canberra, Australia

StevenSchmid 33 posts Joined 07/11
03 Oct 2013

Sorry my bad, checked the wrong DBQL system, both report as 2 rows returned, but Viewpoint doesn't display anything

Steven Schmid
Teradata DBA
Canberra, Australia

29 Jul 2014

Hello,
Does anyone know SQL to cancel query execution?
I tried using Statement.cancel() from JDBC API, but it works when server receives signal, Most of times it takes longer(minutes) to cancel.
I am using Teradata v13.
Thanks for your help in advance.
Thanks,
Vasu T

krishaneesh 140 posts Joined 04/13
29 Jul 2014
SELECT AbortSessions (1, 'username', 0, 'Y', 'Y');

 

tomnolan 594 posts Joined 01/08
30 Jul 2014

Regarding this:
I tried using Statement.cancel() from JDBC API, but it works when server receives signal, Most of times it takes longer(minutes) to cancel.
 
 
Please keep in mind that when you call Statement.cancel, your open transaction will be rolled back.
 
So the Statement.cancel operation typically completes quickly, but the associated rollback operation may take a long time, depending on what work had been pending in the transaction.
 

24 May 2015

We're experimenting with allowing users to abort their own queries in Viewpoint. So far, we've:
1. granted abortsessions
2. revoked execute on syslib.abortsessions
3. and locked down Abort to the MyQueries Viewpoint portlet
Now we'd like an audit trail. I checked out the above post on this, but DBC.Software_Event_LogV only has info on when, not who aborted a session; just a generic"3265: Transaction has been Aborted by Administrator or Operations"
Is there another table recording who submitted the abort request?

Damo

You must sign in to leave a comment.