Question & Answer
Question
You have migrated all the data and databases from current netezza box to new netezza box, using nz_migrate. How we can migrate users, groups and permissions?
Cause
When you migrate databases from one NPS system to another using the nz_migrate command, users, groups and global permissions need to be restored separately.
Answer
The nzbackup -users command will backup all users and groups regardless of whether they are referenced by any permission grants and global level
privileges. The global level privileges include GRANT operation on various databases for a particular user such as
GRANT CREATE TABLE TO <user>.
[nz@nzfabric userBackup]$ nzbackup -users -dir /export/home/nz/user3/userBackup/
Backup of users, groups, and global permissions completed successfully.
(The nzbackup -users command will not backup database level privileges like GRANT LIST ON <database> TO <user> or GRANT SELECT ON <table> TO <user>.
The nzbackup -db <database> command backs up all privileges for a specified database along with database objects. )
You can also backup database level privileges using the nz_ddl script located in the /nz/support/contrib/<NPS Version>/bin directory. The
following example shows how to create DDL for a database and its users or groups and privileges. You can grep out necessary lines.
.
[nz@nzfabric bin]$ nz_ddl test | grep -w GRANT
GRANT select ON "tab1" TO "user2" ;
GRANT list ON "test" TO "user1" ;
The nzrestore -users command is used to restore the backup of all users/groups and global level privileges. This command does not modify
existing users, groups and privileges information. It will only add new users/groups/permissions if they do not exist.
[nz@nzfabric1 userBackup]$ nzrestore -users -dir /export/home/nz/user3/userBackup/ -npshost nzfabric
Restore of users, groups, and global permissions completed successfully.
A backup and restore with the -users option does not include the admin user or the public group.
Was this topic helpful?
Document Information
Modified date:
17 October 2019
UID
swg22007319