Grants for all tables

[Josh]

Is there a quick way to grant select on all the tables of a schema to a user?

[JimKnicely]

Josh,

You can do this from within vsql rather easily by using SQL to generate SQL grant statements.

Say I want to grant the select privilege on the tables in the schema v_schema1 to the v_main_user user. I could do this in vsql:

Code: Select all

dbadmin=> \o grants.sql
dbadmin=> select 'grant select on v_schema1.' || table_name || ' to v_main_use;' from tables where table_schema = 'v_schema1';
dbadmin=> \o
dbadmin=> \i grants.sql

Note: The \o switch saves the results from the SELECT statement into an output file and the \i switch allows you to execute the commands from a file.