Thread: how to revoke multiple users permission from multiple tables at the same time?

how to revoke multiple users permission from multiple tables at the same time?
Or in simple is there a way to revoke multiple users grant access from multiple tables under a schema.?

I use Revoke below command to execute on each table one by one.

revoke SELECT/ALL on testtable from user1;

Thanks for taking your time to read this.

thanks
Deepak
On Wed, Apr 22, 2009 at 4:19 PM, DM <dm.aeqa@gmail.com> wrote:
> how to revoke multiple users permission from multiple tables at the same
> time?
> Or in simple is there a way to revoke multiple users grant access from
> multiple tables under a schema.?

Best way is to NOT grant multiple users permissions, but to grant a
role the permissions and grant that role to users.  That way you only
have to revoke persmissions from the role to revoke it from all the
users.

> I use Revoke below command to execute on each table one by one.
> revoke SELECT/ALL on testtable from user1;

Note that you can build a set of revoke commands by using selects and
concatenations if you need them.  Something like this (use psql -E to
see the queries \ commands invoke in psql)

SELECT 'revoke all from somename on '||n.nspname||'.'||  c.relname ||';'
FROM pg_catalog.pg_class c
     JOIN pg_catalog.pg_roles r ON r.oid = c.relowner
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','v','S','')
  AND n.nspname <> 'pg_catalog'
  AND n.nspname !~ '^pg_toast'
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1;

                   ?column?
----------------------------------------------
 revoke all from somename on public.colony;
 revoke all from somename on public.delegate;
 revoke all from somename on public.jt1;
 revoke all from somename on public.jt2;
 revoke all from somename on public.mytable;
 revoke all from somename on public.test1;
 revoke all from somename on public.test2;
 revoke all from somename on public.tmp;
(8 rows)

On Wed, Apr 22, 2009 at 5:01 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> On Wed, Apr 22, 2009 at 4:19 PM, DM <dm.aeqa@gmail.com> wrote:
>> how to revoke multiple users permission from multiple tables at the same
>> time?
>> Or in simple is there a way to revoke multiple users grant access from
>> multiple tables under a schema.?
>
> Best way is to NOT grant multiple users permissions, but to grant a
> role the permissions and grant that role to users.  That way you only
> have to revoke persmissions from the role to revoke it from all the
> users.
>
>> I use Revoke below command to execute on each table one by one.
>> revoke SELECT/ALL on testtable from user1;
>
> Note that you can build a set of revoke commands by using selects and
> concatenations if you need them.  Something like this (use psql -E to
> see the queries \ commands invoke in psql)
>
> SELECT 'revoke all from somename on '||n.nspname||'.'||  c.relname ||';'
> FROM pg_catalog.pg_class c
>     JOIN pg_catalog.pg_roles r ON r.oid = c.relowner
>     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
> WHERE c.relkind IN ('r','v','S','')
>  AND n.nspname <> 'pg_catalog'
>  AND n.nspname !~ '^pg_toast'
>  AND pg_catalog.pg_table_is_visible(c.oid)
> ORDER BY 1;
>
>                   ?column?
> ----------------------------------------------
>  revoke all from somename on public.colony;
>  revoke all from somename on public.delegate;
>  revoke all from somename on public.jt1;
>  revoke all from somename on public.jt2;
>  revoke all from somename on public.mytable;
>  revoke all from somename on public.test1;
>  revoke all from somename on public.test2;
>  revoke all from somename on public.tmp;
> (8 rows)

In my hurry I got the order wrong, you want the revoke to look like this:

revoke all on tablename from somename;

I leave it to you to rebuild the query to get what ya need.

Thanks Scott.

Good answer, I was consolidating the schemas here, there were too many users were granted permission to tables.I wanted to consolidate/optimize to bring it to one role and granting this role to the user (same way as you mentioned).

Thanks for the solution.

Thanks
Deepak


On Wed, Apr 22, 2009 at 4:02 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
On Wed, Apr 22, 2009 at 5:01 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> On Wed, Apr 22, 2009 at 4:19 PM, DM <dm.aeqa@gmail.com> wrote:
>> how to revoke multiple users permission from multiple tables at the same
>> time?
>> Or in simple is there a way to revoke multiple users grant access from
>> multiple tables under a schema.?
>
> Best way is to NOT grant multiple users permissions, but to grant a
> role the permissions and grant that role to users.  That way you only
> have to revoke persmissions from the role to revoke it from all the
> users.
>
>> I use Revoke below command to execute on each table one by one.
>> revoke SELECT/ALL on testtable from user1;
>
> Note that you can build a set of revoke commands by using selects and
> concatenations if you need them.  Something like this (use psql -E to
> see the queries \ commands invoke in psql)
>
> SELECT 'revoke all from somename on '||n.nspname||'.'||  c.relname ||';'
> FROM pg_catalog.pg_class c
>     JOIN pg_catalog.pg_roles r ON r.oid = c.relowner
>     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
> WHERE c.relkind IN ('r','v','S','')
>  AND n.nspname <> 'pg_catalog'
>  AND n.nspname !~ '^pg_toast'
>  AND pg_catalog.pg_table_is_visible(c.oid)
> ORDER BY 1;
>
>                   ?column?
> ----------------------------------------------
>  revoke all from somename on public.colony;
>  revoke all from somename on public.delegate;
>  revoke all from somename on public.jt1;
>  revoke all from somename on public.jt2;
>  revoke all from somename on public.mytable;
>  revoke all from somename on public.test1;
>  revoke all from somename on public.test2;
>  revoke all from somename on public.tmp;
> (8 rows)

In my hurry I got the order wrong, you want the revoke to look like this:

revoke all on tablename from somename;

I leave it to you to rebuild the query to get what ya need.