Creating an administrative user group

About this task

As described in Default Netezza Performance Server groups and users, the default admin user account is a powerful database super-user account. Use that account rarely, such as for documented maintenance or administrative tasks, or when you first set up an Netezza Performance Server for Cloud Pak for Data System system.

For continuing administration tasks, create an administration group that reflects an appropriate set of permissions and capabilities. You might decide to give your admin users an equivalent set of permissions as admin, or only a subset of permissions. You can then assign users to that group to grant them their administrative permissions. Additionally, this group can also be used as a resource group to specify how much of the resources these administrative users should receive compared to the users in other resource groups. If you do not use resource management, then the administrative users are considered equal to the other users (except admin) when they compete for resources. If you use resource management, you can use GRA to allocate a percentage of system resources for them compared to the other resource groups.

To create an administrators group that provides similar object and administrative privileges as the admin user, complete the following steps:

Procedure

  1. Connect to the System database as the admin user.
    For example: 
    [nz@nzhost ~]$ nzsql -d system -u admin -pw password 
Welcome to nzsql, the Netezza SQL interactive terminal.
  2. Create a group for your administrative users.
    For example: 
    SYSTEM.ADMIN(ADMIN)=> CREATE GROUP administrators;
CREATE GROUP
  3. Grant the group all administrative permissions.
    For example: 
    SYSTEM.ADMIN(ADMIN)=> GRANT ALL ADMIN TO administrators WITH GRANT 
OPTION;
GRANT
  4. Grant the group all object permissions.
    For example: 
    SYSTEM.ADMIN(ADMIN)=> GRANT ALL ON DATABASE, GROUP, SCHEMA, SEQUENCE,
SYNONYM, TABLE, EXTERNAL TABLE, FUNCTION, AGGREGATE, USER, VIEW, PROCEDURE, 
LIBRARY TO administrators WITH GRANT OPTION;
GRANT
  5. Grant the group select permissions on all system and management objects.
    For example:
    SYSTEM.ADMIN(ADMIN)=> GRANT LIST, SELECT ON SYSTEM TABLE, SYSTEM VIEW, 
MANAGEMENT TABLE, MANAGEMENT VIEW TO administrators WITH GRANT OPTION;
GRANT
  6. Add users to the group to grant them the permissions of the group.
    For example: 
    SYSTEM.ADMIN(ADMIN)=> ALTER USER jlee WITH IN GROUP administrators;
ALTER USER
    or 
    SYSTEM.ADMIN(ADMIN)=> ALTER GROUP administrators WITH USER jlee, bob;
ALTER GROUP