List all Teradata Access Right for user granted on system using Macros

/* Create macro AllUserRights */

replace macro systemfe.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;

);

  

To execute the macro submit:
     
exec systemfe.AllUserRights('<username>');


Example:

    exec systemfe.AllUserRights('Systemfe');

UserName

AccessType

RoleName

DatabaseName

TableName

ColumnName

AccessRight

AccessRightDesc

GrantAuthority

GrantorName

SystemFe

User

 

Crashdumps

All

All

CT

CREATE TABLE

N

DBC

SystemFe

User

 

Crashdumps

All

All

DT

DROP TABLE

N

DBC

SystemFe

User

 

Crashdumps

All

All

R

RETRIEVE/SELECT

N

DBC

SystemFe

User

 

DBC

Acctg

All

R

RETRIEVE/SELECT

N

DBC

SystemFe

User

 

DBC

All

All

R

RETRIEVE/SELECT

Y

DBC

 

….

….

….

….

….

N

….

….

 

…..

…..

…..

…..

…..

….

….

….

 

……

……

…….

…….

…..

…..

….

Comments