Re: List Permissions

On Tue, Oct 25, 2011 at 6:04 PM, Maton, Brett <matonb(at)ltresources(dot)co(dot)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's
>
> select * from all_tab_privs_recd where grantee = 'your user'
>
> Thanks again,
> Brett
>
>
>
You have that too...

select * from information_schema.role_table_grants where grantee='your
user';

---
Regards,
Raghavendra
EnterpriseDB Corporation
Blog: http://raghavt.blogspot.com/

> On 25 October 2011 13:21, Venkat Balaji <venkat(dot)balaji(at)verse(dot)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 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
>>
>> http://www.postgresql.org/docs/9.0/static/functions-info.html
>>
>> Hope this helps !
>>
>> Thanks
>> VB
>>
>
>