Обсуждение: Re: Granting any privilege for a specific set of tables in postgresin a single shot!..

Поиск
Список
Период
Сортировка

Re: Granting any privilege for a specific set of tables in postgresin a single shot!..

От
pavan95
Дата:
Hi all,

Hope my mail finds you in good time.  I have a question in regards to
"Grant" command. 

How can I grant a particular privilege to a specific set of objects(tables)
in a single shot ?

FYI, please consider the following scenario. I have a database "abc"  with
users as "u1","u2","u3","u4". And there are 1500 tables in my database.

Now my requirement is to grant select on 800 tables(specific set of tables)
which suits my requirement. I know that I can use the below statement 800
times.

grant select on table1 to u1;
grant select on table2 to u1;
.
.
.
.
.
.
grant select on table800 to u1;

My question is can't I do it in a single shot?  Can I fit some select query
which passes the table name as a parameter in the middle of the grant
statement?

Please advice.

Thanks in Advance.

Regards,
Pavan





--
Sent from: http://www.postgresql-archive.org/PostgreSQL-admin-f2076596.html


Re: Granting any privilege for a specific set of tables in postgresin a single shot!..

От
Scott Ribe
Дата:
3 suggestions

- use roles so you don't have to grant to so many individual users
- use schemas so you don't have to grant to individual tables
- select 'grant select on ' || ? || ' to ' || ?, into a script which you then execute

--
Scott Ribe
scott_ribe@elevated-dev.com
https://www.linkedin.com/in/scottribe/



> On Jul 25, 2018, at 7:32 AM, pavan95 <pavan.postgresdba@gmail.com> wrote:
>
> Hi all,
>
> Hope my mail finds you in good time.  I have a question in regards to
> "Grant" command.
>
> How can I grant a particular privilege to a specific set of objects(tables)
> in a single shot ?
>
> FYI, please consider the following scenario. I have a database "abc"  with
> users as "u1","u2","u3","u4". And there are 1500 tables in my database.
>
> Now my requirement is to grant select on 800 tables(specific set of tables)
> which suits my requirement. I know that I can use the below statement 800
> times.
>
> grant select on table1 to u1;
> grant select on table2 to u1;
> .
> .
> .
> .
> .
> .
> grant select on table800 to u1;
>
> My question is can't I do it in a single shot?  Can I fit some select query
> which passes the table name as a parameter in the middle of the grant
> statement?
>
> Please advice.
>
> Thanks in Advance.
>
> Regards,
> Pavan
>
>
>
>
>
> --
> Sent from: http://www.postgresql-archive.org/PostgreSQL-admin-f2076596.html
>