Обсуждение: List Permissions
Hi,
How can I list a users permissions table by table?
i.e. User Joe
has read/write on table1
has read on table2
no access on table 3
Or something....
Thanks for any help!
How can I list a users permissions table by table?
i.e. User Joe
has read/write on table1
has read on table2
no access on table 3
Or something....
Thanks for any help!
You can get it from psql terminal.
postgres=# \z table-name
On Tue, Oct 25, 2011 at 4:50 PM, Maton, Brett <matonb@ltresources.co.uk> wrote:
Hi,
How can I list a users permissions table by table?
i.e. User Joe
has read/write on table1
has read on table2
no access on table 3
Or something....
Thanks for any help!
Forgot to post the reference manual link. Here you go.
On Tue, Oct 25, 2011 at 5:21 PM, Raghavendra <raghavendra.rao@enterprisedb.com> wrote:
You can get it from psql terminal.postgres=# \z table-nameOn Tue, Oct 25, 2011 at 4:50 PM, Maton, Brett <matonb@ltresources.co.uk> wrote:Hi,
How can I list a users permissions table by table?
i.e. User Joe
has read/write on table1
has read on table2
no access on table 3
Or something....
Thanks for any help!
My answers are in line in RED -
How can I list a users permissions table by table?
i.e. User Joe
has read/write on table1
has read on table2
no access on table 3
For a particular user you can use below function. You can write a SQL query or script which takes table names from "pg_tables" one by one.
has_table_privilege(user, table, privilege)
Example :
I am checking if user "postgres" has "select" privilege on "table1".
postgres=# select has_table_privilege('postgres','public.table1','select');
has_table_privilege
---------------------
t
(1 row)
For current user (user you logged in as) you can use the following function
has_table_privilege(table, privilege)
I am checking if the current_user has "select" privilege on "table1"
Example:
postgres=# select current_user;
current_user
--------------
postgres
(1 row)
postgres=# select has_table_privilege('public.table1','select');
has_table_privilege
---------------------
t
Below link has all the other functions regarding checking permissions
Hope this helps !
Thanks
VB
Thanks for the replies.
Actually this was question posed by one of my colleagues, what he really wants to know is if there is the equivalent of MySQL's
Brett
Actually this was question posed by one of my colleagues, what he really wants to know is if there is the equivalent of MySQL's
select * from all_tab_privs_recd where grantee = 'your user'
Thanks again,Brett
On 25 October 2011 13:21, Venkat Balaji <venkat.balaji@verse.in> wrote:
My answers are in line in RED -How can I list a users permissions table by table?
i.e. User Joe
has read/write on table1has read on table2
no access on table 3For a particular user you can use below function. You can write a SQL query or script which takes table names from "pg_tables" one by one.has_table_privilege(user, table, privilege)Example :I am checking if user "postgres" has "select" privilege on "table1".postgres=# select has_table_privilege('postgres','public.table1','select');has_table_privilege---------------------t(1 row)For current user (user you logged in as) you can use the following functionhas_table_privilege(table, privilege)I am checking if the current_user has "select" privilege on "table1"Example:postgres=# select current_user;current_user--------------postgres(1 row)postgres=# select has_table_privilege('public.table1','select');has_table_privilege---------------------tBelow link has all the other functions regarding checking permissionsHope this helps !ThanksVB
On Tue, Oct 25, 2011 at 6:04 PM, Maton, Brett <matonb@ltresources.co.uk> wrote:
Thanks for the replies.
Actually this was question posed by one of my colleagues, what he really wants to know is if there is the equivalent of MySQL'sThanks again,select * from all_tab_privs_recd where grantee = 'your user'
Brett
You have that too...
select * from information_schema.role_table_grants where grantee='your user';
On 25 October 2011 13:21, Venkat Balaji <venkat.balaji@verse.in> wrote:My answers are in line in RED -How can I list a users permissions table by table?
i.e. User Joe
has read/write on table1has read on table2
no access on table 3For a particular user you can use below function. You can write a SQL query or script which takes table names from "pg_tables" one by one.has_table_privilege(user, table, privilege)Example :I am checking if user "postgres" has "select" privilege on "table1".postgres=# select has_table_privilege('postgres','public.table1','select');has_table_privilege---------------------t(1 row)For current user (user you logged in as) you can use the following functionhas_table_privilege(table, privilege)I am checking if the current_user has "select" privilege on "table1"Example:postgres=# select current_user;current_user--------------postgres(1 row)postgres=# select has_table_privilege('public.table1','select');has_table_privilege---------------------tBelow link has all the other functions regarding checking permissionsHope this helps !ThanksVB
Bingo!
Thanks very much
Thanks very much
On 25 October 2011 13:47, Raghavendra <raghavendra.rao@enterprisedb.com> wrote:
On Tue, Oct 25, 2011 at 6:04 PM, Maton, Brett <matonb@ltresources.co.uk> wrote:Thanks for the replies.
Actually this was question posed by one of my colleagues, what he really wants to know is if there is the equivalent of MySQL'sThanks again,select * from all_tab_privs_recd where grantee = 'your user'
BrettYou have that too...select * from information_schema.role_table_grants where grantee='your user';On 25 October 2011 13:21, Venkat Balaji <venkat.balaji@verse.in> wrote:My answers are in line in RED -How can I list a users permissions table by table?
i.e. User Joe
has read/write on table1has read on table2
no access on table 3For a particular user you can use below function. You can write a SQL query or script which takes table names from "pg_tables" one by one.has_table_privilege(user, table, privilege)Example :I am checking if user "postgres" has "select" privilege on "table1".postgres=# select has_table_privilege('postgres','public.table1','select');has_table_privilege---------------------t(1 row)For current user (user you logged in as) you can use the following functionhas_table_privilege(table, privilege)I am checking if the current_user has "select" privilege on "table1"Example:postgres=# select current_user;current_user--------------postgres(1 row)postgres=# select has_table_privilege('public.table1','select');has_table_privilege---------------------tBelow link has all the other functions regarding checking permissionsHope this helps !ThanksVB