See related post: Netezza: user & group permissions view
This SQL replicates \dpu and \dpgu commands at nzsql terminal. The augmented version of these beloved commands outputs same data in relational (rows and columns) table format. This is simple version of SQL query intended to run on any NPS. This simplified query does not have dependencies. It will run at any Netezza appliance provided you have proper permissions to access underlying system views.
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
;
Output examples:
This is original server-side nzsql output:
SYSTEM.ADMIN(ADMIN)=> \dpgu ADMIN_GRP
--- This is output from the query above:
select * from nzdba..V_PERMISSIONS;
No comments:
Post a Comment