Lists all user/group permissions for each object in every database

#NETEZZA list_all_permissions.md

Lists all user/group permissions

SELECT CASE WHEN _V_USER.USERNAME  IS NOT NULL THEN 'USER'
           WHEN _V_GROUP.GROUPNAME IS NOT NULL THEN 'GROUP'
               ELSE 'UNKNOWN' END                            AS USERS_GROUPS,
        COALESCE(_V_USER.USERNAME,_V_GROUP.GROUPNAME)        AS NAME,             
    NVL(_V_DATABASE. DATABASE,'GLOBAL' )                     AS DATABASE ,
    _T_OBJECT.OBJNAME                                        AS OBJECTNAME,
    NVL(_V_RELOBJCLASSES.CLASSNAME, 'CLASS')                 AS OBJECTTYPE,
--- WILL DISPLAY 1 OR NULL (EMPTY) IF USERS HAS PERMISSIONS.
    CASE WHEN ( MOD (   PRIVILEGES ,      2 ) /       1) = 1 THEN 1 ELSE NULL END AS "LIST",
    CASE WHEN ( MOD (   PRIVILEGES ,      4 ) /       2) = 1 THEN 1 ELSE NULL END AS "SELECT",
    CASE WHEN ( MOD (   PRIVILEGES ,      8 ) /       4) = 1 THEN 1 ELSE NULL END AS "INSERT",
    CASE WHEN ( MOD (   PRIVILEGES ,     16 ) /       8) = 1 THEN 1 ELSE NULL END AS "UPDATE",
    CASE WHEN ( MOD (   PRIVILEGES ,     32 ) /      16) = 1 THEN 1 ELSE NULL END AS "DELETE",
    CASE WHEN ( MOD (   PRIVILEGES ,     64 ) /      32) = 1 THEN 1 ELSE NULL END AS "TRUNCATE",
    CASE WHEN ( MOD (   PRIVILEGES ,    128 ) /      64) = 1 THEN 1 ELSE NULL END AS "LOCK",
    CASE WHEN ( MOD (   PRIVILEGES ,    256 ) /     128) = 1 THEN 1 ELSE NULL END AS "ALTER",
    CASE WHEN ( MOD (   PRIVILEGES ,    512 ) /     256) = 1 THEN 1 ELSE NULL END AS "DROP",
    CASE WHEN ( MOD (   PRIVILEGES ,   1024 ) /     512) = 1 THEN 1 ELSE NULL END AS "ABORT",
    CASE WHEN ( MOD (   PRIVILEGES ,   4096 ) /    2048) = 1 THEN 1 ELSE NULL END AS "LOAD",
    CASE WHEN ( MOD (   PRIVILEGES ,   8192 ) /    4096) = 1 THEN 1 ELSE NULL END AS "GENSTATS",
    CASE WHEN ( MOD (   PRIVILEGES ,  32768 ) /   16384) = 1 THEN 1 ELSE NULL END AS "GROOM",
    CASE WHEN ( MOD (   PRIVILEGES ,  16384 ) /    8192) = 1 THEN 1 ELSE NULL END AS "EXECUTE",
    CASE WHEN ( MOD (   PRIVILEGES , 131072 ) /   65536) = 1 THEN 1 ELSE NULL END AS "LABEL ACCESS",
    CASE WHEN ( MOD (   PRIVILEGES , 262144 ) /  131072) = 1 THEN 1 ELSE NULL END AS "LABEL RESTRICT",
    CASE WHEN ( MOD (   PRIVILEGES , 524288 ) /  262144) = 1 THEN 1 ELSE NULL END AS "LABEL EXPAND",
    CASE WHEN ( MOD (   PRIVILEGES , 1048576 ) / 524288) = 1 THEN 1 ELSE NULL END AS "EXECUTE AS",
    --AS PRIVILEGES,
    CASE WHEN ( MOD ( G_PRIVILEGES ,      2 ) /       1) = 1 THEN 1 ELSE NULL END AS "GRANT LIST",
    CASE WHEN ( MOD ( G_PRIVILEGES ,      4 ) /       2) = 1 THEN 1 ELSE NULL END AS "GRANT SELECT",
    CASE WHEN ( MOD ( G_PRIVILEGES ,      8 ) /       4) = 1 THEN 1 ELSE NULL END AS "GRANT INSERT",
    CASE WHEN ( MOD ( G_PRIVILEGES ,     16 ) /       8) = 1 THEN 1 ELSE NULL END AS "GRANT UPDATE",
    CASE WHEN ( MOD ( G_PRIVILEGES ,     32 ) /      16) = 1 THEN 1 ELSE NULL END AS "GRANT DELETE",
    CASE WHEN ( MOD ( G_PRIVILEGES ,     64 ) /      32) = 1 THEN 1 ELSE NULL END AS "GRANT TRUNCATE",
    CASE WHEN ( MOD ( G_PRIVILEGES ,    128 ) /      64) = 1 THEN 1 ELSE NULL END AS "GRANT LOCK",
    CASE WHEN ( MOD ( G_PRIVILEGES ,    256 ) /     128) = 1 THEN 1 ELSE NULL END AS "GRANT ALTER",
    CASE WHEN ( MOD ( G_PRIVILEGES ,    512 ) /     256) = 1 THEN 1 ELSE NULL END AS "GRANT DROP",
    CASE WHEN ( MOD ( G_PRIVILEGES ,   1024 ) /     512) = 1 THEN 1 ELSE NULL END AS "GRANT ABORT",
    CASE WHEN ( MOD ( G_PRIVILEGES ,   4096 ) /    2048) = 1 THEN 1 ELSE NULL END AS "GRANT LOAD",
    CASE WHEN ( MOD ( G_PRIVILEGES ,   8192 ) /    4096) = 1 THEN 1 ELSE NULL END AS "GRANT GENSTATS",
    CASE WHEN ( MOD ( G_PRIVILEGES ,  32768 ) /   16384) = 1 THEN 1 ELSE NULL END AS "GRANT GROOM",
    CASE WHEN ( MOD ( G_PRIVILEGES ,  16384 ) /    8192) = 1 THEN 1 ELSE NULL END AS "GRANT EXECUTE",
    CASE WHEN ( MOD ( G_PRIVILEGES , 131072 ) /   65536) = 1 THEN 1 ELSE NULL END AS "GRANT LABEL ACCESS",
    CASE WHEN ( MOD ( G_PRIVILEGES , 262144 ) /  131072) = 1 THEN 1 ELSE NULL END AS "GRANT LABEL RESTRICT",
    CASE WHEN ( MOD ( G_PRIVILEGES , 524288 ) /  262144) = 1 THEN 1 ELSE NULL END AS "GRANT LABEL EXPAND",
    CASE WHEN ( MOD ( G_PRIVILEGES , 1048576 ) / 524288) = 1 THEN 1 ELSE NULL END AS "GRANT EXECUTE AS"
    --AS GRANT_PRIVILEGES,
