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' ;
Well one thing you may want to consider doing is validating your application(s) still work on a test instance that contains the same database and upgrading it to the newer service pack. While rare and usually changes are backward compatible, there could be behavior changes to things like the optimizer that would be impossible to guess exactly how they might affect your application without testing. The risk is certainly less with a service pack than a version upgrade, for example, but installing a service pack on a cluster does not exactly have a trivial "undo" button - so in this scenario the backups won't really help you directly (they are still an absolutely sound idea, of course).
Best Answer
In order to give some privileged users access to
xp_sqlagent_enum_jobs
we did the following.master
named something likeSQLAgentJobManager
.EXECUTE
rights onxp_sqlagent_enum_jobs
.Then via Logins or a Domain group, granted users a membership in the
SQLAgentJobManager
role inmaster
.EDIT: The way we implement this permission is through Domain Group login(s) such as (
SQLAgentJobManagerGroup
). Then add the group login tomaster
'sSQLAgentJobManager
role (for runningxp_sqlagent_enum_jobs
) and tomsdb
'sSQLAgentReaderRole
orSQLAgentUserRole
.After that we always add or remove logins from the
SQLAgentJobManagerGroup
to control the group membership for those rights.