SQL returns Warning: 5626 "An invalid role identifier is found in DBC.RoleGrants table" WrittenSep 27, 2017


Product Categories
Teradata Database

A SQL returns Warning: 5626 "An invalid role identifier is found in DBC.RoleGrants table".
Sample SQL and output:
 BTEQ -- Enter your SQL request or BTEQ command:
help database db1;
 *** Help information returned. 1 rows.
 *** Warning: 5626 An invalid role identifier is found in DBC.RoleGrants table.

Table/View/Macro name          Kind Comment
------------------------------ ---- ---------------------------------------
t1                             T    ?
This sample SQL(HELP DATABASE) is just an example and 5626 warning can be returned for any SQL.

TDBMS 15.10

There are 2 possible causes:
1) DBC.RoleGrants has a role identifier that does not have a corresponding entry in DBC.Roles.
    (There is left-over entry in DBC.RoleGrants table)
2) role is dropped during SQL execution
   Below is sample scenario for case 2:
   e.g. role1 is assigned to user1.  user1 runs "Select * From DB1.Tbl1;".  
         2-1) we retrieve RoleId of role1 from DBC.RoleGrants
         2-2) role1 is dropped by other request
         2-3) we try to retrieve row from DBC.Roles using RoleId
         2-4) Since the RoleId has been already dropped, 5626 warning is returned
                (But SELECT works fine as user1 itself or other role has SELECT right on DB1 )

SQL which returned 5626 warning did not get error/failure so no remedy is needed for the SQL.
Please run SQL below to review if there is left-over RoleId in DBC.RoleGrants.
Select u.databasename "UserName", rg.roleID as "InvalidRoleID", rg.CreateTimeStamp, count(ar.accessright)
From DBC.Dbase u
Inner Join
  DBC.RoleGrants rg
  On u.databaseID = rg.GranteeID
Left outer join
  DBC.AccessRights ar
  On rg.roleID = ar.userid
  rg.roleID not in (Select roleID From DBC.Roles)
Group By 1,2,3
/* Sample output */
UserName        InvalidRoleID      CreateTimeStamp  Count(AccessRight)
--------------  -------------  -------------------  ------------------
DBC             0000D334       2017-09-11 17:32:26                   0
DBC             0000D834       2017-09-11 17:32:27                   1
DBC             00008B27       2017-09-11 17:25:29                   0
If there is invalid roleId, please engage GSO/DBS to clear the left-over entries.
If no rows returned, no action is needed.

Special Considerations

Additional Information
Invalid RoleId is logged in /var/log/messages when 5626 warning is returned.
lnx2092:~ # psh 'grep "Invalid RoleId" /var/log/messages'
<---------------------  localhost  -------------------------------->
Sep 26 21:25:58 lnx2092 Teradata[27545]: Invalid RoleId - 13523
     <<<<< RoleId: 13523 == 0x34D3 in hex
Note RoleId is byte-flipped in system table so you need to flip bytes when you query using roleID.
e.g.  RoleId is reported as 13523 in /var/log/messages
 1) 13523 == 0x34D3 in hex
 2) Flip bytes:  34D3   >>>>> D334
 3) Run query using flipped roleID
Select * From DBC.RoleGrants Where RoleID='0000D334'xb;  
 *** Query completed. One row found. 6 columns returned.
GranteeId  GranteeKind  RoleId    WithAdmin  GrantorId      CreateTimeStamp
---------  -----------  --------  ---------  ---------  -------------------
00000100   U            0000D334  Y          00000100   2017-09-11 17:32:26