Generate DDL for privilege for Database or Role in Teradata

SELECT

x.ROLENAME,

x.AccessRight,

x.DATABASENAME,

x.TABLENAME,

'GRANT '|| 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 'ABORT SESSION'

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 'MONITOR RESOURCE'

WHEN ACCESSRIGHT = 'MS' THEN 'MONITOR SESSION'

WHEN ACCESSRIGHT = 'NT' THEN 'NONTEMPORAL'

WHEN ACCESSRIGHT = 'OA' THEN 'OVERRIDE DUMP'

WHEN ACCESSRIGHT = 'OD' THEN 'OVERRIDE DELETE POLICY'

WHEN ACCESSRIGHT = 'OI' THEN 'OVERRIDE INSERT POLICY'

WHEN ACCESSRIGHT = 'OP' THEN 'CREATE OWNER PROCEDURE'

WHEN ACCESSRIGHT = 'OS' THEN 'OVERRIDE SELECT POLICY'

WHEN ACCESSRIGHT = 'OU' THEN 'OVERRIDE UPDATE POLICY'

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 'SECURITY CONSTRAINT ASSIGNMENT (system wide)'

WHEN ACCESSRIGHT = 'SD' THEN 'SECURITY CONSTRAINT DEFINITION (system wide)'

WHEN ACCESSRIGHT = 'SH' THEN 'SHOW'

WHEN ACCESSRIGHT = 'SR' THEN 'SET RESOURCE RATE'

WHEN ACCESSRIGHT = 'SS' THEN 'SET SESSION RATE'

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'

END || ' ON '||TRIM(x.databasename)||' to '||TRIM(x.ROLENAME)||';' AS Permission

FROM

(SELECT distinct A.ROLENAME as ROLENAME,

B.DATABASENAME as DATABASENAME,

B.TABLENAME as TABLENAME,

B.GRANTORNAME as GRANTORNAME,

B.AccessRight as AccessRight

FROM DBC.ROLEMEMBERS A

JOIN DBC.ALLROLERIGHTS B

ON A.ROLENAME = B.ROLENAME

WHERE DATABASENAME =' < database_name > ' --- database name between quotes

-- WHERE B.ROLENAME=' < role_name > ' --- role name between quotes

GROUP BY 1,2,3,4,5) as x

order by 1,2;