---
---
select 'DEV' as ENV,
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,
lst.members as MEMBERS,
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",
----
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"
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)
left outer join (
--- Concatenate in comma delimited list: USERS and GROUPS
select USERNAME as uname,
SQLEXT.. replace ( --- for Users: list all groups the user is part of.
SQLEXT.. replace(
SQLEXT..XMLserialize(
SQLEXT..XMLagg(
SQLEXT..XMLElement( 'X',GROUPNAME)))
, '<X>','' )
, '</X>',',' ) as members
from SYSTEM .ADMIN ._V_USERGROUPS
where GROUPNAME <> 'PUBLIC'
GROUP BY USERNAME
UNION ALL
select GROUPNAME,
SQLEXT.. replace ( --- for Groups: list all users part of the group.
SQLEXT.. replace(
SQLEXT..XMLserialize(
SQLEXT..XMLagg(
SQLEXT..XMLElement( 'X',USERNAME)))
, '<X>','' )
, '</X>',',' )
from SYSTEM .ADMIN ._V_USERGROUPS
where GROUPNAME <> 'PUBLIC'
GROUP BY GROUPNAME
) as lst on (lst.uname = coalesce(_v_user.username,_v_group.groupname))
ORDER BY 2 ,3 ,4 ,5
;
Output example:
This is original server-side output for nzsql \dpgu command:
SYSTEM.ADMIN(ADMIN)=> \dpgu ADMIN_GRP
