Archives of the TeradataForum

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

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

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>>