AccessRights reporting macros

Database
Highlighted
Senior Apprentice

AccessRights reporting macros

Dear all,

 

Shown below are two macros that you may find useful for AccessRights reporting. Please feel free to use them on your systems.

 

(Note: For those of you who used to subscribe to 'Teradata Forum' these are updated versions of the macros that I used to distribute there).

 

The two macros are described below along with their parameters:

 

AllDatabaseRightsV

The first parameter (required) is a database name and lists all AccessRights granted to users on that database. These AccessRights may have been granted directly to the user, via Roles or via nested Roles.

The second parameter (optional, defaults to blank) controls display of certain types of AccessRights held on the named database.

  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)

 

AllUserRightsV

The first parameter (required) is a user name and lists all AccessRights granted to that user. These AccessRights may have been granted directly to the user, via Roles or via nested Roles.

The second parameter (optional, defaults to blank) controls display of certain types of AccessRights held on the named database.

  PUB - display AccessRights granted to PUBLIC
  SELF - display AccessRights the named user has on itself
  DBC - display AccessRights that the named user has on DBC

(by default none of these sets of AccessRights are displayed)

 

Teradata Version support

These are expected to run on any version of Teradata from TD13.10 up to and including TD16.00.

If you're on TD12 (upgrade! Smiley Happy ) or earlier the UDF needs to change. Contact me.

 

Installation Requirements

You should be able to install these macros and the required UDF using SQL Assistant or TDStudio.

 

These macros both use a common SQL UDF which is intended to be created in database SYSLIB (but you can create it anywhere). If you put it into a database other than SYSLIB then you may have to change the macro code so that the UDF is found.

 

These macros can be created in any database/user that you like. The database/user where you create them will require certain AccessRights. The required AccessRights for the database/user containing these macros are:

 

      GRANT SELECT ON DBC.ACCESSRIGHTS TO <userdb> WITH GRANT OPTION;
      GRANT SELECT ON DBC.DBASE TO <userdb> WITH GRANT OPTION;
      GRANT SELECT ON DBC.ROLEGRANTS TO <userdb> WITH GRANT OPTION;
      GRANT SELECT ON DBC.ROLES TO <userdb> WITH GRANT OPTION;
      GRANT SELECT ON DBC.TVM TO <userdb> WITH GRANT OPTION;
      GRANT SELECT ON DBC.TVFIELDS TO <userdb> WITH GRANT OPTION;
      GRANT EXECUTE FUNCTION ON SYSLIB TO <userdb> WITH GRANT OPTION;

where '<userdb>' is the name of the database/user where the macros are created.

 

Installation Sequence

Create the UDF first, then create the macros.

 

UDF code

REPLACE FUNCTION WA_ACCRIGHT_NAME(AccessRight CHAR(2))
/***************************************************************************
 NOTICES:
  Copyright 2017 by Ward Analytics Ltd. All Rights Reserved.
  You are free to use or modify this code as required, but please leave this comment block intact.
 
 FUNCTION:
  Simple SQL UDF to convert AccessRight code to more useful description.

 CHANGE LOG:
 2017-02-24  V16.00.0001  Dave W  Updated for TD16.00.
 2016-07-19  V15.10.0005  Dave W  Initial coding.
 ***************************************************************************/
