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.

1. Create a view 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 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
);
				

2. Select and save to a file the view's SQL column, ordered on the grant_order column.

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

3. Upgrade Vertica.
4. 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

5. 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

6. To restore any missing grants, run the remaining grants in grants-list.txt, if any.

=> \i 'grants-list.txt'