Friday, March 4, 2016

Netezza: news NPS 7.0 is no longer maintained


From the IBM news letter: 

The last regularly scheduled quarterly Fixpack for NPS v7.0.4 was posted to Fix Central on 2015. While you can still open PMRs for this version and get support, any code changes to address your bug or enhancement will be available only in NPS version 7.1 and later. To obtain the updates, you must upgrade your NPS software to the later release. We strongly recommend that you upgrade NPS to the latest patch level available on NPS v7.2. Contact the customer support team by creating a PMR to upgrade your system. Please note that depending on your current RHEL and firmware levels, the NPS upgrade may require the OS and firmware to be upgraded as well.

The table below reflects the last dates on which IBM will deliver final fixes of defects for a given version/release of PureData System for Analytics Netezza Platform Software (NPS).
Major Release
Minor Release
Maintenance (Dot) Releases
Date Released
Comments
Final Fix
Version 6
6.0.3
Jul-2011
Dec-2013
6.0.5
Sep-2011
Dec-2013
6.0.8
Aug-2012
Dec-2014
Version 7
7.0.0
Aug-2012
Merged with 7.0.2*
Jun-2014
7.0.2
Jan-2013
Dec-2015
7.0.3
May-2013
Superceded by 7.0.4
Aug-2013
7.0.4
Oct-2013
Dec-2015
7.1.0
Feb-2014
Aug-2016
7.2.0
7.2.0
Oct-2014
Jun-2017
7.2.1
Nov-2015
Jun-2018
*7.0.0 and 7.0.2 have no feature content difference. 

Refer to IBM PureData System for Analytics Netezza Platform Software Final Fix (FF) Dates.


Thursday, March 3, 2016

Netezza: user & group permissions view


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:

This is original server-side output for nzsql \dpgu command:
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: