Skip to content

Steps to migrate Netezza database security groups across servers

Steps to migrate Netezza database security groups across servers:

1) Run below query in source server to make a list of groups to copy. Ignore this step if you already have a list of groups to move.

nzsql -At -c ” select groupname from _v_group where groupname like ‘DEV_LOG%’ or groupname like ‘DEV_DB%’ or groupname like ‘DEV_DB1%’ or groupname like ‘DEV_DB2%’;” > groups_move.lst

2) Create a script to generate a script for create group for all the groups in groups_move.lst (created above)

more generate_create_group.sh
for tab in `cat groups_move.lst`
do
nz_ddl_group $tab
done

chmod 775 generate_create_group.sh

3) Run generate_create_group.sh script and save output to create_group.sql

generate_create_group.sh > create_group.sql

create_group.sql will have a create group script for all the required groups.

4) Run create_group.sql script on target server system database

nzsql -host targetserver -u username -pw password -f create_group.sql

or copy the script to target server and run

nzsql -f create_group.sql

5) Now we need to know the database list in target server for which these groups permissions needs to me migrated. Once you have a list move to next step.

6) On source server run below query to get all the groups permissions in a database.

nz_ddl_grant_group DBNAME > DBNAME_groupaccess.sql

Run this for each database in step 5

Verify DBNAME_groupaccess.sql and remove any unwanted group that we are not migrating.

7) Now run DBNAME_groupaccess.sql script against respective DBNAME on target server

nzsql -host targetserver -u username -pw password -f DBNAME_groupaccess.sql

or copy the script to target srever and run

nzsql -f DBNAME_groupaccess.sql

Comments

  1. Srinivas says:

    Is there any way to assign one group permission to another group with in the same server?

    Thanks in Advance…

    Regards,
    Srinivas