How does Privilege Precedence work on IBM PureData Systems for Analytics?

When customer grant a global privilege, as we will show on this example using nzbackup, the user will be able to backup all the databases from the NPS system.

As example we will create user bkpusr1 and add this user to the group bkp and later grant global backup privilege to bkp group:

SYSTEM.ADMIN(ADMIN)=> create userbkpusr1 with password 'password';
CREATE USER
SYSTEM.ADMIN(ADMIN)=> create groupbkp add user bkpusr1;
CREATE GROUP
SYSTEM.ADMIN(ADMIN)=> grant backup to group bkp;
GRANT 

Verify the privilege for group bkp:

SYSTEM.ADMIN(ADMIN)=> \dpG bkp
                                             Group object permissions for group 'BKP'
Database Name | Schema Name | Object Name | L S I U D T L A D B L G O E C R X A | D G U S T E X Q Y V M I B R C S H F A L P N S R
---------------+-------------+-------------+-------------------------------------+-------------------------------------------------
GLOBAL        | GLOBAL      | GLOBAL      |                                     |                         X
(1 rows) 

After that we are able to backup all the databases with user id bkpusr1

nzbackup -db TESTDB -u bkpusr1  -pw password -dir  /nzscratch/bkp_dir/
Backup of database TESTDB to backupset 20141115222940 completed successfully. 

The problem will occur when we grant any one of the privileges below directly to the database.

SYSTEM.ADMIN(ADMIN)=> grant create sequence, create synonym, create table, create external table, create temp table, create AGGREGATE, create view, create materialized view, create procedure, create library, restore, unfence, vacuum IN "TESTDB".ALL to group bkp; 

Verify the actual privileges from group bkp, you will see that GLOBAL still there, but also you have granted direct on database TESTDB, in this case you will loose the backup on TESTDB as show example below:

SYSTEM.ADMIN(ADMIN)=> \dpG bkp
                                             Group object permissions for group 'BKP'
Database Name | Schema Name | Object Name | L S I U D T L A D B L G O E C R X A | D G U S T E X Q Y V M I B R C S H F A L P N S R
---------------+-------------+-------------+-------------------------------------+-------------------------------------------------
TESTDB        | GLOBAL      | GLOBAL      |                                     |         X X X X X X X     X X       X X X X
GLOBAL        | GLOBAL      | GLOBAL      |                                     |                         X
(2 rows) 

In this case the global privilege of backup will failed for database TESTDB.

nzbackup -db TESTDB -u bkpusr1 -pw password -dir  /nzscratch/bkp_dir/
Error: login failed - you must have 'BACKUP' privileges to perform this action. 

In order to be able to use GLOBAL backup privilege we will have to revoke all the privileges that we grant direct to TESTDB database.

SYSTEM.ADMIN(ADMIN)=> revoke create sequence, create synonym, create table, create external table, create temp table, create AGGREGATE, create view, create materialized view, create procedure, create library, restore, unfence, vacuum IN "TESTDB".ALL from group bkp ; 

After that we will be able to run backups again.

Have in mind that regarding to Privilege Precedence: IBM® Netezza® uses the following order of precedence for permissions:
• Privileges granted on a particular object within a particular database and a particular schema, for systems that support multiple schemas
• Privileges granted on an object class within a particular database and a particular schema, for systems that support multiple schemas
• Privileges granted on a particular object within all schemas of a particular database
• Privileges granted on an object class within all schemas of a particular database
• Privileges granted on an object within the system database
• Privileges granted on an object class within the system database