This discussion is archived
1 2 Previous Next 21 Replies Latest reply: Oct 15, 2012 1:05 PM by Mark Malakanov (user11181920) RSS

need answer for below question

945922 Newbie
Currently Being Moderated
Hi I have data base and two schemas A and B
A schema have procedure proc1 contain logic to update table emp

and B schema have emp table.

Schema A given permision to Schema B for execute proc1 in Schema B

after execution porc1 in schema B which schema emp table get updates. and why?
  • 1. Re: need answer for below question
    ranit B Expert
    Currently Being Moderated
    >
    B schema have emp table.
    >
    I guess only schema- B has got "Emp" table.

    But i didn't get your last question clearly.
    >
    which schema emp table get updates.
    >

    Please clarify.
    Ranit B.
  • 2. Re: need answer for below question
    yoonas Expert
    Currently Being Moderated
    Hi,

    Hope this will answer your query

    http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/subprograms.htm#i18574

    Regards
    Yoonas
  • 3. Re: need answer for below question
    945922 Newbie
    Currently Being Moderated
    procedure Proc1 contain loginc of update table emp. proc1 owner is schema A

    emp table present in both schemas A and B

    Schema A had given execute permison to Schema B for proc1

    If schema B execute proc1 .. which schema emp table get updates..
  • 4. Re: need answer for below question
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    First you have to check your procedure if it already determine schema name inside if not it will be locally .

    for example
    in your procedure :
    update b.emp set ..... --it will be on schema B
    update A.emp set ..... --it will be on schema A
    if nothing has been determine it will be locally on schema B
  • 5. Re: need answer for below question
    ranit B Expert
    Currently Being Moderated
    There shouldn't be any confusion if you have used Schema_A.emp in the update statement of Proc-1.

    It'll update table 'emp' table of schema A.
    Else table of other schema B.
  • 6. Re: need answer for below question
    945922 Newbie
    Currently Being Moderated
    Procedure logic doesnt contain any schema name like schema.table( A. emp)..

    if schema B execute then emp table of schema B updates? are you sure ?
  • 7. Re: need answer for below question
    945922 Newbie
    Currently Being Moderated
    let say schema B does nt contain emp table .

    and proc1 does nt logic update emp table like schemaA.emp

    like

    proc1
    begin
    update emp set...

    end;

    now Schema B executing proc1 .. what happens is it show error or emp of schema A is updates?
  • 8. Re: need answer for below question
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    yes
  • 9. Re: need answer for below question
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    942919 wrote:
    let say schema B does nt contain emp table .

    and proc1 does nt logic update emp table like schemaA.emp

    like

    proc1
    begin
    update emp set...

    end;

    now Schema B executing proc1 .. what happens is it show error or emp of schema A is updates?
    it  will generate error , you should create synonym which related to schema A so update will be on A
  • 10. Re: need answer for below question
    yoonas Expert
    Currently Being Moderated
    Hi,

    It will update emp of A .

    see the link below , i have posted this before

    http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/subprograms.htm#i18574

    Regards
    Yoonas
  • 11. Re: need answer for below question
    ranit B Expert
    Currently Being Moderated
    942919 wrote:
    let say schema B does nt contain emp table .

    and proc1 does nt logic update emp table like schemaA.emp

    like

    proc1
    begin
    update emp set...

    end;

    now Schema B executing proc1 .. what happens is it show error or emp of schema A is updates?
    Yes. Of course it'll throw error.

    Suppose, it doesn't throw this error, some other schema(say Schema 'VIRUS') can easily modify this table and corrupt the data, right?
    So, as a best practice always use SCHEMA.Object_name to avoid any sort of confusions and get secured.

    HTH
    Ranit B.
  • 12. Re: need answer for below question
    EdStevens Guru
    Currently Being Moderated
    942919 wrote:
    Hi I have data base and two schemas A and B
    A schema have procedure proc1 contain logic to update table emp

    and B schema have emp table.

    Schema A given permision to Schema B for execute proc1 in Schema B

    after execution porc1 in schema B which schema emp table get updates. and why?
    In less time than this thread has been running, you could have empirically discovered the answer for you self by setting up a very simple test case ....
  • 13. Re: need answer for below question
    rp0428 Guru
    Currently Being Moderated
    >
    Hi I have data base and two schemas A and B
    A schema have procedure proc1 contain logic to update table emp

    and B schema have emp table.

    Schema A given permision to Schema B for execute proc1 in Schema B

    after execution porc1 in schema B which schema emp table get updates. and why?
    >
    Tell us what answer you gave when you were asked this question. This seems to be either an interview question or a test question from a class.

    Whatever the source of the question your answer should have been that there is not enough information to know for sure what the result will be or why. Missing information that is needed includes

    1. HOW is user B trying to execute the proc in schema A? Directly or within a procedure?
    2. HOW was user B given permission to execute the proc in schema A? Directly or thru a role?

    Grants have to be given directly if B is trying to execute the proc in schema A within a proc of schema B.

    3. HOW was the proc in schema A defined? Using INVOKER rights or DEFINER rights?

    4. Was user B given any privileges to the EMP table in schema A? If so was the privilege granted directly or through a role?

    Without the above information there is no way to know for sure if an exception will be raised or, if no exception is raised, which table will be accessed.
  • 14. Re: need answer for below question
    Mark Malakanov (user11181920) Expert
    Currently Being Moderated
    A.EMP will be updated, it does not matter which user calls it and if it has or does not have its EMP table.
    When A.PROC1 is created it is compiled to update A.EMP.
1 2 Previous Next

Legend

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