/******** Manage Access Rights ***************/
/******** Assume you have DBA work database called scriptdb ************/
-- First step creat Access Right Abbreviation table on your database
CREATE SET TABLE scriptdb.AccessRightsAbbv( Description VARCHAR(50), AccessRight CHAR(2))PRIMARY INDEX(AccessRight) ;
-- Second step insert the abbreviations
ins scriptdb.AccessRightsAbbv('CHECKPOINT','CP');
ins scriptdb.AccessRightsAbbv('CREATE AUTHORIZATION','CA');
ins scriptdb.AccessRightsAbbv('CREATE MACRO','CM');
ins scriptdb.AccessRightsAbbv('CREATE PROCEDURE','PC');
ins scriptdb.AccessRightsAbbv('CREATE TABLE','CT');
ins scriptdb.AccessRightsAbbv('CREATE TRIGGER','CG');
ins scriptdb.AccessRightsAbbv('CREATE VIEW','CV');
ins scriptdb.AccessRightsAbbv('DELETE','D');
ins scriptdb.AccessRightsAbbv('DROP AUTHORIZATION','DA');
ins scriptdb.AccessRightsAbbv('DROP FUNCTION ','DF');
ins scriptdb.AccessRightsAbbv('DROP MACRO','DM');
ins scriptdb.AccessRightsAbbv('DROP PROCEDURE','PD');
ins scriptdb.AccessRightsAbbv('DROP TRIGGER','DG');
ins scriptdb.AccessRightsAbbv('DROP VIEW','DV');
ins scriptdb.AccessRightsAbbv('DUMP','DP');
ins scriptdb.AccessRightsAbbv('INSERT','I');
ins scriptdb.AccessRightsAbbv('RESTORE','RS');
ins scriptdb.AccessRightsAbbv('SELECT','R');
ins scriptdb.AccessRightsAbbv('UPDATE','U');
ins scriptdb.AccessRightsAbbv('CREATE DATABASE','CD');
ins scriptdb.AccessRightsAbbv('CREATE USER','CU');
ins scriptdb.AccessRightsAbbv('DROP DATABASE','DD');
ins scriptdb.AccessRightsAbbv('DROP USER','DU');
ins scriptdb.AccessRightsAbbv('ALTER EXTERNAL PROCEDURE','AE');
ins scriptdb.AccessRightsAbbv('ALTER FUNCTION','AF');
ins scriptdb.AccessRightsAbbv('ALTER PROCEDURE','AP');
ins scriptdb.AccessRightsAbbv('CREATE EXTERNAL PROCEDURE','CE');
ins scriptdb.AccessRightsAbbv('CREATE FUNCTION','CF');
ins scriptdb.AccessRightsAbbv('EXECUTE FUNCTION','EF');
ins scriptdb.AccessRightsAbbv('EXECUTE PROCEDURE','PE');
ins scriptdb.AccessRightsAbbv('EXECUTE','E');
ins scriptdb.AccessRightsAbbv('DROP TABLE','DT');
ins scriptdb.AccessRightsAbbv('INDEX','IX');
ins scriptdb.AccessRightsAbbv('REFERENCES','RF');
ins scriptdb.AccessRightsAbbv('ABORT SESSION','AS');
ins scriptdb.AccessRightsAbbv('MONRESOURCE','MR');
ins scriptdb.AccessRightsAbbv('MONSESSION','MS');
ins scriptdb.AccessRightsAbbv('SETRESRATE','SR');
ins scriptdb.AccessRightsAbbv('SETSESSRATE','SS');
ins scriptdb.AccessRightsAbbv('CREATE PROFILE','CO');
ins scriptdb.AccessRightsAbbv('CREATE ROLE','CR');
ins scriptdb.AccessRightsAbbv('DROP PROFILE','DO');
ins scriptdb.AccessRightsAbbv('DROP ROLE','DR');
ins scriptdb.AccessRightsAbbv('REPLCONTROL','RO');
/ ***************SQL for AccessRights held by a user***********************/
SELECT UserName, DatabaseName, TableName,ColumnName,
CASE WHEN Abbv.AccessRight IS NOT NULL THEN Abbv.Description
ELSE ALRTS.AccessRight
END AS AccessRight, GrantAuthority, GrantorName, AllnessFlag,
CreatorName, CreateTimeStamp
FROM DBC.ALLRIGHTS ALRTS LEFT OUTER JOIN scriptdb.AccessRightsAbbv Abbv ON ALRTS.AccessRight = Abbv.AccessRight
WHERE UserName='???'
AND DatabaseName='???'
Order By 2,3,4,5;
/*************SQL for AccessRights held by a ROLE************************/
SELECT RoleName, DatabaseName, TableName,ColumnName,
CASE WHEN Abbv.AccessRight IS NOT NULL THEN Abbv.Description
ELSE ALRTS.AccessRight
END AS AccessRight, GrantorName, CreateTimeStamp
FROM DBC.ALLROLERIGHTS ALRTS LEFT OUTER JOIN scriptdb.AccessRightsAbbv Abbv
ON ALRTS.AccessRight = Abbv.AccessRight
WHERE RoleName='???'
AND DatabaseName='???'
Order By 2,3,4,5;
