SELECT A.ROLENAME, A.GRANTEE, A.GRANTOR, B.DATABASENAME, B.TABLENAME, B.COLUMNNAME, D.ACCESSRIGHT_DESC ACCESSRIGHT, B.GRANTORNAME, C.DEFAULTDATABASE, C.DEFAULTACCOUNT, C.ROLENAME DEFAULTROLENAME, C.PROFILENAME, C.OWNERNAME FROM DBC.ROLEMEMBERS A JOIN DBC.ALLROLERIGHTS B ON A.ROLENAME=B.ROLENAME JOIN DBC.USERS C ON C.USERNAME=A.GRANTEE JOIN DBA.ACCESSRIGHT_REF D ON B.ACCESSRIGHT=D.ACCESSRIGHT ;
Here is where I'm having trouble: How about a query that picks up accessrights directly granted to a user, and accessrights granted through a role (like the query in the post ) and accessrights granted through a sub-role (granteekind=role) for a particular database ?
result set might look like:
accessthru useraccount role subrole database accessright DIRECT user1 N/A N/A testdb1 CT ROLE user1 R_role1 N/A testdb2 R SUBROLE user1 R_role2 R_subr1 testdb3 R SUBROLE user1 R_role2 R_subr2 testdb4 R
where r_subr1 has R on testdb3, r_subr2 has R on testdb4, r_subr1 and r_subr2 were granted to r_role2, and r_role2 was granted to user1 ;
Looks like a recursive SQL statement would do the trick here.
REPLACE MACRO RecursiveRights ( inUserName VARCHAR(30) ) AS ( WITH RECURSIVE rights_query(AccessThru, Username, RoleName, SubRoleName, DatabaseName, AccessRight) AS ( SELECT AccessThru ,:inUserName ,RoleName ,'N/A'(VARCHAR(30)) ,DatabaseName ,AccessRight FROM ( SELECT 'USER'(VARCHAR(30)) AS AccessThru ,'N/A'(VARCHAR(30)) AS RoleName ,DatabaseName ,AccessRight FROM "DBC".AllRights WHERE UserName = :inUserName UNION ALL SELECT 'ROLE'(VARCHAR(30)) AS AccessThru ,DBC.RoleMembers.RoleName AS RoleName ,DatabaseName ,AccessRight FROM "DBC".RoleMembers JOIN "DBC".AllRoleRights ON "DBC".RoleMembers.RoleName = "DBC".AllRoleRights.RoleName AND "DBC".RoleMembers.Grantee = :inUserName ) AS DT1 UNION ALL SELECT 'SUBROLE'(VARCHAR(30)) ,rights_query.UserName ,rights_query.RoleName ,DBC.RoleMembers.RoleName ,"DBC".AllRoleRights.DatabaseName ,"DBC".AllRoleRights.AccessRight FROM "DBC".RoleMembers JOIN "DBC".AllRoleRights ON "DBC".RoleMembers.RoleName = "DBC".AllRoleRights.RoleName JOIN rights_query ON ( "DBC".RoleMembers.Grantee = rights_query.RoleName OR "DBC".RoleMembers.Grantee = rights_query.SubRoleName ) ) SELECT AccessThru ,Username ,RoleName ,SubRoleName ,DatabaseName ,AccessRight FROM rights_query GROUP BY AccessThru ,Username ,RoleName ,SubRoleName ,DatabaseName ,AccessRight ; );
Creates a macro that takes in a UserName parameter. Hope this helps.
If you've got a large number of nested roles, you might want to modify it to restrict from going too far down the path. You should be able to modify the existing macro to filter on a database instead of a user. The same basic approach should work there.
Thanks - my further work with your macro shows if works fine if there are no nested roles. In TD 12 (which we use), I believe roles can nest only one level deep. It shouldn't be possible for circular nesting, correct ? Also, EXPLAIN shows a product join. I'll keep working with it. Thank you again.
Not sure about the product join. It's probably one of the ways the optimizer evaluates the recursive query, but I could be wrong. This macro would also take a while if there are a lot of rights assigned to a user and the roles they belong to. It's not ideal, but it gives you a pretty good picture.