This discussion is archived
8 Replies Latest reply: Nov 14, 2012 1:46 AM by 946279 RSS

explain plan without permissions?

946279 Newbie
Currently Being Moderated
we have some rules on production. one of them is that we can query the database as a developers but we can not change it. there are other logins (application) that can change data. since we can't do any DML on production we also can't see explain plan for a DML statements that changes data. every time we have to ask a DBA to send us back an excution plan, which is simply very inconvenient.

QUESTION: is it possible to wrap up "explain plan" statement into some procedure that would accept DML code as a parameter which developer is not able to run/don't have permissions to tun by himself, and would evaluate such explain plan as another, privileged user ("application user") and sends back the result (ie. evaluated execution plan) to the original user who invoked that procedure?

the idea is to have a mechanism among unpliviliged users to see what is really going on with the code which, after all, we modify and correct, but cannot run anyway (it is run under "application user" eventually).

I would appreciate any ideas how to solve this. there is no production copy or something like this where we could do what we would like and play with the code as another user etc.

thank you
  • 1. Re: explain plan without permissions?
    sb92075 Guru
    Currently Being Moderated
    943276 wrote:
    we have some rules on production. one of them is that we can query the database as a developers but we can not change it. there are other logins (application) that can change data. since we can't do any DML on production we also can't see explain plan for a DML statements that changes data. every time we have to ask a DBA to send us back an excution plan, which is simply very inconvenient.

    QUESTION: is it possible to wrap up "explain plan" statement into some procedure that would accept DML code as a parameter which developer is not able to run/don't have permissions to tun by himself, and would evaluate such explain plan as another, privileged user ("application user") and sends back the result (ie. evaluated execution plan) to the original user who invoked that procedure?

    the idea is to have a mechanism among unpliviliged users to see what is really going on with the code which, after all, we modify and correct, but cannot run anyway (it is run under "application user" eventually).

    I would appreciate any ideas how to solve this. there is no production copy or something like this where we could do what we would like and play with the code as another user etc.

    thank you
    1) do DML
    2) generate EXPLAIN PLAN
    3) ROLLBACK;
    4) observe that nothing got changed!
  • 2. Re: explain plan without permissions?
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    943276 wrote:

    QUESTION: is it possible to wrap up "explain plan" statement into some procedure that would accept DML code as a parameter which developer is not able to run/don't have permissions to tun by himself, and would evaluate such explain plan as another, privileged user ("application user") and sends back the result (ie. evaluated execution plan) to the original user who invoked that procedure?
    Yes, possible. Basic example:
    // as schema production or whatever
    SQL> create or replace type TStrings is table of varchar2(4000);
      2  /
    Type created.
    
    SQL> 
    SQL> create or replace function ExplainPlan( sqlStatement varchar2 )
      2          return TStrings pipelined authid definer is
      3          pragma autonomous_transaction;
      4          statementName   varchar2(50)    default 'sql.1';
      5  begin
      6          execute immediate
      7                  'explain plan set statement_id = '''||statementName||''' for '||sqlStatement;
      8          commit;
      9  
     10          for c in (
     11                  select
     12                          plan_table_output as LINE
     13                  from TABLE(DBMS_XPLAN.Display('PLAN_TABLE',statementName,'TYPICAL'))
     14          ) loop
     15                  pipe row( c.line );
     16          end loop;
     17  
     18          return;
     19  end;
     20  /
    
    Function created.
    
    SQL> 
    SQL> grant execute ExplainPlan to some_other_user;
    ..
    As some other user (requiring only a create session privilege):
    SQL> select * from TABLE(SchemaProd.ExplainPlan('select count(*) from emp'));
    
    COLUMN_VALUE
    --------------------------------------------------------------------------------
    Plan hash value: 2937609675
    
    -------------------------------------------------------------------
    | Id  | Operation        | Name   | Rows  | Cost (%CPU)| Time     |
    -------------------------------------------------------------------
    |   0 | SELECT STATEMENT |        |     1 |     1   (0)| 00:00:01 |
    |   1 |  SORT AGGREGATE  |        |     1 |            |          |
    |   2 |   INDEX FULL SCAN| PK_EMP |    14 |     1   (0)| 00:00:01 |
    -------------------------------------------------------------------
    
    9 rows selected.
    
    SQL> 
    Word of warning though. This uses dynamic SQL. This is dangerous.

    It creates a very weak spot in the production schema - as a non-privilege user is allowed to pass dynamic SQL to a production schema that is, in the above example, blindly executed.

    If you want to pursue this basic approach, then you will need to do some serious hardening to the ExplainPlan() function in order to ensure that SQL cannot be injected and cause production security and data to be compromised.
  • 3. Re: explain plan without permissions?
    946279 Newbie
    Currently Being Moderated
    thanks Billy.

    could you give some example for this vulnerability you mention? I need to talk to DBAs and don't quite understand the danger behind the code presented. I see explain plan executed immediate with "glued" part for actual statement. what statement has it to be to be considered dangerous?

    thank you
  • 4. Re: explain plan without permissions?
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    Dynamic code always raise the specter of code injection - and there are some very ingenious methods like using the seemingly benign NLS_DATE_FORMAT as discussed by by Tom.

    So when you implement dynamic code you need to determine the risk - and manage that.

    The only secure system is in a vault at the bottom of the Marianas Trench - and that would be of use to no-one. So you need to expose applications, systems, interfaces, networks and so on, and evaluate and manage the risk of doing that.
  • 5. Re: explain plan without permissions?
    rp0428 Guru
    Currently Being Moderated
    >
    could you give some example for this vulnerability you mention? I need to talk to DBAs and don't quite understand the danger behind the code presented. I see explain plan executed immediate with "glued" part for actual statement. what statement has it to be to be considered dangerous?
    >
    Billy wasn't necessarily talking about the exact code sample he provided he was trying to warn you that when not written properly dynamic sql can be misused to give people access to data they should not have access to and give them the ability to alter data they should not be allowed to alter.

    Even in his simple example Billy used techniques to minimize the chance of that happening.
      6          execute immediate
      7                  'explain plan set statement_id = '''||statementName||''' for '||sqlStatement;
      8          commit;
      9  
     10          for c in (
     11                  select
     12                          plan_table_output as LINE
     13                  from TABLE(DBMS_XPLAN.Display('PLAN_TABLE',statementName,'TYPICAL'))
    Note that although the user can pass in any arbitrary sql statement the only thing the procedure will do is an 'explain plan' for that statement. And the only output of the procedure is data from the plan table. This limits the exposure to information that might appear in the plan table. That procedure cannot access any data from any other table at all so the risk of injection is pretty minimal.

    But it would be easy for one of your developer's to make seemingly innocent modifications to that procedure that would open up big gaping holes in the security. I believe that is what Billy was warning you about. You need to learn the difference between the two.

    Search the AskTom site for 'sql injection' and you will find plenty of good examples of what NOT to do.
  • 6. Re: explain plan without permissions?
    Nikolay Savvinov Guru
    Currently Being Moderated
    Hi,

    while Billy has provided you with a perfectly good solution for your problem, I would like to add a few points.

    Explain plans are overrated. Countrary to popular belief, they are useless for troubleshooting real performance issues. They may be useful for testing (for example, when you create a new index and want to quickly find whether you query would pick it), but on production, you need something else. You need the actual execution plan for the statement that causes a performance issue. Explain plan cannot give you that, but you can find it in V$ or DBA_HIST% views (e.g. you can use dbms_xplan.display_cursor or dbms_xplan.display_awr). You don't need DML access to do that.

    Best regards,
    Nikolay
  • 7. Re: explain plan without permissions?
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    Excellent points.
  • 8. Re: explain plan without permissions?
    946279 Newbie
    Currently Being Moderated
    thanks for all the answers!

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points