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'