Gather_schema_statistics for other schema
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>