Back up and restore users, groups, and permissions
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.
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.
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 touser1
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 foruser1
, 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.