Generate DDL for a Role in a Teradata Database

There are times I would like to copy the permissions for a role with a Teradata database to use on another database.  Below is a nice query to give me the DDL.

Note: I am not the author of this query, just re-posting it:

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
GROUP BY 1,2,3,4,5) as x
order by 1,2

ExtractDDLforRoles

Final Note: If you have an improvement on this query, I would be happy to post it.

Leave a Reply