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. 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.
- Create a view user_ddl which contains the grants on all objects in the database.
- Select and save to a file the view's SQL column, ordered on the grant_order column.
- Upgrade Vertica.
- Select and save to a different file the view's SQL column with the same command.
- Create a diff between
pre-upgrade.txt
andpost-upgrade.txt
. This collects the missing grants intogrants-list.txt
. - To restore any missing grants, run the remaining grants in
grants-list.txt
, if any.
=> 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 roles ) UNION ALL ( SELECT 1 AS step, -- 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 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 STORAGE 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 || '.', '') || object_name || CASE WHEN object_type = 'PROCEDURE' THEN (SELECT DECODE(procedure_argument_types, '', '()', '(' || procedure_argument_types || ')') 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 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 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 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 vs_schemata ) UNION ALL ( select 6, 'public', 'ALTER DATABASE ' || database_name || ' SET disableinheritedprivileges = ' || current_value || ';' , 'DATABASE', database_name from vs_configuration_parameters cross join databases where parameter_name ilike '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 );
=> \o pre-upgrade.txt SELECT sql FROM user_ddl ORDER BY grant_order ASC; \o
=> \o post-upgrade.txt SELECT sql FROM user_ddl ORDER BY grant_order ASC; \o
$ diff pre-upgrade.txt post-upgrade.txt > grants-list.txt
=> \i 'grants-list.txt'