select * from ( select r.privilege
from user_role_privs u,role_sys_privs r
where u.granted_role=r.role
union all
select privilege
from user_sys_privs
)
where privilege = 'ANALYZE ANY'
and rownum < 2;
PRIVILEGE
----------------------------------------
ANALYZE ANY
select *from dba_role_privs;
select *from role_sys_privs;
select *from role_tab_privs;
SQL> select r.role, r.privilege
from user_role_privs u,role_sys_privs r
where u.granted_role=r.role
and r.privilege='ANALYZE ANY'
/
no rows selected
SQL> select privilege
from user_sys_privs
where privilege='ANALYZE ANY'
/
PRIVILEGE
----------------------------------------
ANALYZE ANY
1 row selected.
CHANGES
CAUSE
Several default roles were missing necessary privileges :
SQL> select * from USER_ROLE_PRIVS
order by granted_role;
USERNAME GRANTED_ROLE ADM DEF OS_
SYS OEM_ADVISOR YES YES NO
SYS SCHEDULER_ADMIN YES YES NO
2 rows selected.
SQL> select * from ROLE_SYS_PRIVS
order by role;
ROLE PRIVILEGE ADM
---------------- ------------------------- ---
OEM_ADVISOR ADMINISTER SQL TUNING SET NO
OEM_ADVISOR ADVISOR NO
OEM_ADVISOR CREATE JOB NO
SCHEDULER_ADMIN CREATE ANY JOB YES
SCHEDULER_ADMIN CREATE EXTERNAL JOB YES
SCHEDULER_ADMIN CREATE JOB YES
SCHEDULER_ADMIN EXECUTE ANY CLASS YES
SCHEDULER_ADMIN EXECUTE ANY PROGRAM YES
SCHEDULER_ADMIN MANAGE SCHEDULER YES
9 rows selected.
Roles like DBA, EXP_FULL_DATABASE, IMP_FULL_DATABASE are not granted to SYS anymore or are lacking the necessary privileges.
SOLUTION
In a clean created 10G database there should be are 297 privileges granted to the SYS-roles :
SQL> select role, count(*)
from ROLE_SYS_PRIVS
group by role
order by role;
ROLE COUNT(*)
------------------------------ ----------
AQ_ADMINISTRATOR_ROLE 6
CONNECT 1
DBA 160
EXP_FULL_DATABASE 8
IMP_FULL_DATABASE 68
JAVADEBUGPRIV 2
MGMT_USER 2
OEM_ADVISOR 3
OEM_MONITOR 7
OLAP_DBA 10
OLAP_USER 5
RECOVERY_CATALOG_OWNER 11
RESOURCE 8
SCHEDULER_ADMIN 6
14 rows selected.