Archives of the TeradataForum

Message Posted: Tue, 22 Jul 2014 @ 10:11:15 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hi Dave,

With the AlldatabaseRights, is the subrole process working as expected?

     UNION ALL
     SELECT                          -- GET RIGHTS HELD THROUGH A SUBROLE
          :DATABASENAME AS DATABASENAME
         ,MBRx.GRANTEE AS UR
         ,MBRx.rolename
         ,ROL.TABLENAME
         ,ROL.COLUMNNAME
         ,ROL.ACCESSRIGHT
         ,NULL (CHAR(1)) AS GRANTAUTHORITY
         ,ROL.GRANTORNAME
         ,NULL (CHAR(1)) AS ALLNESSFLAG
         ,NULL (CHAR(1)) AS CREATORNAME
         ,ROL.CREATETIMESTAMP
      FROM DBC.ALLROLERIGHTSV ROL
      JOIN DBC.ROLEMEMBERSV MBRx
        ON MBRx.ROLENAME = ROL.ROLENAME
      WHERE MBRx.GRANTEE IN (SELECT ROLENAME FROM DBC.ALLROLERIGHTSV WHERE DATABASENAME =
     :DATABASENAME)

The following is looking at the other roles that have been granted to the role that has access - MBRx.GRANTEE IN (SELECT ROLENAME FROM DBC.ALLROLERIGHTSV WHERE DATABASENAME = :DATABASENAME).

If I take an example of:

     Grant select on DB1  to Role1
     Grant Role2 to Role1;
     Grant Role3 to Role1;
     Grant Role1 to RoleA;
     Grant Role1 to RoleB;

I'd expect to be reported on RoleA & B, not Role 2 & 3.

Currently it looks to be looking to give me roles that Role1 has been granted (ROLE1 is the grantee/receiver).

     Role1
        Role2
        Role3

So Role 2 and 3 are being reported as Role1 is a grantee - but these have no access to DB1 (only ROLE1 has in this instance).

Shouldn't it be looking for any sub roles that are grantees of the role that has access? e.g. Role1 has select access on DB, give me all sub roles that have been granted Role1.

     RoleA
        Role1

     RoleB
        Role1

So show RoleA & RoleB.

Regards,

Martin Barrow

	<Prev	Next>		<<First	<Prev	Next>	Last>>