Wednesday, February 8, 2017

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

2 comments:

  1. Thanks for sharing valuable information. Your blogs were helpful to Teradata learners. I request to update the blog through step-by-step. Also, find the Teradata news at
    Teradata dba Online Training Hyderabad

    ReplyDelete