REPLACE MACRO AllDatabaseRightsV (DATABASENAME VARCHAR(128) CHARACTER SET UNICODE ,OPTIONS VARCHAR(100) DEFAULT '' NOT CASESPECIFIC) AS ( /* List rights held by any user on a named database, either as direct grants or through roles. -- Usage The 'Options' parameter controls the display of some rows. - this is a delimited set of values, I suggest that you use a space (' ') or a comma (',') - the values are: PUB - display AccessRights on the named database granted to PUBLIC SELF - display AccessRights the named database has on itself DBC - display AccessRights that user DBC has on the database (by default none of these sets of AccessRights are displayed) The column 'Grant Type' now describes how the user has received the AccessRight: - User: the AccessRight was specifically granted to the user - Direct Role: the AccessRight was granted to a role and that role was granted to the user - Nested Role: the AccessRight was granted to a role which was granted to another role that was granted to the user - Public: the AccessRight was granted to 'Public' (by default these AccessRights are not shown, see 'OPTIONS') -- Change Log - 2015-07-28 Initial coding, based on existing AllDatabaseRightsV macro but coded directly against DBC tables for efficiency. -- Notices - Developed and maintained by Ward Analytics Ltd 2015 - You are free to use and redistribute this code but please leave this header here. */ LOCKING ROW FOR ACCESS SELECT CAST(UDB.DATABASENAME AS VARCHAR(128)) AS USERNAME ,(CASE WHEN AR_TYPE IS NOT NULL THEN AR_TYPE WHEN AR_TYPE IS NULL AND USERNAME <> 'PUBLIC' THEN 'User' ELSE 'Public' end) (VARCHAR(11)) (NAMED GRANTTYPE, TITLE 'Grant Type') ,dt1.ROLENAME (VARCHAR(128)) (NAMED DIRECTROLENAME, TITLE 'Direct RoleName') ,dt1.ROLENAME2 (VARCHAR(128)) (NAMED NESTEDROLENAME, TITLE 'Nested RoleName') ,dt1.DATABASENAME ,TBL.TVMNAME (NAMED TABLENAME, TITLE 'TableName') ,FLD.FIELDNAME (NAMED COLUMNNAME, TITLE 'ColumnName') ,DT1.ACCESSRIGHT ,CASE WHEN DT1.ACCESSRIGHT = 'AE' THEN 'ALTER EXTERNAL PROCEDURE' WHEN DT1.ACCESSRIGHT = 'AF' THEN 'ALTER FUNCTION' WHEN DT1.ACCESSRIGHT = 'AP' THEN 'ALTER PROCEDURE' WHEN DT1.ACCESSRIGHT = 'AR' THEN 'OVERRIDE RESTORE' WHEN DT1.ACCESSRIGHT = 'AS' THEN 'ABORTSESSION' WHEN DT1.ACCESSRIGHT = 'CA' THEN 'CREATE AUTHORIZATION' WHEN DT1.ACCESSRIGHT = 'CD' THEN 'CREATE DATABASE' WHEN DT1.ACCESSRIGHT = 'CE' THEN 'CREATE EXTERNAL PROCEDURE' WHEN DT1.ACCESSRIGHT = 'CF' THEN 'CREATE FUNCTION' WHEN DT1.ACCESSRIGHT = 'CG' THEN 'CREATE TRIGGER' WHEN DT1.ACCESSRIGHT = 'CM' THEN 'CREATE MACRO' WHEN DT1.ACCESSRIGHT = 'CO' THEN 'CREATE PROFILE' WHEN DT1.ACCESSRIGHT = 'CP' THEN 'CHECKPOINT' WHEN DT1.ACCESSRIGHT = 'CR' THEN 'CREATE ROLE' WHEN DT1.ACCESSRIGHT = 'CT' THEN 'CREATE TABLE' WHEN DT1.ACCESSRIGHT = 'CU' THEN 'CREATE USER' WHEN DT1.ACCESSRIGHT = 'CV' THEN 'CREATE VIEW' WHEN DT1.ACCESSRIGHT = 'CZ' THEN 'CREATE ZONE' /* Added for TD 15.00 */ WHEN DT1.ACCESSRIGHT = 'D ' THEN 'DELETE' WHEN DT1.ACCESSRIGHT = 'DA' THEN 'DROP AUTHORIZATION' WHEN DT1.ACCESSRIGHT = 'DD' THEN 'DROP DATABASE' WHEN DT1.ACCESSRIGHT = 'DF' THEN 'DROP FUNCTION' WHEN DT1.ACCESSRIGHT = 'DG' THEN 'DROP TRIGGER' WHEN DT1.ACCESSRIGHT = 'DM' THEN 'DROP MACRO' WHEN DT1.ACCESSRIGHT = 'DO' THEN 'DROP PROFILE' WHEN DT1.ACCESSRIGHT = 'DP' THEN 'DUMP' WHEN DT1.ACCESSRIGHT = 'DR' THEN 'DROP ROLE' WHEN DT1.ACCESSRIGHT = 'DT' THEN 'DROP TABLE' WHEN DT1.ACCESSRIGHT = 'DU' THEN 'DROP USER' WHEN DT1.ACCESSRIGHT = 'DV' THEN 'DROP VIEW' WHEN DT1.ACCESSRIGHT = 'DZ' THEN 'DROP ZONE' /* Added for TD 15.00 */ WHEN DT1.ACCESSRIGHT = 'E ' THEN 'EXECUTE' WHEN DT1.ACCESSRIGHT = 'EF' THEN 'EXECUTE FUNCTION' WHEN DT1.ACCESSRIGHT = 'GC' THEN 'CREATE GLOP' WHEN DT1.ACCESSRIGHT = 'GD' THEN 'DROP GLOP' WHEN DT1.ACCESSRIGHT = 'GM' THEN 'GLOP MEMBER' WHEN DT1.ACCESSRIGHT = 'I' THEN 'INSERT' WHEN DT1.ACCESSRIGHT = 'IX' THEN 'INDEX' WHEN DT1.ACCESSRIGHT = 'MR' THEN 'MONRESOURCE' WHEN DT1.ACCESSRIGHT = 'MS' THEN 'MONSESSION' WHEN DT1.ACCESSRIGHT = 'NT' THEN 'NONTEMPORAL' WHEN DT1.ACCESSRIGHT = 'OA' THEN 'OVERRIDE DUMP CONSTRAINT' WHEN DT1.ACCESSRIGHT = 'OD' THEN 'OVERRIDE DELETE CONSTRAINT' WHEN DT1.ACCESSRIGHT = 'OI' THEN 'OVERRIDE INSERT CONSTRAINT' WHEN DT1.ACCESSRIGHT = 'OP' THEN 'CREATE OWNER PROCEDURE' WHEN DT1.ACCESSRIGHT = 'OS' THEN 'OVERRIDE SELECT CONSTRAINT' WHEN DT1.ACCESSRIGHT = 'OU' THEN 'OVERRIDE UPDATE CONSTRAINT' WHEN DT1.ACCESSRIGHT = 'PC' THEN 'CREATE PROCEDURE' WHEN DT1.ACCESSRIGHT = 'PD' THEN 'DROP PROCEDURE' WHEN DT1.ACCESSRIGHT = 'PE' THEN 'EXECUTE PROCEDURE' WHEN DT1.ACCESSRIGHT = 'R' THEN 'SELECT' WHEN DT1.ACCESSRIGHT = 'RF' THEN 'REFERENCE' WHEN DT1.ACCESSRIGHT = 'RO' THEN 'REPLCONTROL' WHEN DT1.ACCESSRIGHT = 'RS' THEN 'RESTORE' WHEN DT1.ACCESSRIGHT = 'SA' THEN 'CONSTRAINT ASSIGNMENT' WHEN DT1.ACCESSRIGHT = 'SD' THEN 'CONSTRAINT DEFINITION' WHEN DT1.ACCESSRIGHT = 'SH' THEN 'SHOW' WHEN DT1.ACCESSRIGHT = 'SR' THEN 'SETRESRATE' WHEN DT1.ACCESSRIGHT = 'SS' THEN 'SETSESSRATE' WHEN DT1.ACCESSRIGHT = 'ST' THEN 'STATISTICS' WHEN DT1.ACCESSRIGHT = 'TH' THEN 'CTCONTROL' WHEN DT1.ACCESSRIGHT = 'U' THEN 'UPDATE' WHEN DT1.ACCESSRIGHT = 'UM' THEN 'UDT METHOD' WHEN DT1.ACCESSRIGHT = 'UT' THEN 'UDT TYPE' WHEN DT1.ACCESSRIGHT = 'UU' THEN 'UDT USAGE' WHEN DT1.ACCESSRIGHT = 'ZO' THEN 'ZONE OVERRIDE' /* Added for TD 15.00 */ ELSE DT1.ACCESSRIGHT END (VARCHAR(26)) (NAMED ACCESSRIGHTDESC, TITLE 'AccessRight Description') ,DT1.WITHGRANT (NAMED GRANTAUTHORITY, TITLE 'GrantAuthority') ,COALESCE(GRANTORDB.DATABASENAME,'Dropped User') (NAMED GRANTORNAME, TITLE 'GrantorName') ,DT1.ALLNESSFLAG ,COALESCE(CREATORDB.DATABASENAME,'Dropped User') (NAMED CREATORNAME, TITLE 'CreatorName') ,DT1.CREATETIMESTAMP FROM (/* Direct grants on the database to the user */ SELECT AR1.USERID ,CAST(NULL AS VARCHAR(11)) AS AR_TYPE ,CAST(NULL AS VARCHAR(128)) AS ROLENAME ,CAST(NULL AS VARCHAR(128)) AS ROLENAME2 ,UDB.DATABASENAME ,AR1.TVMID ,AR1.FIELDID ,AR1.ACCESSRIGHT ,AR1.GRANTORID ,AR1.CREATETIMESTAMP ,AR1.WITHGRANT ,AR1.ALLNESSFLAG ,AR1.CREATEUID FROM DBC.ACCESSRIGHTS AS AR1 INNER JOIN DBC.DBASE AS UDB ON UDB.DATABASEID = AR1.DATABASEID AND UDB.DATABASENAMEI = :DATABASENAME (UPPERCASE) AND (AR1.USERID <> AR1.DATABASEID OR (AR1.USERID = AR1.DATABASEID AND POSITION('SELF' IN :OPTIONS) > 0)) -- control AR's on self AND (AR1.USERID <> '00000100'XB OR (AR1.USERID = '00000100'XB AND POSITION('DBC' IN :OPTIONS) > 0)) -- control display of AR's on DBC UNION ALL /* Grants to a role which is granted directly to the user */ SELECT RG.GRANTEEID ,CAST('Direct Role' AS VARCHAR(11)) AS AR_TYPE ,R.ROLENAME ,CAST(NULL AS VARCHAR(128)) AS ROLENAME2 ,DB.DATABASENAME ,AR2.TVMID ,AR2.FIELDID ,AR2.ACCESSRIGHT ,AR2.GRANTORID ,AR2.CREATETIMESTAMP ,AR2.WITHGRANT ,AR2.ALLNESSFLAG ,AR2.CREATEUID FROM DBC.ACCESSRIGHTS AS AR2 INNER JOIN DBC.DBASE AS DB ON DB.DATABASEID = AR2.DATABASEID AND DB.DATABASENAMEI = :DATABASENAME (UPPERCASE) AND (AR2.USERID <> AR2.DATABASEID OR (AR2.USERID = AR2.DATABASEID AND POSITION('SELF' IN :OPTIONS) > 0)) -- control AR's on self AND (AR2.USERID <> '00000100'XB OR (AR2.USERID = '00000100'XB AND POSITION('DBC' IN :OPTIONS) > 0)) -- control display of AR's on DBC INNER JOIN DBC.ROLES AS R ON AR2.USERID = R.ROLEID INNER JOIN DBC.ROLEGRANTS AS RG ON R.ROLEID = RG.ROLEID AND RG.GRANTEEKIND = 'U' UNION ALL /* Grants to a nested role with the 'parent' role granted to the user */ SELECT NRG.GRANTEEID ,CAST('Nested Role' AS VARCHAR(11)) AS AR_TYPE ,NR.ROLENAME ,DR.ROLENAME AS ROLENAME2 ,DB.DATABASENAME ,AR2.TVMID ,AR2.FIELDID ,AR2.ACCESSRIGHT ,AR2.GRANTORID ,AR2.CREATETIMESTAMP ,AR2.WITHGRANT ,AR2.ALLNESSFLAG ,AR2.CREATEUID FROM DBC.DBASE AS DB INNER JOIN DBC.ACCESSRIGHTS AS AR2 ON DB.DATABASEID = AR2.DATABASEID AND DB.DATABASENAMEI = :DATABASENAME (UPPERCASE) AND (AR2.USERID <> AR2.DATABASEID OR (AR2.USERID = AR2.DATABASEID AND POSITION('SELF' IN :OPTIONS) > 0)) -- control AR's on self AND (AR2.USERID <> '00000100'XB OR (AR2.USERID = '00000100'XB AND POSITION('DBC' IN :OPTIONS) > 0)) -- control display of AR's on DBC INNER JOIN DBC.ROLES AS DR ON AR2.USERID = DR.ROLEID INNER JOIN DBC.ROLEGRANTS AS DRG ON DR.ROLEID = DRG.ROLEID AND DRG.GRANTEEKIND = 'R' INNER JOIN DBC.ROLEGRANTS AS NRG ON DRG.GRANTEEID = NRG.ROLEID AND NRG.GRANTEEKIND = 'U' INNER JOIN DBC.ROLES AS NR ON NR.ROLEID = NRG.ROLEID ) AS DT1 INNER JOIN DBC.DBASE AS UDB ON DT1.USERID = UDB.DATABASEID AND (UDB.DATABASEID <> '00000100'XB OR (UDB.DATABASEID = '00000100'XB AND POSITION('DBC' IN :OPTIONS) > 0) ) -- control display of AR's on DBC AND (UDB.DATABASENAMEI <> 'PUBLIC' (UPPERCASE) OR (UDB.DATABASENAMEI = ('PUBLIC' (UPPERCASE)) AND POSITION('PUB' IN :OPTIONS) > 0) ) -- control AR's granted to PUBLIC INNER JOIN DBC.TVM AS TBL ON DT1.TVMID = TBL.TVMID INNER JOIN DBC.TVFIELDS AS FLD ON DT1.FIELDID = FLD.FIELDID AND (DT1.TVMID = FLD.TABLEID OR FLD.FIELDID = 0) LEFT OUTER JOIN DBC.DBASE AS GRANTORDB ON DT1.GRANTORID = GRANTORDB.DATABASEID LEFT OUTER JOIN DBC.DBASE AS CREATORDB ON DT1.CREATEUID = CREATORDB.DATABASEID ORDER BY 1,6,7,8,2; );