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;

Comments