Gather_schema_statistics for other schema
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: Gather_schema_statistics for other schema

  1. #1
    Join Date
    Aug 2007
    Location
    Cyberjaya,kuala lumpur
    Posts
    340

    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

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    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.

  3. #3
    Join Date
    Aug 2007
    Location
    Cyberjaya,kuala lumpur
    Posts
    340
    Quote Originally Posted by PAVB View Post
    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

  4. #4
    Join Date
    Aug 2007
    Location
    Cyberjaya,kuala lumpur
    Posts
    340
    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

  5. #5
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727
    please post the outout of

    conn test2/test2
    select * from session_privs;

    Thanks,
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

  6. #6
    Join Date
    Aug 2007
    Location
    Cyberjaya,kuala lumpur
    Posts
    340
    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

  7. #7
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by gopu_g View Post
    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.

  8. #8
    Join Date
    Aug 2007
    Location
    Cyberjaya,kuala lumpur
    Posts
    340
    Quote Originally Posted by PAVB View Post
    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

  9. #9
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727
    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 06:20 AM.
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

  10. #10
    Join Date
    Aug 2007
    Location
    Cyberjaya,kuala lumpur
    Posts
    340
    Quote Originally Posted by Thomasps View Post
    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
  •  


Click Here to Expand Forum to Full Width