Nested roles

Database
Highlighted
Enthusiast

Nested roles

Hi... I am looking for a query or macro that will produce a result that that contains a 'parent' rolename, the names of roles nested under that role name, and the databases and access rights ultimately granted to the 'parent' role.

 

Any help is appreciated.

1 REPLY 1
Teradata Employee

Re: Nested roles

Something like this?

SELECT p.RoleName AS ParentRole, n.RoleName AS NestedRole, a.DatabaseName, a.TableName, a.AccessRight
FROM dbc.RoleMembersV p
INNER JOIN dbc.RoleMembersV n
ON p.Grantee = n.RoleName AND p.GranteeKind='Role'
INNER JOIN dbc.AllRoleRightsV a
ON a.RoleName = n.RoleName
-- Include rights granted directly to the parent role
UNION ALL
SELECT p.RoleName AS ParentRole, '' AS NestedRole, a.DatabaseName, a.TableName, a.AccessRight
FROM (SELECT DISTINCT RoleName from dbc.RoleMembersV WHERE GranteeKind='Role') p
INNER JOIN dbc.AllRoleRightsV a ON a.RoleName = p.RoleName