RETURNS VARCHAR(256)
LANGUAGE SQL
DETERMINISTIC
CONTAINS SQL
SPECIFIC WA_ACCRIGHT_NAME
RETURNS NULL ON NULL INPUT
SQL SECURITY DEFINER
COLLATION INVOKER
INLINE TYPE 1
RETURN       CASE
       WHEN ACCESSRIGHT = 'AE' THEN 'ALTER EXTERNAL PROCEDURE'
       WHEN ACCESSRIGHT = 'AF' THEN 'ALTER FUNCTION'
       WHEN ACCESSRIGHT = 'AP' THEN 'ALTER PROCEDURE'
       WHEN ACCESSRIGHT = 'AR' THEN 'OVERRIDE RESTORE'
       WHEN ACCESSRIGHT = 'AS' THEN 'ABORTSESSION'
       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 = 'CS' THEN 'CREATE SERVER' /* Added for TD 15.10 */
       WHEN ACCESSRIGHT = 'CT' THEN 'CREATE TABLE'
       WHEN ACCESSRIGHT = 'CU' THEN 'CREATE USER'
       WHEN ACCESSRIGHT = 'CV' THEN 'CREATE VIEW'
       WHEN ACCESSRIGHT = 'CZ' THEN 'CREATE ZONE' /* ADDED FOR TD 15.00 */
       WHEN ACCESSRIGHT = 'C1' THEN 'CREATE DATASET SCHEMA' /* Added for TD 16.00 */
       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 = 'DS' THEN 'DROP SERVER' /* Added for TD 15.10 */
       WHEN ACCESSRIGHT = 'DT' THEN 'DROP TABLE'
       WHEN ACCESSRIGHT = 'DU' THEN 'DROP USER'
       WHEN ACCESSRIGHT = 'DV' THEN 'DROP VIEW'
       WHEN ACCESSRIGHT = 'DZ' THEN 'DROP ZONE' /* ADDED FOR TD 15.00 */
       WHEN ACCESSRIGHT = 'D1' THEN 'DROP DATASET SCHEMA' /* Added for TD 16.00 */
       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 = 'MC' THEN 'CREATE MAP' /* Added for TD 16.00 */
       WHEN ACCESSRIGHT = 'MD' THEN 'DROP MAP' /* Added for TD 16.00 */
       WHEN ACCESSRIGHT = 'MR' THEN 'MONRESOURCE'
       WHEN ACCESSRIGHT = 'MS' THEN 'MONSESSION'
       WHEN ACCESSRIGHT = 'NT' THEN 'NONTEMPORAL'
       WHEN ACCESSRIGHT = 'OA' THEN 'OVERRIDE DUMP CONSTRAINT'
       WHEN ACCESSRIGHT = 'OD' THEN 'OVERRIDE DELETE CONSTRAINT'
       WHEN ACCESSRIGHT = 'OI' THEN 'OVERRIDE INSERT CONSTRAINT'
       WHEN ACCESSRIGHT = 'OP' THEN 'CREATE OWNER PROCEDURE'
       WHEN ACCESSRIGHT = 'OS' THEN 'OVERRIDE SELECT CONSTRAINT'
       WHEN ACCESSRIGHT = 'OU' THEN 'OVERRIDE UPDATE CONSTRAINT'
       WHEN ACCESSRIGHT = 'PC' THEN 'CREATE PROCEDURE'
       WHEN ACCESSRIGHT = 'PD' THEN 'DROP PROCEDURE'
       WHEN ACCESSRIGHT = 'PE' THEN 'EXECUTE PROCEDURE'
       WHEN ACCESSRIGHT = 'R' THEN 'SELECT'
       WHEN ACCESSRIGHT = 'RF' THEN 'REFERENCE'
       WHEN ACCESSRIGHT = 'RO' THEN 'REPLCONTROL'
       WHEN ACCESSRIGHT = 'RS' THEN 'RESTORE'
       WHEN ACCESSRIGHT = 'SA' THEN 'CONSTRAINT ASSIGNMENT'
       WHEN ACCESSRIGHT = 'SD' THEN 'CONSTRAINT DEFINITION'
       WHEN ACCESSRIGHT = 'SH' THEN 'SHOW'
       WHEN ACCESSRIGHT = 'SR' THEN 'SETRESRATE'
       WHEN ACCESSRIGHT = 'SS' THEN 'SETSESSRATE'
       WHEN ACCESSRIGHT = 'ST' THEN 'STATISTICS'
       WHEN ACCESSRIGHT = 'TH' THEN 'CTCONTROL'
       WHEN ACCESSRIGHT = 'U' THEN 'UPDATE'
       WHEN ACCESSRIGHT = 'UM' THEN 'UDT METHOD'
       WHEN ACCESSRIGHT = 'UT' THEN 'UDT TYPE'
       WHEN ACCESSRIGHT = 'UU' THEN 'UDT USAGE'
       WHEN ACCESSRIGHT = 'W1' THEN 'WITH DATASET SCHEMA' /* Added for TD 16.00 */
       WHEN ACCESSRIGHT = 'ZO' THEN 'ZONE OVERRIDE' /* ADDED FOR TD 15.00 */
       ELSE ACCESSRIGHT
       END;

Macro code

