How To Find All Access Rights In Teradata

The problem with the table DBC.AccessRights is that the data is not something easily readable.  Many of the columns use a 4-byte IDs.

DBC.AccessRights

By joining a couple of tables to the DBC.AccessRights table, the information brought back is more meaningful.  Also by adding a couple of predicates in a where clause will help isolate this information to the desired rights on a user, database, or what ever is being searched for.

Meaningfull Access Rights

sel    
      UI.DatabaseNameI as "UserName",
      DI.DatabaseNameI as "DatabaseName",
      TI.TVMNameI as "ObjectName",
      TI.TableKind as "ObjectType",
      AR.FieldId,
      AR.AccessRight,
      AR.WithGrant,
      GI.DatabaseNameI as "GrantorName",
      AR.AllnessFlag,
      CI.DatabaseNameI as "CreatorName",
      AR.CreateTimeStamp,
     AR.LastAccessTimeStamp,
     AR.AccessCount
from DBC.AccessRights AR
inner join dbc.dbase UI 
    on AR.UserId = UI.DATABASEID
inner join dbc.dbase DI 
    on AR.DatabaseId = DI.DATABASEID
inner join dbc.dbase GI 
    on AR.GrantorID = GI.DATABASEID
inner join dbc.dbase CI 
    on AR.CreateUID = CI.DATABASEID
inner join dbc.tvm TI 
    on AR.TVMId = TI.TVMId
order by 2;

Leave a Comment