Discussion:
Privileges required for dbms_stats
(too old to reply)
Jack Wang
2003-12-30 05:55:57 UTC
Permalink
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 to
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
Jack Wang
2003-12-30 06:27:23 UTC
Permalink
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
Post by Jack Wang
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 to
dbms_stats.gather_schema_stats(ownname=>'someone else'). What is required
for the cascade=>true bit?
Given 'select any table' still produces
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
Daniel Morgan
2003-12-30 14:47:31 UTC
Permalink
Post by Jack Wang
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
Post by Jack Wang
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
to
Post by Jack Wang
dbms_stats.gather_schema_stats(ownname=>'someone else'). What is required
for the cascade=>true bit?
Given 'select any table' still produces
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
***@x.washington.edu
(replace 'x' with a 'u' to reply)
Jack Wang
2003-12-30 19:54:21 UTC
Permalink
Thanks. Any suggestion on whether having schema owner gather its own stats or having a central user
in charge of gathering all other schemas' stats.

- Jack
Post by Daniel Morgan
Post by Jack Wang
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
Post by Jack Wang
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
to
Post by Jack Wang
dbms_stats.gather_schema_stats(ownname=>'someone else'). What is required
for the cascade=>true bit?
Given 'select any table' still produces
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
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
(replace 'x' with a 'u' to reply)
Daniel Morgan
2003-12-30 23:36:14 UTC
Permalink
Post by Jack Wang
Thanks. Any suggestion on whether having schema owner gather its own stats or having a central user
in charge of gathering all other schemas' stats.
- Jack
If you are talking production, and I presume you are, it should be done
with DBMS_JOB as part of a scheduled job ... one job per application.
And I prefer schema owners to SYS but only because too many things are
done as SYS that don't need to be.

But if by schema owner you mean a person ... I'd say "no way."
--
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
***@x.washington.edu
(replace 'x' with a 'u' to reply)
Loading...