AllDatabaseRightsV

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.
   -- Setup
      The database/user (<userdb> in the SQL shown below) where this macro is 
      installed needs the following Access Rights.
      GRANT SELECT ON DBC.ACCESSRIGHTS TO <userdb> WITH GRANT OPTION;
      GRANT SELECT ON DBC.DBASE TO <userdb> WITH GRANT OPTION;
      GRANT SELECT ON DBC.ROLEGRANTS TO <userdb> WITH GRANT OPTION;
      GRANT SELECT ON DBC.ROLES TO <userdb> WITH GRANT OPTION;
      GRANT SELECT ON DBC.TVM TO <userdb> WITH GRANT OPTION;
      GRANT SELECT ON DBC.TVFIELDS TO <userdb> WITH GRANT OPTION;
      GRANT EXECUTE FUNCTION ON SYSLIB TO <userdb> WITH GRANT OPTION;
	  
   -- 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
      - 2017-02-24 Updated to use SQL UDF for AccessRght names.
      - 2016-09-06 Changes for TD16.00 and corrected some minor issues.
      - 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-2017.
      - 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
      ,WA_ACCRIGHT_NAME(DT1.ACCESSRIGHT) (NAMED ACCESSRIGHTNAME, TITLE 'AccessRight Name')
      ,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 a 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 display of 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 for 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 display of 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 for 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 display of 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 for 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 for 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;
);

COMMENT ON MACRO AllDatabaseRightsV AS 'VER=16.00.0001';

Macro code

AllUserRightsV

REPLACE MACRO AllUserRightsV 
   (USERNAME VARCHAR(128) CHARACTER SET UNICODE
   ,OPTIONS VARCHAR(100) DEFAULT '' NOT CASESPECIFIC)  
