Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Fri, 28 Jun 2013 @ 20:25:16 GMT

  <Prev Next>   <<First <Prev

Subj:   Re: Only Directly Granted Rights Displayed in SQL Administrator
From:   Dempsey, Mike

Teradata Administrator does show you both Direct and Role rights at the table level but it only shows Direct rights at the database level.

I guess no one specifically asked for it at the database level (as they did at the table level) ... and for some reason I didn't think of it when I implemented the change for tables.

However most of the SQL used in the app (including all the 'reports') can be customized by the user.

I suggest taking a backup copy before you play with it but the SQL is in a file called SQL\winddi.def (under the directory you installed the product into)

Note - if you use the X views the file is called winddix.def.

The specific item you would need to change starts 'S6=' (The line for table level rights is 'S21='. There is a comment line that, rather cryptically, tells you this.)

Note that the file contains separate sections for each major database version. Eg [14.0], [13.0], etc. so you must change the section that corresponds to the database version you are connecting to.

Basing the change on what is used in the table level report I think you are looking for:

     WITH Parms (DbName) AS (SELECT ?)
     SELECT UserName,AccessRight,GrantAuthority(CHAR(4)),GrantorName,AllnessFlag

     FROM dbc.AllRightsV,Parms
     WHERE DatabaseName=Parms.DbName
       AND TableName='All'
     SELECT RoleName,AccessRight,'Role',GrantorName,''
     FROM dbc.AllRoleRightsV,Parms
     WHERE DatabaseName=Parms.DbName
       AND TableName='All'
     ORDER BY 1,2

(all on one line and following 'S6=')

Note that this will show you rights for both users and roles but it will not show you which users have rights by way of membership in a role... which may be what you are looking for.

That would require an additional join to the RoleMembersV view in the second select.

Mike Dempsey

  <Prev Next>   <<First <Prev
  Top Home Privacy Feedback  
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023