ORA-20000: Insufficient privileges to analyze an object in Schema

今天收到一些foglight的報警，說一些自動執行的dbms_stats.gather_schema_stats報這些錯：

Fri Nov 23 13:47:29 2007
Errors in file c:oracleadminrcatudumprcat_j001_3928.trc:
ORA-12012: error on auto execute of job 22
ORA-20000: Insufficient privileges to analyze an object in Schema
ORA-06512: at "SYS.DBMS_STATS", line 9635
ORA-06512: at "SYS.DBMS_STATS", line 9800
ORA-06512: at "SYS.DBMS_STATS", line 9854
ORA-06512: at "SYS.DBMS_STATS", line 9831
ORA-06512: at "SYSTEM.DBA_TABLE_ANALYZE", line 12
ORA-06512: at line 1

到metalink查了一下，是一個bug，實際上很簡單，如果用sys使用者是沒有問題的，如果用的是system使用者，則需要用sys使用者給system使用者授權：

sql>grant select any table ,anylyze any to system;

這樣就可以了。

[@more@]
Problem statement:

ORA-20000 INSUFFICIENT PRIVILEGES RUNNING JOB W/DBMS_STATS.GATHER_SCHEMA

---

 
 
*** 01/27/04 01:29 pm *** 
TAR: 
---- 
3572308.996 
. 
PROBLEM: 
-------- 
If a job is submitted by the SYSTEM user and that job calls  
DBMS_STATS.GATHER_SCHEMA_STATS ... and the schema in question contains a table  
with a single index (PK) ... The job will fail with 
. 
ORA-12012: error on auto execute of job 100 
ORA-20000: Insufficient privileges to analyze an object in Schema 
ORA-06512: at "SYS.DBMS_STATS", line 9876 
ORA-06512: at "SYS.DBMS_STATS", line 10041 
ORA-06512: at "SYS.DBMS_STATS", line 10095 
ORA-06512: at "SYS.DBMS_STATS", line 10072 
ORA-06512: at line 1 
. 
If the DBMS_STATS.GATHER_SCHEMA_STATS is run from the prompt as SYSTEM ... it  
succeeds  
. 
If the job is run as the owner of the table/pk index ... it succeeds 
. 
If the job run as SYS it succeeds  
. 
DIAGNOSTIC ANALYSIS: 
-------------------- 
See testcase  
. 
WORKAROUND: 
----------- 
Execute the job as either SYS or the user whos schema is to be analyzed  
. 
RELATED BUGS: 
------------- 
Bug.2738070/3165470 ORA-20000 ON DBMS_STATS.EXPORT_TABLE_STATS WHEN OTHER  
SCHEMA HAS INDEX ON TABLE 
. 
REPRODUCIBILITY: 
---------------- 
100% 
. 
. 
========= 
TESTCASE:  
========= 
. 
Location : rmtdcsol4/refresh/REF920U6 ... /testcases/kbcook 
. 
sqlplus "/ as sysdba" 
. 
CREATE USER MSHASHIDHAR IDENTIFIED BY "hello123"; 
. 
GRANT "RESOURCE" TO MSHASHIDHAR; 
. 
ALTER USER MSHASHIDHAR DEFAULT ROLE "RESOURCE"; 
. 
GRANT ALTER SESSION TO MSHASHIDHAR; 
. 
GRANT CREATE PUBLIC SYNONYM TO MSHASHIDHAR; 
. 
GRANT CREATE SESSION TO MSHASHIDHAR; 
. 
GRANT UNLIMITED TABLESPACE TO MSHASHIDHAR; 
. 
EXIT  
. 
imp system/manager file=da_constraint.dmp fromuser=www_owner  
touser=mshashidhar 
. 
sqlplus system/manager 
. 
--SQL> select object_name, object_type from dba_objects where owner =  
'MSHASHIDHAR'; 
. 
--OBJECT_NAME 
------------------------------------------------------------------------------- 
- 
--OBJECT_TYPE 
------------------ 
--DA_CONSTRAINT 
--TABLE 
. 
--DA_CONSTRAINT_PK 
--INDEX 
. 
begin 
  sys.dbms_job.isubmit(job => 100, 
                      what => 'begin dbms_stats.gather_schema_stats (ownname  
=>  
                      ''MSHASHIDHAR'',degree => 3,cascade => TRUE);end;', 
                      next_date => to_date('28-01-2004', 'dd-mm-yyyy'), 
                      interval => 'TRUNC(SYSDATE + 2, ''DD'') +  1/24'); 
  commit; 
