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:
this is optional, i only do this to avoid the headache of not being able to see who has access to what.
i never give direct access to a user, always prefer role to propagate.
the main reason i use this approach is to avoid clear text password and this is a serious security issue.
the file can only be accesed by the dbadmin user.
The add_schema.sh content:
the location of the add_schema.sh should be inside the default procedure folder of your catalog.
the script will user the username and password that is passed by the .profile file and will execute the SQL script attaching the parameter value we will pass to the Procedure at execution time.
Make sure you run the following command when you create the add_schema.sh script:
Create the UDP using the admintools
Create the Procedure in your database
i normally have specific schema for my own use that i call dba, here i store all my procedures and all other tables & views used to manage the database.
i called my procedure add_schema, i am trying to make it as obvious as possible.
After the procedure add_schema was created try to see if it works.
See if schema was created.
See if the roles have been created.
See if the roles have the right grants on the new schema.
Ok, that looks good, so the procedure work well.
Feel free to use it and make it better.