replace macro AllUserRights (UserName char(30)) as ( /* List rights held by a user as direct grants or through roles. -- 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 for Teradata 14.10 by Ward Analytics Ltd 2013 -- You are free to use and redistribute this code but please leave this header here. */ locking row for access select UserName (varchar(30)) ,AccessType (varchar(30)) as AccessType_Or_Child_RoleName ,RoleName (varchar(30)) as Parent_RoleName ,DatabaseName (varchar(30)) ,TableName (varchar(30)) ,ColumnName (varchar(30)) ,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 = '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 = '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' 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.ALLRIGHTS 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.ALLROLERIGHTS ROL JOIN DBC.ROLEMEMBERS 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.ALLROLERIGHTS ROL JOIN DBC.ROLEMEMBERS MBR ON MBR.ROLENAME = ROL.ROLENAME WHERE MBR.GRANTEE IN (SELECT ROLENAME FROM DBC.ROLEMEMBERS WHERE GRANTEE = :USERNAME) ) AllRights -- where DatabaseName not = 'dbc' -- uncomment to exclude DBC objects order by 4,5,6,7; );