Easy procedure to clone a Vertica Database User

This is a very common task for a DBA(Database Administrator) and it can be time consuming in some cases. So in this article we will see how we can build a User Defined Procedure in HP Vertica to clone a Database user. We have to user User Defined Procedure due to the lack of programmability features offered by Vertica(Vertica was build for super smooth Sql stuff). So first we will go over the scripts we will use and them see how we can call them from Database as UDP(User Defined Procedure).

1 - First Script:

. /home/dbadmin/.profile
  • this file will have to contain the user and pasword used for the porcedure to run and execute(in most cases will be dbadmin user).
#!/bin/bash

username=db_user_name
password=db_user_password
export username
export password
  • we will use this "trick" to avoid text/hard coded password when running scripts against the database
Note:
  • this file is of extreme importance !!! so make sure only the owner has access to it.
[dbadmin]$ ls -la /home/dbadmin/.profile
-rwx------ 1 dbadmin verticadba 179 Jul  6 12:14 /home/dbadmin/.profile

2 - Second Script

/vertica/catalog/database_name/procedures/clone_user.sh 
/opt/vertica/bin/vsql -U $username -w $password -t -o /tmp/create_user.sql -c"
SELECT
    ' CREATE USER $1 identified by ''$2'' RESOURCE POOL '||resource_pool ||
    ' search_path '|| search_path ||'; '
FROM
    users
WHERE
    user_name='$3'
UNION ALL
SELECT
    ' ALTER USER $1 DEFAULT ROLE all;'
FROM
    users
WHERE
    user_name='$3'
UNION ALL
SELECT
    'GRANT ' || REPLACE(privileges_description, '*', '') || ' ON ' || COALESCE(object_schema, '')
    || '.' || object_name || ' TO ' || '$1' || ';' AS GRANTS
FROM
    v_catalog.grants
WHERE
    COALESCE(privileges_description, '')  ''
AND grantee='$3'
AND object_TYPE NOT IN ('SCHEMA',
                        'RESOURCEPOOL')
UNION ALL
SELECT
    'GRANT ' || REPLACE(privileges_description, '*', '') || ' ON schema ' || COALESCE(object_schema
    , '') || object_name || ' TO ' || '$1' || ';' AS GRANTS
FROM
    v_catalog.grants
WHERE
    COALESCE(privileges_description, '')  ''
AND grantee='$3'
AND object_TYPE IN ('SCHEMA');
"
  • this script will generate the final SQL that will contain the new user SQL definition.
  • note that we have 3 parameter that we pass to this script $1(new_user),$2(password),$3(old_user), they will be echoed into the /tmp/create_user.sql.

3 - Third Script

Script execution. 
/opt/vertica/bin/vsql -U $username -w $password -t -f /tmp/create_user.sql
  • this part is pretty obvious, will execute the content of the /tmp/create_user.sql script

Full script

  • this will hold all the described script above.
#!/bin/bash
. /home/dbadmin/.profile

/opt/vertica/bin/vsql -U $username -w $password -t -o /tmp/create_user.sql -c"
SELECT
    ' CREATE USER $1 identified by ''$2'' RESOURCE POOL '||resource_pool ||
    ' search_path '|| search_path ||'; '
FROM
    users
WHERE
    user_name='$3'
UNION ALL
SELECT
    ' ALTER USER $1 DEFAULT ROLE all;'
FROM
    users
WHERE
    user_name='$3'
UNION ALL
SELECT
    'GRANT ' || REPLACE(privileges_description, '*', '') || ' ON ' || COALESCE(object_schema, '')
    || '.' || object_name || ' TO ' || '$1' || ';' AS GRANTS
FROM
    v_catalog.grants
WHERE
    COALESCE(privileges_description, '')  ''
AND grantee='$3'
AND object_TYPE NOT IN ('SCHEMA',
                        'RESOURCEPOOL')
UNION ALL
SELECT
    'GRANT ' || REPLACE(privileges_description, '*', '') || ' ON schema ' || COALESCE(object_schema
    , '') || object_name || ' TO ' || '$1' || ';' AS GRANTS
FROM
    v_catalog.grants
WHERE
    COALESCE(privileges_description, '')  ''
AND grantee='$3'
AND object_TYPE IN ('SCHEMA');
"
/opt/vertica/bin/vsql -U $username -w $password -t -f /tmp/create_user.sql

Now lets go and install this script as a UDP, so we can call it as a Vertica SQL action.

 1 - We need to create the script into the procedure folder of the Vertica Database. The Folder is located at /vertica/catalog/database_name/procedures/, the folder naming convention is specific to each database.
  • after you create the script , lets call it clone_user.sh you need to change the privilege definition on it.
chmod 4750 /vertica/catalog/database_name/procedures/clone_user.sh
2 - Install the store Procedure on the Vertica Cluster.
  • you must have the script on all nodes that are part of the cluster.
. /home/dbadmin/.profile
 admintools -t install_procedure -f /vertica/catalog/database/procedures/clone_user.sh -d Database_name -p $password
  • must be logged as dbamin user.
3 - Install the Procedure on your Database 
. /home/dbadmin/.profile
/opt/vertica/bin/vsql -U $username -w $password -c "CREATE PROCEDURE schema.clone_user(NewUser varchar,Password varchar,OldUser varchar) 
AS 'clone_user.sh' LANGUAGE 'external' USER 'dbadmin';"
  • must be logged as dbamin user.

Lets the the procedure in action

I have a user called test_user that has the following access: 
dbadmin= select object_name,object_type,grantee from grants where grantee='test_user';
       object_name        | object_type  |  grantee
--------------------------+--------------+-----------

 general                  | RESOURCEPOOL | test_user
 production_schemausage   | ROLE         | test_user
 production_datareader    | ROLE         | test_user
and definition: 
dbadmin= select user_name,profile_name,resource_pool,search_path from users where user_name='test_user';
 user_name | profile_name | resource_pool |                        search_path
-----------+--------------+---------------+------------------------------------------------------------

 test_user | default      | general       | "$user", public, v_catalog, v_monitor, v_internal, production

Lets run our clone_user procedure and create a new user called test_user_clone

  • the output of the procedure is:
dbadmin= select dba.clone_user('test_user_clone','secret_password','test_user');
INFO 4427:  Procedure reported:
CREATE USER
ALTER USER

 clone_user
------------

          0
(1 row)

Lets check the new user definition:

dbadmin= select user_name,profile_name,resource_pool,search_path from users where user_name='test_user_clone';
    user_name    | profile_name | resource_pool |                        search_path
-----------------+--------------+---------------+------------------------------------------------------------

 test_user_clone | default      | general       | "$user", public, v_catalog, v_monitor, v_internal, production
(1 row)

And grants:

dbadmin= select object_name,object_type,grantee from grants where grantee='test_user_clone';
       object_name        | object_type  |     grantee
--------------------------+--------------+-----------------

 general                  | RESOURCEPOOL | test_user_clone
 production_schemausage   | ROLE         | test_user_clone
 production_datareader    | ROLE         | test_user_clone
(3 rows)
... i guess it worked boys !!! I hope this was useful and if you have comments or suggestions please fell free to leave them here.

Note:

Use this script at your own RISK, so make sure you test it in DEV before you break your production :)

Copyright 2020. All Rights Reserved