Change Vertica User Privileges from Existing User to New user

In Vertica Copying User Privileges From ‘Existing User’ To ‘New User’

Here we need to follow four steps to copy the user privileges.

• For schema and resource pools
• For schema objects (tables, libraries, views, etc)
• Set search_path
• Set resource pool

 Note: Only a superuser can give privileges. Schema privileges must be granted first before schema object privileges.

For schema and resource pools: 

I am going to use "Grants" table to create, code to give Schema and Resource pool privileges to the new user from existing user. We can find user privileges in "Grants" table. This table provides information about privileges granted on various objects, the granting user and grantee user.

SELECT 'GRANT ' || REPLACE(privileges_description,'*','')  ||
       '   ON  SCHEMA ' || COALESCE(object_name,'') ||
       '   TO  <New User Name> ;'
FROM GRANTS
WHERE COALESCE(privileges_description,'') > ''

and object_type in ('SCHEMA', 'RESOURCE_POOL')

and grantee = '<Existing User Name>'
ORDER BY 1;

            Take the query result set and execute same.

 For schema objects (tables, libraries, views, etc)

SELECT 'GRANT ' || REPLACE(privileges_description,'*','')  ||
       '   ON ' || COALESCE(object_schema,'') || '.' || object_name ||
       '   TO  <New User Name> ;'
FROM GRANTS
WHERE COALESCE(privileges_description,'') > ''

and object_type not in ('SCHEMA', 'RESOURCE_POOL')

and grantee = '<Existing User Name>'
ORDER BY 1;

            Take the query result set and execute same.

Set search_path:

 I am going to use "Users" Table to create, code to set search path. 

SELECT 'ALTER USER <New User Name> SEARCH_PATH ' || SEARCH_PATH  || ';'
FROM USERS
WHERE USER_NAME = '<Existing User Name>';

Take the query result set and execute same.

 Set resource pool:

I am going to use "Users" Table to create, code to set search path.

SELECT 'ALTER USER <New User Name> resource_pool ' || resource_pool  || ';'
FROM USERS
WHERE USER_NAME = '<Existing User Name>';

Take the query result set and execute same.