List all users who have been assigned a particular role
-- Change 'DBA' to the required role
select * from dba_role_privs where granted_role = 'DBA'
List all roles given to a user
-- Change 'PHIL@ to the required user
select * from dba_role_privs where grantee = 'PHIL';
List all privileges given to a user
select
lpad(' ', 2*level) || granted_role "User, his roles and privileges"
from
(
/* THE USERS */
select
null grantee,
username granted_role
from
dba_users
where
username like upper('%&enter_username%')
/* THE ROLES TO ROLES RELATIONS */
union
select
grantee,
granted_role
from
dba_role_privs
/* THE ROLES TO PRIVILEGE RELATIONS */
union
select
grantee,
privilege
from
dba_sys_privs
)
start with grantee is null
connect by grantee = prior granted_role;
Note: Taken from http://www.adp-gmbh.ch/ora/misc/recursively_list_privilege.html
List which tables a certain role gives SELECT access to?
-- Change 'DBA' to the required role.
select * from role_tab_privs where role='DBA' and privilege = 'SELECT';
List all tables a user can SELECT from?
--Change 'PHIL' to the required user
select * from dba_tab_privs where GRANTEE ='PHIL' and privilege = 'SELECT';
List all users who can SELECT on a particular table (either through being given a relevant role or through a direct grant (ie grant select on atable to joe))? The result of this query should also show through which role the user has this access or whether it was a direct grant.
-- Change 'TABLENAME' below
select Grantee,'Granted Through Role' as Grant_Type, role, table_name
from role_tab_privs rtp, dba_role_privs drp
where rtp.role = drp.granted_role
and table_name = 'TABLENAME'
union
select Grantee,'Direct Grant' as Grant_type, null as role, table_name
from dba_tab_privs
where table_name = 'TABLENAME' ;
You want to make your functions SECURITY DEFINER
and have them owned by a user that does have the requisite rights.
Be very careful when coding SECURITY DEFINER
functions. Don't make them owned by a superuser and read the manual carefully. Create a role that has only the rights required and no more; give it ownership of the SECURITY DEFINER
functions. Where appropriate create multiple roles for different access levels.
See CREATE FUNCTION
.
Best Answer
At this point, there's no right to grant, it's hardcoded to superuser. That's been discussed on the mailing list lately, and may change in 9.5 if someone finds the time to work on it.
As a workaround, you can create a
SECURITY DEFINER
function that is owned by the superuser, and runs the query you want. This will allow non-superusers to see the contents ofpg_stat_activity
by calling the function.E.g., run as a superuser:
Note that free access to
pg_stat_activity
is restricted for a reason. It's possible to snoop sensitive information from other people's queries - imagine for example if another user was using pgcrypto. Rather than granting rights topublic
you should grant them only to a specific user or role that is to act as a surrogate user for monitoring.