External Proc to Allow Non-Owning Users to Truncate Tables

Moderator: NorbertKrupa

Post Reply
User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1806
Joined: Sat Jan 21, 2012 4:58 am
Contact:

External Proc to Allow Non-Owning Users to Truncate Tables

Post by JimKnicely » 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!
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

User avatar
nnani
Master
Master
Posts: 302
Joined: Fri Apr 13, 2012 6:28 am
Contact:

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

Post by nnani » Wed Sep 11, 2013 9:35 am

Thanks a ton Jim,

You explained it on a very detailed level. :)
nnani........
Long way to go

You can check out my blogs at vertica-howto

Post Reply

Return to “Vertica Tips, Lessons and Examples”