Archives of the TeradataForum

Message Posted: Wed, 29 Aug 2012 @ 14:29:14 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hi,

Hope, We cannot rename a role.

Instead, as you know, we need to create a new role and generate the existing rights.

The below query may be useful for generating rights of a role.

     SELECT
      'GRANT'                                 AS "GRANT"

      , CASE WHEN TRIM(AccessRight)='AE' THEN 'ALTER EXTERNAL PROCEDURE      '
             WHEN TRIM(AccessRight)='AF' THEN 'ALTER FUNCTION                '
             WHEN TRIM(AccessRight)='AP' THEN 'ALTER PROCEDURE               '
             WHEN TRIM(AccessRight)='AS' THEN 'ABORT SESSION                 '
             WHEN TRIM(AccessRight)='CA' THEN 'CREATE AUTHORIZATION          '
             WHEN TRIM(AccessRight)='CD' THEN 'CREATE DATABASE               '
             WHEN TRIM(AccessRight)='CE' THEN 'CREATE EXTERNAL PROCEDURE     '
             WHEN TRIM(AccessRight)='CF' THEN 'CREATE FUNCTION               '
             WHEN TRIM(AccessRight)='CG' THEN 'CREATE TRIGGER                '
             WHEN TRIM(AccessRight)='CM' THEN 'CREATE MACRO                  '
             WHEN TRIM(AccessRight)='CO' THEN 'CREATE PROFILE                '
             WHEN TRIM(AccessRight)='CP' THEN 'CHECKPOINT                    '
             WHEN TRIM(AccessRight)='CR' THEN 'CREATE ROLE                   '
             WHEN TRIM(AccessRight)='CT' THEN 'CREATE TABLE                  '
             WHEN TRIM(AccessRight)='CU' THEN 'CREATE USER                   '
             WHEN TRIM(AccessRight)='CV' THEN 'CREATE VIEW                   '
             WHEN TRIM(AccessRight)='D'  THEN 'DELETE                        '
             WHEN TRIM(AccessRight)='DA' THEN 'DROP AUTHORIZATION            '
             WHEN TRIM(AccessRight)='DD' THEN 'DROP DATABASE                 '
             WHEN TRIM(AccessRight)='DF' THEN 'DROP FUNCTION                 '
             WHEN TRIM(AccessRight)='DG' THEN 'DROP TRIGGER                  '
             WHEN TRIM(AccessRight)='DM' THEN 'DROP MACRO                    '
             WHEN TRIM(AccessRight)='DO' THEN 'DROP PROFILE                  '
             WHEN TRIM(AccessRight)='DP' THEN 'DUMP                          '
             WHEN TRIM(AccessRight)='DR' THEN 'DROP ROLE                     '
             WHEN TRIM(AccessRight)='DT' THEN 'DROP TABLE                    '
             WHEN TRIM(AccessRight)='DU' THEN 'DROP USER                     '
             WHEN TRIM(AccessRight)='DV' THEN 'DROP VIEW                     '
             WHEN TRIM(AccessRight)='E'  THEN 'EXECUTE                       '
             WHEN TRIM(AccessRight)='EF' THEN 'EXECUTE FUNCTION              '
             WHEN TRIM(AccessRight)='GC' THEN 'CREATE GLOP                   '
             WHEN TRIM(AccessRight)='GD' THEN 'DROP GLOP                     '
             WHEN TRIM(AccessRight)='GM' THEN 'GLOP MEMBER                   '
             WHEN TRIM(AccessRight)='I'  THEN 'INSERT                        '
             WHEN TRIM(AccessRight)='IX' THEN 'INDEX                         '
             WHEN TRIM(AccessRight)='MR' THEN 'MONITOR RESOURCE              '
             WHEN TRIM(AccessRight)='MS' THEN 'MONITOR SESSION               '
             WHEN TRIM(AccessRight)='NT' THEN 'NONTEMPORAL                   '
             WHEN TRIM(AccessRight)='OP' THEN 'CREATE OWNER PROCEDURE        '
             WHEN TRIM(AccessRight)='PC' THEN 'CREATE PROCEDURE              '
             WHEN TRIM(AccessRight)='PD' THEN 'DROP PROCEDURE                '
             WHEN TRIM(AccessRight)='PE' THEN 'EXECUTE PROCEDURE             '
             WHEN TRIM(AccessRight)='R'  THEN 'SELECT                        '
             WHEN TRIM(AccessRight)='RF' THEN 'REFERENCE                     '
             WHEN TRIM(AccessRight)='RO' THEN 'REPLCONTROL                   '
             WHEN TRIM(AccessRight)='RS' THEN 'RESTORE                       '
             WHEN TRIM(AccessRight)='SA' THEN 'SECURITY CONSTRAINT ASSIGNMENT'
             WHEN TRIM(AccessRight)='SD' THEN 'SECURITY CONSTRAINT DEFINITION'
             WHEN TRIM(AccessRight)='SH' THEN 'SHOW                          '
             WHEN TRIM(AccessRight)='SR' THEN 'SET RESOURCE RATE             '
             WHEN TRIM(AccessRight)='SS' THEN 'SET SESSION RATE              '
             WHEN TRIM(AccessRight)='ST' THEN 'STATISTICS                    '
             WHEN TRIM(AccessRight)='TH' THEN 'CTCONTROL                     '
             WHEN TRIM(AccessRight)='U'  THEN 'UPDATE                        '
             WHEN TRIM(AccessRight)='UM' THEN 'UDT Method                    '
             WHEN TRIM(AccessRight)='UT' THEN 'UDT Type                      '
             WHEN TRIM(AccessRight)='UU' THEN 'UDT Usage                     '
        END                                   AS Granted_Rights

      , 'ON'                                  AS "ON"
      , DatabaseName                          AS On_Database
      , TableName
      , ColumnName
      , 'TO'                                  AS "TO"
      , RoleName                              AS RoleName
      , ';'                                   AS "END"

     FROM DBC.AllRoleRights

     WHERE TRIM(RoleName) LIKE '%%'
     AND TRIM(DatabaseName) LIKE '%%'

     GROUP BY 1, 2, 3, 4,5,6,7,8,9
     ;

Thanks,

Sravan.

	<Prev	Next>		<<First	<Prev	Next>	Last>>