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.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/3345/viewspace-983350/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/3345/viewspace-983350/