Re: Privileges required for dbms_stats

Jack Wang wrote:

> I have isolated the issue. I had to issue 'execute any procedure' to the
> user who runs dbms_stats, because the schema to be analyzed involves a FBI
> which is associated with a procedure.
>
> It was said that, 'select any table' is no longer needed for 9202 above to
> gather index stats. It is funny that I still needed with 9203.
>
> Another question arises though, which is the practical approach?
>
> 1. Let one user gather all other schema stats periodically. I could wrap it
> in a procedure and schdule it thru dbms_job.
>
> 2. Let each schema gather its own stats, in which case I don't have to
> bother with 'select any table' and 'execute any procedure' etc.
>
> Thanks.
>
> - Jack
>
> "Jack Wang" <nospam_at_nospam.com> wrote in message
> news:Nr8Ib.96007$ss5.72702_at_clgrps13...
>

>>Oracle 9203 on W2K with SP4
>>
>>I wonder what are privileges required for
>>dbms_stats.gather_schema_stats(ownname=>'someone else', cascade=>true)?
>>
>>A user with the privileges of 'create session' and 'analyze any' was able

>>dbms_stats.gather_schema_stats(ownname=>'someone else'). What is required
>>for the cascade=>true bit?
>>
>>Given 'select any table' still produces
>>
>>ERROR at line 1:
>>ORA-01031: insufficient privileges
>>ORA-06512: at "SYS.DBMS_STATS", line 9357
>>ORA-06512: at "SYS.DBMS_STATS", line 9839
>>ORA-06512: at "SYS.DBMS_STATS", line 10023
>>ORA-06512: at "SYS.DBMS_STATS", line 10077
>>ORA-06512: at "SYS.DBMS_STATS", line 10054
>>ORA-06512: at line 1
>>
>>Any help is appreciated.
>>
>>- Jack

Granting EXECUTE ANY PROCEDURE is not a solution ... it is a nightmare. You have just gutted the system's security. Revoke that privilege and do the following:

GRANT execute ON dbms_stats TO <schema_name>;

While effective: One should not use a 2000 pound bomb as a way to kill mosquitos.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)