Back up and restore users, groups, and permissions

Deployment options: Netezza Performance Server for Cloud Pak for Data System Netezza Performance Server for Cloud Pak for Data

When you back up a database by using nzbackup -db dbname, the backup includes permissions on objects in the database, and any users and groups that are referenced by those permissions. The database backup contains only the users, groups, and privileges as saved in the specific database; it does not include global privileges that are defined in the system catalog.

To back up all users, groups, global permissions, specify nzbackup -globals. The nzbackup -globals command backs up all users and groups regardless of whether they are referenced by any permission grants within a database, and any security categories, cohorts, and levels for multi-level security. The system also backs up all global-level permissions that are not associated with particular databases. The system does not back up permissions that are defined in specific databases. Those permissions are saved in the regular database backups for those databases.

For example, suppose that you have four users (user1 to user4) and you grant them the following permissions:
nzsql
SYSTEM.ADMIN(ADMIN)=> GRANT CREATE TABLE TO user1;
SYSTEM.ADMIN(ADMIN)=> \c db_product 
DB_PRODUCT.SCH(ADMIN)=> GRANT CREATE TABLE TO user2;
DB_PRODUCT.SCH(ADMIN)=> GRANT LIST ON TABLE TO user3;
DB_PRODUCT.SCH(ADMIN)=> GRANT LIST ON emp TO user4; 

User1 has global Create Table permission, which allows table creation in all databases. User2 and User3 have Create and List permission to tables in the db_product database. User4 has List permission only to the emp table in the database db_product.

The following table describes the results when you start the nzbackup and nzrestore commands with different options.

Table 1. Backup and Restore Behavior
Method User backed up/restored Permission backed up/restored
nzbackup/nzrestore -db db_product user2 CREATE tables in the db_product database.
user3 LIST on all tables in the db_product database.
user4 LIST on the emp table in the db_product database.
nzbackup/nzrestore -globals user1 CREATE tables in the system database.
user2  
user3  
user4  
  • A regular backup of the db_product database does not include user1 or the CREATE TABLE GRANT to user1 because those privileges are defined in the system database (the system catalog).
  • A -globals backup and restore includes all users (in this case, users1 - user4), but it includes only the Create Table permission for user1, which is also defined in the system database. The -globals backup and restore does not include the privileges that are defined specifically in the db_product database.
  • A -globals backup and restore does not include the admin user or the public group.

By using the nzrestore -globals command, you can restore users, groups, and permissions. The restoration of users and groups is nondestructive, that is, the system creates users and groups only if they do not exist. It does not drop users and groups. Permission restoration is also nondestructive, that is, the system only grants permissions. It does not revoke permissions.

Remember: When you restore data and users from a backup, the process reverts your system to a point in the past when the backup was made. Your user community and their access rights might change, or if you are restoring to a new system, a stale backup might not reflect your current user community. After you make any significant user community changes, back up the latest changes. After you restore from a backup, check that the resulting users, groups, and permissions match your current community permissions.