Archives of the TeradataForum
Message Posted: Fri, 28 Jun 2013 @ 20:25:16 GMT
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.
|Copyright 2016 - All Rights Reserved|
|Last Modified: 24 Jul 2020|