Script to Replicate Privileges in Teradata

Sometime we may need to replicate exact access grant of an user to another user.

Here is the script to prepare "Grant" statements of a particular user..

Here we go..

SELECT 'GRANT ' || CASE

WHEN ACCESSRIGHT = 'AP' THEN 'ALTER PROCEDURE'

WHEN ACCESSRIGHT = 'AS' THEN 'ABORT SESSION'

WHEN ACCESSRIGHT = 'CD' THEN 'CREATE DATABASE'

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 = 'DD' THEN 'DROP DATABASE'

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 = 'I' THEN 'INSERT'

WHEN ACCESSRIGHT = 'IX' THEN 'INDEX'

WHEN ACCESSRIGHT = 'MR' THEN 'MONITOR RESOURCE'

WHEN ACCESSRIGHT = 'MS' THEN 'MONITOR SESSION'

WHEN ACCESSRIGHT = 'PC' THEN 'CREATE PROCEDURE'

WHEN ACCESSRIGHT = 'PD' THEN 'DROP PROCEDURE'

WHEN ACCESSRIGHT = 'PE' THEN 'EXECUTE PROCEDURE'

WHEN ACCESSRIGHT = 'RO' THEN 'REPLICATION OVERRIDE'

WHEN ACCESSRIGHT = 'R'THEN 'SELECT'

WHEN ACCESSRIGHT = 'RF' THEN 'REFERENCE'

WHEN ACCESSRIGHT = 'RS' THEN 'RESTORE'

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

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

WHEN ACCESSRIGHT = 'U' THEN 'UPDATE'

END || ' ON ' || TRIM(DATABASENAME) || ' TO TargetUserName ' || CASE WHEN GRANTAUTHORITY = 'Y' THEN ' WITH GRANT OPTION;' ELSE ' ;' END

FROM DBC.ALLRIGHTS WHERE USERNAME = 'SourceUserName'  --- and Databasename='Targetdatabasename' GROUP BY 1 ;

Note: This would generate privileges assigned directly to the user, not through role