Netezza Performance Server database users, groups, and roles

To access the database, users must have Netezza Performance Server database user accounts.

When a user accesses a Netezza Performance Server database, by means either of a nzsql command-line session or of another SQL interface, the database account determines the access privileges to database objects and the administrative permissions to various tasks and capabilities.

You can assign privileges to a specific database user account as needed. If you have several users who require similar privileges, you can create user groups to organize those users and thus simplify access management. You can also use roles to assign privileges to a number of users at a time.

Note: A group can be one or both of the following types:
User group
A group with one or more members is a user group. Each member of a user group inherits its privileges and other settings, with the exception of its resource minimum, resource maximum, and job maximum settings. User groups are used to simplify access management.
Resource group
A group that specifies a nonzero minimum resource percentage is a resource group. Each resource group also specifies a resource maximum and job maximum, either explicitly or by default. These three settings are called the group's resource settings. Each user is assigned to exactly one resource group. Resource groups are used for workload management.
A group can be both a user group and a resource group, but its user group and resource group aspects, including user group membership and resource group assignment, are completely separate:
  • A user might be assigned to a resource group but not be a member of that group. That user is unaffected by any privileges or settings of that group, except for the resource settings.
  • A user might be a member of a user group but be assigned to a different resource group. That user is unaffected by the user group's resource settings.

If a user is a member of more than one group, the user inherits the union of all privileges from those groups, plus any privileges that were assigned to the user account specifically. If you remove a user from a user group, the privileges that were provided by that group are removed from the user. For example, if you remove a user from a group that has the Create Table privilege, the user loses that privilege unless the user is a member of another group that grants that privilege or the user account was granted that privilege directly.

As a best practice, use groups to manage the privileges of your database users rather than managing user accounts individually. Groups are an efficient and a time-saving way to manage privileges, even if a group has only one member. Over time, you typically add new users, drop existing users, and change user privileges as roles evolve. New Netezza Performance Server software releases often add new privileges that you might need to apply to your users. Rather than manage these changes on an account-by-account basis, manage the privileges with groups and group membership.

A role is a potential grantee or grantor of privileges and of other roles. Similarly to a user, a role can own schemas and other objects. Roles can own database objects such as tables or functions. By using roles, you can also assign privileges.

This section describes how to manage users and groups by using the SQL commands.