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
Final Note: If you have an improvement on this query, I would be happy to post it.
Leave a Comment