/* 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');