In this short article i will go over the steps used to create a simple Vertica UDP (user defined procedure) that will help us create a new schema and also create the database roles in a single SQL command. This will help you work faster and spend less time writing simple SQL code. Normally when i create schema i make sure that the default role also come with it. My Default roles:
#!/bin/bash
username=dbadmin
password=UltraSecretPassowrd
export username
export password
#!/bin/bash
. /home/dbadmin/.profile
/opt/vertica/bin/vsql -U $username -w $password -t -c"
create schema $1;
--add inheritance
ALTER SCHEMA $1 DEFAULT INCLUDE PRIVILEGES;
--create roles
create role $1_datareader;
create role $1_datawriter;
create role $1_schemausage;
--grant specific access to role
grant select on schema $1 to $1_datareader;
grant usage on schema $1 to $1_schemausage;
grant insert,update,delete on schema $1 to $1_datawriter;
"
chmod 4750 add_schema.sh
. /home/dbadmin/.profile
admintools -t install_procedure -f /vertica_catalog/catalog/dbname/procedures/add_schema.sh -d $username -p $password
. /home/dbadmin/.profile
/opt/vertica/bin/vsql -U $username -w $password -c "CREATE PROCEDURE dba.add_schema(schema_name varchar) AS 'add_schema.sh' LANGUAGE 'external' USER 'dbadmin';"
dbadmin= select schema_name,schema_owner from schemata where schema_name ilike 'test_schema%';
schema_name | schema_owner
-------------+--------------
test_schema | dbadmin
(1 row)
dbadmin= select * from roles where name ilike 'test_schema%';
role_id | name | assigned_roles
-------------------+-------------------------+----------------
45036003061793588 | test_schema_datareader |
45036003061793590 | test_schema_datawriter |
45036003061793592 | test_schema_schemausage |
(3 rows)
dbadmin= select grantor,privileges_description,object_name,object_type,grantee
from grants where grantee in (select name from roles where name ilike 'test_schema%');
grantor | privileges_description | object_name | object_type | grantee
---------+------------------------+-------------+-------------+-------------------------
dbadmin | SELECT | test_schema | SCHEMA | test_schema_datareader
dbadmin | USAGE | test_schema | SCHEMA | test_schema_schemausage
dbadmin | INSERT, UPDATE, DELETE | test_schema | SCHEMA | test_schema_datawriter
(3 rows)