-
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>
Thanks/Gopu
-
Gathering stats on a non-owned schema requires 'analyze any" system privilege and also either 'select any' OR select on specific tables.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
Originally Posted by PAVB
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
Thanks/Gopu
-
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.
Thanks/Gopu
-
please post the outout of
conn test2/test2
select * from session_privs;
Thanks,
-
Hi Thomas,
Please find the Output..
SQL> conn test2/test2
Connected.
SQL> select * from session_privs;
PRIVILEGE
----------------------------------------
CREATE SESSION
CREATE TABLE
SQL>
thanks
Gopu
Thanks/Gopu
-
Originally Posted by gopu_g
SQL> conn test2/test2
Connected.
SQL> select * from session_privs;
PRIVILEGE
----------------------------------------
CREATE SESSION
CREATE TABLE
SQL>
Well... needed privs are not there
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
Originally Posted by PAVB
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.
Thanks/Gopu
-
Thanks. What is your Oracle version and OS?
BTW, I can't find this bug in Metalink...
Thanks,
Last edited by Thomasps; 07-12-2011 at 05:20 AM.
-
Originally Posted by Thomasps
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 :(
Thanks/Gopu
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|