REPLACE MACRO AllDatabaseRightsV (DatabaseName VARCHAR(128) CHARACTER SET UNICODE)  AS (
/* List rights held on a database as direct grants or through roles.
   -- 2014-06-12 Updated to include TD 15.00 AccessRights values.
   -- 2014-06-03 Initial coding.
   -- This is based on macro AllUserRightsV which was originally developed by a Teradata PS consultant
   -- This version created by Ward Analytics Ltd 2014
   -- You are free to use and redistribute this code but please leave this header here.

The different ways in which a user may receive an AccessRight is shown in column GRANTEE_TYPE
- USER: the accessright is granted directly to the user. USER_OR_ROLE is the name of the user.
- ROLE: the accessright is granted to a role. USER_OR_ROLE is the name of the role.
- other: GRANTEE_TYPE and USER_OR_ROLE will both be role names
*/ 
LOCKING ROW FOR ACCESS SELECT
     DatabaseName     (VARCHAR(128))
    ,GRANTEE_TYPE   (VARCHAR(128)) AS GRANTEE_TYPE
    ,USERNAME     (VARCHAR(128)) AS USER_OR_ROLE
    ,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
     :DATABASENAME AS DATABASENAME
    ,'USER' (VARCHAR(128)) AS GRANTEE_TYPE
    ,USERNAME
    ,TABLENAME
    ,COLUMNNAME
    ,ACCESSRIGHT
    ,GRANTAUTHORITY
    ,GRANTORNAME
    ,ALLNESSFLAG
    ,CREATORNAME
    ,CREATETIMESTAMP
  FROM DBC.ALLRIGHTSV
 WHERE DATABASENAME = :DATABASENAME
   AND USERNAME NOT = :DATABASENAME    -- EXCLUDE RIGHTS HELD BY DATABASE ON ITSELF
UNION ALL
SELECT                          -- GET RIGHTS HELD THROUGH A ROLE
     :DATABASENAME AS DATABASENAME
    ,'ROLE' AS UR
    ,ROL.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
 WHERE ROL.DATABASENAME = :DATABASENAME
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)
 ) AllRights
-- where DatabaseName not = 'dbc' -- uncomment to exclude DBC objects  
ORDER BY TABLENAME, USERNAME, ColumnName, AccessRight;  
);