The Vertica Database Forums

Posted: **Tue Sep 10, 2013 7:50 pm**

Hi,

Typically only the owner of a table can truncate that table. For fun, shown below is a "simple" example that shows how to use an external procedure to allow a non-owning user to truncate the table:

1. Create a table owned by the dbadmin user and insert a record:

Code: Select all

dbadmin=> create table public.test (c1 int);
CREATE TABLE

dbadmin=> insert into public.test values (1);
 OUTPUT
--------
      1
(1 row)

dbadmin=> commit;
COMMIT

dbadmin=> \dt public.test;
              List of tables
 Schema | Name | Kind  |  Owner  | Comment
--------+------+-------+---------+---------
 public | test | table | dbadmin |
(1 row)

2. Create a new user and grant the SELECT privilege on the table to the user so that we can query the table later:

Code: Select all

dbadmin=> create user jim;
CREATE USER

dbadmin=> grant select on public.test to jim;
GRANT PRIVILEGE

3. Now exit vsql and create a shell script for the external procedure:

Code: Select all

dbadmin=> \q

[dbadmin@vertica01 ~]$ cat truncate_table.sh
#!/bin/bash
/opt/vertica/bin/vsql -c "TRUNCATE TABLE $1;"
exit 0

[dbadmin@vertica01 ~]$ chmod +x+s truncate_table.sh

[dbadmin@vertica01 ~]$ ls -lrt truncate_table.sh
-rwsrwsr-x. 1 dbadmin dbadmin 48 Sep 10 13:29 truncate_table.sh

4. Use admintools command line to install the procedure:

Code: Select all

[dbadmin@vertica01 ~]$ admintools -t install_procedure -d mydbtest -f truncate_table.sh

Installing external procedure...

External procedure installed

5. Now go back into vsql and create the procedure:

Code: Select all

[dbadmin@vertica01 ~]$ vsql
Welcome to vsql, the Vertica Analytic Database interactive terminal.

Type:  \h or \? for help with vsql commands
       \g or terminate with semicolon to execute query
       \q to quit

dbadmin=> CREATE PROCEDURE public.truncate_table(arg1 VARCHAR) AS 'truncate_table.sh' LANGUAGE 'external' USER 'dbadmin';
CREATE PROCEDURE

6. We need to grant the EXECUTE privilege on the new procedure to the user JIM we created earlier:

Code: Select all

dbadmin=> GRANT EXECUTE ON PROCEDURE public.truncate_table(arg1 VARCHAR) TO jim;
GRANT PRIVILEGE

7. Now connect as the user JIM and run the procedure to see if we can truncate the table that the user does not own:

Code: Select all

dbadmin=> \c dbadmin jim;
You are now connected to database "dbadmin" as user "jim".

dbadmin=> SELECT * FROM public.test;
 c1
----
  1
(1 row)

dbadmin=> TRUNCATE TABLE public.test;
ROLLBACK 3989:  Must be owner of relation test

dbadmin=> SELECT public.truncate_table('public.test');
INFO 4427:  Procedure reported:
TRUNCATE TABLE

 truncate_table
----------------
              0
(1 row)

dbadmin=> SELECT * FROM public.test;
 c1
----
(0 rows)

The table was truncated as a non-owning user!

If you plan on using this method make sure you test it out!!! And be careful who gets granted access to the procedure!

Have fun!

Posted: **Wed Sep 11, 2013 9:35 am**

Thanks a ton Jim,

You explained it on a very detailed level.

The Vertica Database Forums

External Proc to Allow Non-Owning Users to Truncate Tables

External Proc to Allow Non-Owning Users to Truncate Tables

Re: External Proc to Allow Non-Owning Users to Truncate Tabl