|
|
Archives of the TeradataForum
Message Posted: Wed, 28 Jan 2004 @ 22:13:16 GMT
Subj: | | Re: Transferring grants b/w environments |
|
From: | | RICHARD GOMEZ |
I found this script that I have used in the past, I haven't used it quite awhile, but it should help put you in the right direction.
SEL 'GRANT '||
CASE
WHEN ACCESSRIGHT = 'CD' THEN 'CREATE DATABASE'
WHEN ACCESSRIGHT = 'CG' THEN 'CREATE TRIGGER'
WHEN ACCESSRIGHT = 'CM' THEN 'CREATE MACRO'
WHEN ACCESSRIGHT = 'CP' THEN 'CHECKPOINT'
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 = 'DP' THEN 'DUMP'
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 = 'PC' THEN 'CREATE PROCEDURE'
WHEN ACCESSRIGHT = 'PD' THEN 'DROP PROCEDURE'
WHEN ACCESSRIGHT = 'PE' THEN 'EXECUTE PROCEDURE'
WHEN ACCESSRIGHT = 'R'THEN 'SELECT'
WHEN ACCESSRIGHT = 'RS' THEN 'RESTORE'
WHEN ACCESSRIGHT = 'U' THEN 'UPDATE'
END||' ON '||TRIM(TRAILING FROM DATABASENAME)||' TO '||TRIM(TRAILING
FROM
USERNAME)||
CASE
WHEN GRANTAUTHORITY = 'N' THEN ';'
ELSE ' WITH GRANT OPTION;'
END
FROM DBC.ALLRIGHTS WHERE
ACCESSRIGHT IN
('CD','CG','CM','CP','CT','CU','CV','D','DD','DG','DM','DP','DT','DU',
'DV','E','I','PC','PD','PE','R','RS','U');
| |