Gather_schema_statistics for other schema

[gopu_g]

Non-schema userid having privilege to gather schema stats on other schema which non-schema id has no access to. Is this expected behaviour ?

Below is the example:
--------------------


SQL> create user test identified by test;

User created.

SQL> grant create session to test;

Grant succeeded.

SQL> CONN test/test
Connected.
SQL> exec dbms_stats.gather_schema_stats(ownname => 'WMEXT',method_opt =>'for all columns size auto', estimate_percent=>100, cascade => true, options => 'gather auto');

PL/SQL procedure successfully completed.

SQL> conn / as sysdba
Connected.
SQL> show user
USER is "SYS"
SQL> create user test2 identified by test2;

User created.

SQL> alter user test2 quota unlimited on users;

User altered.

SQL> grant create table, create session to test2;

Grant succeeded.

SQL> conn test2/test2
Connected.
SQL> show user
USER is "TEST2"
SQL> create table dummy (name varchar2(20));

Table created.

SQL> conn test/test
Connected.
SQL> show user
USER is "TEST"
SQL> exec dbms_stats.gather_schema_stats(ownname => 'TEST2',method_opt =>'for all columns size auto', estimate_percent=>100, cascade => true, options => 'gather auto');
BEGIN dbms_stats.gather_schema_stats(ownname => 'TEST2',method_opt =>'for all columns size auto', estimate_percent=>100, cascade => true, options => 'gather auto'); END;

*
ERROR at line 1:
ORA-20000: Insufficient privileges to analyze an object in Schema
ORA-06512: at "SYS.DBMS_STATS", line 18553
ORA-06512: at "SYS.DBMS_STATS", line 19009
ORA-06512: at "SYS.DBMS_STATS", line 19132
ORA-06512: at "SYS.DBMS_STATS", line 19088
ORA-06512: at line 1


SQL> exec dbms_stats.gather_schema_stats(ownname => 'WMEXT',method_opt =>'for all columns size auto', estimate_percent=>100, cascade => true, options => 'gather auto');

PL/SQL procedure successfully completed.

SQL> conn test2/test2
Connected.
SQL> show user
USER is "TEST2"
SQL> exec dbms_stats.gather_schema_stats(ownname => 'WMEXT',method_opt =>'for all columns size auto', estimate_percent=>100, cascade => true, options => 'gather auto');

PL/SQL procedure successfully completed.

SQL>

[PAVB]

Gathering stats on a non-owned schema requires 'analyze any" system privilege and also either 'select any' OR select on specific tables.

[gopu_g]

Gathering stats on a non-owned schema requires 'analyze any" system privilege and also either 'select any' OR select on specific tables.

Yes, but here i am not granted any of these privileges to the user test and test1, but still they are able to gather the stats of the schema "WMEXT".
why it is happening ?

thanks
Gopu

[gopu_g]

I guess that EXECUTE privilege on DBMS_STATS Oracle DB package is granted to PUBLIC. if yes, that is the reason why the new user test is also able to gather schema stats using the package.

[Thomasps]

please post the outout of

conn test2/test2
select * from session_privs;

Thanks,

[gopu_g]

Hi Thomas,

Please find the Output..



SQL> conn test2/test2
Connected.
SQL> select * from session_privs;

PRIVILEGE
----------------------------------------
CREATE SESSION
CREATE TABLE

SQL>


thanks
Gopu

[PAVB]

SQL> conn test2/test2
Connected.
SQL> select * from session_privs;

PRIVILEGE
----------------------------------------
CREATE SESSION
CREATE TABLE

SQL>

Well... needed privs are not there

[gopu_g]

Well... needed privs are not there

Hi Pavb,

I think you are not clear with the issue

I have logged SR with Oracle for the same and they have created bug# 12714396 with their development team, and Oracle BDE team is working on the issue now, i will update you all once i will get the feedback from Oracle support.

[Thomasps]

Thanks. What is your Oracle version and OS?

BTW, I can't find this bug in Metalink...

Thanks,

[gopu_g]

Thanks. What is your Oracle version and OS?

BTW, I can't find this bug in Metalink...

Thanks,

Database version is 11.1.0.7.0
OS is AIX
U can't find it, Oracle Development team is working on that, till now i am waiting for the update from Oracle :(