AS (
/* List rights held by the specified user as direct grants or through roles.
   -- Setup
      The database/user (<userdb> in the SQL shown below) where this macro is 
      installed needs the following Access Rights.
      GRANT SELECT ON DBC.ACCESSRIGHTS TO <userdb> WITH GRANT OPTION;
      GRANT SELECT ON DBC.DBASE TO <userdb> WITH GRANT OPTION;
      GRANT SELECT ON DBC.ROLEGRANTS TO <userdb> WITH GRANT OPTION;
      GRANT SELECT ON DBC.ROLES TO <userdb> WITH GRANT OPTION;
      GRANT SELECT ON DBC.TVM TO <userdb> WITH GRANT OPTION;
      GRANT SELECT ON DBC.TVFIELDS TO <userdb> WITH GRANT OPTION;
      GRANT EXECUTE FUNCTION ON SYSLIB TO <userdb> WITH GRANT OPTION;

   -- 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 granted to PUBLIC
                        SELF - display AccessRights the named user has on itself
                        DBC - display AccessRights that the named user has on DBC
                  (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
      - 2017-02-24 Updated to use SQL UDF for AccessRght names.
      - 2016-09-06 Changes for TD16.00 and corrected some minor issues.
      - 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-2017.
      - You are free to use and redistribute this code but please leave this header here.
*/ 
LOCKING ROW FOR ACCESS 
SELECT DT1.TGTUSER AS USERNAME
      ,DT1.AR_TYPE (VARCHAR(11)) (NAMED GRANTTYPE, TITLE 'Grant Type')
      ,DT1.ROLENAME (NAMED DIRECTROLENAME, TITLE 'Direct RoleName')
      ,DT1.ROLENAME2 (NAMED NESTEDROLENAME, TITLE 'Nested Rolename')
      ,DB.DATABASENAME
      ,TBL.TVMNAME (NAMED TABLENAME, TITLE 'TableName')
      ,FLD.FIELDNAME (NAMED COLUMNNAME, TITLE 'ColumnName')
      ,DT1.ACCESSRIGHT
      ,WA_ACCRIGHT_NAME(DT1.ACCESSRIGHT) (NAMED ACCESSRIGHTNAME, TITLE 'AccessRight Name')
      ,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 to the user */
      SELECT :USERNAME AS TGTUSER
            ,CAST( (CASE
                    WHEN UDB.DATABASENAME = :USERNAME THEN 'User' 
                    ELSE 'Public'
                    END) AS VARCHAR(11)) AS AR_TYPE
            ,CAST(NULL AS VARCHAR(128)) AS ROLENAME
            ,CAST(NULL AS VARCHAR(128)) AS ROLENAME2
            ,AR1.DATABASEID
            ,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.USERID
          AND (UDB.DATABASENAMEI = :USERNAME (UPPERCASE)
               OR (UDB.DATABASENAMEI = 'PUBLIC' AND POSITION('PUB' IN :OPTIONS) > 0) ) -- control AR's granted to PUBLIC
          AND (AR1.USERID <> AR1.DATABASEID 
               OR (AR1.USERID = AR1.DATABASEID AND POSITION('SELF' IN :OPTIONS) > 0)) -- control display of AR's on self
          AND (AR1.DATABASEID <> '00000100'XB
               OR (AR1.DATABASEID = '00000100'XB AND POSITION('DBC' IN :OPTIONS) > 0)) -- control display of AR's on DBC
      UNION ALL
      /* Grants to the user's direct roles */
      SELECT :USERNAME AS TGTUSER
            ,CAST('Direct Role' AS VARCHAR(11)) AS AR_TYPE
            ,R.ROLENAME
            ,CAST(NULL AS VARCHAR(128)) AS ROLENAME2
            ,AR2.DATABASEID
            ,AR2.TVMID
            ,AR2.FIELDID
            ,AR2.ACCESSRIGHT
            ,AR2.GRANTORID
            ,AR2.CREATETIMESTAMP
            ,AR2.WITHGRANT
            ,CAST(NULL AS CHAR(1)) AS ALLNESSFLAG
            ,AR2.CREATEUID
      FROM DBC.ROLEGRANTS AS RG
      INNER JOIN DBC.DBASE AS UDB
         ON UDB.DATABASEID = RG.GRANTEEID
          AND UDB.DATABASENAMEI = :USERNAME (UPPERCASE)
      INNER JOIN DBC.ROLES AS R
         ON RG.ROLEID = R.ROLEID
      INNER JOIN DBC.ACCESSRIGHTS AS AR2
         ON R.ROLEID = AR2.USERID
          AND (AR2.DATABASEID <> '00000100'XB
               OR (AR2.DATABASEID = '00000100'XB AND POSITION('DBC' IN :OPTIONS) > 0)) -- control display of AR's on DBC
       WHERE (UDB.DATABASEID <> AR2.DATABASEID 
              OR (UDB.DATABASEID = AR2.DATABASEID AND POSITION('SELF' IN :OPTIONS) > 0)) -- control display of AR's on self
      UNION ALL
      /* Grants to the user's nested roles */
      SELECT :USERNAME AS TGTUSER
            ,CAST('Nested Role' AS VARCHAR(11)) AS AR_TYPE
            ,DR.ROLENAME AS DIRECT_ROLE
            ,NR.ROLENAME AS NESTED_ROLE
            ,AR3.DATABASEID
            ,AR3.TVMID
            ,AR3.FIELDID
            ,AR3.ACCESSRIGHT
            ,AR3.GRANTORID
            ,AR3.CREATETIMESTAMP
            ,AR3.WITHGRANT
            ,CAST(NULL AS CHAR(1)) AS ALLNESSFLAG
            ,AR3.CREATEUID
      FROM DBC.DBASE AS UDB
      INNER JOIN DBC.ROLEGRANTS AS DRG
         ON UDB.DATABASENAMEI = :USERNAME (UPPERCASE)
          AND UDB.DATABASEID = DRG.GRANTEEID
          AND DRG.GRANTEEKIND = 'U'
      INNER JOIN DBC.ROLES AS DR
         ON DRG.ROLEID = DR.ROLEID
      INNER JOIN DBC.ROLEGRANTS AS NRG
         ON DRG.ROLEID = NRG.GRANTEEID
          AND NRG.GRANTEEKIND = 'R'
      INNER JOIN DBC.ROLES AS NR
         ON NRG.ROLEID = NR.ROLEID
      INNER JOIN DBC.ACCESSRIGHTS AS AR3
         ON NR.ROLEID = AR3.USERID
          AND (AR3.DATABASEID <> '00000100'XB
               OR (AR3.DATABASEID = '00000100'XB AND POSITION('DBC' IN :OPTIONS) > 0)) -- control display of AR's on DBC
      WHERE (UDB.DATABASEID <> AR3.DATABASEID 
             OR (UDB.DATABASEID = AR3.DATABASEID AND POSITION('SELF' IN :OPTIONS) > 0)) -- control display of AR's on self
     ) AS DT1
INNER JOIN DBC.DBASE AS DB
  ON DT1.DATABASEID = DB.DATABASEID
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 5,6,7,8,2;
);

COMMENT ON MACRO AllUserRightsV AS 'VER=16.00.0001';

If you have any questions or comments please let me know.

 

Cheers,

Dave

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com