Archives of the TeradataForum

Message Posted: Mon, 01 Feb 2010 @ 13:51:08 GMT

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

Try this:

     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='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'
     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 = user
     and CreatorName not= User -- 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 = User
     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=user)
     ) AllRights
     -- where DatabaseName not='dbc' -- uncomment to exclude DBC objects
     order by 4,5,6,7;

Berges, Diego G.
Consultor - Servicios Profesionales
Teradata Argentina

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