How can users abort their own queries without being able to abort anyone else's?

Viewpoint
Enthusiast

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?

Tags (1)
16 REPLIES
Teradata Employee

Re: How can users abort their own queries without being able to abort anyone else's?

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.

Enthusiast

Re: How can users abort their own queries without being able to abort anyone else's?

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?

Teradata Employee

Re: How can users abort their own queries without being able to abort anyone else's?

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

Re: How can users abort their own queries without being able to abort anyone else's?

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%';

)

Re: How can users abort their own queries without being able to abort anyone else's?

How can I know the user who has killed my session?

Is there any query or mechanism to find this?

Thanks

Junior Contributor

Re: How can users abort their own queries without being able to abort anyone else's?

There recently was a thread on this topic on the TeradataForum:

http://www.teradataforum.com/teradata/20130606_124329.htm

Dieter

Enthusiast

Re: How can users abort their own queries without being able to abort anyone else's?

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

Enthusiast

Re: How can users abort their own queries without being able to abort anyone else's?

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?

Enthusiast

Re: How can users abort their own queries without being able to abort anyone else's?

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