Archives of the TeradataForum

Message Posted: Tue, 05 Mar 2013 @ 19:52:31 GMT

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

Bob-

Robert Hanson, Teradata PS provided the following macro some time ago.

-dave.clark

     -----------------------------------------
     replace macro sysdba.AllUserRights (UserName char(30))  as (
     /* List rights held by a user as direct grants or through roles */ locking row for access
     select
          UserName     (varchar(30))
         ,AccessType   (varchar(30))
         ,RoleName     (varchar(30))
         ,DatabaseName (varchar(30))
         ,TableName    (varchar(30))
         ,ColumnName   (varchar(30))
         ,AccessRight
         ,case
             when accessright='AE' then 'ALTER EXTERNALPROCEDURE'
             when accessright='AF' then 'ALTER FUNCTION'
             when accessright='AP' then 'ALTER PROCEDURE'
             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='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='RO' then 'REPLICATION OVERRIDE'
             when accessright='R'  then 'RETRIEVE/SELECT'
             when accessright='RF' then 'REFERENCE'
             when accessright='RS' then 'RESTORE'
             when accessright='SA' then 'SECURITY CONSTRAINT ASSIGNMENT'
             when accessright='SD' then 'SECURITY CONSTRAINT DEFINITION'
             when accessright='ST' then 'STATISTICS'
             when accessright='SS' then 'SET SESSION RATE'
             when accessright='SR' then 'SET RESOURCE RATE'
             when accessright='TH' then 'CTCONTROL'
             when accessright='U'  then 'UPDATE'
             when accessright='UU' then 'UDT Usage'
             when accessright='UT' then 'UDT Type'
             when accessright='UM' then 'UDT Method'
             else''
          end (varchar(26)) as AccessRightDesc
         ,GrantAuthority
         ,GrantorName (varchar(30))
         ,AllnessFlag
         ,CreatorName (varchar(30))
         ,CreateTimeStamp
      from
     (
     select                         -- get direct user rights
          UserName
         ,'User' (varchar(30)) as AccessType
         ,'' (varchar(30)) as RoleName
         ,DatabaseName
         ,TableName
         ,ColumnName
         ,AccessRight
         ,GrantAuthority
         ,GrantorName
         ,AllnessFlag
         ,CreatorName
         ,CreateTimeStamp
       from dbc.allrights
      where UserName = :username
        and CreatorName not = :username    -- exclude objects created by user
     union all
     select                          -- get rights held through a role
          Grantee as UserName
         ,'Member' as UR
         ,r.RoleName
         ,DatabaseName
         ,TableName
         ,ColumnName
         ,AccessRight
         ,null (char(1)) as GrantAuthority
         ,GrantorName
         ,null (char(1)) as AllnessFlag
         ,null (char(1)) as CreatorName
         ,CreateTimeStamp
      from dbc.allrolerights r
      join dbc.rolemembers m
        on m.RoleName = r.RoleName
      where UserName = :username
     union all
     select                          -- get rights held through a subrole
          User as UserName
         ,m.Grantee as UR
         ,r.RoleName
         ,DatabaseName
         ,TableName
         ,ColumnName
         ,AccessRight
         ,null (char(1)) as GrantAuthority
         ,GrantorName
         ,null (char(1)) as AllnessFlag
         ,null (char(1)) as CreatorName
         ,CreateTimeStamp
      from dbc.allrolerights r
      join dbc.rolemembers m
        on m.RoleName = r.RoleName
      where m.grantee in (select rolename from dbc.rolemembers where grantee = :username)
      ) AllRights
     -- where DatabaseName not = 'dbc' -- uncomment to exclude DBC objects  order by 4,5,6,7;
     );

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