Migrating users, groups, and privileges from IBM PureData® System for Analytics (Netezza) to Db2

As part of Netezza® to Db2® migration, you must migrate your Netezza users, groups, and privileges.

1. Collect information about your Netezza environment by issuing the statements and commands in the following table for each Netezza database.
   To issue each SELECT statement, put it in a file and issue the following command:

   nzsql dbname –f sql_file > output_file_dbname

   Information to collect	Statement or command to issue
   List of users	SELECT USERNAME FROM _V_USER
   List of groups	SELECT DISTINCT(GROUPNAME) FROM _V_USERGROUPS
   List of users and their associated groups	SELECT USERNAME, GROUPNAME FROM _V_GROUPUSERS
   List of group privileges	nz_ddl_grant_group -usrobj dbname > output_file_dbname
   List of user privileges	nz_ddl_grant_user -usrobj dbname > output_file_dbname

2. Replicate the roles on your Db2 system as follows:
   1. Create the roles by issuing the following statement:

      CREATE ROLE ROLE_NAME

   2. Verify that the roles were added in one of the following ways:
      • Issue the dbsql command with the \dr option.
      • Issue the following statement:

        SELECT ROLENAME FROM SYSCAT.ROLES

3. Grant privileges and authorities to roles as follows.
   For a mapping of Netezza privileges to Db2 privileges and authorities, see IBM PureData System for Analytics (Netezza) and Db2 privilege compatibility.

   1. Issue the following statements:

      GRANT PRIVILEGE ON OBJECT TO ROLE ID
      GRANT AUTHORITY ON DATABASE TO ROLE ID

   2. Verify that the privileges were granted by issuing the following statement:

      
      SELECT  *  FROM  SYSIBMADM.PRIVILEGES  WHERE  AUTHID='BLUADMIN’ AND OBJECTNAME =’MY_OBJECT’

   3. Verify that the authorities were granted by issuing the following statement:

      SELECT CHAR(GRANTEE,8) AS GRANTEE, CHAR(GRANTEETYPE,1) AS TYPE, 
      CHAR(DBADMAUTH,1) AS DBADM, CHAR(CREATETABAUTH,1) AS CREATETAB,  
      CHAR(BINDADDAUTH,1) AS BINDADD, CHAR(CONNECTAUTH,1) AS CONNECT, 
      CHAR(NOFENCEAUTH,1) AS NOFENCE, CHAR(IMPLSCHEMAAUTH,1) AS IMPLSCHEMA, 
      CHAR(LOADAUTH,1) AS LOAD, CHAR(EXTERNALROUTINEAUTH,1) AS EXTROUTINE,  
      CHAR(QUIESCECONNECT AUTH,1) AS QUIESCECONN, CHAR(LIBRARYADMAUTH,1) AS LIBADM, 
      CHAR(SECURITYADMAUTH,1) AS SECURITYADM 
      FROM  SYSCAT.DBAUTH WHERE GRANTEE=’BLUADMIN’

4. Replicate the users on your Db2 system as follows:
   1. Create the users by using one of the following methods:
      • Use the web console.
      • Use the REST API as follows:

        
        curl -k -u "bluadmin:bluadmin" 
        -H 'Content-Type: application/json'
        -H 'Accept: t' 
        -d '{"users": 
        [{"password":"password","userid":"userid","userProfile":"","userRole":[{"role":"Administrator"}]}]}' 
        -X POST "https://<dashlocal-host>:8443/dashdb-api/users" 

   2. Check that the users were created by using one of the following methods:
      • Use the web console.
      • Issue the dbsql command with the \du option.
5. Grant roles to users as follows:
   1. Issue the following statement:

      GRANT ROLE ROLE1 TO USER USER1

   2. Verify the role–user mapping by issuing the dbsql command with the \dR option.
6. If necessary, grant privileges and authorities directly to users.
   This is usually not recommended: using roles simplifies administration.

   To grant privileges and authorities to users, adapt the instructions in step 3.