Home Page for the TeradataForum
 

Archives of the TeradataForum

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


     
  <Prev Next>   <<First <Prev
Next>
Last>>
 


Subj:   Re: Only Directly Granted Rights Displayed in SQL Administrator 13.10.0.03
 
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'
     UNION
     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
Next>
Last>>
 
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016