Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2009
    Posts
    8

    Unanswered: dbms_stats question

    Hi all,

    I have one question about the necessary grants to one user to be able to execute the dbms_stats.gather_schema_stats about other one. The situation is this:

    • I have one user “A” who is the owner of the schema which contents the tables, indexes and all data of the application.

    • I have another user “B”. This one only has select permissions over the “A” schema.

    My doubt is how I can execute the dbms_stats.gather_schema_stats from “B” user? Which grants are necessary to be able to do this’?

    Best regards,
    Jordi Ullate

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Why not run it as the A user?
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Feb 2009
    Posts
    8
    Yes, you're right but, this is the easiest way and, off course, the customer don't accept it. He has a very strict rules.

    The "A" user is the owner of the structure and the user which can modify it, but the user "B" is the application user having granted the select right over all "A" schema, but it can't modify it.

    Another example is: the customer don't accept bring a grant analyze any to "B", because in this instance he has more schemas.

    This is the reason, is not my decision!

  4. #4
    Join Date
    Sep 2002
    Location
    Ohio
    Posts
    204
    Can you write a stored procedure as user "A" that runs dbms_stats.gather_schema_stats and then give user "B" execute rights on the procedure?

    HTH,
    Patrick

  5. #5
    Join Date
    Feb 2009
    Posts
    8
    Hi Parick,

    We know this possibility of create a procedure in "A" and grant privileges to "B" but we're looking for another possibility. The reason is that in this instance, exist more than 200 schemas with the same problem between "A" and "B". For each application, our customer have two instances with this two different roles that I explain before. If I will apply the procedure in "A" instance, I have to do it never to 90 times (for each application).

    We're looking for another way, more easy to maintain, like create a public procedure were I can pass the schema "A" (to do the dbms_stats) like a parameter and can be called for each "B" user. I don't know if it can be possible, or I'm just dreaming on...

    Thanks all for your comments!

    Regards,
    Jordi Ullate

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    What happens if you, GRANT ANALYZE ANY TO USER_B
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Feb 2009
    Posts
    8
    I can't do this, because at this instance, exists more schemas, and "B" user only have to be able to do the statistics of the "A" schema.

  8. #8
    Join Date
    Sep 2002
    Location
    Ohio
    Posts
    204
    Based on the list of things you "can't" do, I'd like to add one item. You "can't" do what you are asking. I know of no privilege that grants a user "B" to analyze user "A" objects only, and no other user's objects.

    I still think the only(?) way is to create a stored proc as "A" and grant execute rights to "B". You can script this out and replace "A" and "B" with the values for the other 90 schema combinations. Otherwise, you have to grant the "ANALYZE ANY" privilege and promise to only analyze the schemas you are supposed to.

  9. #9
    Join Date
    Jan 2009
    Location
    Dhaka, Bangladesh
    Posts
    51
    hi,
    as far as i know, you can not grant ANALYZE privilege to one specific schema .
    Mohammad Hasan Shaharear
    E-mail
    Blog: http://shaharear.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •