Netezza: GROOM priviliges to suspend MATERIALIZED VIEW when running nz_goom -mview

PROBLEM: 

Running  nz_goom -mview  renders error with materialized views: 

...

Table: SOME_TABLE

ERROR:  This script encountered an unexpected error when it tried to suspend
        the materialized views for the table 'SOME_TABLE'.
        This script is not going to continue.

ERROR:  ALTER VIEWS: permission denied.

...

.

Help for nz_goom -mview explains that is does the following steps with materialized views:

-mview  If a table has one (or more) active materialized views associated script will
                1) Automatically suspend any such materialized views
                2) Perform the GROOM operation
                3) Refresh the materialized views upon completion

SOLUTION:

Documentation for IBM PureData System for Analytics 7.2.0 suggests the following list of permissions for materialized view (aka SPM) ALTER VIEW and ALTER VIEWS ON.

For materialized views

You must be the admin user, or the owner of the database or schema where the view is defined. For all other users, the following table lists the privileges required.

Table 3. Materialized view privileges

Task	Privilege
Create an SPM view	Your account must have the Create Materialized View administration privilege.
Alter an SPM view	Your account must have the Alter object privilege for a specific view or the View object class.
Drop an SPM view	Your account must have the Drop object privilege for a specific view or the View object class.
Select from an SPM view	Your account must have the Select object privilege for a specific view or the View object class.
Alter Views on a table	Your account must have the Insert object privilege for a specific table or the Table object class.
List on SPM views	Your account must have the List object privilege for a specific view or the View object class.

Refer: ALTER VIEW and ALTER VIEWS ON.

However, granting all listed permission still does NOT solves the problem.  nz_groom still responds with the same error.  Experimentally, it was found that LOAD ON TABLE permission has to be granted too for nz_groom -mview to succeed.

           grant LOAD, INSERT ON TABLE to <my_user>;

---

PUTTING THIS ALL TOGETHER:

This is minimal permission set for a user to succeed running nz_goom -mview  without errors: 

create user ADMIN4GROOM with password 'xxxxx' AUTH LOCAL;

set catalog SYSTEM ; 

grant LIST   on DATABASE          to ADMIN4GROOM;

grant SELECT on MANAGEMENT TABLE to ADMIN4GROOM;

grant SELECT on SYSTEM TABLE      to ADMIN4GROOM;  

grant LIST

     ,SELECT

     ,INSERT

     ,LOAD

     ,GROOM  on TABLE            to ADMIN4GROOM; 

grant CREATE MATERIALIZED VIEW    to ADMIN4STATS;

grant  ALTER                              

      ,DROP  

      ,SELECT 

      ,LIST  on MATERIALIZED VIEW to ADMIN4GROOM;

grant ALTER   on  VIEW             to ADMIN4GROOM;

Related documentation: Tips for creating materialized views and Groom and backup synchronization

Netezza: grant UPDATE ON SEQUENCE permission for SELECT NEXT VALUE FOR

In order to run  SELECT NEXT VALUE FOR <my_sequence> that are owned by other users in that same database an executing user must have an UPDATE ON SEQUENCE permission on all sequences within this database. Just having CREATE SEQUENCE is not sufficient.  Issue shows up when user creates a  sequence and changes ownership to ADMIN, typically, for cosmetic reasons.  To solve this problem you must issue  grant UPDATE ON SEQUENCE do:

grant UPDATE ON SEQUENCE TO <my_user>;

EXAMPLE: 

A user TESTSD creates a sequence and successfully tests it:

CREATE SEQUENCE my_sequence;

SELECT NEXT VALUE FOR my_sequence;

/* Records Affected: 1. */

Change ownership of the newly created sequence:

ALTER SEQUENCE my_sequence OWNER TO ADMIN;

Selecting value from the same sequence fails:

/* ERROR [HY000] ERROR:  Permission denied on "MY_SEQUENCE". */

SELECT NEXT VALUE FOR my_sequence;

As ADMIN user,  grant UPDATE ON SEQUENCE to user TESTSD;

[nz@server]$ nzsql -db SANDBOX -u ADMIN

SANDBOX.ADMIN(ADMIN)=> grant UPDATE ON SEQUENCE TO TESTSD;

GRANT

Now, TESTSD user has ability to use sequence:

SELECT NEXT VALUE FOR sandbox..y_sequence;

/* Records Affected: 1. */

SELECT NEXT VALUE FOR my_sequence;

/* Records Affected: 1. */