External Proc to Allow Non-Owning Users to Truncate Tables
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:
2. Create a new user and grant the SELECT privilege on the table to the user so that we can query the table later:
3. Now exit vsql and create a shell script for the external procedure:
4. Use admintools command line to install the procedure:
5. Now go back into vsql and create the procedure:
6. We need to grant the EXECUTE privilege on the new procedure to the user JIM we created earlier:
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:
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!
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)
Code: Select all
dbadmin=> create user jim;
CREATE USER
dbadmin=> grant select on public.test to jim;
GRANT PRIVILEGE
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
Code: Select all
[dbadmin@vertica01 ~]$ admintools -t install_procedure -d mydbtest -f truncate_table.sh
Installing external procedure...
External procedure installed
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
Code: Select all
dbadmin=> GRANT EXECUTE ON PROCEDURE public.truncate_table(arg1 VARCHAR) TO jim;
GRANT PRIVILEGE
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)
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!