Backing up and restoring grants

After an upgrade, if the prototypes of UDx libraries change, Vertica will drop the grants on those libraries since they aren't technically the same function anymore.

After an upgrade, if the prototypes of UDx libraries change, Vertica will drop the grants on those libraries since they aren't technically the same function anymore. To resolve these types of issues, it's best practice to back up the grants on these libraries so you can restore them after the upgrade.

  1. Save the following SQL to a file named user_ddl.sql. It creates a view named user_ddl which contains the grants on all objects in the database.

    CREATE OR REPLACE VIEW user_ddl AS
    (
    SELECT 0 as grant_order,
           name principal_name,
           'CREATE ROLE "' || name || '"' ||    ';' AS sql,
           'NONE' AS object_type,
           'NONE' AS object_name
      FROM v_internal.vs_roles vr
     WHERE NOT vr.predefined_role -- Exclude system roles
       AND ldapdn = ''            -- Limit to NON-LDAP created roles
    )
    UNION ALL
    (
      SELECT 1, -- CREATE USERs
             user_name,
             'CREATE USER "' || user_name || '"' ||
             DECODE(is_locked, TRUE, ' ACCOUNT LOCK', '') ||
             DECODE(grace_period, 'undefined', '', ' GRACEPERIOD  ''' || grace_period || '''') ||
             DECODE(idle_session_timeout, 'unlimited', '', ' IDLESESSIONTIMEOUT ''' || idle_session_timeout || '''') ||
             DECODE(max_connections, 'unlimited', '', ' MAXCONNECTIONS ' || max_connections || ' ON ' || connection_limit_mode) ||
             DECODE(memory_cap_kb, 'unlimited', '', ' MEMORYCAP ''' || memory_cap_kb || 'K''') ||
             DECODE(profile_name, 'default', '', ' PROFILE ' || profile_name) ||
             DECODE(resource_pool, 'general', '', ' RESOURCE POOL ' || resource_pool) ||
             DECODE(run_time_cap, 'unlimited', '', ' RUNTIMECAP ''' || run_time_cap || '''') ||
             DECODE(search_path, '', '', ' SEARCH_PATH ' || search_path) ||
             DECODE(temp_space_cap_kb, 'unlimited', '', ' TEMPSPACECAP ''' || temp_space_cap_kb || 'K''') || ';' AS sql,
             'NONE' AS object_type,
             'NONE' AS object_name
        FROM v_catalog.users
       WHERE NOT is_super_user -- Exclude database superuser
         AND ldap_dn = ''      -- Limit to NON-LDAP created users
    )
    UNION ALL
    (
      SELECT 2, -- GRANTs
             grantee,
             'GRANT ' || REPLACE(TRIM(BOTH ' ' FROM words), '*', '') ||
             CASE
               WHEN object_type = 'RESOURCEPOOL' THEN ' ON RESOURCE POOL '
               WHEN object_type = 'STORAGELOCATION' THEN ' ON LOCATION '
               WHEN object_type = 'CLIENTAUTHENTICATION' THEN 'AUTHENTICATION '
               WHEN object_type IN ('DATABASE', 'LIBRARY', 'MODEL', 'SEQUENCE', 'SCHEMA') THEN ' ON ' || object_type || ' '
               WHEN object_type = 'PROCEDURE' THEN (SELECT ' ON ' || CASE REPLACE(procedure_type, 'User Defined ', '')
                                                                       WHEN 'Transform' THEN 'TRANSFORM FUNCTION '
                                                                       WHEN 'Aggregate' THEN 'AGGREGATE FUNCTION '
                                                                       WHEN 'Analytic' THEN 'ANALYTIC FUNCTION '
                                                                       ELSE UPPER(REPLACE(procedure_type, 'User Defined ', '')) || ' '
                                                                     END
                                                      FROM vs_procedures
                                                     WHERE proc_oid = object_id)
               WHEN object_type = 'ROLE' THEN ''
               ELSE ' ON '
             END ||
             NVL2(object_schema, object_schema || '.', '') || CASE WHEN object_type = 'STORAGELOCATION' THEN (SELECT '''' || location_path || ''' ON ' || node_name FROM storage_locations WHERE location_id = object_id) ELSE object_name END ||
             CASE
               WHEN object_type = 'PROCEDURE' THEN (SELECT CASE WHEN procedure_argument_types = '' OR procedure_argument_types = 'Any' THEN '()' ELSE '(' || procedure_argument_types || ')' END
                                                      FROM vs_procedures
                                                     WHERE proc_oid = object_id)
               ELSE ''
             END ||
             ' TO ' || grantee ||
             CASE WHEN INSTR(words, '*') > 0 THEN ' WITH GRANT OPTION' ELSE '' END
             || ';',
             object_type,
             object_name
      FROM (SELECT grantee, object_type, object_schema, object_name, object_id,
                     v_txtindex.StringTokenizerDelim(DECODE(privileges_description, '', ',' , privileges_description), ',')
                       OVER (PARTITION BY grantee, object_type, object_schema, object_name, object_id)
              FROM v_catalog.grants) foo
     ORDER BY CASE REPLACE(TRIM(BOTH ' ' FROM words), '*', '') WHEN 'USAGE' THEN 1 ELSE 2 END
    )
    UNION ALL
    (
      SELECT 3, -- Default ROLEs
             user_name,
             'ALTER USER "' || user_name || '"' ||
               DECODE(default_roles, '', '', ' DEFAULT ROLE ' || REPLACE(default_roles, '*', ''))  || ';' ,
             'NONE' AS object_type,
             'NONE' AS object_name
        FROM v_catalog.users
       WHERE default_roles <> ''
    )
    UNION ALL -- GRANTs WITH ADMIN OPTION
    (
      SELECT 4, user_name, 'GRANT ' || REPLACE(TRIM(BOTH ' ' FROM words), '*', '') || ' TO ' || user_name || ' WITH ADMIN OPTION;',
             'NONE' AS object_type ,
             'NONE' AS object_name
        FROM (SELECT user_name, v_txtindex.StringTokenizerDelim(DECODE(all_roles, '', ',', all_roles), ',') OVER (PARTITION BY user_name)
                FROM v_catalog.users
               WHERE all_roles <> '') foo
       WHERE INSTR(words, '*') > 0
    )
    UNION ALL
    (
      SELECT 5, 'public', 'ALTER SCHEMA ' || name || ' DEFAULT ' || CASE WHEN defaultinheritprivileges THEN 'INCLUDE PRIVILEGES;' ELSE 'EXCLUDE PRIVILEGES;' END, 'SCHEMA', name
        FROM v_internal.vs_schemata
       WHERE NOT issys -- Exclude system schemas
    )
    UNION ALL
    (
      SELECT 6, 'public', 'ALTER DATABASE ' || database_name ||  ' SET disableinheritedprivileges = ' || current_value || ';',
             'DATABASE', database_name
        FROM v_internal.vs_configuration_parameters
       CROSS JOIN v_catalog.databases
       WHERE parameter_name = 'DisableInheritedPrivileges'
    )
    UNION ALL -- TABLE PRIV INHERITENCE
    (
      SELECT 7, 'public' , 'ALTER TABLE ' || table_schema || '.' || table_name ||
             CASE WHEN inheritprivileges THEN ' INCLUDE PRIVILEGES;' ELSE ' EXCLUDE PRIVILEGES;' END,
             'TABLE' AS object_type,
             table_schema || '.' || table_name AS object_name
        FROM v_internal.vs_tables
        JOIN v_catalog.tables ON (table_id = oid)
    )
    UNION ALL -- VIEW PRIV INHERITENCE
    (
      SELECT 8, 'public', 'ALTER VIEW ' || table_schema || '.' || table_name || CASE WHEN inherit_privileges THEN ' INCLUDE PRIVILEGES;' ELSE ' EXCLUDE PRIVILEGES; ' END,
             'TABLE' AS object_type, table_schema || '.' || table_name AS object_name
        FROM v_catalog.views
    )
    UNION ALL
    (
      SELECT 9, owner_name, 'ALTER TABLE ' || table_schema || '.' || table_name || ' OWNER TO ' || owner_name || ';',
             'TABLE', table_schema || '.' || table_name
        FROM v_catalog.tables
    )
    UNION ALL
    (
      SELECT 10, owner_name, 'ALTER VIEW ' || table_schema || '.' || table_name || ' OWNER TO ' || owner_name || ';', 'TABLE',
             table_schema || '.' || table_name
        FROM v_catalog.views
    );
    
  2. From the Linux command line, run the script in the user_ddl.sql file:

    $ vsql -f user_ddl.sql
    CREATE VIEW
    
  3. Connect to Vertica using vsql.

  4. Export the content of the user_ddl's sql column ordered on the grant_order column to a file:

    => \o pre-upgrade.txt
    => SELECT sql FROM user_ddl ORDER BY grant_order ASC;
    => \o
    
  5. Upgrade Vertica.

  6. Select and save to a different file the view's SQL column with the same command.

    => \o post-upgrade.txt
    => SELECT sql FROM user_ddl ORDER BY grant_order ASC;
    => \o
    
  7. Create a diff between pre-upgrade.txt and post-upgrade.txt. This collects the missing grants into grants-list.txt.

    $ diff pre-upgrade.txt post-upgrade.txt > grants-list.txt
    
  8. To restore any missing grants, run the remaining grants in grants-list.txt, if any:

    => \i 'grants-list.txt'