FROM
(
----- OBJECTS THAT USERS HAVE EXPLICITLY BEEN GRANTED ACCESS TO
    SELECT
             UOPDB                   AS DATABASE_OBJID,
             UOPOBJECT               AS OBJECT_OBJID,
             UOPOBJPRIV              AS PRIVILEGES,
             UOPGOBJPRIV             AS G_PRIVILEGES,
             UOPUSER                 AS USER_OBJID,
             NULL::INT4              AS GROUP_OBJID
    FROM   _T_USROBJ_PRIV
    WHERE  UOPOBJECT != 0               -- SKIP ADMIN PRIVILEGES
UNION ALL
--- GET USERS THAT DONT HAVE PERMISSIONS DIRECTLY GRANTED.
    SELECT 0 ,0 ,0 ,0 ,OBJID,0  --- PERMISSIONS MAY BE INHERITED THROUGH GROUPS.
    FROM _V_USER           
    WHERE OBJID <> 4900      -- EXCLUDE ADMIN USER
         AND OBJID NOT IN ( SELECT DISTINCT UOPUSER FROM _T_USROBJ_PRIV)
UNION ALL
----- OBJECTS THAT GROUPS HAVE EXPLICITLY BEEN GRANTED ACCESS TO
    SELECT
             GOPDB                   AS DATABASE_OBJID,
             GOPOBJECT               AS OBJECT_OBJID,
             GOPOBJPRIV              AS PRIVILEGES,
             GOPGOBJPRIV             AS G_PRIVILEGES,
             NULL::INT4              AS USER_OBJID,
             GOPGROUP                AS GROUP_OBJID
    FROM    _T_GRPOBJ_PRIV
    WHERE   GOPOBJECT != 0               -- SKIP ADMIN PRIVILEGES
) AS SUB1
INNER JOIN         _T_OBJECT         ON (SUB1.OBJECT_OBJID = _T_OBJECT.OBJID)
LEFT OUTER JOIN    _V_RELOBJCLASSES  ON (_T_OBJECT.OBJCLASS = _V_RELOBJCLASSES.OBJCLASS)
LEFT OUTER JOIN    _V_DATABASE       ON (SUB1.DATABASE_OBJID = _V_DATABASE.OBJID)
LEFT OUTER JOIN    _V_USER           ON (SUB1.USER_OBJID = _V_USER.OBJID)
LEFT OUTER JOIN    _V_GROUP          ON (SUB1.GROUP_OBJID = _V_GROUP.OBJID)
ORDER BY 2 ,3 ,4 ,5;

Credit to Sergey Dubov