replace macro AllUserRightsV (UserName varchar(128) character set unicode) as ( /* List rights held by a user as direct grants or through roles. -- 2014-05-21 Updated to include TD 15.00 AccessRights values. -- 2013-04-10 Changed to correct the display of the UserName column. -- Also changed the title of some columns to describe their content more accurately. -- The original was developed by a Teradata PS consultant -- This version updated and maintained by Ward Analytics Ltd 2013-2014 -- You are free to use and redistribute this code but please leave this header here. */ locking row for access select UserName (varchar(128)) ,AccessType (varchar(128)) as AccessType_Or_Child_RoleName ,RoleName (varchar(128)) as Parent_RoleName ,DatabaseName (varchar(128)) ,TableName (varchar(128)) ,ColumnName (varchar(128)) ,AccessRight ,case when accessright = 'AE' then 'ALTER EXTERNAL PROCEDURE' when accessright = 'AF' then 'ALTER FUNCTION' when accessright = 'AP' then 'ALTER PROCEDURE' when accessright = 'AR' then 'OVERRIDE RESTORE' when accessright = 'AS' then 'ABORTSESSION' when accessright = 'CA' then 'CREATE AUTHORIZATION' when accessright = 'CD' then 'CREATE DATABASE' when accessright = 'CE' then 'CREATE EXTERNAL PROCEDURE' when accessright = 'CF' then 'CREATE FUNCTION' when accessright = 'CG' then 'CREATE TRIGGER' when accessright = 'CM' then 'CREATE MACRO' when accessright = 'CO' then 'CREATE PROFILE' when accessright = 'CP' then 'CHECKPOINT' when accessright = 'CR' then 'CREATE ROLE' when accessright = 'CT' then 'CREATE TABLE' when accessright = 'CU' then 'CREATE USER' when accessright = 'CV' then 'CREATE VIEW' when accessright = 'CZ' then 'CREATE ZONE' /* Added for TD 15.00 */ when accessright = 'D ' then 'DELETE' when accessright = 'DA' then 'DROP AUTHORIZATION' when accessright = 'DD' then 'DROP DATABASE' when accessright = 'DF' then 'DROP FUNCTION' when accessright = 'DG' then 'DROP TRIGGER' when accessright = 'DM' then 'DROP MACRO' when accessright = 'DO' then 'DROP PROFILE' when accessright = 'DP' then 'DUMP' when accessright = 'DR' then 'DROP ROLE' when accessright = 'DT' then 'DROP TABLE' when accessright = 'DU' then 'DROP USER' when accessright = 'DV' then 'DROP VIEW' when accessright = 'DZ' then 'DROP ZONE' /* Added for TD 15.00 */ when accessright = 'E ' then 'EXECUTE' when accessright = 'EF' then 'EXECUTE FUNCTION' when accessright = 'GC' then 'CREATE GLOP' when accessright = 'GD' then 'DROP GLOP' when accessright = 'GM' then 'GLOP MEMBER' when accessright = 'I' then 'INSERT' when accessright = 'IX' then 'INDEX' when accessright = 'MR' then 'MONRESOURCE' when accessright = 'MS' then 'MONSESSION' when accessright = 'NT' then 'NONTEMPORAL' when accessright = 'OA' then 'OVERRIDE DUMP CONSTRAINT' when accessright = 'OD' then 'OVERRIDE DELETE CONSTRAINT' when accessright = 'OI' then 'OVERRIDE INSERT CONSTRAINT' when accessright = 'OP' then 'CREATE OWNER PROCEDURE' when accessright = 'OS' then 'OVERRIDE SELECT CONSTRAINT' when accessright = 'OU' then 'OVERRIDE UPDATE CONSTRAINT' when accessright = 'PC' then 'CREATE PROCEDURE' when accessright = 'PD' then 'DROP PROCEDURE' when accessright = 'PE' then 'EXECUTE PROCEDURE' when accessright = 'R' then 'SELECT' when accessright = 'RF' then 'REFERENCE' when accessright = 'RO' then 'REPLCONTROL' when accessright = 'RS' then 'RESTORE' when accessright = 'SA' then 'CONSTRAINT ASSIGNMENT' when accessright = 'SD' then 'CONSTRAINT DEFINITION' when accessright = 'SH' then 'SHOW' when accessright = 'SR' then 'SETRESRATE' when accessright = 'SS' then 'SETSESSRATE' when accessright = 'ST' then 'STATISTICS' when accessright = 'TH' then 'CTCONTROL' when accessright = 'U' then 'UPDATE' when accessright = 'UM' then 'UDT METHOD' when accessright = 'UT' then 'UDT TYPE' when accessright = 'UU' then 'UDT USAGE' when accessright = 'ZO' then 'ZONE OVERRIDE' /* Added for TD 15.00 */ else accessright end (varchar(26)) as AccessRightDesc ,GrantAuthority ,GrantorName (varchar(128)) ,AllnessFlag ,CreatorName (varchar(128)) ,CreateTimeStamp from ( SELECT -- GET DIRECT USER RIGHTS :USERNAME AS USERNAME ,'USER' (VARCHAR(128)) AS ACCESSTYPE ,'' (VARCHAR(128)) AS ROLENAME ,DATABASENAME ,TABLENAME ,COLUMNNAME ,ACCESSRIGHT ,GRANTAUTHORITY ,GRANTORNAME ,ALLNESSFLAG ,CREATORNAME ,CREATETIMESTAMP FROM DBC.ALLRIGHTSV WHERE USERNAME = :USERNAME AND CREATORNAME NOT = :USERNAME -- EXCLUDE OBJECTS CREATED BY USER UNION ALL SELECT -- GET RIGHTS HELD THROUGH A ROLE :USERNAME AS USERNAME ,'MEMBER' AS UR ,ROL.ROLENAME ,ROL.DATABASENAME ,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 MBR ON MBR.ROLENAME = ROL.ROLENAME WHERE MBR.GRANTEE = :USERNAME UNION ALL SELECT -- GET RIGHTS HELD THROUGH A SUBROLE :USERNAME AS USERNAME ,MBR.GRANTEE AS UR ,ROL.ROLENAME ,ROL.DATABASENAME ,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 MBR ON MBR.ROLENAME = ROL.ROLENAME WHERE MBR.GRANTEE IN (SELECT ROLENAME FROM DBC.ROLEMEMBERSV WHERE GRANTEE = :USERNAME) ) AllRights -- where DatabaseName not = 'dbc' -- uncomment to exclude DBC objects order by 4,5,6,7; );