end; 
/ 
. 
exec dbms_job.run(100); 
. 
! tail /refresh/64bit/app/oracle/admin/REF920U6/bdump/alert_REF920U6.log 
. 
--Current log# 2 seq# 408 mem# 0:  
/refresh/64bit/app/oracle/oradata/REF920U6/reg 
--Tue Jan 27 10:25:23 2004 
--Errors in file  
/refresh/64bit/app/oracle/admin/REF920U6/udump/ref920u6_ora_1699: 
--ORA-12012: error on auto execute of job 100 
--ORA-20000: Insufficient privileges to analyze an object in Schema 
--ORA-06512: at "SYS.DBMS_STATS", line 9876 
--ORA-06512: at "SYS.DBMS_STATS", line 10041 
--ORA-06512: at "SYS.DBMS_STATS", line 10095 
--ORA-06512: at "SYS.DBMS_STATS", line 10072 
--ORA-06512: at line 1 
. 
STACK TRACE: 
------------ 
. 
SUPPORTING INFORMATION: 
----------------------- 
. 
24 HOUR CONTACT INFORMATION FOR P1 BUGS: 
---------------------------------------- 
. 
DIAL-IN INFORMATION: 
-------------------- 
. 
IMPACT DATE: 
------------ 
. 
*** 01/27/04 01:37 pm *** (CHG: Sta->16) 
*** 01/28/04 10:32 am *** (CHG: Asg->NEW OWNER) 
*** 01/29/04 02:14 pm *** (CHG: Sta->32) 
*** 01/29/04 02:14 pm *** 
This looks like expected behavior. 
You can grant 'analyze any' to system: 
sqlplus "/ as sysdba" 
grant analyze any to system; 
and then you will not see the error. 
. 
You may also need to grant 'select any table' to system if you have not  
granted select on da_conatraints to system as the table owner: 
i.e. 
sqlplus "/ as sysdba" 
grant select any table to system; 
or 
grant select on < tablename> to system; 
. 
The DBMS_STATS.* package "run[s] with invoker rights -- [it does] not run as  
the owner (SYS) but rather as the current user."  "An invoker-rights procedure  
. 
executes with all of the invoker's privileges. Roles are enabled unless the  
invoker-rights procedure was called directly or indirectly by a definer-rights  
. 
procedure."  (Quotes taken from Concepts Manual.) 
. 
"When you force a job to run using the procedure DBMS_JOB.RUN, the job is run  
by your user process and with your default privileges only. 
Privileges granted to you through roles are unavailable. You must be  
explicitly granted the necessary object privileges for all objects 
referenced within the job definition." (Quote taken from Database Admin  
Guide.) 
. 
These two quotes explain why when you run: 
. 
connect system/manager 
execute dbms_stats.gather_schema_stats(ownname=> 'MSHASHIDHAR', degree  
=>3,cascade => TRUE); 
. 
you don't get an error, but when you run: 
. 
connect system/manager 
execute dbms_job.run(100); 
. 
you do get an error. 
. 
In the first case, the roles granted to system are enabled.  The role  
imp_full_database, which is granted to the dba role which in turn is granted  
to system, includes the 'analyze any' privilege. 
. 
In the second case, the roles granted to system are unavailable so the  
privilege 'analyze any' must be explicitly granted to system.