See related post: Netezza: user & group permissions view (simple version)
This query replicates \dpu and \dpgu commands at nzsql terminal. Query produces the same output as \dpu and \dpgu do with some added enhancements. The SQL query has the following features:
- Query adds concatenated list of members for users groups, for groups list of users.
- Query adds users that doen have permissions directly granted. \dpu and \dpgu displays no records in this case. In case of user has no direct permissions granted, query displays one record with all nulls.
- Query omits ADMIN user and PUBLIC group in the concatenated list.
For ease of use, consider creating a database view based on this query:
---
--- DEPENDENCY: Must have installed SQL Extention Toolkit.
---
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:
SYSTEM.ADMIN(ADMIN)=> \dpgu ADMIN_GRP
--- This is output from the query above:
select * from nzdba..V_PERMISSIONS;
Pulling same data in Excel for easy filtering and navigation:
Hi,
ReplyDeleteHow to find which users has create permission on table on a database.
Thank you,
Abbas
Very useful, but the admin privileges are missing...can you complete??
ReplyDelete