REPLACE MACRO AllUserRights 
   (USERNAME VARCHAR(128) CHARACTER SET LATIN
   ,OPTIONS VARCHAR(100) DEFAULT '' NOT CASESPECIFIC)  
AS (
/* List rights held by the specified user as direct grants or through roles.
   -- Usage
      The 'Options' parameter controls the display of some rows.
      - this is a delimited set of values, I suggest that you use a space (' ') or a comma (',')
      - the values are: PUB - display AccessRights on the named database granted to PUBLIC
                        SELF - display AccessRights the named database has on itself
                        DBC - display AccessRights that user DBC has on the database
                  (by default none of these sets of AccessRights are displayed)

      The column 'Grant Type' now describes how the user has received the AccessRight:
      - User: the AccessRight was specifically granted to the user
      - Direct Role: the AccessRight was granted to a role and that role was granted to the user
      - Nested Role: the AccessRight was granted to a role which was granted to another role that was granted to the user
      - Public: the AccessRight was granted to 'Public' (by default these AccessRights are not shown, see 'OPTIONS')

   -- Change Log
      - 2015-07-24 Changed some column titles to be more descriptive.
                   Updated to clarify Role / Nested Role AccessRights.
      - 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.

   -- Notices
      - The original was developed by a Teradata PS consultant
      - This version updated and maintained by Ward Analytics Ltd 2013-2015
      - You are free to use and redistribute this code but please leave this header here.
*/ 
LOCKING ROW FOR ACCESS 
SELECT CAST(TGTUSER AS CHAR(30)) AS USERNAME
      ,AR_TYPE (VARCHAR(11)) (NAMED GRANTTYPE, TITLE 'Grant Type')
      ,ROLENAME  (VARCHAR(128)) (NAMED DIRECTROLENAME, TITLE 'Direct RoleName')
      ,ROLENAME2     (VARCHAR(128)) (NAMED NESTEDROLENAME, TITLE 'Nested RoleName')
      ,DATABASENAME (VARCHAR(128))
      ,TABLENAME    (VARCHAR(128)) (NAMED TABLENAME, TITLE 'TableName')
      ,COLUMNNAME   (VARCHAR(128)) (NAMED COLUMNNAME, TITLE 'ColumnName')
      ,ACCESSRIGHT
      ,CASE
		WHEN DT1.ACCESSRIGHT = 'AE' THEN 'ALTER EXTERNAL PROCEDURE'
		WHEN DT1.ACCESSRIGHT = 'AF' THEN 'ALTER FUNCTION'
		WHEN DT1.ACCESSRIGHT = 'AP' THEN 'ALTER PROCEDURE'
		WHEN DT1.ACCESSRIGHT = 'AR' THEN 'OVERRIDE RESTORE'
		WHEN DT1.ACCESSRIGHT = 'AS' THEN 'ABORTSESSION'
		WHEN DT1.ACCESSRIGHT = 'CA' THEN 'CREATE AUTHORIZATION'
		WHEN DT1.ACCESSRIGHT = 'CD' THEN 'CREATE DATABASE'
		WHEN DT1.ACCESSRIGHT = 'CE' THEN 'CREATE EXTERNAL PROCEDURE'
		WHEN DT1.ACCESSRIGHT = 'CF' THEN 'CREATE FUNCTION'
		WHEN DT1.ACCESSRIGHT = 'CG' THEN 'CREATE TRIGGER'
		WHEN DT1.ACCESSRIGHT = 'CM' THEN 'CREATE MACRO'
		WHEN DT1.ACCESSRIGHT = 'CO' THEN 'CREATE PROFILE'
		WHEN DT1.ACCESSRIGHT = 'CP' THEN 'CHECKPOINT'
		WHEN DT1.ACCESSRIGHT = 'CR' THEN 'CREATE ROLE'
		WHEN DT1.ACCESSRIGHT = 'CT' THEN 'CREATE TABLE'
		WHEN DT1.ACCESSRIGHT = 'CU' THEN 'CREATE USER'
		WHEN DT1.ACCESSRIGHT = 'CV' THEN 'CREATE VIEW'
		WHEN DT1.ACCESSRIGHT = 'CZ' THEN 'CREATE ZONE' /* Added for TD 15.00 */
		WHEN DT1.ACCESSRIGHT = 'D ' THEN 'DELETE'
		WHEN DT1.ACCESSRIGHT = 'DA' THEN 'DROP AUTHORIZATION'
		WHEN DT1.ACCESSRIGHT = 'DD' THEN 'DROP DATABASE'
		WHEN DT1.ACCESSRIGHT = 'DF' THEN 'DROP FUNCTION'
		WHEN DT1.ACCESSRIGHT = 'DG' THEN 'DROP TRIGGER'
		WHEN DT1.ACCESSRIGHT = 'DM' THEN 'DROP MACRO'
		WHEN DT1.ACCESSRIGHT = 'DO' THEN 'DROP PROFILE'
		WHEN DT1.ACCESSRIGHT = 'DP' THEN 'DUMP'
		WHEN DT1.ACCESSRIGHT = 'DR' THEN 'DROP ROLE'
		WHEN DT1.ACCESSRIGHT = 'DT' THEN 'DROP TABLE'
		WHEN DT1.ACCESSRIGHT = 'DU' THEN 'DROP USER'
		WHEN DT1.ACCESSRIGHT = 'DV' THEN 'DROP VIEW'
		WHEN DT1.ACCESSRIGHT = 'DZ' THEN 'DROP ZONE' /* Added for TD 15.00 */
		WHEN DT1.ACCESSRIGHT = 'E ' THEN 'EXECUTE'
		WHEN DT1.ACCESSRIGHT = 'EF' THEN 'EXECUTE FUNCTION'
		WHEN DT1.ACCESSRIGHT = 'GC' THEN 'CREATE GLOP'
		WHEN DT1.ACCESSRIGHT = 'GD' THEN 'DROP GLOP'
		WHEN DT1.ACCESSRIGHT = 'GM' THEN 'GLOP MEMBER'
		WHEN DT1.ACCESSRIGHT = 'I' THEN 'INSERT'
		WHEN DT1.ACCESSRIGHT = 'IX' THEN 'INDEX'
		WHEN DT1.ACCESSRIGHT = 'MR' THEN 'MONRESOURCE'
		WHEN DT1.ACCESSRIGHT = 'MS' THEN 'MONSESSION'
		WHEN DT1.ACCESSRIGHT = 'NT' THEN 'NONTEMPORAL'
		WHEN DT1.ACCESSRIGHT = 'OA' THEN 'OVERRIDE DUMP CONSTRAINT'
		WHEN DT1.ACCESSRIGHT = 'OD' THEN 'OVERRIDE DELETE CONSTRAINT'
		WHEN DT1.ACCESSRIGHT = 'OI' THEN 'OVERRIDE INSERT CONSTRAINT'
		WHEN DT1.ACCESSRIGHT = 'OP' THEN 'CREATE OWNER PROCEDURE'
		WHEN DT1.ACCESSRIGHT = 'OS' THEN 'OVERRIDE SELECT CONSTRAINT'
		WHEN DT1.ACCESSRIGHT = 'OU' THEN 'OVERRIDE UPDATE CONSTRAINT'
		WHEN DT1.ACCESSRIGHT = 'PC' THEN 'CREATE PROCEDURE'
		WHEN DT1.ACCESSRIGHT = 'PD' THEN 'DROP PROCEDURE'
		WHEN DT1.ACCESSRIGHT = 'PE' THEN 'EXECUTE PROCEDURE'
		WHEN DT1.ACCESSRIGHT = 'R' THEN 'SELECT'
		WHEN DT1.ACCESSRIGHT = 'RF' THEN 'REFERENCE'
		WHEN DT1.ACCESSRIGHT = 'RO' THEN 'REPLCONTROL'
		WHEN DT1.ACCESSRIGHT = 'RS' THEN 'RESTORE'
		WHEN DT1.ACCESSRIGHT = 'SA' THEN 'CONSTRAINT ASSIGNMENT'
		WHEN DT1.ACCESSRIGHT = 'SD' THEN 'CONSTRAINT DEFINITION'
		WHEN DT1.ACCESSRIGHT = 'SH' THEN 'SHOW'
		WHEN DT1.ACCESSRIGHT = 'SR' THEN 'SETRESRATE'
		WHEN DT1.ACCESSRIGHT = 'SS' THEN 'SETSESSRATE'
		WHEN DT1.ACCESSRIGHT = 'ST' THEN 'STATISTICS'
		WHEN DT1.ACCESSRIGHT = 'TH' THEN 'CTCONTROL'
		WHEN DT1.ACCESSRIGHT = 'U' THEN 'UPDATE'
		WHEN DT1.ACCESSRIGHT = 'UM' THEN 'UDT METHOD'
		WHEN DT1.ACCESSRIGHT = 'UT' THEN 'UDT TYPE'
		WHEN DT1.ACCESSRIGHT = 'UU' THEN 'UDT USAGE'
		WHEN DT1.ACCESSRIGHT = 'ZO' THEN 'ZONE OVERRIDE' /* Added for TD 15.00 */
        ELSE DT1.ACCESSRIGHT
     END (VARCHAR(26))  (NAMED ACCESSRIGHTDESC, TITLE 'AccessRight Description')
    ,GRANTAUTHORITY
    ,GRANTORNAME (VARCHAR(128)) (NAMED GRANTORNAME, TITLE 'GrantorName')
    ,ALLNESSFLAG
    ,CREATORNAME (VARCHAR(128))
    ,CREATETIMESTAMP

FROM (/* Direct grants to the user */
      SELECT :USERNAME AS TGTUSER
            ,CAST( (CASE
                    WHEN AR1.USERNAME = TGTUSER THEN 'User' 
                    ELSE 'Public'
                    END) AS VARCHAR(11)) AS AR_TYPE
            ,CAST(NULL AS VARCHAR(128)) AS ROLENAME
            ,CAST(NULL AS VARCHAR(128)) AS ROLENAME2
            ,AR1.DATABASENAME
            ,AR1.TABLENAME
            ,AR1.COLUMNNAME
            ,AR1.ACCESSRIGHT
            ,AR1.GRANTORNAME
            ,AR1.CREATETIMESTAMP
            ,AR1.GRANTAUTHORITY
            ,AR1.ALLNESSFLAG
            ,AR1.CREATORNAME (VARCHAR(128))
      FROM DBC.ALLRIGHTS AS AR1
      WHERE (USERNAME = :USERNAME
             OR (USERNAME = 'PUBLIC' AND POSITION('PUB' IN :OPTIONS) > 0) ) -- control AR's granted to PUBLIC
        AND (USERNAME <> AR1.DATABASENAME 
             OR (USERNAME = AR1.DATABASENAME AND POSITION('SELF' IN :OPTIONS) > 0)) -- control AR's on self
        AND (AR1.DATABASENAME <> 'DBC'
             OR (AR1.DATABASENAME = 'DBC' AND POSITION('DBC' IN :OPTIONS) > 0)) -- control display of AR's on DBC
      UNION ALL
      /* Grants to the user's direct roles */
      SELECT :USERNAME AS TGTUSER
            ,CAST('Direct Role' AS VARCHAR(11)) AS AR_TYPE
            ,AR2.ROLENAME
            ,CAST(NULL AS VARCHAR(128)) AS ROLENAME2
            ,AR2.DATABASENAME
            ,AR2.TABLENAME
            ,AR2.COLUMNNAME
            ,AR2.ACCESSRIGHT
            ,AR2.GRANTORNAME
            ,AR2.CREATETIMESTAMP
            ,CAST(NULL AS CHAR(1)) AS GRANTAUTHORITY
            ,CAST(NULL AS CHAR(1)) AS ALLNESSFLAG
            ,CAST(NULL AS VARCHAR(128)) AS CREATORNAME
      FROM DBC.ROLEMEMBERS AS RM
      INNER JOIN DBC.ALLROLERIGHTS AS AR2
         ON RM.GRANTEE = :USERNAME
          AND RM.GRANTEEKIND = 'USER'
          AND AR2.ROLENAME = RM.ROLENAME
        AND (TGTUSER <> AR2.DATABASENAME 
             OR (TGTUSER = AR2.DATABASENAME AND POSITION('SELF' IN :OPTIONS) > 0)) -- control AR's on self
        AND (AR2.DATABASENAME <> 'DBC'
             OR (AR2.DATABASENAME = 'DBC' AND POSITION('DBC' IN :OPTIONS) > 0)) -- control display of AR's on DBC
      UNION ALL
      /* Grants to the user's nested roles */
      SELECT :USERNAME AS TGTUSER
            ,CAST('Nested Role' AS VARCHAR(11)) AS AR_TYPE
            ,RM1.ROLENAME
            ,AR3.ROLENAME
            ,AR3.DATABASENAME
            ,AR3.TABLENAME
            ,AR3.COLUMNNAME
            ,AR3.ACCESSRIGHT
            ,AR3.GRANTORNAME
            ,AR3.CREATETIMESTAMP
            ,CAST(NULL AS CHAR(1)) AS GRANTAUTHORITY
            ,CAST(NULL AS CHAR(1)) AS ALLNESSFLAG
            ,CAST(NULL AS VARCHAR(128)) AS CREATORNAME
      FROM DBC.ROLEMEMBERS AS RM1
      INNER JOIN DBC.ROLEMEMBERS AS RM2
         ON RM1.GRANTEE = :USERNAME
          AND RM1.GRANTEEKIND = 'USER'
          AND RM1.ROLENAME = RM2.GRANTEE
          AND RM2.GRANTEEKIND = 'ROLE'
      INNER JOIN DBC.ALLROLERIGHTS AS AR3
         ON AR3.ROLENAME = RM2.ROLENAME
          AND (TGTUSER <> AR3.DATABASENAME 
               OR (TGTUSER = AR3.DATABASENAME AND POSITION('SELF' IN :OPTIONS) > 0)) -- control AR's on self
          AND (AR3.DATABASENAME <> 'DBC'
               OR (AR3.DATABASENAME = 'DBC' AND POSITION('DBC' IN :OPTIONS) > 0)) -- control display of AR's on DBC
     ) AS DT1
ORDER BY 5,6,7,8,2;
);