Generate Scripts to grant DML priv to other user or role

posted Apr 28, 2017, 4:19 PM by Sachchida Ojha
sachi=> select 'grant select,insert,update, delete on '||schemaname||'.'||tablename||' to gpadmin' from pg_tables where tableowner='sachi';
                                   ?column?                                   
------------------------------------------------------------------------------
 grant select,insert,update, delete on sachi.countries to gpadmin
 grant select,insert,update, delete on sachi.departments to gpadmin
 grant select,insert,update, delete on sachi.employees to gpadmin
 grant select,insert,update, delete on sachi.job_history to gpadmin
 grant select,insert,update, delete on sachi.jobs to gpadmin
 grant select,insert,update, delete on sachi.locations to gpadmin
 grant select,insert,update, delete on sachi.regions to gpadmin
 grant select,insert,update, delete on sachi.ext_countries_error to gpadmin
 grant select,insert,update, delete on sachi.ext_countries to gpadmin
 grant select,insert,update, delete on sachi.ext_departments_error to gpadmin
 grant select,insert,update, delete on sachi.ext_departments to gpadmin
 grant select,insert,update, delete on sachi.ext_employees_error to gpadmin
 grant select,insert,update, delete on sachi.ext_employees to gpadmin
 grant select,insert,update, delete on sachi.ext_job_history_error to gpadmin
 grant select,insert,update, delete on sachi.ext_job_history to gpadmin
 grant select,insert,update, delete on sachi.ext_jobs_error to gpadmin
 grant select,insert,update, delete on sachi.ext_jobs to gpadmin
 grant select,insert,update, delete on sachi.ext_locations_error to gpadmin
 grant select,insert,update, delete on sachi.ext_locations to gpadmin
 grant select,insert,update, delete on sachi.ext_regions_error to gpadmin
 grant select,insert,update, delete on sachi.ext_regions to gpadmin
(21 rows)
Comments